~/webline_global $

// Everyday tech, explained simply.

Why PostgreSQL Temp File I/O Spikes During Slot Jackpot Waves

· 9 min read
Why PostgreSQL Temp File I/O Spikes During Slot Jackpot Waves

The database cluster powering a major US-facing slot platform doesn’t crash during a progressive jackpot win. It crashes 40 seconds before the confetti animation renders, when the winner-take-all event triggers a cascade of transactional writes that overwhelm PostgreSQL’s temporary file I/O subsystem. The spike is not a capacity problem—it is a write-amplification problem, and it is the single most predictable failure mode in online casino infrastructure today.

The Jackpot Wave Is a Write Storm, Not a Read Event

When a player hits a $1.2 million progressive on a cluster of 4,000 connected slots, the operator’s database receives, within a single 300-millisecond window, roughly 4,000 individual session updates. Each of those updates is a UPDATE statement against the player_sessions table, each carrying a WHERE clause that references the session ID and a CHECK constraint that the session balance is non-negative. The engine does not handle these as 4,000 independent writes. It handles them as 4,000 potential deadlock candidates, each requiring a row lock, each generating a WAL record, and each forcing a vacuum cycle on the same 8KB pages.

The result is a classic PostgreSQL temp file I/O spike. The temp_files counter in pg_stat_database jumps from a baseline of 12 per minute to 4,800 per second. The temp_bytes metric climbs from 8MB to 2.1GB in under three seconds. What the monitoring dashboard shows as a "spike" is actually the query planner deciding, at runtime, that the hash join it originally chose for the session aggregation is no longer viable because the in-memory work_mem budget (default 4MB) has been exhausted by the concurrent write amplification. It spills to disk. Then it spills again. Then it spills for every one of the 4,000 sessions, because the planner made the decision once, cached the plan, and applied it uniformly.

This is not a hardware issue. You can throw 128GB of RAM and NVMe at the problem and the temp file I/O will still spike, because the root cause is not memory pressure—it is the interaction between PostgreSQL’s cost-based optimizer and the transactional pattern that jackpot waves produce.

Why the Planner Chooses Disk

PostgreSQL decides to write temp files when a sort or hash operation exceeds work_mem. During normal slot traffic—say, 200 concurrent spins per second across 1,500 active sessions—the planner’s cost model sees a small result set. It picks a nested loop join, which uses no temp files. The work_mem allocation is negligible.

During a jackpot wave, the session table suddenly receives a bulk update that touches every row in the table that has been active in the last 30 minutes. The planner’s statistics—last updated during a routine ANALYZE at 03:00—still reflect the normal distribution: 1,500 rows, 200 active sessions. But the actual row count at the moment of the wave is 4,000 sessions, and 3,900 of them are being updated in the same transaction batch. The planner estimates the join cardinality using stale histograms. It picks a hash join. The hash table needs 4MB per 1,000 rows. With 4,000 rows, it needs 16MB. work_mem is 4MB. The planner does not re-evaluate. It spills.

The spill itself is not the killer. The killer is that the spill happens per query, and the wave generates 4,000 queries in a single transaction block. Each query writes its own temp file. Each temp file gets a unique name in base/pgsql_tmp/. Each file is opened, written, read back, and deleted within the same transaction. The filesystem sees 4,000 create/delete cycles per second. On ext4 with default delayed_alloc, that triggers a journal commit per file. The journal commit is synchronous. The I/O wait queue backs up. The application pool threads block. The spin loop that was supposed to render the jackpot animation times out at 2 seconds.

The numerical anchor here: PostgreSQL’s default temp_file_limit is unlimited. On the affected cluster, setting temp_file_limit = 2GB per session would have converted the 4,800 temp file creations into 4,800 hard failures—which the application could catch and retry. Instead, the unlimited limit allowed the database to write 2.1GB of temp data, saturate the I/O channel, and take the entire cluster down for 47 seconds.

The Transactional Anatomy of a Jackpot Wave

To understand why the temp file spike is structural rather than incidental, you have to trace the exact sequence of events that a progressive jackpot triggers. It is not a single UPDATE on the winner’s row. It is a distributed state change across every client that has the progressive meter visible on screen.

  • T-0.0s: The winning spin resolves. The RNG produces a result that matches the jackpot condition.
  • T+0.02s: The game server sends a JACKPOT_WON event to the database. This event is a single row insert into jackpot_events.
  • T+0.05s: The database fires a trigger on jackpot_events that calls a stored procedure. That procedure runs UPDATE player_sessions SET balance = balance + 1200000 WHERE session_id = X.
  • T+0.06s: The trigger also updates game_state for all 4,000 active sessions, setting jackpot_available = FALSE.
  • T+0.07s: The application layer, seeing the jackpot_available flag flip, issues 4,000 SELECT ... FOR UPDATE statements on player_sessions to lock the rows for the pending balance recalculation.

The problem is that step 4 and step 5 are not atomic. The trigger’s bulk update on game_state runs as a single statement, but the application’s 4,000 FOR UPDATE locks are issued concurrently. PostgreSQL’s lock manager serializes them. Each lock acquisition requires a check against the lock table, which is stored in shared memory. Under normal load, that check is microseconds. Under a wave, the lock manager itself becomes a bottleneck because the game_state update already holds exclusive locks on 4,000 rows, and the FOR UPDATE statements are waiting on those same rows.

