Unusual high number of exclusive locks in PostgreSQL database

I am pretty new to database design and PostgreSQL in general, but I am aware of the general concept behind row versioning, transactions and exclusive locks in Postgres (e.g. this article gives a pretty good overview).

My current problem is that a) I am not sure why I get so many exclusive locks show up in my PG database log files and b) why these locks are happening at all.

I run PostgreSQL 10 (+ PostGIS extension) with about 300 Million rows over 5 tables (200GB). I have about 5 scripts (4x PHP and 1x Python Psycopg2) running 24/7 that make a lot of inserts (and DO UPDATE with COALESCE, in case the entry already exist). However, as far as I understand, PHP Postgres extension commits automatically after each SQL Query and in my Python Script, increasing commits does not significantly reduce locks. I have a couple of triggers that dynamically update rows, but as far as I can tell from the log files they are not the cause of locks. It should generally be very rare that two or more of my scripts insert/update the same row at the same time.

This is an example log entry:

2018-01-31 01:04:02 CET [808]: [258-1] user=user1,db=maindb,app=[unknown],client=::1 LOG:  process 808 still waiting for ExclusiveLock on page 0 of relation 26889 of database 16387 after 1015.576 ms
2018-01-31 01:04:02 CET [808]: [259-1] user=user1,db=maindb,app=[unknown],client=::1 DETAIL:  Process holding the lock: 680. Wait queue: 1728, 152, 808.
2018-01-31 01:04:02 CET [808]: [260-1] user=user1,db=maindb,app=[unknown],client=::1 STATEMENT:  

                INSERT INTO "table1" (...) 
                VALUES (...)                                                                                                                                                                                              
                ON CONFLICT (...)                                                                                                                                                                                                                                                       
                DO UPDATE SET                                                                                                                                                                                                       

I have similar log entries about every 2-3 Minutes. Are they problematic? What do they mean exactly, are the locks finally resolved or is the data of the transaction lost? There is no log entry that states that locks are resolved or updates are finally committed to the database.

The second type of frequent log entry is similar to this:

2018-01-31 07:22:16 CET [2504]: [16384-1] user=,db=,app=,client= LOG:  checkpoint complete: wrote 9999 buffers (3.8%); 0 WAL file(s) added, 0 removed, 7 recycled; write=269.842 s, sync=0.218 s, total=270.123 s; sync files=85, longest=0.054 s, average=0.002 s; distance=66521 kB, estimate=203482 kB
2018-01-31 07:22:46 CET [2504]: [16385-1] user=,db=,app=,client= LOG:  checkpoint starting: time  

Does this mean Auto-Vaccum or Auto-Commit that resolves all locks?

My general question: should I be concerned and do something or simply leave things as they are?

One thought on “Unusual high number of exclusive locks in PostgreSQL database”

  1. After a while, I found out what is causing these locks and also how to solve them. All Exclusive locks happened on one relation in the database:
    ...ExclusiveLock on page 0 of relation 26889 of database...
    What is 26889?
    SELECT relname FROM pg_class WHERE OID=26889
    Result: idx_post_hashtags
    It was all caused by a GIN index over a particular column with Array (Text). Furthermore, this GIN index was useless, as the array had a variable length and looking up any specific array value did not benefit from the index. I dropped it: all Exclusive Locks gone!
    Reading the logs carefully really helps.

Leave a Reply

Your email address will not be published.