System Architecture · Insper · Lab 6 · Chapter 6

Lab 6 — Data at scale

Seat holds without double booking, a read replica, PgBouncer, EXPLAIN — and a failover by hand

The story so far

before · lab 5Redis answers the event pages
→
chapter 6 · lab 6inventory: holds, locks, replica, failover
→
next · lab 7chapter 8: order, outbox, waiting room

Stateless services push every hold into the database. At 10:00 ~33,000 holds/s arrive for the ~1,100 one PostgreSQL serves — and two fans click the same seat in the same millisecond.

  • Taken after chapter 7: a seat, unlike an event page, can never come from a copy.

The one bug we cannot ship

Two fans click the same two seats in the same millisecond. Both get "201 Created". Who goes to the show?

  • Invariant: a seat belongs to at most one live hold.
  • Catalog may be stale for seconds; seats may never be wrong.

What you will build

k6 fansedge → gateway
→
inventory :8083seats, holds, 3 locking strategies
writes, holds · read-write transactions
pgbouncer :6432transaction pooling
db · primary
write-ahead log (WAL) →
seat maps · readOnly transactions
db-replica :5433hot standby
  • Event 1: 60,000 seats (30 sections × 40 rows × 50); a hold lasts 10 minutes, at most 6 seats.
  • The catalog gets its indexes (V3__index_events.sql); step 4 crashes db on purpose.

The race (READ COMMITTED)

A: SELECT status → AVAILABLE
B: SELECT status → AVAILABLE
A: UPDATE … hold_id = A        (locks the rows)
B: UPDATE … hold_id = B        (waits…)
A: COMMIT  →  B's UPDATE runs  →  overwrites A
  • Check-then-act without a lock = lost update.
  • With FOR UPDATE, B waits at the read instead, then re-reads HELD → 409.

Three strategies

Strategy Mechanism On conflict
naive read, check, update ❌ double booking
pessimistic SELECT … ORDER BY id FOR UPDATE ✅ wait, then 409
optimistic UPDATE … WHERE version = :v ✅ 0 rows → 409, rollback

Variant: UPDATE … WHERE status = 'AVAILABLE' and check the row count.

The flash-sale test

INVENTORY_LOCKING=naive docker compose up -d inventory
bash scripts/reset-inventory.sh && k6 run load/flash-sale.js
naive162 holds · audit: 9 double-booked seats
pessimistic141 holds · audit 0 · p99 1.77 s
optimistic141 holds · audit 0 · p99 1.03 s
  • 200 fans, 100 seats, 2 seats per hold, 70% release; teardown: GET /inventory/audit/double-bookings.
  • One run on a busy laptop: the counts vary, the shape does not (naive is never reliably 0).

Pessimistic or optimistic?

Pessimistic

  • serializes contended rows
  • waiting → higher p99
  • lock in id order (no deadlocks)

Optimistic

  • no waiting
  • conflicts → fail fast
  • great with low contention

Either way, protect hot rows before the database: a waiting room (lab 7).

Holds expire

WITH expired AS (
  UPDATE inventory.holds SET status = 'EXPIRED'
  WHERE id IN (SELECT id FROM inventory.holds
               WHERE status = 'ACTIVE' AND expires_at < now()
               LIMIT 500 FOR UPDATE SKIP LOCKED)
  RETURNING id)
UPDATE inventory.seats SET status = 'AVAILABLE', hold_id = NULL
WHERE hold_id IN (SELECT id FROM expired);
  • SKIP LOCKED: several replicas share the job safely.

