performance - Table Locking in PostgreSQL -

i have pl/pgsql function takes data staging table our target table. process executes every night. due server restart or maintenance issues process executed manually.

the problem facing: whenever start process manually after 7 am, takes 2 hours complete (read staging table , insert target table). whenever executes per schedule, i.e., before 7 am, takes 22-25 minutes on average.

what issue? if required, can share function snippet here.

the typical reason general concurrent activity in database, competes same resources function , may cause lock contention. check db log activities starting around 7 a.m.

the postgres wiki on lock monitoring

a function runs single transaction. locks acquired along way , released @ end of transaction. makes long running functions particularly vulnerable lock contention.

you may able optimize general performance behavior towards concurrent transactions make run faster. or more radically: if @ possible, split big function in separate parts, call in separate transactions.

• postgresql obtain , release lock inside stored function

how split huge updates:

• how do large non-blocking updates in postgresql?

there additional things consider when packing multiple big operations single function:

• execute multiple functions without losing performance