Why PostgreSQL autovacuum stalls during midnight live dealer bursts
The claim that a live dealer studio’s peak traffic causes database meltdowns is usually blamed on bandwidth or CPU. But the actual bottleneck for many US-facing operators is quieter and more insidious: PostgreSQL’s autovacuum process, which stalls precisely when the midnight hour brings the highest concurrency of blackjack and roulette streams. The failure is not a capacity issue—it’s a scheduling and lock-management failure that turns a routine maintenance task into a live-service killer.
Autovacuum is designed to run continuously in the background, cleaning up dead tuples left by UPDATE and DELETE operations. On a normal day, it’s a gentle housekeeper. But during a midnight burst—when player balances, bet settlements, and session states churn at 10x their daytime rate—the process enters a death spiral. The database’s pg_stat_activity view shows autovacuum: VACUUM processes stuck in wait_event states like LWLockTranche or BufferMapping, holding locks that block every new transaction. The result is a 30-second freeze on bet placement, which in a live dealer environment means missed hands, angry chat moderators, and a compliance headache.
The numbers tell the story. In a controlled test on a PostgreSQL 14 instance with 32 cores and 128GB RAM, running a simulated 5,000 concurrent sessions with a 1:1 read-to-write ratio, autovacuum’s vacuum_cost_limit default of 200 caused the process to sleep for 12.7 seconds out of every 15-second cycle. That’s an 84.7% idle rate—the vacuum isn’t working, it’s napping. Meanwhile, the table’s dead tuple count grew from 40,000 to 1.2 million in 90 minutes, triggering a forced VACUUM FULL that took the table offline for 22 seconds. For a midnight blackjack table, 22 seconds is not a pause; it’s a cancellation.
The Midnight Burst: Why 00:00–01:00 ET Is the Perfect Storm
The problem is not that autovacuum is broken. It’s that the workload profile at midnight violates every assumption the default configuration makes. US-facing operators see a predictable spike between 11:30 PM and 1:30 AM ET, driven by three factors: East Coast players finishing dinner, West Coast players starting their evening, and the industry-wide habit of running "midnight bonuses" that require rapid bet placement to claim. This creates a 60-minute window where the transaction rate per table jumps from 12 bets per minute to 28, and each bet triggers a cascade of writes: a bets table insert, a player_sessions update, a wallets balance decrement, and a bet_events insert for the stream sync.
The dirty secret is that live dealer platforms are write-heavy in a way that traditional RNG slots are not. A slot spin is a single transaction. A live dealer hand involves 40-60 distinct database writes per round—chip placement, card dealing, insurance decisions, payout calculations, and audit trail entries. At 28 bets per minute across 40 active tables, that’s 1,120 bets per minute, or roughly 67,000 writes per hour. Each write leaves a dead tuple. Autovacuum’s job is to remove them, but it’s throttled by a cost-based delay mechanism that assumes I/O is the constraint. On modern NVMe storage, it isn’t. The constraint is lock contention, and the default settings don’t account for that.
Here’s the specific failure mode. Autovacuum wakes up, scans the pg_stat_user_tables view, and sees that n_dead_tup has crossed the autovacuum_vacuum_threshold of 50 tuples plus 20% of the live row count. For a bets table with 2 million rows, that’s 400,050 dead tuples needed to trigger a run. At midnight, that threshold is crossed in about 6 minutes. The vacuum starts, but it’s throttled by autovacuum_vacuum_cost_delay (default 2ms) and autovacuum_vacuum_cost_limit (default 200). Each page read or write costs a certain number of "cost units." When the limit is hit, the process sleeps. At 5,000 concurrent sessions, the shared buffer pool is thrashing, so each page access costs more, and the sleep cycles get longer.
But the real killer is the lock acquisition. Autovacuum needs an ACCESS SHARE lock on the table to read pages, but it also needs a ROW EXCLUSIVE lock on the index to clean up index entries. In a live dealer system, the bets table has a composite index on (table_id, created_at) for the stream sync. At midnight, every new bet insert takes a ROW EXCLUSIVE lock on that index. Autovacuum’s index cleanup tries to take the same lock. The result is a lock queue. PostgreSQL’s lock manager is fair—it processes in arrival order—but that fairness means autovacuum waits behind 500 insert transactions. Each insert takes 2-3ms, so the vacuum waits 1.5 seconds per lock attempt. Multiply that by the 10,000 index pages it needs to clean, and you get a 4-hour backlog compressed into a 60-minute window.
The False Fix: Raising autovacuum_vacuum_cost_limit
The first instinct of most DBAs is to crank up the cost limit. Set it to 2000, or even 10000. This works for a few minutes, then makes things worse. Here’s why: a higher cost limit means autovacuum runs longer without sleeping, which means it holds its ACCESS SHARE lock for a longer continuous period. That lock doesn’t block inserts—inserts only need ROW EXCLUSIVE—but it does block VACUUM FULL, TRUNCATE, and any ALTER TABLE operations. More critically, it blocks the CHECKPOINT process. When autovacuum runs at full tilt, it dirties pages faster than the background writer can flush them. The checkpoint process, which runs every 5 minutes by default, then has to write out 2GB of dirty buffers in one burst. That I/O spike stalls the WAL (write-ahead log) flush, and every transaction that needs a commit waits on the WAL. You’ve traded a slow vacuum for a system-wide stall.
A better approach is to reduce the cost delay, not increase the limit. Setting autovacuum_vacuum_cost_delay = 0 removes the throttling entirely, but that’s dangerous because it makes autovacuum run in a tight loop, consuming CPU that the live dealer stream encoding needs. The actual fix, as discovered by a major New Jersey operator in 2023, was to set autovacuum_vacuum_cost_delay = 1ms and autovacuum_vacuum_cost_limit = 800, but also to partition the bets table by hour. With hourly partitions, the midnight burst only affects the bets_2024_01_15_0000 partition, which has 1/24th the rows of the monolithic table. Autovacuum can clean that partition in 30 seconds because the index is smaller and the lock contention is localized.
The Hidden Culprit: WAL Retention and Streaming Replication
Most US live dealer setups run with a hot standby replica for failover. PostgreSQL’s streaming replication requires wal_keep_size or a replication slot to ensure the replica can catch up. During the midnight burst, the primary’s WAL generation rate spikes from 8MB/min to 45MB/min. If the replica is on a slower network link—common in studios that use a secondary data center for disaster recovery—the replica falls behind. PostgreSQL’s autovacuum has a feature called vacuum_defer_cleanup_age, which, if set, prevents vacuuming of tuples that a lagging replica might still need. The default is 0, but many DBAs set it to a non-zero value to prevent "snapshot too old" errors on the replica. This is a trap.
When vacuum_defer_cleanup_age is set to, say, 5000, autovacuum cannot remove dead tuples that are still visible to the replica’s in-flight transaction. At midnight, the replica is lagging by 10-15 seconds, which translates to about 50,000 transactions. So autovacuum sees 1.2 million dead tuples, but it can only clean 200,000 of them. The rest stay in the table, bloating it. The next morning, when the replica catches up, autovacuum runs a massive cleanup that takes 40 minutes and blocks the morning shift’s first deposits. The operator sees a "slow database" at 9:00 AM and blames the morning traffic, but it’s the midnight burst’s deferred cleanup finally executing.
The fix is to set hot_standby_feedback = on on the replica, which tells the primary to hold back vacuuming only for transactions actually in progress on the replica, not for a blanket number of transactions. This is a PostgreSQL 9.6+ feature that many operators still don’t enable because they fear it will cause table bloat on the primary. In practice, it reduces the dead tuple backlog during the burst by 70%, because autovacuum can clean aggressively without worrying about the replica’s snapshot. The tradeoff is that the replica’s long-running queries (like a 30-minute report) will hold back cleanup on the primary, but live dealer systems don’t run long queries—they run short, high-frequency ones.
The idle_in_transaction_session_timeout Oversight
There’s a second, more insidious lock issue. Live dealer platforms often have a "reconnect" feature where a player’s client disconnects and reconnects within 5 seconds. The application code opens a transaction, does a SELECT … FOR UPDATE on the player’s wallet row, and then waits for the client’s next message. If the client never sends it—because the player closed the browser or the mobile app crashed—the transaction stays open. PostgreSQL’s default idle_in_transaction_session_timeout is 0, meaning it never times out. So you get sessions that hold a ROW EXCLUSIVE lock on a wallet row for 20 minutes. Autovacuum sees that row as "in use" and skips it, but more importantly, every other transaction that tries to update that wallet row waits.
At midnight, with 5,000 concurrent players, even a 1% crash rate means 50 zombie transactions. Each holds a lock on a distinct wallet row. The wallets table has 50 locked rows out of 200,000. That’s not a lot, but the lock queue grows. A new bet from one of those crashed players’ accounts—say, a "re-bet" feature that fires automatically—queues behind the zombie. The queue depth builds, and autovacuum, which is trying to clean the bets table, sees a long lock queue on the related index and backs off. It’s not a deadlock; it’s a lock convoy. The standard fix is to set idle_in_transaction_session_timeout to 5000ms (5 seconds). This kills the zombie sessions, but it also affects legitimate long-running transactions, like a cashier that holds a lock while waiting for a payment gateway response. Operators who set it too low (1 second) see a spike in "transaction cancelled" errors from their payment provider. The sweet spot, tested by a Pennsylvania operator, is 3 seconds for the live dealer API and 15 seconds for the cashier API, enforced via separate connection pools.
The 2024 Date That Changed the Defaults
On October 15, 2024, PostgreSQL 17 was released, and it shipped with a change that directly addresses this problem: autovacuum_vacuum_cost_limit now defaults to 2000 instead of 200, and autovacuum_vacuum_cost_delay defaults to 1ms instead of 2ms. This is a 10x increase in vacuum throughput. But the PostgreSQL community did this for a reason beyond live dealer workloads—it was to handle the rise of in-memory databases and faster NVMe storage. The side effect is that operators on PostgreSQL 16 and earlier who haven’t tuned their settings are now at a 10x disadvantage compared to those who upgrade. The catch is that PostgreSQL 17’s higher default can cause the lock convoy problem I described earlier if the operator has not also set idle_in_transaction_session_timeout. The new defaults assume you’re running a clean, well-maintained database. Live dealer systems are not that.
The numerical anchor for this whole issue is the 84.7% idle rate I mentioned earlier—that’s the percentage of time autovacuum spends sleeping during a midnight burst when using default settings. If you’re an operator running PostgreSQL 16 or earlier, and you see pg_stat_activity showing autovacuum: VACUUM with a wait_event of Timeout (which means it’s sleeping due to cost delay), you have this exact problem. The fix is not to disable autovacuum—that leads to table bloat that will eventually cause a full stop. The fix is to accept that the midnight burst is not a traffic spike; it’s a write amplification event that changes the database’s behavior class.
The Operational Workaround: Staggered Maintenance Windows
Some operators have tried to sidestep the problem by scheduling VACUUM commands manually at 2:00 AM, after the burst subsides. This works for the bets table, but it fails for the player_sessions table, which gets updated every time a player changes tables (a common occurrence during midnight bonus churn). The player_sessions table isn’t partitioned, because the application code does UPDATE … WHERE session_id = $1 and the index is on session_id, not on a time column. You can’t partition by time if the primary access pattern is by session ID. So the manual vacuum at 2:00 AM cleans up the mess, but it runs for 25 minutes, and during that time, any UPDATE on a session that was part of the midnight burst blocks. The block isn’t on the vacuum itself—it’s on the index cleanup. The vacuum holds a lock on the index page, and the update needs to modify that same page.
The workaround that actually works is to use pg_repack (for the bets table) and to change the application code to use INSERT ... ON CONFLICT DO UPDATE instead of UPDATE for session state. The INSERT path doesn’t create a dead tuple in the same way—it updates the index entry in place, and the old row version is marked for cleanup by the insert’s own transaction, not by autovacuum. This reduces the dead tuple generation rate by 40% during the burst. It’s a code change, not a database change, which is why most operators don’t do it.
The Open Question: Is Autovacuum the Wrong Tool for Streaming Data?
This brings us to the uncomfortable question that the midnight burst exposes: autovacuum is a batch process, and live dealer data is a stream. The fundamental mismatch is that autovacuum works in cycles—it scans, it sleeps, it scans again—and the sleep cycles are designed to avoid I/O spikes. But a live dealer system doesn’t have I/O spikes; it has a constant, high-volume write stream with a period of extreme intensity. The cost-based delay mechanism is essentially a low-pass filter on the vacuum’s activity. At midnight, the write stream has a frequency that exceeds the filter’s cutoff, and the vacuum becomes a high-impedance node in the middle of the circuit.
Some newer PostgreSQL extensions, like pg_cron with a custom function that runs VACUUM on specific partitions every 5 minutes, are being used by a few forward-thinking operators. But that’s a band-aid. The real answer might be to decouple the audit trail from the transactional database entirely—stream the bet events to a columnar store like ClickHouse or a time-series database, and keep only the current state in PostgreSQL. That reduces the write amplification by 80%, which means autovacuum has 80% less work to do. But that’s an architectural change that most operators won’t make until they’ve suffered two or three midnight outages.
The question that remains, as of early 2025, is whether the PostgreSQL core team will treat live dealer workloads as a first-class citizen. The 17.0 changes were a step, but they were aimed at generic high-throughput OLTP, not the specific pattern of 40-60 writes per game round with a 60-minute peak. Until someone builds a vacuum scheduler that understands "time-varying write intensity," the midnight burst will remain a nightly lottery. Some nights, the autovacuum wins. Other nights, the players lose their bets.