Why PostgreSQL autovacuum stalls during weekend slot tournaments
The claim that a weekend slot tournament is a purely front-end phenomenon—a frenzy of flashing reels and celebratory sound effects—is a convenient fiction for database administrators. The real action, the kind that brings systems to their knees, happens in the unglamorous back end of PostgreSQL, where the autovacuum daemon is supposed to be quietly pruning dead tuples. The specific failure mode is this: during a high-volume weekend tournament, the rate of tuple invalidation (UPDATEs and DELETEs) can outpace the autovacuum worker’s ability to process a table, leading to a stall. When that stall happens, the table bloats, query plans degrade from index scans to sequential scans, and the latency spike becomes a player’s worst nightmare—a spinning wheel that never resolves.
The problem isn’t that autovacuum is broken. It’s that the workload profile of a slot tournament is uniquely hostile to the assumptions baked into PostgreSQL’s default configuration. Default settings are calibrated for a steady, predictable trickle of writes. A tournament is a flood, and the flood arrives in a pattern that defeats the daemon’s threshold-based logic.
The Anatomy of a Tournament Write Storm
To understand why autovacuum stalls, you have to look at what a slot tournament actually does to the database. It’s not just a spike in SELECT traffic. Every spin is a transaction. That transaction updates the player’s balance, inserts a spin record, and updates a tournament leaderboard. In a well-designed system, the leaderboard is the killer. Consider a tournament with 10,000 active players. Each player spins at an average rate of 5 spins per minute. That’s 50,000 spin transactions per minute, or roughly 833 transactions per second. Each transaction touches the leaderboard table, either updating a running score or inserting a new row for a player who just crossed a threshold.
The leaderboard table is the classic hot spot. It’s small enough to be cached in memory, which is good for performance, but that’s precisely why autovacuum gets blindsided. The table has a few thousand rows. The default autovacuum_vacuum_scale_factor is 0.2, meaning autovacuum will trigger a vacuum on a table when 20% of its rows are dead. For a 10,000-row leaderboard, that’s 2,000 dead tuples. At 833 transactions per second, with each transaction invalidating at least one row (the player’s previous score), you hit that threshold in about 2.4 seconds. Autovacuum wakes up, launches a worker, and starts cleaning. But the worker is processing a table that is being written to faster than it can be read. The vacuum process scans the table, but by the time it reaches the end, another 2,000 tuples have died. The worker is stuck in a loop, perpetually chasing a moving target.
This is the stall. It’s not a deadlock. It’s a starvation scenario. The autovacuum worker is active, consuming CPU and I/O, but it never reaches a clean state. The table’s relfrozenxid advances, but the bloat grows. The vacuum’s vacuum_cost_limit (default 200) throttles its own I/O, making the situation worse. The daemon is effectively fighting itself.
The 20% Threshold Is a Trap
The default scale factor of 20% is reasonable for a large, static table like a game catalog. For a hot, small table, it’s a disaster. Here’s the numerical anchor that should be on every DBA’s whiteboard: At 833 TPS on a 10,000-row table, the default 0.2 scale factor triggers a vacuum every 2.4 seconds. That’s not a maintenance operation; that’s a constant background load that was never part of the capacity plan.
The fix is counterintuitive: you have to make autovacuum trigger more often, not less. Setting autovacuum_vacuum_scale_factor = 0.01 (or even 0.0 with a low autovacuum_vacuum_threshold) on the leaderboard table forces the daemon to kick in after just 100 dead tuples. That means a vacuum runs every 0.12 seconds. That sounds worse, but it’s actually a relief. The vacuum now has a chance to finish a pass in under a second, because the working set is tiny. It’s a constant, low-level hum instead of a spiking, thrashing battle.
But there’s a second, more insidious problem: the autovacuum worker pool is a shared resource. PostgreSQL defaults to autovacuum_max_workers = 3. During a tournament, every table that gets write traffic—the spin history, the player sessions, the audit logs—is also approaching its threshold. If three workers are all stuck on high-frequency tables, a fourth table (say, the player balance table) waits in the queue. That wait is the stall that players actually feel. The balance table hasn’t been vacuumed for 15 minutes. It’s bloated to 3x its size. The query planner, seeing the bloat, decides that a sequential scan is cheaper than the index scan. Now every balance check is a full table read. The latency goes from 2ms to 200ms. The tournament’s front-end, which was designed to handle 833 TPS, is now timing out on 10% of requests.
Why the Autovacuum Cost Model Fails Under Burst Load
The autovacuum cost model is designed to be polite. It uses vacuum_cost_page_hit (1), vacuum_cost_page_miss (10), and vacuum_cost_page_dirty (20) to budget its own I/O. The vacuum_cost_limit is the total budget per cycle, and vacuum_cost_delay (default 0, but often set to 2ms) is the sleep between cycles. This is a great design for a shared production database where you don’t want a vacuum to starve the OLTP traffic. It’s a terrible design for a burst event.
During a tournament, the write workload is not uniform. It comes in waves—spins cluster around bonus rounds, leaderboard challenges, and the final 10 minutes of the event. In a 2-minute burst, the TPS can spike to 2,500. The autovacuum worker, already throttled by the cost model, is now processing a table with a write rate that is 10x its own read rate. The cost model was never designed to keep up with a write storm; it was designed to avoid interfering with one. The result is a vacuum that is perpetually in the "needs to run" state but never completes a full pass.
The deeper issue is that the cost model applies to the worker’s I/O, but it does not account for the dirty pages generated by the vacuum’s own writes. When a vacuum cleans a dead tuple, it writes a new version of the page. In a hot table, that page is already in the shared buffer pool and will be dirtied again by the next transaction. The vacuum is doing the same work twice. It reads the page, cleans it, writes it, and then the application writes to the same page again. The vacuum_cost_page_dirty penalty (20) is supposed to account for this, but it’s a flat cost. It doesn’t know that the same page is being dirtied by the application at a rate of 100 times per second.
This is where the standard advice—"just raise autovacuum_cost_limit"—fails. Raising the limit to 2000 or 20000 gives the worker more budget, but it also means the worker is competing directly with the application for I/O bandwidth. On a disk-backed system, this can cause severe I/O wait. On an SSD with a good controller, it might work. But the real fix is to stop the vacuum from needing to run at all during the tournament window.
H3: The Case for Pre-Warming the Wrapper
The most robust solution is to isolate the tournament data from the main transactional tables. A common pattern is to use a separate tournament score table that is truncated and rebuilt for each event. But truncation is a DDL operation, and it takes an ACCESS EXCLUSIVE lock. You can’t truncate a table that players are actively writing to. The alternative is to use a partitioned table where each tournament gets its own partition. When the tournament starts, you create a new partition. When it ends, you drop the old one. Dropping a partition is a metadata operation, not a data operation, so it’s fast and doesn’t require a vacuum.
This is the architectural fix. It moves the problem from "how do I tune autovacuum" to "how do I avoid generating dead tuples." If the leaderboard is a partition that lives for 48 hours, the dead tuple rate is bounded by the tournament’s duration. At 833 TPS for 48 hours, you’d generate about 144 million dead tuples. That’s a lot, but the table is never vacuumed during the event. You let it bloat. You let the query planner use sequential scans. Because the partition is small (a few thousand rows), a sequential scan is still fast. The bloat is contained, and the vacuum runs during the dead window between tournaments.
This is the counter-intuitive insight: For a hot, small table, the optimal autovacuum setting is to turn it off entirely for the duration of the event. You set autovacuum_enabled = false on the partition. The table bloats, but the bloat is bounded. The real enemy is unbounded bloat on a table that is also being queried. A partition that grows to 100MB but is only scanned sequentially is fine. A table that grows to 100MB and is being indexed is a disaster.
The Human Factor: The 3 AM Alert
The incident reports from operators are almost identical. The alert fires at 3 AM on a Sunday. The dashboard shows autovacuum: VACUUM public.leaderboard (to prevent wraparound) running for 4 hours. The CPU is at 100%. The latency is climbing. The on-call DBA has two options: kill the vacuum (risking transaction ID wraparound) or let it run (risking a total outage). Neither is good.
The root cause is almost never a single misconfiguration. It’s a compound failure. The scale factor is too high. The cost limit is too low. The worker count is too low. The table isn’t partitioned. The monitoring threshold for "vacuum running too long" is set to 30 minutes, so nobody noticed until the damage was done. The fix is a checklist, not a single parameter.
Here’s the checklist that would have prevented the 3 AM alert:
- Set a per-table scale factor of 0.01 for any table that receives more than 100 writes per second. This is a simple ALTER TABLE statement. It’s the single highest-impact change you can make.
- Increase
autovacuum_max_workersto 6 for the tournament database. The default of 3 is a shared pool, and a single hot table can monopolize all three. - Set
autovacuum_vacuum_cost_delayto 0 for the tournament database. The cost model is a throttle; you don’t want a throttle during a burst event. If you’re worried about I/O, use a separate storage tier for the tournament DB. - Monitor
n_dead_tupon the leaderboard table with a 1-minute granularity. If it’s climbing at a rate that suggests you’ll hit the threshold in under 10 seconds, you’re in the stall zone. Kill the tournament or accept the latency hit. - Use a partition per tournament. This is the nuclear option, but it’s also the only one that guarantees a clean state.
The uncomfortable truth is that PostgreSQL’s autovacuum is not designed for this workload. It’s designed for a database that has a steady state. A weekend slot tournament is a deliberate, engineered spike. You either engineer around it or you accept that the database will be the bottleneck.
The Wraparound Clock Is Ticking
The most dangerous outcome of an autovacuum stall isn’t the latency spike. It’s the wraparound. PostgreSQL uses a 32-bit transaction ID counter. When it runs out, the database shuts down to prevent data corruption. The counter wraps around at roughly 2 billion transactions. At 833 TPS, you burn through 2 billion transactions in about 27 days. That’s a long time, but it’s not infinite. If a tournament runs for 48 hours, you’ve consumed about 144 million transaction IDs, or roughly 7% of the wraparound budget.
The autovacuum daemon is responsible for advancing the relfrozenxid on each table. If a table is stalled—if the vacuum can’t complete a pass—the relfrozenxid stays stale. The database gets closer to wraparound with every tournament. You might run three tournaments a month. That’s 432 million transaction IDs per month. You’re now at 20% of the budget per month. In five months, you’re at 100%. The database will shut down during the middle of a weekend tournament. The players will see a "Connection Lost" error. The operator will see a $250,000 loss in revenue and a PR nightmare.
The standard mitigation is autovacuum_freeze_max_age (default 200 million). If a table’s relfrozenxid is older than this, autovacuum will force a vacuum, even if the scale factor hasn’t been hit. This is the "to prevent wraparound" vacuum that you see in the logs. It’s a hard override. But the override doesn’t solve the stall. It just makes the vacuum run with a higher priority, which means it will be even more aggressive in competing with the application for resources. The stall becomes a deadlock: the database forces a vacuum to prevent wraparound, the vacuum can’t finish, the database gets closer to wraparound, and the force becomes more urgent.
The only real answer is to reduce the transaction rate on the table that matters. That means moving the leaderboard out of the transactional path. Use a Redis cluster for the live leaderboard. Write to PostgreSQL every 5 seconds with a batch UPDATE. That drops the transaction rate from 833 TPS to 2 TPS. Now the default autovacuum settings work fine. The 20% threshold takes 1,000 seconds to hit. The vacuum runs once every 15 minutes and finishes in milliseconds.
But that’s a redesign, not a tuning fix. And redesigns require budget, timeline, and sign-off. The DBA who is on call at 3 AM doesn’t have any of those. They have a pg_terminate_backend command and a prayer.
What the Next Tournament Will Teach Us
The pattern is predictable. The next tournament will have a new game, a new bonus structure, and a higher player cap. The database will be given a bit more memory, a few more workers. The autovacuum settings will be tweaked—a 0.05 scale factor here, a 500 cost limit there. The system will hold for the first hour. Then the leaderboard table will hit the threshold. The vacuum will start. The latency will climb. The alert will fire. The on-call DBA will make a judgment call.
The question is not whether autovacuum can be tuned to handle a weekend slot tournament. It can, but only if you treat the tournament as a special event with its own schema, its own monitoring, and its own operational playbook. The question is whether the organization will invest in that playbook before the next tournament, or after the next outage. The history of iGaming operations suggests it will be after. The wraparound clock is always ticking, and the next spin is always a transaction.