10 min read
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.
PostgreSQL Topic Archive
Locks and Transactions PostgreSQL Articles
Lock waits, deadlocks, blocking chains, idle transactions, and transaction hygiene.
10 min read
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.
9 min read
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.
10 min read
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.
10 min read
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.
6 min read
Long transactions quietly hold back vacuum, keep locks alive, exhaust pools, and make old row versions stick around long after users moved on.
6 min read
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.
5 min read
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.
13 min read
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.
6 min read
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.
8 min read
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.