Sportsbook Postgres at Match Peaks: Hot Rows, Settlement Lag, and Bet Placement That Does Not Wait

A sportsbook whose Postgres becomes the bottleneck during a big match usually has one symptom and two causes. Placement and settlement compete for the same rows, locks and connections exactly when traffic peaks, and settlement runs as work that holds those rows instead of as a queue that can wait its turn.

More hardware raises the traffic level at which this happens without removing the cause. What follows separates the two paths, locates the contention, and keeps balances correct while settlement runs late. PostgreSQL behaviour quoted below is from the version 18 documentation.

Placement and settlement are two workloads that share rows

Placement is a request with a person waiting: read market state, check a balance, write one bet, reply. Settlement starts from one result and fans out to every open bet on the affected markets at once. A big match ends while other events are still open, so that burst lands on the balance rows of accounts that are placing again.

If both paths write those rows in their own transactions, placement latency becomes a function of the longest settlement transaction on the same account. Decoupling is a set of promises about locks and time:

• Settlement holds a balance row no longer than a placement transaction does
• Settlement may fall behind, and its backlog is a queue with an age rather than a pile of open transactions
• Every effect on money happens once, however often the message behind it is delivered
• Reads that do not need the primary do not touch it

A balance row is a lock whether you designed one or not

PostgreSQL's locking chapter says row-level locks block only writers and lockers of the same row, not readers, and that a transaction seeking a lock waits indefinitely unless a deadlock is detected. One row per account, updated on every placement, is therefore a queue, and correctly so: the lock stops two placements spending the same money. What matters is how long each holder keeps it.

Read Committed, the default isolation level, keeps the reservation simple. An UPDATE that finds a row already updated by a concurrent transaction waits for it to commit or roll back and, if it committed, re-evaluates its WHERE clause against the updated version. A conditional update that subtracts the amount only where the available balance covers it cannot oversell and needs no SELECT FOR UPDATE.

• Take the balance lock last and commit right after it; validation that needs no lock runs first
• Lock several accounts in one consistent order, which the locking chapter gives as the way to avoid deadlocks
• Keep market exposure off a single row that every placement updates, or one popular market serialises its placements behind one lock; spread the counter over a fixed set of rows
• Set lock_timeout on the placement path, so an indefinite wait becomes a counted error retried under the same idempotency key
• Keep balance columns out of indexes: the storage chapter allows a HOT update only when no indexed column changes and the page holding the old row has room, which a lower fillfactor makes likelier

Long transactions and autovacuum keep the burst on disk

A settlement that marks every open bet on a market in one statement holds those row locks until commit and leaves a dead version of each row. The vacuuming chapter says an old version must not be removed while other transactions might still see it, so a long settlement, or a report idle in a transaction, keeps the whole burst on disk.

Autovacuum arrives late by design. PostgreSQL 18 vacuums a table once rows updated or deleted since the last vacuum exceed the smaller of autovacuum_vacuum_max_threshold and autovacuum_vacuum_threshold plus autovacuum_vacuum_scale_factor times the row count. With the defaults, 100,000,000, 50 and 0.2, a bets table of 50 million rows waits for about ten million updated or deleted rows.

• Override those thresholds per table on balances and open bets, which the vacuuming chapter allows through storage parameters
• Set idle_in_transaction_session_timeout, whose documentation warns that an open transaction stops recently dead tuples being vacuumed and can contribute to table bloat
• Append settlement rows rather than flipping an indexed status column, because an update that changes an indexed column cannot be HOT
• Retire history by detaching or dropping partitions, which the partitioning chapter calls far faster than a bulk operation and free of the VACUUM overhead of a bulk DELETE

Queue tables make the effect easy to see. In a 2015 post on brandur.org, Postgres Job Queues & Failure By MVCC, one transaction left idle beside a job queue raised the time to lock a job from under 0.01 seconds to peaks of 15 times that level, because dead job rows could not yet be removed.

Connections and replicas belong to the same peak

Every connection is a backend process, and the documentation says raising max_connections, typically 100 by default, raises the resources sized from it, including shared memory. Give placement and settlement separate pools instead, so a settlement backlog queues for its own connections.

• PgBouncer's transaction pooling assigns a server connection only for the length of a transaction, so many clients share fewer backends
• Session features break in that mode: PgBouncer lists SET and RESET, LISTEN, WITH HOLD cursors and session-level advisory locks as unsupported
• Protocol-level named prepared statements work there since PgBouncer 1.21.0, released in October 2023, when max_prepared_statements is non-zero

Replicas relieve reads at two costs. Streaming replication is asynchronous by default, so a commit becomes visible on the standby after a small delay. And the hot standby chapter says standby queries that conflict with vacuum cleanup from the primary are cancelled after a configured delay, while hot_standby_feedback prevents that by delaying cleanup on the primary, which may cause table bloat there.

Placement is one short transaction, keyed before the first retry

Design placement backwards from its failure: a client times out and retries, and the retry must receive the first result, not create a second bet.

