You own a queue-like table in an MVCC database where rows are inserted and then updated to a completed state thousands of times per second, and background cleanup can never keep up. How would you redesign around dead-version accumulation?

## Why queue tables are the worst case for MVCC A queue table concentrates every property that generates garbage: high insert rate, at least one update per row (often several), deletion of everything eventually, and hot access to the newest rows. Each update produces a dead version plus index maintenance; each delete produces more cleanup work. The table's row count may stay flat while it grows all day. On undo-based engines the pain shows as undo growth and long version chains on the hottest rows; on append-style engines as heap and index bloat with scans reading mostly dead tuples. Same cause, different symptom. ## Lever 1 — produce fewer versions - **Count the updates per item.** `pending → claimed → processing → done` is four versions plus a delete. Can it be two? Often a claim and a completion suffice; intermediate states are for observability and belong in a log or metric, not in row updates. - **Kill heartbeat columns.** A `last_seen` updated every second per in-flight item is a version factory. Move liveness into a separate narrow table keyed by worker, or into an out-of-database store. - **Narrow the churned row.** In append-style engines an update rewrites the whole tuple, so a 2 KB payload column is rewritten on every status change. Split into `job(id, status, claimed_at)` and `job_payload(id, body)`: the churned table becomes tiny, and the payload is written once. This one change routinely cuts garbage by an order of magnitude. - **Fewer indexes on the churned table.** Every index needs its entries cleaned. Keep the one the dequeue query needs and drop opportunistic ones. - **Batch.** Acknowledge 500 completions in one statement rather than 500 statements: fewer transactions, less log, and cleanup gets contiguous work. ## Lever 2 — make removal structural The decisive insight is that **deleting rows creates work while dropping a partition destroys it**. Partition by completion time (hourly or daily) or maintain a pending table and an archive table: - pending work lives in a small, hot partition that stays cache-resident; - completed work ages out, and yesterday's partition is dropped — a metadata operation returning space instantly with no version cleanup at all; - scans of pending work never traverse the corpse of last month's traffic. A variant when partitioning is awkward: keep a small `pending` table and move completed rows to `history` in batches. It costs a write but keeps the hot object permanently small — and small objects are cheap to clean, cheap to index and cheap to cache. Be honest about the cost: partitioning adds catalog objects and planning overhead, and the partition key must appear in queries for pruning to work. For a queue table those are easy constraints to satisfy. ## Lever 3 — make cleanup aggressive where it matters Default cleanup thresholds are tuned for ordinary tables — trigger after some percentage of rows change. On a table that turns over entirely every hour that default is far too lax. Per-table overrides are the right tool: trigger on a small absolute number of changed rows, raise the I/O budget so cleanup is not throttled to a trickle, and ensure enough workers exist that this table is never queued behind a big cold table. On undo-based engines the analogue is purge thread count and batch size. Also ensure cleanup is not competing hopelessly: if the table needs continuous cleaning to survive, that is itself a signal the design should change. ## The invariant beneath everything None of it works if the visibility horizon stalls. One idle-in-transaction session or one long report freezes reclamation database-wide, and a high-churn table degrades fastest under that condition — it can double in size in minutes. So enforce idle-in-transaction and statement timeouts, keep the dequeue transaction to claim-and-commit rather than claim-work-commit, and never hold the transaction while the job runs. Alert on oldest-transaction age. ## The strategic question A principal-level answer should also ask whether the database is the right home for this workload at this rate. Relational queues are attractive because they give transactional enqueue with the business write — the outbox pattern — which a separate broker cannot. That is a genuine and often decisive advantage. But at thousands of operations per second the storage engine is doing version management that a purpose-built log or broker simply does not have. A good synthesis: keep a small transactional **outbox** in the database, written in the same transaction as the business change, drained promptly into a broker that handles fan-out, retries and long-lived state. The outbox stays tiny and short-lived, so its churn is trivial; the high-volume queue semantics live where they are cheap. If the queue must stay in the database, then partitioned storage plus narrow rows plus tuned cleanup is the design that survives. ## How to present it Lead with the three levers and their order of leverage, name partition-drop-instead-of-delete as the structural fix, state the transaction-lifetime invariant as non-negotiable, and close with the honest architectural question about whether the queue belongs in the database at that rate.