System Architecture · Insper · Lab 6

Lab 6 — Data at scale

Seat holds without double booking, a read replica, PgBouncer and EXPLAIN

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.

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.

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
  • 200 fans, 100 seats, 2 seats per hold, 70% release.
  • Teardown: GET /inventory/audit/double-bookings.

Pessimistic and optimistic: 0. Naive: not 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.
  • WAL streamed asynchronously; replica is read-only.
  • @Transactional(readOnly = true) → replica pool, via LazyConnectionDataSourceProxy.

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 ↑
  • The fan holds a seat… and the seat map says AVAILABLE.
  • Fix: own recent writes from the primary, sticky reads, or wait for the LSN.

PgBouncer

Client connections Server connections
no pooler 200 200 processes
PgBouncer (transaction mode) 200 ≤ default_pool_size
  • Breaks session state → Flyway connects directly.
  • Over the limit: clients queue instead of "too many clients".

EXPLAIN before you cache

Limit → Sort → Hash Join → Seq Scan on events (200,000 rows)   ≈ 40 ms

CREATE INDEX events_starts_at_idx ON catalog.events (starts_at, id);
  • Index delivers the order → stop after 20 rows → < 1 ms.
  • 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 → every write hangs; Ctrl+C: "already committed locally".
  • One synchronous standby = a single point of failure for writes → ANY 1 over two.

Failover by hand

  1. Ana's hold answers 201 → docker compose kill db.
  2. pg_promote() on the replica: a new timeline.
  3. Move the name: Docker network alias db → the new primary (like an RDS endpoint).
  4. 15 s later writes work again… and Ana's hold is 404: the RPO of async.
  5. Old primary back as it was → two primaries, one name: split brain → fence it.
  6. pg_rewind (needs wal_log_hints) → it rejoins as a replica; then switch back.

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

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.

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.