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 : [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 : [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 : [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 : [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 : [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?