Simulating a Postgres hot-row convoy and MultiXact collapse

A model of how a background task pipeline can take down a Postgres database while DB CPU stays low the whole time. The widgets below build the failure from its parts so you can change the inputs and watch it happen.

The chain, in one line: many transactions on one hot row → foreign key locks turn the parent row's lock into a big MultiXact → a long-running transaction keeps old MultiXacts in play → MultiXact lookups miss a tiny cache and queue on one LWLock → everything holds its connection longer → connections run out.

What's a MultiXact, and why does it grow so fast?

• A Postgres row header has one xmax slot for the transaction that locked or deleted it.
• INSERT into a child table checks the foreign key with SELECT ... FOR KEY SHARE on the parent row. So every insert puts a shared lock on its parent until commit.
• When two or more transactions hold a lock on the same row, Postgres writes a MultiXact: a list of member transaction IDs stored in pg_multixact/, with its ID in xmax.
• A MultiXact can't be changed after it's written. Adding a locker means writing a new one that copies all the current members and adds the new one.

• N concurrent lockers write about N²/2 members. Doubling the lockers roughly quadruples the member writes.
• Reading a row whose xmax is a MultiXact means looking up its members to see if any are still running. That includes a plain SELECT doing a visibility check.
• Members are read through an SLRU cache in shared memory. On PG15 it's a fixed 16 pages (about 26k members). PG17 makes the size configurable (multixact_member_buffers).
• Postgres can skip the lookup for a MultiXact older than every running transaction. One long-running transaction turns that shortcut off for everything created after it started.

The simulation

Two task queues dispatch background work. Most tasks update the same hot row, a per-day counter. Each of those tasks also inserts a child row that references a parent row, and an API reads that parent row on every request. Everything shares one connection budget.

How the model works

• Tasks. Each queue slot takes a task and a connection. 75% of tasks (adjustable) are for the hot row. The rest touch other rows for 40 ms and leave.
• Hot task. It inserts a child row first, which takes KEY SHARE on the parent row and joins its MultiXact (a new MultiXact of size N+1). Then it queues for the hot row lock, does 2 ms of work plus 3 MultiXact lookups while holding it, and commits.
• MultiXact working set. The members written since the oldest running transaction started. A lookup misses the cache with probability 1 − cache / working set.
• SLRU LWLock. One FIFO server. A hit costs 0.01 ms and a miss 1 ms. Each waiter adds 0.4% overhead, standing in for LWLock wakeup and retry costs.
• API. Poisson arrivals. Each request takes a connection and does 4 MultiXact lookups (the tuple versions it checks on the parent row). Clients time out after 10 s and retry twice, and the server keeps running the abandoned query and holding its connection.
• lock_timeout aborts a transaction that waits too long for the hot row lock, and the task retries after 1 s. It doesn't cover LWLock waits, just like the real setting.
• Long transaction. A session with an old snapshot. It holds the horizon still, so the working set only grows.

Things to try

1. One queue at 100. Start from One queue.

• About 95 transactions are queued on the hot row at any time, and throughput is about 490 commits/s.
• The working set is about 9k members, well under the 26k cache. It's a convoy, but a stable one.

2. Add a second queue. Pick Two queues, or move Queue B to 100.

• Throughput drops to about 260/s. Doubling the concurrency made the system slower.
• The working set grows to about 40k, past the cache. About a third of lookups miss.
• Why: the working set is roughly M², where M is the number of queued lockers. Queue wait is M ÷ throughput, and MultiXacts get written at throughput × M per second, so the throughput cancels out. With a 26k cache, the tipping point is about √26k ≈ 160 lockers.

3. Drop to 10 concurrent. Move Queue A to 10 and Queue B to 0.

• Throughput stays close to 490/s. The hot row is the bottleneck, so extra concurrency only adds waiters.

4. Open a long transaction. Pick Two queues + long txn, or press Open long txn.

• The working set climbs without limit. Misses go to about 90%, and the LWLock queue jumps from about 10 to hundreds within 20 s.
• Connections fill up, and API p95 hits the 10 s client timeout. Retries push API attempts to about 3× normal.
• DB CPU falls to about 0%. The database is waiting, not working.

5. End the long transaction. Press End long txn once it has collapsed.

• The working set drops back, but the system stays down. The LWLock queue and connections stay full, because abandoned API queries and retries keep refilling them. This is a metastable failure: the trigger is gone, but the load it caused keeps it down.
• Press Pause queues, then Restart DB. It recovers within seconds.

6. Try the fixes on the two-queue setup.

• lock_timeout 100 ms: M stays around 50, the working set drops to about 4k, and throughput goes back to about 490/s with 200 slots. Now open a long transaction: it still collapses. lock_timeout limits heavyweight lock waits, not LWLock waits.
• 1024 member buffers (PG17): the long transaction takes about 20 s longer to cause trouble, then it degrades anyway. More cache buys time.
• Fewer hot tasks: move Tasks hitting the hot row to 10% (for example, skipping writes for events that change nothing). The hot row still runs at full speed, but only about 20 lockers queue on it, and the working set drops under 1k.
• None of these survive a long transaction for long, because the working set keeps growing for as long as it stays open. What actually works is ending it early: idle_in_transaction_session_timeout and statement_timeout, plus an alert on the age of the oldest transaction.

Signs of this in a real system

• DB CPU is low while latency climbs and write throughput (commits/s, XIDs per minute) falls.
• pg_stat_activity.wait_event is dominated by LWLock: MultiXactMemberSLRU / MultiXactMemberBuffer / MultiXactOffsetSLRU, with Lock: transactionid and Lock: tuple next.
• log_lock_waits shows still waiting for ShareLock on transaction N, and the CONTEXT lines point at the same one or two tuples.
• pg_stat_slru shows blks_read climbing for the MultiXact caches.
• now() - xact_start for the oldest transaction is minutes or hours.
• Primary-key lookups on the parent table take seconds.
• Connections climb until every service sits at its cap, while the task backlog grows.
• pg_terminate_backend doesn't end sessions stuck in these LWLock waits. A restart does.

What the model leaves out

• The numbers are picked to show the shape of the failure, not measured from a real database. In a real database the collapse can take tens of minutes, not seconds.
• A cache miss here is a coin flip based on working set vs cache size. Real SLRU behaviour depends on which pages each lookup touches, on dirty page writeback, and on MultiXact offsets as well as members.
• There's one hot row and one parent row. Real systems have several parents per insert (user, team, account), each with its own MultiXact.
• Transaction and connection handling is simplified: no pool queues, no per-instance connection caps, no autovacuum.