Re: Exclusive Locks on Insert into large Logging tables

Peter Neave wrote:
> I’ve been trying to get down to the source of some locks. I’ve enabled log_loc_waits
> in the logs and I’ve had a script monitor the lock file and run a query to a file
> when it detects locks. From there, I’ve been able to determine that intermittently
> the largest tables in my database are have Exclusive Locks on them for up to 1-5
> seconds (For example -
> LOG:  process 116665 acquired ExclusiveLock on extension of relation 59049887 of database 59049867 after 5838.015
ms).
>  
> I’m running PG10. The machine has 16GB of RAM.
>  
> The tables are mainly log tables . The queries that are waiting and those blocking
> are both insert statements.  The tables are 41GB, 34 GB and 33GB and contain many
> years of historical logs.
>  
> My question is, why would locking prevent an insertion. From what I’ve read
> (https://www.citusdata.com/blog/2018/02/15/when-postgresql-blocks/) insert
> statements should not lock another insert.

Exclusive locks conflict with everything but SELECTs, see
https://www.postgresql.org/docs/current/explicit-locking.html#TABLE-LOCK-COMPATIBILITY

This particular exclusive lock is takes when the relation has to be extended
with new blocks because there is no more room for the new row in the
existing blocks of the table.

The table extension lock is queued behind all other INSERTs that have come
earlier, so it takes 5 seconds for those to finish. I can't tell if that is
because there are so many of them queued or because the transactions are kept
open longer that is necessary.

> Would partitioning the table help? Or would having a smaller record set mean fewer locks?

Perhaps, if the INSERTs affect different partitions.

I'd check if the inserting transactions take longer than strictly necessary.
The shorter they are, the shorter the extension has to queue.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com