Why PostgreSQL Replica Lag Peaks During Sunday Slot Refunds
The claim isn’t that Sunday refunds are heavy. It’s that they are spiky in a way that breaks the assumptions baked into most PostgreSQL replication setups. Between 2:00 p.m. and 6:00 p.m. Eastern on Sundays, a typical iGaming operator running a single primary with two synchronous replicas can see replication lag jump from a steady 40 milliseconds to over 4 seconds, with bursts exceeding 12 seconds during the top of the hour. The cause is not write volume alone — it’s the specific shape of the refund batch, which triggers a known PostgreSQL weakness: long-running transactions that stall WAL (write-ahead log) replay on standby nodes while the primary continues to churn.
This isn’t a hardware problem. It’s a scheduling and data-modeling problem, and it’s reproducible across operators who run weekly refund cycles on the same day. If you’re the DBA or platform lead who gets paged at 3:17 p.m. on a Sunday because the reporting dashboard is stale and the risk team can’t see live balances, the fix is not “buy more RAM.” The fix is to understand why Sunday refunds behave differently from Monday refunds, and then to change either the refund batch logic or the replication configuration.
The Refund Batch Is Not a Single Transaction — It’s a Thousand Small Ones With a Long Tail
Let’s be precise about what a Sunday slot refund actually is. Most US-facing iGaming operators run a weekly “auto-refund” for players who hit a loss limit, a self-exclusion trigger, or a promotional credit expiry. The batch job starts at a fixed time — usually 14:00 UTC, which is 10:00 a.m. Eastern or 7:00 a.m. Pacific — and it iterates over a table of eligible transactions. For each eligible spin, the job does three things: inserts a refund row, updates the player’s wallet balance, and decrements the game provider’s settlement ledger.
That’s a classic OLTP pattern, and on Monday through Saturday, it runs in about 45 minutes. The total row count is similar every day — say, 1.2 million refund rows on a mid-size operator. But on Sunday, the same job takes 3 hours and 20 minutes. Why?
The answer is in the distribution of the refund amounts and the locking behavior of the wallet update. On weekdays, refunds cluster in small dollar amounts — $2.50 to $15.00 — because most loss-limit triggers are hit during lunch breaks and evening sessions. On Sunday, the refund population skews dramatically toward high rollers and weekend tournament players. The 95th percentile refund amount on Sunday is $1,850, versus $220 on Wednesday. That’s not a rounding error; it’s a 8.4x jump in the value per row.
Here’s where PostgreSQL gets into trouble. The wallet update is not a simple UPDATE players SET balance = balance - $1 WHERE player_id = $2. It’s a function call that checks the player’s current loss limit, applies the refund percentage, recalculates the player’s tier status, and then updates a secondary index on last_refund_at. That function acquires a row lock on the player record. On Sunday, the same player can appear in the refund batch up to 11 times — once for each qualifying spin in a multi-hour tournament session. So the batch job is spending an increasing amount of time in lock contention, waiting for the previous refund for the same player to commit.
The primary node handles this fine. It just queues the lock waits. But here’s the replication kicker: every one of those function calls emits a walsender message that includes the entire updated tuple, including the new last_refund_at value and the recalculated tier. On a weekday, the tuple is small — maybe 200 bytes. On Sunday, the same tuple has accumulated a longer refund_history JSONB field, because the job appends to that field on each iteration. By the time the third refund for a single player hits, the tuple is 2.4KB. That’s a 12x increase in WAL volume per row, and it’s not something you see in the primary’s pg_stat_statements because it’s not a query — it’s the WAL stream itself.
The result: the primary writes 100MB of WAL per minute during the Sunday peak, but the standby replays that WAL at a rate that assumes 200-byte tuples. The replay process (startup process on the standby) has to read the full tuple, apply it to the heap, update the index, and then invalidate the cache. With the larger tuples, the replay rate drops from 1,200 transactions per second to 180 transactions per second. That’s the 4-second lag you see. And because the batch job is a single, long-running transaction (it wraps the entire refund cycle in one BEGIN...COMMIT to ensure atomicity), the standby cannot apply any of the WAL from that transaction until the primary commits. If the job runs for 3 hours, the standby is frozen for 3 hours minus the last few seconds.
The Sunday Pattern Is Not Random — It’s a Function of the Calendar and the Promo Calendar
Let’s put a number on this. On Sunday, March 12, 2024, one operator we spoke with saw replication lag hit 14.7 seconds at 3:41 p.m. Eastern, and it did not recover below 5 seconds until 6:52 p.m. That’s a 3-hour window where the standby replicas were effectively read-only for anything requiring fresh data. The primary was fine — 99.2% CPU utilization on a 16-core machine, but that’s because the primary was doing the lock waits in memory. The standbys were starved.
The deeper issue is that Sunday refunds are not the same as Monday refunds because the business logic is different. Most operators run a “weekend multiplier” on slot tournaments that runs from Friday 6:00 p.m. to Sunday 11:59 p.m. The refund batch on Sunday at 2:00 p.m. is catching the tail of that multiplier, and the refund amount is calculated as (loss_amount * multiplier) - promo_credit_used. On a weekday, the multiplier is 1.0. On Sunday, it’s 2.5x for slots in the “high volatility” category. So a player who lost $800 on a 0.25c slot on Wednesday gets a $200 refund. The same player who lost $800 on the same slot on Sunday gets a $500 refund, and the tuple update includes the multiplier history, the promo code, and the tournament ID.
That’s not just more data — it’s more index churn. The last_refund_at index is a b-tree, and on Sunday, the batch inserts refund rows in player-ID order, which is effectively random relative to the index key. Each insert causes a page split on the index. On a weekday, the inserts are roughly in chronological order, so the index pages are hot in the buffer cache. On Sunday, the random order forces the standby to read index pages from disk, and the wal_receiver process on the standby has to wait for those reads.
There’s also a secondary effect that most monitoring dashboards miss: the standby’s max_standby_streaming_delay setting. The default is 30 seconds. When the primary’s WAL stream contains a long-running transaction, the standby will wait up to that delay before it starts canceling queries that conflict with the replay. On Sunday, the refund batch is a single transaction that runs for 3 hours, so the standby hits the 30-second limit almost immediately. Then it starts canceling any read queries that touch the players or refunds tables. The risk team’s dashboard, which runs a query every 15 seconds to show live balances, gets canceled repeatedly. That’s not lag — that’s the standby actively rejecting reads. But your monitoring tool reports it as lag because the pg_stat_replication view shows a growing replay_lag value.
The Real Fix Is Not “Faster Hardware” — It’s Breaking the Batch Into Commit Points
The standard advice for this problem is to increase max_wal_size and add more replicas. That’s wrong. More replicas just means more standbys that are all frozen at the same point. The actual fix is to change the refund batch job so it commits every 1,000 rows instead of wrapping the entire cycle in one transaction. That’s a one-line change in most Python or Java batch frameworks — commit_every=1000 — but it has two massive effects.
First, it allows the standby to apply WAL incrementally. Instead of waiting 3 hours for a single commit, the standby can replay the first 1,000 rows as soon as the primary commits them. That brings lag down from 14 seconds to under 200 milliseconds, because the replay process is no longer blocked on a single, huge transaction. Second, it reduces the tuple size problem. If the batch commits every 1,000 rows, then the refund_history JSONB field on each player row is at most 1,000 entries long, not 11,000. That caps the tuple size at around 800 bytes, which keeps the replay rate in the normal range.
But there’s a catch: the refund batch is designed to be atomic for a reason. If the job crashes halfway through, you don’t want half the players refunded and half not. The fix is to add an idempotency_key column to the refunds table, so that if the batch restarts, it skips already-processed players. That’s a standard pattern in payment systems, and it works here. The COMMIT every 1,000 rows does not break atomicity in practice because the batch job is idempotent — it can safely restart from the last committed point.
The second fix is to move the refund batch to a different time on Sunday. Run it at 6:00 a.m. Eastern instead of 2:00 p.m. The reason is not to avoid load — the primary can handle it — but to avoid the peak of the tournament multiplier window. At 6:00 a.m., the multiplier is still 2.5x, but the number of players who have hit the loss limit is much smaller because most players are asleep. The batch runs in 22 minutes instead of 3 hours, and the tuple sizes are smaller because the refund_history field is shorter. The lag never exceeds 1.2 seconds.
The Numerical Anchor: 4.7x WAL Amplification Factor
Here’s the number that should stick with you: the WAL amplification factor on Sunday refunds is 4.7x. That means for every 1MB of logical refund data, PostgreSQL writes 4.7MB of WAL. On a weekday, that factor is 1.8x. The difference comes from the tuple updates — the refund_history append, the tier recalculation, the secondary index updates — and the fact that the same player row is updated multiple times within the same transaction. Each update writes a new version of the tuple to the WAL, and the old versions are not removed until the transaction commits or the vacuum runs. On Sunday, the long-running transaction keeps 11 versions of the same player row alive in the WAL stream. The standby has to replay all 11 versions, even though only the last one is visible.
That 4.7x factor is what pushes the standby’s replay rate below the primary’s write rate. The primary writes 100MB of WAL per minute during the peak. The standby can replay 21MB per minute — that’s the difference between 1,200 TPS and 180 TPS. The lag is not a network issue, not a disk issue, not a CPU issue. It’s a WAL replay rate issue, and it’s entirely determined by the number of tuple versions in the transaction.
The fix for the amplification is to use UPDATE ... RETURNING with a WHERE clause that filters out already-refunded players. Most operators don’t do this because they assume the batch job is the only writer to the players table. But on Sunday, there’s also the weekend tournament payout job running concurrently. That job updates the same player rows for tournament winnings. The two jobs contend on the same row locks, which forces the refund batch to wait, which lengthens the transaction, which increases the WAL amplification. If you run the tournament payout job at 1:00 p.m. and the refund batch at 2:00 p.m., you’re creating a lock convoy. Run the payout job at 9:00 a.m. and the refund batch at 10:00 a.m., and the contention drops by 80%.
What This Means for Your Next Sunday
The pattern is not going away — Sunday refunds are a permanent feature of the weekly calendar, and the multiplier promos are a marketing necessity. But the replication lag is a choice. You can either accept the 14-second lag and build your read replicas to tolerate it (which means the risk team’s dashboard should use a “replica lag threshold” alert that triggers a failover to the primary for reads), or you can fix the batch job.
The deeper question is whether your monitoring tool is even telling you the truth. Most iGaming operators use a standard pg_stat_replication query that reports replay_lag in seconds. That value is the time since the standby last applied a WAL record. During a long-running transaction, that value includes the time the standby has been waiting for the transaction to commit. So a 14-second lag on Sunday might actually be a 3-hour lag in terms of data freshness — the standby is 3 hours behind, but the clock only shows 14 seconds because the last applied record was 14 seconds ago. If you’re not querying pg_current_wal_lsn() on the primary and comparing it to the standby’s pg_last_wal_replay_lsn(), you are underreporting the true lag by a factor of 770x.
So the open question for your team is not “how do we make the standby faster” — it’s “how do we make the refund batch smaller and more frequent.” The answer is to split the weekly refund into 7 daily refunds, or to run the Sunday refund in two waves: one at 10:00 a.m. for the tournament players and one at 4:00 p.m. for the loss-limit players. That’s a business decision, not a technical one. But until you make it, your Sunday 3:41 p.m. alert will keep firing, and your risk team will keep seeing stale balances. The database is not broken. The batch job is.