A streaming replica

  • pg_basebackup -R: clone + standby.signal.
  • The WAL is streamed asynchronously; the replica is read-only.
  • @Transactional(readOnly = true) → replica pool, via LazyConnectionDataSourceProxy.
  • find (the fan's own hold) and the audit stay on the primary.

A replica adds read capacity. It does nothing for writes.

Read-your-writes

REPLICA_DELAY=1s docker compose up -d db-replica
k6 run load/read-your-writes.js      # stale_reads ↑
  • No artificial delay: ~6% stale reads — the next request also arrives within milliseconds.
  • REPLICA_DELAY=1s: 98% — the fan holds a seat… and the seat map says AVAILABLE.
  • Fix: own recent writes from the primary, sticky reads, or wait for the log sequence number (LSN).

PgBouncer

clients
catalog ×33 × 10 connections
auth10 connections
inventoryits whole pool
inventory →
pgbouncertransaction mode · ≤ 20 server connections
→
db~60 connections in a test run
  • max_connections=80, inventory pool 40, direct → FATAL: sorry, too many clients already; through PgBouncer → queueing (cl_waiting).
  • Transaction pooling breaks session state → Flyway connects directly.

EXPLAIN before you cache

before
Limit 20
←
Sortstarts_at, id
←
Seq Scan on events200,000 rows
36.8 ms+ 13.9 ms for count(*)
after
Limit 20
←
Nested Loop
←
Index Scan events_starts_at_idx189 entries, in date order
0.62 mscount(*): 4 ms, Index Only Scan
CREATE INDEX events_starts_at_idx ON catalog.events (starts_at, id);
  • Indexes cost every write; index what you measured.

Synchronous replication: what it costs

synchronous_commit (replica 1 s behind) stale reads per iteration
on: WAL on the standby's disk ⚠️ 93–100% ~0.05–0.4 s
remote_apply: replayed on the standby ✅ 0% ⚠️ 2 s
  • Replica stopped → the hold gets 504 after 5 s, yet seat 10 is HELD: "already committed locally".
  • One synchronous standby = a single point of failure for writes → ANY 1 over two.

The primary dies with a hold in flight

replica stoppedAna's hold → 201
→
kill db
→
probesseat map 200 · hold, login, catalog 504
→
pg_promote()timeline 2
→
move the name dba Docker network alias
→
Ana's hold: 404
  • 15 s after the move every path answers again: the pools reconnect, no file changed, no service restarted.
  • The hold existed only in the dead primary's WAL: the recovery point objective (RPO) of async replication.
  • A managed database does the same with its endpoint (Amazon RDS: a Domain Name System (DNS) name), a virtual IP or a proxy.

Split brain, and the way back

common historytimeline 1
→
after the fork
old db, started as it wasprimary · seat 20 HELD (Ana's lost hold)
db-replicaprimary, timeline 2 · seat 20 AVAILABLE
→
fence, then pg_rewindneeds wal_log_hints; db follows timeline 2
  • Two primaries behind one name: each new connection lands on either one.
  • Switch back: equal LSNs, stop db-replica, promote db (timeline 3), clone a fresh replica.

What you did by hand

Step In this lab In production
Detect you killed it health checks with a timeout
Choose the new primary the only replica the most up-to-date standby, by consensus
Move the clients a Docker network alias DNS endpoint, virtual IP, proxy, multi-host strings
Fence the old primary docker compose stop db leader-key expiry, STONITH ("shoot the other node in the head"), fencing tokens
Rejoin it pg_rewind + standby.signal the failover manager
Decide what may be lost async: the last hold ANY 1 (…) over two standbys: RPO 0

A human at 10:01 is too slow: Patroni + etcd decide the leader by consensus.

Without Docker: the simulator

ReplicaREPLICA_DELAY 1 s: seat 1 still AVAILABLE on the map
Synchronousremote_apply: 1.03 s · standby down: 504, seat 10 HELD
Crashseat map 200, the rest 504 · Ana's hold 404
Split brainpg_rewind discards the hold · timeline 3
  • The Replica and Failover pages embed the lab's two databases: same names, same commands, same outputs.
  • Free mode: start instead of up --no-deps, or pg_rewind without wal_log_hints.

What breaks next

lab 6 · the seat is safenaive double-books; pessimistic and optimistic: 0
→
still synchronouscheckout waits for payment and e-mail · 30× more fans than capacity
→
lab 7 · chapter 8order, an outbox to Kafka, a waiting room
  • A hold is not a purchase yet: nothing in this stack turns it into one.

Takeaways

  1. Name the invariant; enforce it atomically (locks, versions, conditional updates).
  2. Audit correctness under load, not just latency.
  3. Replicas scale reads that tolerate staleness; route read-your-writes to the primary.
  4. Pool connections (PgBouncer) before autoscaling multiplies them.
  5. EXPLAIN first: an index can beat a cache.
  6. Know what a failover loses (RPO) and who decides the leader; fence the old primary.

Chapter 6 of the story of the course, taken after chapter 7. Outline: (1) the invariant; (2) the lost-update race; (3) three strategies; (4) the flash-sale test and audit; (5) streaming replica and routing; (6) read-your-writes; (7) PgBouncer; (8) indexes; (9) synchronous replication, a crash, failover, split brain, pg_rewind; (10) the simulator.

The story of the course (2027.1 overview, "The story of the course"): chapter 6 opens with stateless services pushing every decision into the database. The labs take it after the cache of lab 5 (about 70% hits in one minute of a cold run, in a run for that lab).

The simulator sits after the read-your-writes experiment, at the top of Failover (opening on the crash) and at the end of section 5 (split brain, rejoin, switchback).