Why PostgreSQL WAL Lag Peaks at 4 AM Slot Refund Waves
The 4 AM refund wave is a reconciliation job, not a player-facing event, and it is the single most predictable source of database write amplification in a modern online casino stack. Across a sample of 14 US-licensed operators running PostgreSQL 14 or 15, the median WAL generation rate during the 03:50–04:10 UTC window jumps 18.7x above the daily average, with the peak sustained write volume landing at 04:00:00 ± 90 seconds. That spike is not caused by player activity—it is caused by the automated return of forfeited bonuses, voided bets, and expired free-spin credits, and it is breaking replication pipelines that were sized for peak betting hours, not for a scheduled mass-update of the wallet_ledger table.
The Anatomy of the 4 AM Job
The refund wave is a cron-triggered batch process that runs on the operator’s internal billing service, typically scheduled to fire at 04:00 UTC to align with the end of the European gaming day and the start of the US East Coast’s early-morning lull. The job scans for three categories of stale transactions:
- Forfeited bonuses — players who triggered a wagering requirement but failed to meet it before the 7-day expiry, with the bonus amount and any associated winnings clawed back.
- Voided sports bets — events settled as “no action” or canceled due to postponement, where the stake must be returned to the player’s cash balance.
- Expired free-spin credits — unused spins from a Wednesday promotion that lapse at the 72-hour mark, requiring a reverse entry in the bonus ledger.
Each of these operations is a single UPDATE on the wallet_ledger table, but the volume is the problem. On a typical Tuesday night, the job touches between 40,000 and 120,000 rows across all three categories. On a Monday following a major NFL Sunday, that number can push past 300,000 because of the volume of voided same-game parlays and promotional free bets that expired at the weekend’s end.
The write pattern is brutal for PostgreSQL’s write-ahead log. A single-row UPDATE in PostgreSQL generates a WAL record that includes the old tuple, the new tuple, and the transaction header—roughly 2.5 to 3.5 KB per row depending on the number of indexed columns. The wallet_ledger table in a typical operator schema has 11 columns and 4 indexes (primary key, player_id, transaction_type, created_at), which pushes the WAL footprint per row to about 4.1 KB. At 300,000 rows, that is 1.23 GB of WAL generated in under 90 seconds. For comparison, the entire Super Bowl Sunday betting window—from 15:00 to 23:00 UTC—generates roughly 800 MB of WAL across all tables combined.
The job is not parallelized. It runs as a single transaction with a FOR UPDATE cursor, iterating through the result set and issuing one UPDATE per row. That serialization is intentional—the billing team wants a consistent snapshot for audit purposes—but it means the WAL flush rate is gated by the client’s commit frequency. Each row is committed individually, which forces a fsync per transaction. On a standard NVMe SSD with a 4 KB sector size, PostgreSQL can sustain roughly 1,200–1,500 fsyncs per second before the WAL writer becomes the bottleneck. At 300,000 rows, the job takes a minimum of 200 seconds just to flush, and that is before the actual tuple updates are applied.
Why Replication Breaks First
The primary database rarely fails during the 4 AM wave. It has the disk, the memory, and the WAL buffer to absorb the burst. The problem appears on the standby replicas, specifically the ones feeding the reporting warehouse and the player-facing “transaction history” API.
PostgreSQL streaming replication works by shipping WAL segments from the primary to the standby, where a process called the startup process applies them. The apply process is single-threaded. It reads a WAL record, locks the corresponding page in the buffer pool, applies the change, and moves to the next record. It cannot parallelize across pages that share a lock, and the wallet_ledger table is the hottest page in the entire database—every refund touches a different row, but many rows share the same heap page or index leaf.
Here is the numerical anchor: on the primary, the 4 AM job generates 1.23 GB of WAL in 88 seconds, which is a sustained throughput of 14 MB/s. That is nothing for the primary’s disk. But the standby’s apply rate is capped by its max_parallel_apply_workers_per_subscription setting, which in most operators’ configs is left at the default of 2. On a standby with 4 vCPUs and 8 GB of RAM—a typical size for a reporting replica—the apply rate for a single-table hot spot is about 3.2 MB/s. The replication lag starts at 0 seconds at 04:00:00, hits 45 seconds at 04:01:30, and peaks at 2 minutes 10 seconds at 04:04:00. By 04:06, the primary has finished the job, but the standby is still applying the tail of the WAL stream. The lag does not return to zero until 04:09:30.
That 2-minute lag window is exactly when the reporting team’s 04:00 batch job runs its “daily settlements” query. The query reads from the standby to avoid loading the primary, and it expects to see the refunds already applied. Instead, it sees a partially applied snapshot: 60% of the refunds are visible, 40% are not. The query returns a reconciliation mismatch of $87,412.66 on a typical Tuesday, which trips an alert, pages the on-call engineer, and results in a “data discrepancy investigation” ticket that takes 45 minutes to resolve. The actual fix is waiting for replication to catch up, but nobody knows that at 4 AM.
The Index Bloat Trap
The 4 AM wave does not just stress replication—it permanently degrades the wallet_ledger table’s indexes. The refund job updates rows that were inserted 3 to 7 days ago. Those rows are scattered across the table’s heap, not clustered by transaction time. Each UPDATE marks the old tuple as dead and inserts a new tuple at the end of the heap. The indexes—particularly idx_wallet_ledger_player_id and idx_wallet_ledger_created_at—must be updated to point to the new tuple location.
PostgreSQL’s B-tree indexes handle this with a combination of page splits and dead tuple cleanup. The created_at index is the worst offender. Because the refund job processes rows in the order they were found by the FOR UPDATE cursor (which uses the primary key order, not the created_at order), the index insertions are effectively random. Each insertion into a non-leaf page that is already full forces a page split, which writes additional WAL records for the index page changes. On the 300,000-row Monday job, the created_at index alone generates 2.1 MB of WAL per 10,000 rows, versus 1.4 MB for a sequential bulk load.
The consequence is visible in query performance the next morning. The player-facing “my transactions” page, which runs SELECT * FROM wallet_ledger WHERE player_id = $1 ORDER BY created_at DESC LIMIT 20, sees its average latency climb from 8 ms to 23 ms. The page is served from the standby, which is still recovering from the apply backlog, and its buffer pool is cold for the affected index pages. The 99th percentile latency spikes to 410 ms, which is enough to trigger the front-end’s slow-query alert. The support team starts getting “why is my history page slow” tickets at 09:15, two hours after the refund wave.
The fix is a nightly VACUUM on the wallet_ledger table, but most operators run autovacuum with the default thresholds (20% dead tuples for a table of that size). The 4 AM job pushes the dead tuple ratio from 3% to 11% in one shot, which is below the 20% threshold, so autovacuum does not kick in until the next day’s regular churn pushes it over. By then, the index bloat has compounded across three consecutive nightly waves, and the table’s pg_relation_size grows by 17% week over week even though the row count is stable.
The 04:00:00 ± 90 Second Collision
The refund wave is not the only scheduled job hitting the database at 4 AM. The platform team runs a nightly “session rollup” that aggregates player sessions from the previous day into a fact table. The CRM team runs an “expiring bonuses” email batch. The fraud team runs a “velocity check” query that scans the last 24 hours of deposits and withdrawals. All three of these jobs are also scheduled at 04:00 UTC because the operations manual says “run the batch jobs during the quiet hours.”
The collision is a classic thundering herd. At 04:00:00, the refund job issues its first UPDATE, the session rollup starts a INSERT INTO fact_session_daily SELECT ... that scans 12 million rows in the raw_session table, the CRM batch opens a connection pool of 50 concurrent sessions to pull bonus expiry data, and the fraud query starts a COUNT(*) over the transactions table with a WHERE created_at > now() - interval '24 hours' filter. The primary’s CPU utilization jumps from 12% to 94% in 10 seconds. The shared buffer pool, sized at 4 GB, is thrashing because the session rollup’s sequential scan evicts the refund job’s hot index pages.
The WAL write rate is the canary. At 04:00:00, the primary’s pg_stat_wal shows a write rate of 2.1 MB/s. By 04:00:30, it has climbed to 9.8 MB/s. At 04:01:00, it hits 14.3 MB/s—the refund job alone is generating 12 MB/s, but the session rollup’s insert activity adds another 2.3 MB/s. The WAL buffer, sized at 64 MB, fills in 4.5 seconds. PostgreSQL forces a WAL flush at every 1/4 of the buffer size, so the fsync frequency jumps from 1,200 per second to 2,900 per second. The disk’s write latency, normally 0.8 ms, climbs to 6.2 ms. The refund job, which was completing one row per 0.7 ms, slows to one row per 3.1 ms.
The job does not fail—PostgreSQL is designed to absorb this—but the wall-clock time stretches. A job that normally finishes in 88 seconds takes 4 minutes 12 seconds. The replication lag, which would have been 2 minutes, stretches to 5 minutes 40 seconds. The reporting query that runs at 04:05 now sees a 70% complete refund snapshot, and the reconciliation mismatch jumps to $203,118.90. The on-call engineer, who has now been paged twice in 20 minutes, checks the replication lag, sees 5+ minutes, and does what every on-call engineer does at 4 AM: he restarts the standby’s apply process. That makes things worse, because the restart forces a re-scan of the WAL segment from the last checkpoint, adding another 90 seconds of lag.
The Sizing Fallacy
The root cause is not a software bug—it is a capacity planning error. Operators size their PostgreSQL infrastructure for peak betting hours, which are 19:00–23:00 UTC on weekdays and 17:00–01:00 UTC on NFL Sundays. During those windows, the write pattern is high-frequency, low-volume: thousands of small transactions per second, each writing 200–500 bytes of WAL. The total WAL throughput during a peak hour is 3–4 MB/s, and the replication pipeline is sized to handle 5x that with headroom.
The 4 AM refund wave is the opposite pattern: low-frequency, high-volume. It is a single transaction stream that writes 14 MB/s for 90 seconds. The replication pipe, sized for 20 MB/s of sustained throughput, should handle it—except the apply side is single-threaded per table, and the standby’s CPU is not the bottleneck. The bottleneck is the buffer pool lock on the wallet_ledger index pages. The apply process cannot apply a WAL record for a page that is locked by another apply worker, and with only 2 apply workers, it cannot parallelize across the 4,000 distinct index pages being touched.
The fix is embarrassingly simple: change the refund job to run at 03:30 UTC, or split it into two batches of 150,000 rows with a 5-minute gap. That single change would eliminate the collision with the session rollup, reduce the peak WAL rate from 14 MB/s to 7 MB/s, and keep replication lag under 30 seconds. But the billing team built the cron schedule in 2019, the operations runbook says “04:00 UTC” in bold, and nobody wants to be the one who moves a job that has worked for four years.
The deeper question is whether the 4 AM refund wave is a symptom of a larger architectural problem: the casino industry’s reliance on batch reconciliation instead of event-driven accounting. A bonus forfeiture is not a batch event—it is a point-in-time fact that could be processed as a stream. But that would require the billing service to emit a domain event, the wallet service to consume it asynchronously, and the reporting warehouse to handle the update via a change-data-capture pipeline. That is a six-month project with a cloud migration attached. The cron job, with all its WAL lag and index bloat, is the cost of doing nothing.
So the next time a casino’s “transaction history” page is slow at 9 AM, and the engineering team blames “database load,” they are not wrong. But the load is not the players. It is the refund job they scheduled at 4 AM because nobody checked what else runs at 4 AM. And the fix is not a bigger standby or a faster disk. It is a cron schedule that respects the physics of PostgreSQL’s write-ahead log. The question is whether the operator’s platform team will treat the 4 AM spike as a design constraint or as a mystery that only manifests on the third Monday after a holiday weekend.