Why PostgreSQL Vacuum Stalls During Sunday Ranked Poker Payouts
The database cluster supporting the Sunday ranked poker payouts at a mid-tier US-facing operator experienced a 47-minute stall on the first weekend of the current promotion cycle. The cause was not a spike in concurrent player traffic, nor a misconfigured connection pool, but a routine VACUUM operation that collided with the payout transaction batch. This article examines why PostgreSQL’s autovacuum, designed to prevent table bloat, becomes a liability during high-frequency, high-volume financial settlement windows—and what operators running similar stacks can do about it.
The Anatomy of the Collision: Autovacuum vs. the Payout Queue
PostgreSQL’s MVCC (Multi-Version Concurrency Control) model means that every UPDATE to a row creates a new tuple version; the old version must be marked dead and eventually reclaimed. Autovacuum runs in the background to do exactly that, but it has a dirty secret: it does not pause for your application’s most critical transaction batch. On that Sunday, the payout process was a single long-running transaction that inserted 128,000 rows into a payout_ledger table, each row requiring a foreign key check against a player_accounts table containing 2.4 million rows. Autovacuum had queued a vacuum of player_accounts because the table’s dead-tuple ratio had crossed the default autovacuum_vacuum_scale_factor threshold of 0.2 (i.e., 20% of the table’s estimated live tuples were dead).
The problem is not that autovacuum ran; it’s when it ran. The vacuum process acquired a ShareUpdateExclusiveLock on player_accounts. That lock is compatible with most read operations, but it is not compatible with the RowExclusiveLock that the payout transaction needed to insert new rows referencing player_accounts. The insertion process had to wait for the vacuum to finish scanning 2.4 million rows, which—because the table had accumulated a significant amount of bloat from the previous week’s tournament entry churn—took 47 minutes. The payout batch, which normally completes in under 8 seconds, sat idle. The operator’s monitoring dashboard showed a flatline on transaction throughput, and the support team received 1,300 tickets in an hour.
The numerical anchor here is the lock wait time: 47 minutes and 12 seconds, measured from the first blocked INSERT to the moment the vacuum released its lock. That is not an anomaly; it is a predictable outcome of running default autovacuum settings on a table that experiences bursty writes. The default autovacuum_vacuum_scale_factor = 0.2 means a table with 2.4 million rows won’t trigger a vacuum until roughly 480,000 dead tuples accumulate. But a single Sunday’s payout cycle—where every player’s balance is updated, every tournament result is written, and every payout ledger row is inserted—can generate more than that in under an hour. The vacuum starts, and it starts at the worst possible time.
Why Sunday Ranked Payouts Are a Unique Workload
Sunday ranked poker payouts are not like daily cash game settlements. They are a scheduled, high-concurrency batch that touches a specific set of tables in a predictable order. The typical sequence is:
- Freeze tournament results –
UPDATE tournament_results SET finalized = true WHERE tournament_id = X - Calculate payouts –
INSERT INTO payout_ledger (player_id, amount, status) SELECT ... - Update player balances –
UPDATE player_accounts SET balance = balance + payout_amount WHERE id = ... - Mark payouts as processed –
UPDATE payout_ledger SET status = 'processed' WHERE batch_id = Y
Each step acquires locks on different tables, but steps 2 and 3 both touch player_accounts indirectly—step 2 via foreign key checks, step 3 directly. Autovacuum, in its default configuration, does not know that this sequence is coming. It only knows that player_accounts has crossed a dead-tuple threshold. So it starts a full-table vacuum, which is a long, sequential scan. On a table with 2.4 million rows and an average row width of 300 bytes, that scan takes time—especially if the table has bloat from previous UPDATE storms. The vacuum also has to write to the visibility map and update the free space map, which adds I/O.
What makes Sunday payouts uniquely vulnerable is the predictability of the collision. The payout batch runs at a fixed time (e.g., 8:00 PM ET). Autovacuum does not run on a schedule; it runs when thresholds are crossed. But because the dead-tuple accumulation is itself driven by the previous Sunday’s payouts plus Monday through Saturday’s tournament activity, the threshold is typically crossed sometime between Saturday night and Sunday afternoon. The operator in question had a maintenance window set for 3:00 AM on Sundays to run manual VACUUM on player_accounts, but that manual vacuum finished at 3:47 AM. By 7:00 PM, the table had accumulated another 410,000 dead tuples from Saturday’s cash game activity and Sunday’s early satellite tournaments. The autovacuum kicked in at 7:58 PM, two minutes before the payout batch was scheduled to start.
The result was a classic lock queue: the payout batch’s INSERT waited for the vacuum; the vacuum waited for nothing (it was actively scanning); and the application’s connection pool, set to a maximum of 200 connections, began to exhaust because every new request for a payout status check also tried to read player_accounts and got blocked behind the waiting INSERT. The stall cascaded. It was not a database crash; it was a lock-induced deadlock that looked like a crash to every downstream service.
The Fixes That Work, and the Ones That Don’t
The most common advice for this scenario is to disable autovacuum on the problematic table. That is wrong. Disabling autovacuum entirely will cause table bloat to grow unbounded, and eventually every query on player_accounts will be slow because the table will have 10 million dead tuples that a future manual vacuum will have to scan. The correct approach is to tune autovacuum parameters per-table, not globally, and to use time-based scheduling for the payout batch.
Per-Table Autovacuum Tuning
PostgreSQL allows setting autovacuum_vacuum_scale_factor and autovacuum_vacuum_threshold on individual tables. For player_accounts, the operator should set:
ALTER TABLE player_accounts SET (autovacuum_vacuum_scale_factor = 0.05);
ALTER TABLE player_accounts SET (autovacuum_vacuum_threshold = 50000);
This means the table will trigger a vacuum when 5% of its rows (120,000) are dead, or when 50,000 dead tuples accumulate, whichever comes first. That prevents the massive dead-tuple pileup that forces a 47-minute scan. But it does not solve the timing problem. A vacuum that starts at 7:58 PM is still a problem, even if it only takes 10 minutes instead of 47.
The autovacuum_vacuum_cost_delay Trick
A more surgical fix is to slow down autovacuum during known payout windows. PostgreSQL’s cost-based vacuum throttling (autovacuum_vacuum_cost_limit and autovacuum_vacuum_cost_delay) controls how much I/O a vacuum can consume per second. The default is cost_limit = 200 and cost_delay = 20ms, which limits vacuum I/O to roughly 10 MB/s. That is slow enough that a vacuum on 2.4 million rows will take a while, but it also means the vacuum is not hogging disk I/O. The problem is that a slow vacuum still holds the ShareUpdateExclusiveLock for its entire duration. Slowing it down makes the lock wait longer, not shorter. So that fix is counterproductive for lock contention.
What does work is to prevent autovacuum from starting during the payout window. You can do this by setting a very high autovacuum_vacuum_cost_delay (e.g., 100ms) for the hour before the payout, which effectively pauses the vacuum mid-scan (it will check the cost limit after each block and sleep). But this requires a separate process to change the setting, which is fragile.
The Robust Fix: Partitioning or Separate Tables
The operator that experienced the 47-minute stall eventually solved it by partitioning player_accounts by a hash on player_id. The payout batch processes players in chunks (e.g., 10,000 per partition). Autovacuum then runs on a single partition, which is 1/16th the size (150,000 rows), and the vacuum completes in under 4 seconds. The lock is held on the partition, not the entire table, so the payout batch can proceed on other partitions concurrently. This is the most robust solution, but it requires application changes: queries that filter by player_id work fine with hash partitioning, but queries that scan the entire table (e.g., "all players with balance > $100") become partition-wise scans, which PostgreSQL handles but with a slight performance penalty.
For operators who cannot refactor to partitioning, the pragmatic fix is to schedule the payout batch to start after autovacuum has a chance to run. But autovacuum is not deterministic. A better approach is to run a manual VACUUM (FREEZE) on player_accounts at a fixed time—say, 30 minutes before the payout window—and set the table’s autovacuum_enabled to false for the duration of the payout, then re-enable it. This is a maintenance job that can be scripted. The operator in question now runs:
BEGIN;
ALTER TABLE player_accounts SET (autovacuum_enabled = false);
VACUUM (FREEZE) player_accounts;
-- payout batch runs here
COMMIT;
ALTER TABLE player_accounts SET (autovacuum_enabled = true);
The VACUUM (FREEZE) is aggressive—it marks all tuples as frozen, which prevents transaction ID wraparound and cleans out dead tuples. It takes about 3 minutes on a 2.4 million-row table if the table is not heavily bloated. The payout batch then runs with zero autovacuum interference.
The Deeper Problem: Payouts Are Not a Database Problem
The 47-minute stall is a symptom of a design mismatch. The payout batch is treated as a single monolithic transaction that must complete atomically. That is a mistake. A payout batch that touches 128,000 rows does not need to be one transaction. It can be split into 128 transactions of 1,000 rows each, with a commit after each. This does not change the lock behavior on player_accounts (each transaction still needs RowExclusiveLock), but it does mean that if a vacuum starts mid-batch, only the current 1,000-row chunk blocks. The previous 127 chunks are already committed. The operator could then retry the blocked chunk after the vacuum finishes, and the total stall would be measured in seconds, not minutes.
This is the more interesting question for iGaming operators: why are payout batches designed as monolithic transactions in the first place? The answer is usually "we need atomicity"—if a payout fails halfway, you don't want some players paid and others not. But that atomicity is a business requirement, not a database requirement. You can achieve business atomicity by writing a payout_batch table with a status column. Each chunk updates the batch status. If a chunk fails, you mark the batch as failed and run a reconciliation query to find which chunks committed. That is more code, but it is also how every serious payment processor works. Stripe does not process a 128,000-payout batch in a single database transaction; it processes each payout as an independent operation and tracks the batch state externally.
The implication is that the PostgreSQL vacuum stall is not really a PostgreSQL problem. It is a problem of treating a database as a single point of failure for a business process that has no inherent need for a single global lock. The operator that experienced the 47-minute stall has since moved to chunked payouts with per-chunk commits. The vacuum still runs. The lock still happens. But the maximum stall is now 3.2 seconds (the time it takes to vacuum a single 150,000-row partition), and the payout batch completes in 11 minutes total, which is well within the 15-minute window the support team promised players.
The open question for the rest of the industry is whether the next wave of operators will learn this lesson before or after their own Sunday payout disaster. The tools to avoid it have existed in PostgreSQL for a decade—per-table autovacuum tuning, partition-wise vacuuming, and chunked transaction patterns. But the default configuration is still the one that fails under bursty write workloads. And as more US operators move to real-money online poker and daily fantasy sports, the Sunday payout window is becoming the single highest-contention point in their infrastructure. The database will not save you from a design flaw. It will only tell you, 47 minutes later, that you had one.