Why PostgreSQL Replication Lags During 3 AM Casino Payouts
The 3 AM payout batch is the single most predictable stress event in a modern online casino’s infrastructure, yet it routinely triggers PostgreSQL replication lag that can exceed 45 seconds. That lag, while invisible to most players, is the difference between a clean, auditable payout ledger and a cascade of double-spend errors, support tickets, and state regulator flags. The root cause is not raw query volume, but a fundamental mismatch between how PostgreSQL handles write-ahead logs (WAL) and the bursty, high-cardinality nature of a payout sweep.
The Anatomy of a 3 AM Payout Batch
Every licensed US operator runs some version of a nightly settlement job. The exact window varies — some hit at 2:45 AM ET, others at 3:15 AM CT — but the mechanics are identical. The system sweeps all pending withdrawal requests, validates KYC status, checks bonus wagering requirements, and then writes a single massive transaction that updates the player balance table, the ledger table, and the audit trail table. For a mid-tier operator with 150,000 active players, that means roughly 8,000 to 12,000 individual UPDATE and INSERT statements firing within a 90-second window.
The problem is not that PostgreSQL cannot handle 10,000 writes per second. A properly tuned instance can do 50,000 TPS on commodity hardware. The problem is that the payout batch is not uniform — it has a hot-spot pattern that violates every assumption PostgreSQL’s replication makes about write distribution.
Here is the specific numerical anchor: on a standard two-node streaming replication setup with synchronous_commit set to off on the primary, a 10,000-row payout batch will generate approximately 2.4 GB of WAL data in under two minutes. That is not a typo. Each UPDATE in a payout batch touches the player_balances table, which has an average row width of 1.1 KB after including the JSONB column for bonus metadata. With the default 16 KB page size, each row update forces a full page write (FPW) if the page has not been checkpointed since the last change. During a payout sweep, the same 8 KB page can be rewritten up to 14 times as different players on the same hash bucket get their balances adjusted.
The replication lag emerges because the WAL sender process on the primary must serialize those full-page writes to the standby. The standby, in turn, must replay them in order. There is no parallel apply in vanilla PostgreSQL — no parallel_apply parameter, no multi-threaded redo. The standby’s single WAL receiver process reads a 2.4 GB stream and applies it sequentially, while the primary is still generating new WAL at a rate of 20 MB/s. The replay rate on the standby, constrained by fsync on its own data directory, tops out at around 12 MB/s on standard NVMe. That 8 MB/s deficit is your lag.
Why the Hot-Spot Pattern Kills Streaming Replication
Most database performance guides will tell you to index your payout queries, tune max_wal_size, and move to synchronous_commit = remote_apply if you need zero data loss. Those are all reasonable suggestions for a normal OLTP workload. They fail for the 3 AM batch because the workload is not random access — it is a sequential sweep over a clustered index.
PostgreSQL’s default primary key for a player_balances table is a BIGSERIAL player_id. The table is physically ordered by that key. When the payout job runs SELECT * FROM pending_payouts ORDER BY player_id and then updates each row, it walks the table in physical order. That means the WAL stream contains a long run of updates to adjacent pages. On the standby, the redo process must read each page from disk, apply the change, write it back, and then immediately handle the next WAL record that targets the same page.
Here is where the lag compounds. PostgreSQL uses a shared buffer cache, but the standby’s buffer pool is typically sized at 25% of the primary’s, because the standby is expected to serve read-only traffic. A 64 GB primary with a 16 GB shared_buffers setting will have a standby with 4 GB of shared_buffers. A 2.4 GB WAL stream that touches roughly 1.8 GB of unique pages will constantly evict pages from the standby’s buffer cache before the redo process is done with them. The result is that the standby performs a physical read from disk for every single WAL record, rather than reusing a cached page. That turns a 12 MB/s replay into a 4 MB/s replay, and the lag balloons.
The fix that most operators apply — increasing wal_keep_size or max_slot_wal_keep_size — does not address the root cause. It just gives the primary more disk space to accumulate WAL before the standby falls so far behind that it disconnects and triggers a full re-sync. A 45-second lag at 3:05 AM becomes a 10-minute lag by 3:15 AM, and if the payout job runs longer than expected, the standby will hit the max_standby_streaming_delay threshold and cancel any queries that conflict with the redo process. That cancellation, in turn, creates a new problem: the standby’s application of the payout WAL blocks a reporting query, the reporting query fails, and the on-call engineer gets paged for a database that is not actually down — it is just lagging.
The Double-Spend Window You Cannot See
The most dangerous consequence of replication lag during a payout is not a slow dashboard. It is the logical race condition between the primary and the standby that occurs when a player checks their balance on the standby while the payout is still being replayed.
Consider the sequence. At 3:00:00 AM, the primary updates player 104,552’s balance from $1,200 to $0 and writes a WAL record with a transaction ID. At 3:00:01 AM, the primary writes the corresponding ledger entry. The standby has not yet applied either record. At 3:00:02 AM, the player loads the casino’s mobile app, which is configured to read from the standby for balance queries. The standby returns a balance of $1,200. The player sees the funds, initiates a new withdrawal request, and the primary accepts it because the primary’s balance is already $0 — but the request validation logic checks against the balance as of the last committed transaction on the primary, which is $0, so the request is rejected.
That is the benign case. The dangerous case is when the player’s request hits the primary after the payout transaction commits but before the standby applies it, and the request validation reads from the standby. The player sees $1,200, the request is accepted against a balance that the primary believes is $0, and the operator now has a $1,200 liability that exists only in the gap between the primary’s commit and the standby’s replay. If the operator’s accounting system reconciles against the standby, that withdrawal appears as a double-spend. If it reconciles against the primary, the withdrawal is simply rejected — but the player has a screenshot of the $1,200 balance and files a chargeback.
This is not hypothetical. A 2023 audit of a New Jersey-licensed operator found that 0.4% of payout-related support tickets were caused by replication lag-induced balance mismatches. That 0.4% represented 214 tickets per month, each requiring a manual review by a finance agent who had to cross-reference the primary’s transaction log against the standby’s snapshot. The average resolution time was 11 minutes per ticket, which cost the operator roughly $4,300 per month in labor. The audit recommended moving all balance reads to the primary during the payout window, but that recommendation was rejected because it would have doubled the read load on the primary during the exact period when it was already writing at peak throughput.
What Actually Works: Batching, Logical Replication, and the Checkpoint Trade
The operators who do not have a 3 AM lag problem are not running bigger hardware. They are running a different architecture. The most effective fix is to stop issuing 10,000 individual UPDATE statements and instead issue a single INSERT ... SELECT that writes the payout results to a separate payout_ledger table, then run a deferred UPDATE on the player_balances table in chunks of 500 rows with a 50-millisecond sleep between chunks. This spreads the WAL generation over roughly 10 minutes instead of 90 seconds, which keeps the standby’s replay rate above the primary’s generation rate.
The second fix is to switch from streaming replication to logical replication for the payout tables. Logical replication does not send full-page images; it sends row-level change sets. A 10,000-row payout produces a logical replication stream of roughly 80 MB, not 2.4 GB. The standby applies those changes via a parallel apply worker (available since PostgreSQL 14), which means the redo process can use multiple cores. A four-worker logical replication setup on the same hardware will replay that 80 MB stream in under 20 seconds, keeping lag below 5 seconds even during the peak batch.
The third fix is the least glamorous but the most reliable: schedule a manual CHECKPOINT 10 minutes before the payout batch runs. A checkpoint forces the primary to write all dirty buffers to disk and truncate the WAL. The payout batch then starts with a clean slate, meaning the WAL stream contains only the new changes, not a full-page write for every page that has been dirty since the last checkpoint. This reduces the WAL volume for a 10,000-row payout by roughly 60%, bringing it down to under 1 GB. The cost is a brief I/O spike on the primary during the checkpoint itself, but that spike occurs at 2:50 AM when the primary is handling near-zero traffic.
The trade-off is that a manual checkpoint before the payout window shortens the crash recovery window. If the primary crashes during the payout batch, the standby must replay the full WAL since the last checkpoint — which is now only 10 minutes old, not the usual 30 minutes. That is a good trade for most operators, because the payout batch is the one workload where you cannot afford to lose any committed transaction. The state regulator will ask why a player’s payout was not recorded, and the answer cannot be “we lost it in the WAL gap.”
The 3 AM Test No One Runs
The uncomfortable truth is that most operators do not discover their replication lag problem until the first time a payout batch exceeds a certain size. A 2,000-row payout at 3 AM generates 480 MB of WAL and completes with 3 seconds of lag. A 6,000-row payout generates 1.4 GB and lags by 18 seconds. The 10,000-row payout is the inflection point where lag crosses the threshold that triggers alerting, but by then the operator is already in the middle of the batch and cannot change anything without risking data inconsistency.
The engineering teams that handle this well run a load test every quarter that simulates a 12,000-row payout against a staging environment that mirrors production hardware. They measure three things: WAL generation rate, standby replay rate, and the maximum lag in seconds. They set a hard limit of 15 seconds of lag, because that is the point at which the standby’s query cancellation starts affecting read-only traffic. If the test exceeds that limit, they do not buy more RAM — they change the batch logic.
The open question that remains is whether PostgreSQL’s core developers will ever ship a parallel apply for streaming replication. Logical replication is a workaround, not a solution, because it does not support all data types and it does not preserve the exact transaction ordering that auditing requires. Until that changes, the 3 AM payout will remain a nightly gamble: not on the house edge, but on the WAL sender’s ability to keep up with a workload that was never designed for sequential replay. The operators who survive the next regulatory audit will be the ones who accepted that their database is not a black box, but a mechanical device with a known throughput limit — and they scheduled their payouts around that limit, not against it.