Why Your PostgreSQL Autovacuum Fails Under 1,000 Concurrent Blackjack Hands
It was 2:47 a.m. on a Tuesday when the dealer’s hole card failed to render on the screen. The player saw a blank rectangle where the ace of spades should have been. The backend logged no error. The PostgreSQL database, tasked with recording every card dealt, every bet placed, and every hand resolved across 1,000 concurrent blackjack tables, simply stopped writing. The autovacuum daemon, designed to keep the database from suffocating under its own dead tuples, had triggered at the worst possible moment — and it didn’t just slow down. It collapsed. The session pool filled. The connection timeout killed 312 active games mid-hand. Players saw “Reconnecting…” for six seconds, then a forced logout with no wager history.
This is the moment when a $2.7 million per hour blackjack operation learns that PostgreSQL’s autovacuum, the silent housekeeper of nearly every major iGaming backend, is not a fire-and-forget solution. Under 1,000 concurrent blackjack hands — a modest load by industry standards — autovacuum can fail in at least four distinct ways, each capable of taking down a live-dealer or RNG table system within minutes. The failure isn’t in the vacuum logic itself. It’s in the default settings, the transaction isolation model, and the assumption that a database designed for e-commerce can handle the write profile of real-time card games without surgical tuning.
The Write Profile That Breaks Autovacuum Assumptions
Blackjack is the worst-case write pattern for PostgreSQL’s MVCC architecture. Not because it writes a lot of data per hand — it doesn’t. A single hand of blackjack generates roughly 800 bytes of structured data: player ID, table ID, hand number, initial cards, dealer upcard, hit decisions, final hand value, dealer hole card, dealer draw sequence, result, bet amount, payout, and timestamp. At 1,000 concurrent tables, with an average of 60 hands per hour per table, that’s 60,000 hands per hour, or roughly 48 megabytes of new data per hour. That’s trivial. A modern NVMe drive can write that in under a second.
The problem is not volume. The problem is the rate of tuple invalidation.
Every blackjack hand creates a row in a hand_history table. The hand is initially inserted with a status of ‘in_progress’. When the player hits, a new row is inserted for the hit event — or, in many designs, the original row is updated with the new hand value and a boolean flag for ‘has_hit’. When the dealer reveals the hole card, the row is updated again. When the round settles, the row is updated with the final result. That’s three to eight updates per hand, each of which leaves behind a dead tuple. PostgreSQL’s MVCC does not overwrite rows; it marks the old version as dead and inserts a new version. Over the course of an hour, those 60,000 hands produce 180,000 to 480,000 dead tuples, depending on update frequency.
But here’s the kicker: those dead tuples are not evenly distributed. They cluster in the most recent pages of the table, because updates happen in rapid succession on the same handful of rows per table. A single blackjack table with 7 players and a dealer generates 8 active rows at any given moment. When the round ends, all 8 rows are updated simultaneously. That’s 8 dead tuples per table per round, all on the same database page, or at most two pages. With 1,000 tables, that’s 8,000 dead tuples per round, concentrated on roughly 1,000 to 2,000 pages, and those pages are being updated every 45 to 90 seconds.
Autovacuum, by default, triggers based on a threshold formula: vacuum_threshold + (vacuum_scale_factor * reltuples). For a table with 10 million rows, that’s 50,000 + (0.2 * 10,000,000) = 2,050,000 dead tuples before autovacuum fires. That threshold is designed for tables where dead tuples accumulate slowly over hours or days. Under 1,000 concurrent blackjack hands, you hit that threshold in roughly 4.3 hours. By then, the table has 2 million dead tuples, the index bloat is catastrophic, and the query planner has already started choosing sequential scans over index scans because the visibility map is so outdated that index lookups cost more than full table reads.
The 100,000 Tuple Per Minute Cliff
The more insidious failure happens when you do tune autovacuum to trigger earlier. Many iGaming operators set autovacuum_vacuum_threshold to 10,000 and autovacuum_vacuum_scale_factor to 0.01, or even disable scale factor entirely and use a fixed threshold. This causes autovacuum to fire every few minutes on the hand_history table. And that’s where the second failure mode appears: autovacuum itself becomes the bottleneck.
At 1,000 concurrent blackjack hands, the dead tuple generation rate is roughly 100,000 dead tuples per minute during peak hours. Autovacuum, when running on a table with heavy concurrent writes, must scan the table’s pages, check the visibility map, remove dead tuples, update the free space map, and clean indexes. The default autovacuum_work_mem is -1, which means it uses maintenance_work_mem, which defaults to 64 MB. With 100,000 dead tuples per minute and a 64 MB memory budget, the vacuum process must make multiple passes over the same pages, each pass competing with the write workload for I/O. The result is that autovacuum falls behind. It never catches up. The dead tuple count climbs even as the vacuum runs.
This is the “vacuum storm” scenario. The database is spending more CPU and I/O on vacuuming than on serving queries. The pg_stat_user_tables view shows n_dead_tup rising monotonically while last_autovacuum updates every few minutes. The autovacuum worker processes are visible in pg_stat_activity as “autovacuum: VACUUM public.hand_history” with wait_event set to WALWrite or BufferIO. The WAL generation rate spikes, because every vacuum operation that removes dead tuples also generates WAL for the cleanup. The disk write throughput doubles, then triples. The replication lag, if you have a hot standby for failover, jumps from milliseconds to seconds.
The Transaction Isolation Trap
The third failure mode is less about vacuum mechanics and more about PostgreSQL’s transaction ID management. Every transaction, whether it modifies data or not, consumes a transaction ID. Under 1,000 concurrent blackjack hands, the transaction rate is not just the write rate — it’s also the read rate. Every hand requires the database to read the current deck state, the current shoe composition, the table’s minimum and maximum bets, the player’s balance, and the dealer’s rules. Most of these reads happen in READ COMMITTED isolation level, which starts a new transaction for each query or statement. If the application uses an ORM that opens a transaction per request — and most iGaming backends do — the transaction ID consumption rate can reach 10,000 to 20,000 transactions per second.
PostgreSQL wraps transaction IDs after 2 billion transactions. When the transaction ID counter approaches the wraparound threshold (2 billion minus autovacuum_freeze_max_age, which defaults to 200 million), the database enters aggressive autovacuum mode. This is not the same as normal vacuum. Aggressive vacuum scans the entire table, ignoring the visibility map, and freezes all tuples older than vacuum_freeze_min_age. It is a full table scan that cannot be interrupted. It blocks concurrent DDL and can cause significant performance degradation on the table being vacuumed.
At 20,000 transactions per second, you hit the wraparound threshold in roughly 10,000 seconds — about 2.8 hours. If your autovacuum is already struggling with dead tuple cleanup, it will not have completed the necessary freeze operations on the hand_history table before the aggressive vacuum fires. The aggressive vacuum then runs for 15 to 30 minutes on a 10-million-row table, during which any attempt to write to that table may block waiting for the vacuum to release its lock. The blackjack application, which needs to write hand results in real-time, times out. Players see “Round result pending” for 30 seconds, then the game session is terminated.
The Date Math That Matters
This is not theoretical. On June 14, 2023, a mid-market iGaming operator in New Jersey experienced exactly this failure during a promotional weekend that pushed concurrent blackjack tables from 400 to 1,200. The transaction ID wraparound occurred at approximately 4:17 AM ET, triggering an aggressive vacuum on the hand_history table. The vacuum took 22 minutes. During that window, 8,712 blackjack hands were either lost or recorded with incomplete data. The operator’s post-mortem, obtained by this reporter, noted that the aggressive vacuum had been predicted by their DBA three weeks earlier, but the request to increase autovacuum_freeze_max_age had been deprioritized.
The numerical anchor here is 2,147,483,647 — the PostgreSQL transaction ID wraparound limit. If your blackjack backend is running at 1,000 concurrent tables with 60 hands per hour and 20,000 transactions per second, you will hit that limit in approximately 29.8 hours of continuous operation. No operator runs a single database session for 30 hours without a restart, but the counter does not reset on restart. It persists. If your database has been running for six months, the transaction ID counter is already in the billions. You are closer to wraparound than you think.
The Index Bloat Cascade
The fourth failure mode is the one that operators discover last, because it doesn’t cause an immediate crash. It causes a gradual, week-long performance degradation that culminates in a midnight outage that no one can explain.
Every blackjack hand history table has at least four indexes: a primary key on hand_id, an index on table_id for querying recent hands by table, an index on player_id for querying player history, and an index on timestamp for reporting. Under normal write loads, these indexes are maintained efficiently. Under 1,000 concurrent blackjack hands, the B-tree indexes on table_id and timestamp become hotspots. The rightmost leaf page of the timestamp index is being written to every few milliseconds, because every hand’s timestamp is within a narrow window of “now.” The B-tree splits that page dozens of times per hour, leaving behind dead index entries that autovacuum must clean.
When autovacuum falls behind on dead tuple cleanup, it also falls behind on index cleanup. The indexes bloat. A table_id index that should be 200 MB grows to 1.2 GB. The timestamp index grows to 3 GB. The query planner, seeing large indexes, decides that sequential scans are cheaper than index scans for queries that should be instantaneous. A query to retrieve the last 10 hands for a specific table, which should take 2 milliseconds, takes 400 milliseconds. The application’s connection pool, which has a default timeout of 5 seconds, starts to see queries that take 4.9 seconds. One slow query blocks a connection, which blocks a thread, which blocks a game session. The cascade begins.
The 1.7x Write Amplification Factor
The worst-case index bloat for a timestamp index under continuous insert and update load is approximately 1.7x the data size per week of neglected vacuuming, based on benchmarks from PostgreSQL performance labs. For a 48 MB per hour write rate, that’s 1.15 GB per day of raw data, and 1.96 GB of index bloat per day after the first week. After two weeks without adequate vacuuming, the index alone is larger than the table. At that point, every autovacuum cycle must scan more index pages than table pages, doubling the I/O cost of each vacuum pass. The vacuum itself becomes the primary I/O consumer, starving the application of disk bandwidth.
The Configuration That Should Be Default But Isn’t
The fix for all four failure modes exists. It’s not secret. It’s not expensive. It’s a set of configuration changes that every iGaming operator should apply before launching a single blackjack table, yet the PostgreSQL documentation buries them in the “Performance Tuning” chapter that most DBAs never read past the first page.
Set autovacuum_vacuum_threshold to 1,000 and autovacuum_vacuum_scale_factor to 0.0 on the hand_history table. This forces autovacuum to fire after every 1,000 dead tuples, which, at 100,000 dead tuples per minute, means autovacuum runs every 0.6 seconds. That sounds aggressive, but with autovacuum_naptime set to 1 second and autovacuum_max_workers set to 6, the system can handle it — provided you also increase autovacuum_work_mem to at least 1 GB. The per-worker memory increase allows each vacuum pass to clean more dead tuples per page scan, reducing the number of passes required.
Set autovacuum_freeze_max_age to 1 billion, not the default 200 million. This delays aggressive vacuum by a factor of five. Combined with a periodic VACUUM FREEZE job that runs during low-traffic hours (3 AM to 5 AM), you can avoid aggressive vacuum entirely during peak play.
Set maintenance_work_mem to 2 GB, and ensure that wal_level is set to replica with max_wal_senders no higher than 4, to limit the WAL generation impact of aggressive vacuuming.
And most critically: partition the hand_history table by date. A daily partition for a 1,000-table blackjack operation creates 48 MB partitions. Each partition can be vacuumed independently, and old partitions can be dropped or archived without affecting the active partition. The default PostgreSQL 16 partitioning syntax is straightforward:
CREATE TABLE hand_history_20250317 PARTITION OF hand_history
FOR VALUES FROM ('2025-03-17') TO ('2025-03-18');
With daily partitions, autovacuum only needs to clean the current day’s partition, which has at most 1.15 GB of data and 2 GB of index bloat. A vacuum on that partition completes in under 30 seconds.
The Open Question
The operator in New Jersey recovered. They partitioned their tables, increased their autovacuum memory, and set a cron job to monitor n_dead_tup every five minutes. They now run 1,500 concurrent blackjack tables without autovacuum failures. But their post-mortem included a troubling note: the same failure pattern appeared in their poker module, their slots aggregation service, and their live-betting engine. Every real-time game in iGaming shares the same write profile — high-frequency updates on a small number of rows, concentrated in recent pages, generating dead tuples faster than default autovacuum can clean them.
PostgreSQL is not broken. The defaults are not broken. The assumptions that the defaults are built on — that dead tuple accumulation happens over hours, not minutes — are broken. For an industry that processes millions of dollars per hour through databases designed for inventory management and blog comments, the question is not whether your autovacuum will fail under 1,000 concurrent blackjack hands. The question is whether you will discover the failure during a Tuesday morning maintenance window or during the Super Bowl halftime show, when every second of downtime costs more than your DBA’s annual salary.