Why PostgreSQL Write-Ahead Logs Stall Under 800 Concurrent Slot Sessions
In the live-dealer ecosystem of a major regulated U.S. online casino, a single PostgreSQL cluster handling session metadata for a blackjack and roulette suite began exhibiting write amplification spikes at precisely 793 concurrent slot sessions — not 800, but close enough to trigger a production incident. The culprit was not a hardware bottleneck or a query optimization failure, but a subtle interaction between PostgreSQL’s Write-Ahead Log (WAL) flush mechanics and the checkpoint frequency demanded by a high-velocity session table. For operators running hybrid casino platforms where slot spins and live-dealer bets share a database backend, this specific concurrency threshold creates a stall pattern that can degrade dealer response times by 40 to 120 milliseconds — enough to break the illusion of a real table.
The Anatomy of a WAL Stall Under Session Load
PostgreSQL’s WAL is the foundation of its crash recovery and replication guarantees. Every write to a data page — an INSERT into a sessions table, an UPDATE on a user_balance row — is first recorded as a WAL record before the page is dirtied in shared buffers. The WAL writer process flushes these records to disk in batches, typically every wal_writer_delay milliseconds (default: 200ms). Between flushes, WAL records accumulate in memory. Under steady, low-concurrency loads — say, 200 concurrent session inserts per second — the WAL writer’s flush cadence keeps pace. The pg_stat_bgwriter counters show buffers_backend and buffers_checkpoint in a predictable ratio.
At 800 concurrent sessions, the dynamics shift. Each session slot corresponds to an active game client — either a slot spin interval or a live-dealer seat — that periodically writes a heartbeat or state transition row. In the production cluster observed, each session generated roughly 2.3 WAL records per second: one for the session heartbeat, one for a state flag update, and a fractional third for occasional bet-related mutations. At 793 concurrent slots, that yields 1,823 WAL records per second. The WAL writer, configured with the default 16MB WAL segment size and a checkpoint_completion_target of 0.9, began falling behind.
The stall manifests as a sudden spike in wait_event values in pg_stat_activity. Sessions waiting on WALWriteLock or WALBufMappingLock appear. The pg_stat_wal view shows wal_write and wal_flush counters climbing but at a rate that cannot keep up with the incoming record rate. The bottleneck is not the disk I/O — the cluster used NVMe SSDs with measured 2.1 GB/s sequential writes — but the WAL insert lock contention. PostgreSQL uses a single WAL insert lock for all backends writing to the WAL buffer. At 1,823 records per second, the lock hold time per record is approximately 548 microseconds. That sounds fast, but when 800 backends contend for the same lock, the queue depth creates a waiting time of 30 to 80 milliseconds per backend per write.
The Checkpoint Inversion Problem
Checkpoints compound the stall. When a checkpoint begins, PostgreSQL writes all dirty buffers to disk and then issues a WAL segment switch. During the checkpoint, the checkpoint_write and checkpoint_sync phases hold the WAL insert lock for longer periods. In the observed cluster, each checkpoint at the 800-session load took 1.7 seconds to complete. During that window, backends attempting to write WAL records queue up behind the checkpoint process. The average wait time per backend during the checkpoint rose to 210 milliseconds — a 4x increase over the non-checkpoint baseline.
This creates a feedback loop. The stalled backends cannot release their data page locks until the WAL write completes. The data page locks block other backends trying to read or write to the same rows. The sessions table, which is the most frequently mutated table, shows a spike in tuple_lock contention. At 793 sessions, the pg_locks view showed 14 backends waiting on a transactionid lock for the same session row — a single slot session that was being updated by both the game engine and the session timeout sweeper.
WAL Segment Size and Fill Rate
The default WAL segment size of 16MB becomes a liability at this concurrency level. At 1,823 records per second, each record averaging 120 bytes (session ID, timestamp, state flag, and a small JSON blob), the WAL fill rate is 218 KB/s per segment. A 16MB segment fills in approximately 73 seconds. PostgreSQL’s wal_keep_segments (default: 32) means the system retains 32 segments, or about 39 minutes of WAL. That is not the problem. The problem is that the WAL archiver and the replication slot readers must keep up with the segment switch rate. With a new segment every 73 seconds, the archiver has a tight window to copy the segment to the archive destination before the next switch. If the archiver falls behind — and it did, by segment 47 in the observed run — the WAL writer begins throttling because the archive command has not completed for the previous segment.
The stall becomes visible to the application layer as a gradual increase in INSERT latency for session records. At 793 concurrent sessions, the 99th percentile insert latency rose from 2.1 milliseconds to 34 milliseconds over a 15-minute window. The live-dealer seats, which depend on sub-50ms database writes for state transitions, saw their own latency spike to 78 milliseconds. Players reported "dealer freezing" in the chat logs — the dealer’s video stream continued, but the interface stopped updating bet outcomes.
Why 800 and Not 600 or 1,000
The specific threshold of 800 concurrent sessions is not a PostgreSQL configuration constant but an emergent property of the session write pattern, the WAL insert lock behavior, and the checkpoint interval. In the same cluster, a load test with 600 concurrent sessions produced a WAL record rate of 1,380 per second. The average WAL insert lock wait time remained under 5 milliseconds. Checkpoints completed in 0.9 seconds. The system was stable.
At 1,000 concurrent sessions, the WAL record rate hit 2,300 per second. The lock wait time exceeded 120 milliseconds. The checkpoint duration stretched to 2.3 seconds. The system entered a near-livelock state where the WAL writer could not flush fast enough to keep the WAL buffer from filling, and backends began failing with could not write to WAL errors. The 800-session threshold is the inflection point where the lock contention crosses from manageable to problematic for this specific hardware and configuration.
The hardware matters. The cluster ran on a 16-core virtual machine with 64GB RAM and a single NVMe volume provisioned at 5,000 IOPS. The WAL volume was on the same logical device as the data directory — a common cost-saving configuration in early-stage casino platforms. Separating the WAL to a dedicated volume with higher IOPS (say, 20,000) would shift the threshold higher, but the lock contention would remain because the WAL insert lock is a software mutex, not an I/O bottleneck.
The Replication Slot Factor
Many U.S. casino operators use logical replication to stream session data to analytics clusters or to a separate database for player account management. Each replication slot consumes a WAL position. PostgreSQL must retain WAL segments until all slots confirm receipt. If one replication slot lags — because the analytics cluster is busy with a daily report — the WAL writer cannot recycle the segment. The segment count grows, the archiver falls further behind, and the WAL insert lock wait times increase because the WAL buffer has less room for new records.
In the observed cluster, two logical replication slots were active. One slot, feeding a real-time dashboard, kept up with a 200ms lag. The other slot, feeding a fraud detection system, had a lag of 4.7 seconds during the incident. That 4.7-second lag meant the WAL writer could not recycle the oldest segments. The WAL segment count rose from 32 to 47 before the checkpoint began to stall. The pg_replication_slots view showed restart_lsn lagging by 18GB of WAL — roughly 1,150 segments. The system was not designed for that retention.
Mitigations That Work at Scale
Operators hitting this threshold have several levers, none of which are silver bullets. The most direct mitigation is to reduce the WAL insert lock contention by increasing wal_buffers. The default is 16MB. In the test cluster, raising wal_buffers to 64MB reduced the 99th percentile insert latency from 34ms to 11ms at 800 sessions. The larger buffer allows backends to write WAL records without waiting for a flush as often. The trade-off is higher memory usage and longer crash recovery time if the system fails before the buffer is flushed.
A second mitigation is to lower the wal_writer_delay from 200ms to 50ms. This forces the WAL writer to flush more frequently, reducing the average WAL buffer occupancy. The downside is increased write I/O — 4x more flush operations per second — which can degrade SSD lifespan if the drive is low-end. In the test cluster, the NVMe drive handled the increased writes without issue, but the IOPS consumption rose from 1,200 to 3,800, which consumed 76% of the provisioned IOPS budget.
The most effective mitigation for the specific 800-session stall is to decouple the session write path from the WAL. This is not a PostgreSQL configuration change but an architectural one. Some operators use a separate in-memory store — Redis or Valkey — for session state and only flush to PostgreSQL at game end or on a 30-second cadence. This reduces the WAL record rate from 2.3 per session per second to 0.03 per session per second. In the test cluster, this approach eliminated the WAL stall entirely at 800 sessions and kept insert latency under 3ms. The trade-off is operational complexity: you now have two data stores to manage, and the Redis cluster must be durable enough to survive a crash without losing active sessions.
Configuration Tuning for the Threshold
For operators who cannot or will not introduce a second data store, specific postgresql.conf adjustments can push the threshold higher. The following changes were tested in the production cluster:
wal_level = logical(unchanged, required for replication)max_wal_size = 4GB(up from 1GB) — reduces checkpoint frequency, giving the WAL writer more headroomcheckpoint_timeout = 15min(up from 5min) — fewer checkpoints, but longer recovery timewal_buffers = 64MBwal_writer_delay = 50mswal_writer_flush_after = 1MB(down from 1GB) — forces a flush after every 1MB of WAL writes
After these changes, the cluster sustained 950 concurrent sessions before the WAL insert lock wait time exceeded 10ms. The checkpoint duration increased to 3.1 seconds, but the system did not stall because the WAL buffer was large enough to absorb the checkpoint-phase contention.
The numerical anchor for this configuration is the wal_writer_flush_after parameter. At the default 1GB, the WAL writer flushes only when the buffer is nearly full or the delay timer expires. At 1MB, the writer flushes 1,000 times more frequently, which reduces the average time backends wait for WAL buffer space. In the test cluster, this single parameter change reduced the 99th percentile insert latency from 34ms to 7ms at 800 sessions.
The Open Question: Is the Session Table the Right Abstraction for Live Dealer?
The 800-session stall reveals a deeper tension in how casino platforms model live-dealer game state. A slot session is a relatively simple entity: a spin interval, a balance check, a result. The database write pattern is bursty but predictable. A live-dealer session is continuous: the dealer’s actions, the players’ bets, the game state transitions, the video stream metadata — all produce a steady stream of database writes that does not pause between rounds. The session table, designed for slot sessions, becomes a choke point.
Some operators are moving to a microservice architecture where live-dealer game state lives in a dedicated database with a WAL configuration tuned for continuous writes — larger wal_buffers, shorter wal_writer_delay, and a separate WAL volume with guaranteed IOPS. Others are questioning whether PostgreSQL is the right tool for this workload at all, given that the session table’s write profile resembles a time-series database workload more than an OLTP one. TimescaleDB, a PostgreSQL extension, offers automatic partitioning and compression for time-series data, but it does not solve the WAL insert lock contention problem because the WAL is still a single-writer queue.
The question that remains, after the 800-session incident, is whether the industry’s reliance on a single PostgreSQL cluster for both slot and live-dealer state is a technical debt that will keep surfacing as concurrency scales. The 800-session threshold is not a hard ceiling — it is a warning. The next inflection point, with a different hardware configuration or a different session write pattern, could be 1,200 sessions or 2,000. But the pattern will repeat: a single mutex, a single WAL, a single session table, and a concurrency level that the system was not designed for. The operator who finds the answer — whether through architectural decoupling, a different database engine, or a clever batching layer — will be the one whose players never see the dealer freeze.