PostgreSQL Topic Archive
Lock waits, deadlocks, blocking chains, idle transactions, and transaction hygiene.
Concurrent FOR SHARE locks and foreign key checks consume MultiXact IDs, and multixact has its own wraparound emergency separate from XID wraparound. How to monitor mxid_age(datminmxid), SLRU pressure, and vacuum's freezing before it stops your database.
At peak our p99 commit latency jumped from 4 ms to 2.8 seconds while CPU pinned at 100% and the disks sat idle. pg_stat_activity showed 1,400 sessions queued on wait_event='WALInsertLock' — this is how we found it and what actually fixed it.
Concurrent FOR SHARE locks and foreign key checks consume MultiXact IDs, and multixact has its own wraparound emergency separate from XID wraparound. How to monitor mxid_age(datminmxid), SLRU pressure, and vacuum's freezing before it stops your database.
One runaway query or one forgotten BEGIN can take a production PostgreSQL database down. Three timeout settings are the difference between an incident and a non-event.
MySQL made big-table DDL survivable with online DDL, gh-ost, and pt-osc. PostgreSQL DDL is often instant but brutally lock-sensitive. Here is how to translate your schema-change habits.
Long-running transactions hurt both engines, but differently: InnoDB accumulates undo history and purge lag, PostgreSQL accumulates dead tuples and marches toward xid wraparound. Know what to watch.
InnoDB and PostgreSQL both detect and break deadlocks, but they detect them differently, report them differently, and reward different diagnosis habits. A field guide for migrating teams.
Both MySQL 8.0 and PostgreSQL support SELECT ... FOR UPDATE SKIP LOCKED, which makes database-backed job queues practical. The claim patterns, locking details, and operational costs differ.
InnoDB prevents phantoms by locking gaps; PostgreSQL prevents them with snapshots and serialization checks. Migrating between the two means retraining your instincts about blocking, deadlocks, and retries.
A single connection sitting in 'idle in transaction' can hold locks, pin the vacuum horizon, and quietly bloat every table in the database. I learned this the hard way from a debugger someone left paused overnight.
Everyone learns about transaction ID wraparound eventually. Then one day the log says 'MultiXactId members limit exceeded' and you discover PostgreSQL has a second counter that can run out — and it's usually your foreign keys filling it.
Every query takes lightweight relation locks you never think about. Cross the fast-path limit of 16 per backend, usually via heavy partitioning, and the shared lock manager turns into a bottleneck under concurrency.
Subtransactions are cheap until you cross 64 of them in one transaction or pile up enough of them under load. Then SubtransSLRU contention shows up as global slowness nobody can explain.
One ALTER TABLE froze the whole app for ninety seconds, and it wasn't even slow. It was stuck behind a long query, holding a lock everyone else then queued behind. Here's how I stopped doing that.
Long transactions quietly hold back vacuum, keep locks alive, exhaust pools, and make old row versions stick around long after users moved on.
Deadlocks are the symptom of a design that allows two transactions to hold each other's locks. The fix is rarely retry logic. Here is what to look for instead.
ALTER TABLE is dangerous when you review syntax instead of locks. Some changes are instant, some queue behind writes, and some rewrite the whole table.
Lock incidents feel mysterious because the database looks idle while requests wait. The fix starts with blockers, waiters, transaction age, and code paths that take locks in different orders.
Using a Postgres table as a job queue used to be a recipe for contention. SKIP LOCKED makes it tractable. Here is the pattern that actually scales.
Advisory locks are useful when the database cannot infer the thing you are protecting. They are also dangerous when session scope, pooling, and lock ordering are treated casually.