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?

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s