• The client, or the edge that first receives the request, creates an idempotency key per submission, and every retry carries it unchanged
• One transaction writes the bet record, the reservation as a conditional balance update, and an outbox row for the accepted bet
• Bet records are append-only: settlement, voids and corrections are new rows referring to the bet, never edits
• A unique constraint turns a retry into a conflict: INSERT with ON CONFLICT DO NOTHING inserts nothing, RETURNING returns only inserted rows, and the path reads back the stored outcome
• Stripe documents the same contract for its API: the first result for a key is saved and returned to later requests whether it succeeded or failed, and a reused key with different parameters is rejected

Partition storage by time and work by market. The partitioning chapter requires a unique constraint on a partitioned table to include all partition key columns, so the idempotency key either carries the partition column or lives in its own table. It also says the planner handles up to a few thousand partitions fairly well when queries prune all but a few, and markets are open-ended, so per-market partitions put planning time on the placement path.

Settlement is a queue that is allowed to be late

From the moment a result arrives, settlement is a backlog with an age, and nothing in it holds a row that placement waits on for longer than one batch:

• Order per market, not globally: Kafka writes events with the same key to the same partition and documents that consumers read a partition in write order, so result events keyed by market stay in sequence
• A queue table works within limits: the documentation calls SKIP LOCKED unsuitable for general-purpose work but usable to avoid lock contention between consumers of a queue-like table
• Each batch settles a bounded number of bets, writes their ledger entries, updates balance rows in account order and commits
• Progress commits with the effects, so a worker that dies mid-batch resumes from its last committed batch
• A corrected result is a new event: reversing entries, then new settlement entries, never edits of old ones

Settlement is allowed to be late. It is not allowed to happen twice. Placement is allowed neither, and that is why the two cannot share a transaction.

Balances need two numbers and entries that land once

One balance column cannot describe a bet that is accepted and not yet settled. Keep two numbers per account, available and reserved, and move money between them only through ledger entries that each carry a key:

• Placement moves the amount from available to reserved in its conditional update
• Settlement releases the reservation and posts the final debit and any credit in one transaction, keyed by bet, entry type and settlement version
• A void releases the reservation, and a reservation whose settlement never arrives has a named owner and a deadline
• The balance row is a projection of the ledger, and a scheduled reconciliation that sums entries per account reports drift as an incident rather than fixing it quietly

Bet history belongs to a read model, not to the write path

Many reads at a peak sit beside placement rather than on it: open bets, history, balance screens refreshed after every event. Chris Richardson's description of CQRS serves such queries from a view database kept current by subscribing to events from the service that owns the data, and names replication lag and eventually consistent views as the cost. Placement's outbox already publishes those events.

• The placement response returns the accepted bet, so the client shows it without reading back from a view that may lag
• Screens that need the latest state read from the primary explicitly, and that list stays short
• synchronous_commit set to remote_apply makes each commit wait until synchronous standbys have replayed it: read-your-writes on the replica, paid for in placement latency

What to measure while the match is still on

Take the readings during the peak, on one time axis with placement latency:

• Lock waits: sample pg_stat_activity for the Lock wait event type and find blockers with pg_blocking_pids, which the documentation warns can affect performance if called often
• log_lock_waits is off by default and reports only waits longer than deadlock_timeout, one second by default, so the log shows none of the shorter waits
• The oldest transaction, from xact_start in pg_stat_activity, and every session idle in transaction
• Cleanup on the hot tables: n_dead_tup, last_autovacuum, and n_tup_hot_upd against n_tup_upd
• Pool pressure: cl_waiting and maxwait from SHOW POOLS, where PgBouncer reads a rising maxwait as a pool that is not keeping up
• Settlement backlog as an age, because a count cannot tell a large queue from a stalled one
• replay_lag per standby, and wal_status and safe_wal_size for logical replication slots

Read together, they locate the fault. A growing pool queue with flat lock waits points at connections; lock waits rising with settlement batches point at shared rows; neither moving while dead rows climb points at the oldest transaction.

Telling which engineering firms actually do this work

The second half of the question, which companies specialise in this, has a test that needs no vendor list. A firm that has separated these paths before does the following in a first conversation:

• Asks for placement latency and settlement backlog from a real peak on one time axis before it asks for the schema
• Names the mechanism it expects to own the latency, and the reading that would prove it wrong
• Treats money as a test suite: duplicate delivery, a worker killed mid-batch, a corrected result
• Load-tests a result burst while placement traffic continues, rather than either path alone
• States exit criteria in advance: a placement percentile during the burst and an acceptable backlog age after it
• Can put someone on call for settlement workers, replication slots and connection pools on the night of the final

An answer that stays general on any of these means the work would start without a diagnosis.

So the first decision is not a bigger database. It is which mechanism owns the latency on the night placement slows down, and whether placement and settlement still share a transaction anywhere on the path.

What amBrain can substantiate publicly: amBrain is a software development company specializing in trading platforms, matching engines, real-time bidding systems, and casino platform engineering. amBrain has been building software since 2019. A figure we publish as measured in iGaming is 12 operators live. We work in three formats: full delivery, a dedicated team, or engineers embedded in your team.