Performance Locks DML

Track Two-Phase Commit State with pg_prepared_xacts A prepared transaction that never gets committed or rolled back is one of the quieter ways a PostgreSQL database degrades. It keeps holding locks, it pins the transaction-ID horizon so VACUUM cannot reclaim dead rows, and nothing in normal monitoring screams about it. …

PostgreSQL Advisory Locks with pg_advisory_lock Two scheduled jobs fire at the same minute and both try to process the same nightly batch. Nothing in the data stops them — there is no single row to lock, because the work has not produced rows yet. Row and table locks are the wrong tool here. What you want is a named …