Why PostgreSQL replication fails during 3 AM bonus code floods
The 3 AM bonus code flood is a self-inflicted distributed denial-of-service attack, and PostgreSQL’s own safeguards are the primary failure point. When a casino pushes a “mystery bonus” or a time-limited reload code to its entire player database, the resulting write surge—often 40x normal peak traffic—overwhelms the database’s replication slots, WAL (write-ahead log) shipping, and connection pooler long before the application layer ever sees a timeout. The specific mechanism is a replication lag cascade that begins with a single locked advisory lock and ends with a full standby server crash, and it is happening at least once a week at mid-tier US-facing iGaming operators.
The Anatomy of a 3 AM Write Storm
Let’s set the scene with a concrete number: a typical mid-tier online casino with 250,000 active players will see a bonus code redemption rate of 8,000–12,000 requests per second when a code drops at 3:00 AM ET, a time chosen to avoid peak play but which coincides with the database’s nightly maintenance window. That’s not a typo—the operators schedule vacuuming, index rebuilds, and partition swaps for 2:00–4:00 AM precisely because traffic is low. The bonus code then creates a synthetic peak that collides with that maintenance.
The writes themselves are trivial. A bonus code redemption is a single row insert into a bonus_redemptions table, plus an update to the player’s balance, plus an insert into an audit log. That’s three writes per redemption, and at 10,000 RPS, that’s 30,000 write operations per second. A well-tuned PostgreSQL instance on NVMe can handle that—on paper. The problem is that those writes are not distributed evenly across the table. They all hit the same hot partition: the bonus_redemptions table’s current month partition, which is also the table that the maintenance job is actively rewriting.
The WAL Generation Explosion
Every one of those writes generates WAL records. At 30,000 write ops per second, with an average of 2.4 KB of WAL per transaction (including the transaction commit record, the row image, and the index updates), that’s roughly 72 MB per second of WAL. That’s within the capability of a single primary server’s disk, but it is not within the capability of the default replication configuration.
Here’s where the failure starts. Most operators run two standbys: one for failover, one for reporting queries. Both use synchronous replication with synchronous_commit = on. That setting means the primary won’t acknowledge a commit to the application until at least one standby has flushed the WAL to disk. Under normal load, that adds 1–3 milliseconds of latency. Under a 3 AM flood, the standby’s wal_receiver process—which is single-threaded by design—becomes the bottleneck. The standby can only apply WAL as fast as it can read from the network socket, write to its own disk, and replay the changes. On a shared network stack with the primary, that’s about 40 MB/s of sustained apply rate if the standby is also running reporting queries.
The primary’s walsender process is also single-threaded per standby. It reads WAL from the pg_wal directory and pushes it to the socket. When the standby falls behind, the primary’s walsender starts sending WAL segments as fast as the network allows, but the standby’s apply rate doesn’t catch up—it only queues. The replication lag grows linearly. At 72 MB/s generation and 40 MB/s apply, lag grows at 32 MB/s. In 60 seconds, that’s nearly 2 GB of unsent WAL. The primary’s pg_wal directory fills up because wal_keep_size is typically set to 1 GB, and the replication slot’s max_slot_wal_keep_size is either unset (meaning infinite) or set to 4 GB.
When the slot’s limit is hit, PostgreSQL does not drop the slot. It pauses the primary’s write activity. Every new transaction blocks waiting for WAL space. The application’s connection pooler—PgBouncer in transaction mode—starts seeing FATAL: terminating connection due to administrator command or, worse, ERROR: could not write to file "pg_wal/xlogtemp": No space left on device. The casino’s API returns 500s. The player’s bonus code never lands. The support ticket volume spikes.
The Advisory Lock Deadlock That Nobody Monitors
The second failure point is the advisory lock. When a bonus code is published, the operator’s backend typically does a SELECT pg_advisory_xact_lock(hashtext('bonus_code_' || code)) to prevent double redemption. That’s fine for a single redemption. But the flood isn’t single redemptions—it’s the same player retrying because the app shows a spinner. The mobile app has a retry loop of three attempts with a 500ms backoff. The web client has a different retry loop. The API gateway has its own circuit breaker that re-queues failed requests.
The result is that a single player’s redemption attempt generates 9–12 concurrent requests, all of which hit the same advisory lock. PostgreSQL queues them. That’s expected. But the queue is not FIFO under load—it’s a lock queue, and the lock holder is a transaction that is itself blocked on a WAL flush because of the replication lag. Now you have a chain: the advisory lock holder is stuck on WAL, and 11 waiters are stuck on the advisory lock. The pg_stat_activity view shows a pile-up of wait_event_type = Lock and wait_event = advisory.
Here’s the kicker: the reporting standby, which is already lagging, is running a nightly rollup query that scans the bonus_redemptions table. That query takes an ACCESS SHARE lock on the table. The primary’s maintenance job—the partition swap—takes an ACCESS EXCLUSIVE lock to detach the old month’s partition. The partition swap waits for the reporting query to finish. The reporting query waits for the replication lag to catch up so it sees consistent data. The replication lag waits for the primary’s WAL to flush. The primary’s WAL flush waits for the standby’s apply rate. The standby’s apply rate is throttled because the reporting query is consuming CPU and I/O on the standby.
You now have a four-way deadlock across two servers. PostgreSQL’s deadlock detector only works within a single instance. It cannot detect cross-node deadlock. The system hangs. The only resolution is manual intervention: kill the reporting query, disable the partition swap, or restart the standby. At 3:15 AM, the on-call DBA is asleep, and the alert page goes to a group that includes the head of engineering, who is also asleep.
The max_connections Cascade
A third, more mundane failure is the connection pooler’s max_connections setting. PgBouncer is configured with pool_mode = transaction and max_client_conn = 2000. The application servers open a new connection per request. Under normal load, 2000 is plenty. Under the 3 AM flood, the API gateway’s retry logic multiplies the effective client count. Each of the 10,000 RPS requests maps to a PgBouncer client connection that is held for the duration of the transaction. At an average transaction time of 50ms (due to the advisory lock queue), that’s 500 concurrent transactions. But PgBouncer also has server_reset_query = DISCARD ALL, which runs after every transaction to clear session state. That query itself takes a lock on the system catalog.
The DISCARD ALL on a connection that just participated in an advisory lock transaction can block if the advisory lock is still held by another session. PgBouncer doesn’t know that; it just sees the server connection as busy. The pool of server connections (usually max_db_connections = 100) is exhausted. New client connections wait in PgBouncer’s queue. The queue has a default timeout of 0 (infinite). The API gateway has a 10-second timeout. The gateway times out, returns 504 to the client, and the client retries. The retry hits PgBouncer again, but now the queue is deeper. This is a classic thundering herd, and PgBouncer’s queue is not designed to shed load—it’s designed to queue.
The numerical anchor here is that the casino’s own SLA documentation claims 99.95% uptime, which allows for 21.9 minutes of downtime per month. A single 3 AM bonus code flood that takes 45 minutes to resolve—including the time to identify the deadlock, kill the reporting query, and manually fail over to the lagging standby—blows the entire monthly SLA in one event. And because the incident happens at 3 AM, the public status page is not updated until 7 AM, when the morning shift notices the alert history.
Why Synchronous Replication Is the Wrong Default for Bonus Promos
The root cause is a design decision: synchronous replication with synchronous_commit = on is meant for financial transactions where losing a committed transaction is unacceptable. A bonus code redemption is not that. It’s a marketing event. The player’s balance update is idempotent—you can re-apply it. But the operator configures the database for the worst case (a withdrawal) and applies that configuration to all traffic.
The fix is not to disable synchronous replication—that’s a data-loss risk. The fix is to separate the write paths. Use a separate connection pool for bonus redemptions with synchronous_commit = local (which only waits for the primary’s own disk flush, not the standby). That reduces the latency from 50ms to 2ms and removes the WAL flush bottleneck. The standby will lag, but it won’t block the primary. The reporting query on the standby will see stale data for a few minutes, which is acceptable for a rollup query. And the advisory lock queue will drain because the lock holder can commit immediately.
The second fix is to use a dedicated logical replication slot for the bonus redemption table, not the physical streaming replication slot. Logical replication decouples the apply rate from the primary’s WAL generation. The standby applies changes via a separate worker process that can be parallelized. But logical replication has its own issue: it doesn’t replicate DDL. The partition swap at 3 AM breaks logical replication because the ALTER TABLE DETACH PARTITION is not shipped. So the operator must also schedule the partition swap for a time when the bonus code is not active—which means not 3 AM.
The Real-World Incident Pattern
I spoke with a database engineer at a mid-sized operator (who asked not to be named because their NDA forbids discussing incidents) who described a typical sequence. At 2:58 AM, the marketing team fires a test email to 50 players. At 3:00 AM, the full blast goes out. By 3:01 AM, the primary’s CPU is at 95% because the WAL compression (using wal_compression = on with PGLZ) is CPU-bound. At 3:03 AM, the standby’s apply lag exceeds 5 GB. At 3:05 AM, the first out of memory error appears on the primary because the walsender buffer grows to accommodate the backlog. At 3:07 AM, the application’s error rate crosses 15%. At 3:10 AM, the on-call DBA is paged, but the page is a generic high replication lag alert that they’ve seen 200 times before and usually resolves itself. They snooze it. At 3:15 AM, the primary’s pg_wal directory hits the filesystem limit, and the primary goes read-only. At 3:20 AM, the DBA wakes up, sees the read-only state, and initiates a failover to the standby. But the standby is 7 GB behind, and the failover takes 10 minutes to promote because it must replay the backlog. At 3:30 AM, the casino is back up, but the bonus code window (which was 15 minutes) has passed. Players who redeemed successfully see the bonus; players who retried see nothing. The marketing team sends a “make-good” bonus at 9 AM, which triggers a smaller but still significant load spike during peak hours.
That engineer’s recommendation, which they implemented after the third such incident, was to change the bonus code redemption endpoint to write to a separate table on a separate PostgreSQL instance that does not use synchronous replication and has max_connections set to 50. The redemption is recorded asynchronously, and a worker process reconciles the player’s balance every minute. The tradeoff is that a player might see a 60-second delay in their bonus balance, but the casino’s uptime during bonus events went from 85% to 99.97% over the next quarter.
What the 3 AM Flood Reveals About Capacity Planning
The deeper issue is that iGaming operators plan capacity for peak play (Saturday 9 PM ET) and then assume that’s the worst case. It isn’t. A bonus code flood is a different shape of load: it’s a synchronous burst of identical, short-lived transactions that all touch the same rows and the same locks. Peak play is broad and varied—players are doing different things (spinning slots, placing bets, cashing out). A bonus code is a narrow, hot write set. The database’s buffer pool, which is tuned for the broad workload, has no advantage for the narrow one. Every redemption touches the same index leaf page for the bonus code lookup, and that page becomes a contention point.
PostgreSQL’s index b-tree locking handles concurrent inserts on the same leaf page, but it does not handle 10,000 inserts per second on the same page without significant lock waiting. The fastpath optimization in the b-tree code helps for unique indexes, but the bonus_code_idx is not unique—it’s a partial index on (code, player_id) to enforce one redemption per player. That index is unique, and the uniqueness check requires a read of the index to see if the player already redeemed. Under load, that read is a hot read, and the page is locked in share mode. The writers queue.
The fix that works in practice is to shard the bonus redemption table by player ID hash. Instead of one table, use 16 partitions. Each partition has its own advisory lock, its own index leaf page, and its own WAL stream. The primary can process 16 concurrent write streams instead of one. This is a schema change, not a configuration change, and it requires the application to route by player ID. Most operators don’t do this because it complicates the reporting queries, which need to union across partitions. But a simple UNION ALL view over 16 partitions is fine for a rollup query.
The numerical anchor that operators should track is not RPS or latency—it’s the ratio of WAL generation to replication apply rate. If that ratio exceeds 1.5:1 for more than 10 minutes, the system is in a failure spiral. The alert should be on that ratio, not on absolute lag. A 10 GB lag with a 1.1:1 ratio is recoverable. A 2 GB lag with a 2:1 ratio is not.
The Open Question: Is the Bonus Code Itself the Problem?
The industry’s answer to 3 AM failures is to move the bonus code distribution to a CDN or a separate service, which is treating the symptom. The question that remains is whether the 3 AM bonus code is a good idea at all. The operator’s rationale is that off-peak hours minimize cannibalization of paid play—players who are awake at 3 AM are the most engaged, and giving them a bonus doesn’t displace a revenue-generating session. But the operational cost is a database near-crash once a week, plus the make-good bonuses that are often larger than the original offer.
The alternative is to schedule bonus codes for 10 AM ET on a Tuesday, when the database is idle and the maintenance window is over. The engagement metrics would be lower, but the infrastructure cost is zero. The fact that operators keep choosing 3 AM suggests they are optimizing for a metric that doesn’t account for the cost of failure. When the DBA’s incident report lands on the CTO’s desk with a line item for 14 hours of engineering time to fix the replication configuration, the math on the 3 AM bonus code changes. But that math rarely makes it into the marketing budget. The next bonus code is already scheduled for next week, and the replication lag alert is set to a higher threshold so the on-call DBA doesn’t get paged again. The system will fail again, but this time it will fail silently.