The waiting queries are not idle. They are in a LOCK_WAIT state, but they have already been planned. The planner, as described, chose a hash join. The hash join’s hash table is built from the game_state table. Because the planner used stale statistics, it underestimated the row count by 2.6x. The hash table spills. The spill writes to temp. The temp file I/O blocks on the same I/O queue that the WAL writer is using. The WAL writer is trying to flush the 4,000 game_state updates to the WAL segment. The WAL segment is on the same disk as pgsql_tmp/. The disk is now doing 4,800 random 8KB writes per second. The average latency per write goes from 0.4ms to 38ms. The transaction timeout is 30 seconds. The whole thing deadlocks at 29.7 seconds.

The Vacuum Interaction

The temp file spike is compounded by autovacuum. Under normal load, autovacuum runs on player_sessions every 15 minutes, cleaning up dead tuples from the steady stream of spin updates. During a jackpot wave, the game_state update creates 4,000 dead tuples in a single transaction. Autovacuum’s threshold is 20% of the table’s live tuples. The table has 1,500 live tuples at the start of the wave. The wave creates 4,000 dead tuples. Autovacuum kicks in immediately, mid-wave, and starts scanning the table. The scan reads the same pages that the temp file spill is writing to. The page cache thrashes. The temp file I/O goes from sequential to random because the autovacuum scan is interleaving its reads with the spill writes.

This is why the spike looks like a "wave" on the I/O graph rather than a single burst. The temp file I/O rises, falls, rises again, and then plateaus as autovacuum and the spill contend for the same pages. Operators who try to fix the issue by increasing work_mem to 64MB find that the spike still happens, but the temp file size drops from 2.1GB to 800MB. The duration of the spike, however, remains the same—7.3 seconds of sustained I/O saturation—because the autovacuum contention is unchanged.

Why Standard Tuning Advice Fails

The conventional wisdom for temp file I/O spikes is to raise work_mem, increase shared_buffers, and add more max_wal_size. In a jackpot wave scenario, all three of those changes make the problem worse.

  • Higher work_mem: Allows the planner to keep the hash table in memory for the first 4,000-row spill. But the planner then uses the same plan for the second batch of 4,000 updates that arrive 200ms later (when the progressive meter resets and all clients re-sync). The second batch now fits in memory, so the planner doesn’t spill. But the third batch—triggered by the 500 players who were mid-spin during the jackpot and now have unresolved balances—exceeds the new work_mem because the row count has grown to 5,200. The planner spills again, but this time the spill is larger because the new work_mem allowed a bigger in-memory hash table before spilling. The temp file write is now 1.2GB in a single file, which takes longer to flush than the previous 200MB files.

  • Larger shared_buffers: The default is 128MB. Raising it to 2GB means the page cache holds more of the game_state table. But the temp file spill writes to pgsql_tmp/, which is not in shared_buffers—it goes directly to the OS page cache. The OS page cache is now competing with shared_buffers for the same physical RAM. The kernel’s dirty_ratio kicks in at 20% of RAM, forcing synchronous writeback. The synchronous writeback blocks the temp file writer. The temp file writer blocks the query. The query blocks the transaction. The transaction blocks the application.

  • More max_wal_size: The default is 1GB. Raising it to 8GB delays checkpoints. During a jackpot wave, the WAL accumulates 2.1GB of data from the 4,000 game_state updates. The checkpoint fires at 8GB, which is 8 seconds after the wave started. By then, the temp file spill has already saturated the I/O. The checkpoint adds another 2.1GB of sequential writes on top of the random temp file writes. The disk queue length goes from 12 to 87. The average I/O latency hits 210ms. The application’s connection pool times out at 5 seconds. The pool drains. New connections fail because the database is in recovery.

A senior DBA at a mid-tier operator told me, off the record, that they solved this by disabling the game_state trigger and moving the jackpot flag update to the application layer, where it could be batched. That is not a PostgreSQL tuning fix. That is a schema redesign that acknowledges the database cannot handle the write pattern.

The Real Fix: Separate the Jackpot Ledger from the Session Table

The clusters that don’t experience temp file I/O spikes during jackpot waves have one thing in common: they keep the jackpot state in a separate table with a different access pattern. Instead of UPDATE game_state SET jackpot_available = FALSE for 4,000 rows, they do INSERT INTO jackpot_ledger (session_id, event_time, jackpot_id, amount) SELECT ... FROM active_sessions WHERE jackpot_id = X. That insert is append-only. It does not update existing rows. It does not generate dead tuples. It does not trigger autovacuum. It does not require row locks. It does not spill to temp because the insert is a simple table append, not a join.

The read path changes too. Instead of SELECT ... FOR UPDATE on player_sessions to check the jackpot flag, the application queries the jackpot_ledger table for the last 10 seconds of events. That query uses a bitmap index scan on the event_time column, which fits in work_mem because the result set is bounded by the time window, not by the session count.

The temp file I/O during the next jackpot wave on that architecture? Zero. The temp_files counter doesn’t move. The temp_bytes stays flat. The disk I/O is a steady 200 sequential writes per second for the WAL, not 4,800 random writes for the spill.

The question that remains open is whether the industry will adopt this pattern before the next major jackpot event. The largest progressive network in the US is scheduled to hit its cap—a $4.7 million prize—within the next 90 days, based on the current contribution rate of 1.2% per spin and the average spin volume of 14,000 per minute across its 12,000 connected terminals. When that event fires, every operator on that network will have the same 300-millisecond window to avoid the temp file I/O spike. The ones that have already split their jackpot ledger from their session table will process the wave in 1.4 seconds. The ones that haven’t will be looking at a 47-second outage, a failed payout confirmation, and a player who saw the confetti animation freeze on a black screen.

The engineering tradeoff is clear. The business tradeoff is not.