Skip to content

a. Overview

In this lab

  • Build inventory, the strongly consistent heart of BoxOffice: seats and holds, where one seat can never belong to two fans.
  • Compare three concurrency strategies under a flash sale β€” naive check-then-act, pessimistic locking (SELECT … FOR UPDATE) and optimistic locking (version column) β€” and prove which ones double-book2.
  • Add a read replica with streaming replication, route read-only transactions to it, and observe replication lag breaking read-your-writes.
  • Put PgBouncer between the services and PostgreSQL, and use EXPLAIN ANALYZE + indexes to fix the slow catalog listing.
  • Make replication synchronous and pay for it, crash the primary with a hold in flight, fail over by hand, provoke a split brain and rejoin the old primary with pg_rewind.
  • Measure each change: conflicts, double bookings, p99, lag, connections, query time, lost writes.

The flash sale is the moment BoxOffice exists for: at 10:00, 60,000 seats and two million fans. The catalog could be cached because a few seconds of staleness is harmless. Seats are the opposite: selling the same seat twice is the one bug a ticketing company cannot afford. This lab is about the data layer under pressure1 β€” correctness first (transactions and locks), then scale (replicas, pooling, indexes), then survival (failover) β€” and about the trade-offs between them.

This lab implements the decisions of class 6: one seat, one hold with locks or a version check, a read replica and its lag, connection pooling with PgBouncer, indexes read with EXPLAIN, and a failover with its lost writes and split brain.

The story so far

Chapter 6 of the story of the course, taken in the labs after chapter 7. Lab 5 made the read path cheap: Redis answers the event pages (a cold cache reached about 70% hits in one minute, in a run for that lab) and PostgreSQL sees only the misses. Writes cannot be cached that way. Stateless services push every hold into the database, two fans click the same seat in the same millisecond, and at 10:00 about 33,000 holds/s arrive for the ~1,100 that one PostgreSQL serves (class 6 β€” Databases). This lab builds inventory, a read replica, PgBouncer, an index and a failover, and measures the double bookings of each locking strategy, the stale reads that replication lag causes, the connections a pooler saves, the listing query before and after the index, and the holds a failover loses.

Before you start

Time 5–6 hours (four parts: do one per sitting if needed)
You need the tools of lab 0; Docker with 8 GB (eleven containers, six Java virtual machines, JVMs); ports 80, 8080, 8083, 5432, 5433, 6432, 6379 free
Directory every command runs from the root of your boxoffice/ repository
docker compose version        # Docker Compose v2.x (or newer)
java -version                 # 21 or newer
mvn -v                        # Apache Maven 3.9+
k6 version                    # k6 v1.0+
uv --version                  # uv (runs the Python helper scripts)

Start state β€” the end of lab 5:

boxoffice/ at the start of lab 6
πŸ“ boxoffice/
β”œβ”€β”€ YAML compose.yaml               # db, redis, catalog-1..3, auth, gateway, edge
β”œβ”€β”€  infra/nginx/nginx.conf
β”œβ”€β”€ πŸ“ load/ ...  scripts/ ...  results/ ...
└── πŸ“ services/auth/  services/catalog/ (with the cache)  services/gateway/

Catch up

Skipped or broke lab 5? Download boxoffice-after-lab-5.zip, unzip it and cd boxoffice. This lab adds services/inventory, infra/postgres/*, infra/pgbouncer/*, the catalog migration V3__index_events.sql, four k6 files and scripts/reset-inventory.sh, and infra/postgres/rejoin-as-replica.sh (step 4), and replaces compose.yaml and the gateway's application.yaml and SecurityConfig.java; all are shown in full on the next pages and are in code/. It also needs a fresh database volume (docker compose down -v, explained in step 2).

Slides

Click the slides and use ← β†’ to move through them; F goes full screen and P opens the presenter view, with the notes. Open the slides in a new tab

Theory connection

Before you measure: the playground

Try it: the db-replicas scenario has a primary and read replicas, with reads and writes split. Run it, then raise the share of writes: what happens to the primary while the replicas stay idle? Increase the replication lag and watch the "stale reads" indicator. Write down which kind of traffic replicas help with β€” and which they cannot help with at all.

Now the lab's own pair, boxoffice-read-your-writes: holds on the primary, "my holds" read back from a replica with REPLICA_DELAY=1s, and the primary dying at t = 90 s. Before you build it, predict the share of stale reads and how many holds the failover loses; run it, then try Reads from = primary and replication sync, and note what each fix costs. Steps 2 and 4 measure the same trade-off on the real stack.

Then look at the whole sale in flash-sale: the waiting room, inventory in SQL, payment. Lab 7 builds the waiting room; today you build the inventory box.

What you will build

flowchart LR
    k6([k6 fans]) --> edge[edge] --> gw[gateway]
    gw -->|"/catalog/**"| cat["catalog Γ—3"]
    gw -->|"/inventory/**"| inv["inventory :8083"]
    inv -->|"writes, holds<br/>(read-write tx)"| pgb["pgbouncer :6432"]
    pgb --> db[("db (primary)")]
    inv -->|"seat maps, availability<br/>(readOnly tx)"| rep[("db-replica")]
    db -->|"WAL streaming"| rep
    cat --> db
    cat --> redis[(redis)]
Method Path Auth Database Answer
GET /inventory/events/{eventId}/availability open replica seats per section: available, held, sold
GET /inventory/events/{eventId}/seats?section=S01 open replica the seat map of one section
POST /inventory/holds {eventId, seatIds} token primary 201 hold (10 min), 409 if any seat is taken
GET /inventory/holds/{id} token (owner) primary the hold
POST /inventory/holds/{id}/confirm token (owner) primary seats become SOLD (called by order in lab 7)
DELETE /inventory/holds/{id} token (owner) primary release the seats
GET /inventory/audit/double-bookings token primary {"doubleBookedSeats": 0} β€” the invariant

Event 1 has 60,000 seats (30 sections Γ— 40 rows Γ— 50 seats); events 2–100 have 500 seats each.

Steps

  • 1. Holds and locking


    The inventory service, three locking strategies, the flash-sale test and the double-booking audit.

  • 2. Replica and pooling


    Streaming replication, routing readOnly transactions, replication lag and read-your-writes, PgBouncer.

  • 3. Indexes and EXPLAIN


    Read a query plan, add the right indexes to the catalog listing, measure before and after.

  • 4. Failover


    Synchronous replication and its cost, a crash with a hold in flight, failover by moving a name, split brain, pg_rewind.

Measure it

Each step has its own experiment; collect the results in results/lab6.md:

results/lab6.md
# Lab 6 β€” data at scale

## 1. Flash sale: 200 fans, 100 seats, 30 s (load/flash-sale.js)
| INVENTORY_LOCKING | holds 201 | holds 409 | p50 hold (ms) | p99 hold (ms) | double-booked seats (audit) |
|---|---:|---:|---:|---:|---:|
| naive       | | | | | |
| pessimistic | | | | | |
| optimistic  | | | | | |

## 2. Reads: availability + seat maps at 200 req/s (load/availability.js)
| Reads served by | p50 (ms) | p99 (ms) | CPU db (%) | CPU db-replica (%) |
|---|---:|---:|---:|---:|
| primary (DB_REPLICA_HOST=db) | | | | |
| replica | | | | |

## 3. Read-your-writes (load/read-your-writes.js)
| REPLICA_DELAY | stale_reads (%) |
|---|---:|
| 0  | |
| 1s | |

## 4. Connections
| Setup | connections in pg_stat_activity during the flash sale |
|---|---:|
| inventory β†’ db directly (INVENTORY_DB_HOST=db, PORT=5432) | |
| inventory β†’ pgbouncer | |
PgBouncer SHOW POOLS: cl_active ____ , sv_active ____ , cl_waiting ____

## 5. Catalog listing before/after the indexes (EXPLAIN ANALYZE + sweep)
| | Execution time (ms) | Plan (main node) | Capacity with p99 < 300 ms (req/s) |
|---|---:|---|---:|
| before (V2) | | | |
| after (V3)  | | | |

## 6. Failover (step 4)
| synchronous_commit (REPLICA_DELAY=1s) | stale_reads (%) | iteration_duration avg |
|---|---:|---:|
| on           | | |
| remote_apply | | |
Replica stopped, synchronous: hold HTTP status ____ , seat 10 afterwards ____
Crash: Ana's hold after the failover (HTTP status) ____ ; seconds until holds worked again ____
Split brain: seat 20 on db ____ , on db-replica ____

## Conclusions
- Which locking strategy would you ship for the flash sale, and why?
- Which reads may go to the replica, and which must not?
- Cache (lab 5) or index (lab 6) for the listing β€” or both? Justify with your numbers.
- Should holds be replicated synchronously? Give the RPO and the cost of each choice.

If your numbers differ

Correctness results must match everywhere: naive ends with double bookings (how many varies run to run β€” 9 and 11 in runs for this lab; if you get 0, run it again or raise VUS), pessimistic and optimistic always end with 0. Latencies, stale-read percentages and connection counts depend on your machine; compare them between your own runs only.

Troubleshooting

Symptom Cause Fix
db-replica exits with permission denied on replica-entrypoint.sh the script is not executable chmod +x infra/postgres/*.sh and docker compose up -d db-replica
db-replica logs no pg_hba.conf entry for replication connection the primary's volume is older than this lab, so 10-replication.sh never ran docker compose down -v and docker compose up --build -d (Flyway seeds everything again)
db-replica exits: max_connections ... must be at least the primary's value DB_MAX_CONNECTIONS on the primary is above 100 keep it ≀ 100, or also raise it on the replica
inventory logs password authentication failed via PgBouncer userlist.txt does not match DB_PASSWORD keep both at boxoffice (or update both)
prepared statement "S_1" already exists a PgBouncer without max_prepared_statements (older image or edited ini) use edoburu/pgbouncer:v1.26.0-p0 and the ini of this page
503/404 on /inventory/** through the edge the gateway was not rebuilt with the new route docker compose up --build -d gateway, then curl -s localhost:8080/actuator/gateway/routes \| jq -r '.[].route_id' must list inventory
holds return 401 no token (holds are protected) log in and pass -H "Authorization: Bearer $TOKEN"
ALTER SYSTEM SET synchronous_standby_names fails with syntax error at or near "-" the hyphenated name is not quoted 'FIRST 1 ("db-replica")': double quotes around the name, single quotes around the value
writes hang, pg_stat_activity shows wait_event = SyncRep synchronous replication is on and the replica is down start the replica, or ALTER SYSTEM RESET synchronous_standby_names + SELECT pg_reload_conf()
after docker compose start db-replica, db is running again db-replica depends on db, so start brings it up stop db again before promoting; use docker compose up -d --no-deps db-replica
pg_rewind: target server needs to use either data checksums or "wal_log_hints = on" wal_log_hints was off when the old primary ran (section 1 skipped) the old primary cannot rejoin; start the cluster over with docker compose down -v and docker compose up --build -d (Flyway seeds everything again), then redo step 4 from section 1
writes fail with cannot execute INSERT in a read-only transaction after the failover db resolves to the rejoined standby and to the new primary finish the switchback
db-replica stays unhealthy after a fresh clone, log says waiting for checkpoint an old replica-entrypoint.sh without -c fast update the script and docker compose up -d --force-recreate db-replica
containers killed (exit 137), very slow startup not enough memory for eleven containers give Docker 8 GB, close other programs, or stop catalog-2/catalog-3 during the inventory experiments

Best practices in this lab

  • Decide in the database, not in the application β€” SELECT … FOR UPDATE in ORDER BY id, or a version compare-and-set, in HoldService.java; see Transactions β€” the double-booking bug.
  • Send reads that may be stale to the replica, and everything else to the primary β€” @Transactional(readOnly = true) routed by DataSourceConfig.java; see Replication β€” lag and its anomalies.
  • Pool connections in front of PostgreSQL, and migrate around the pooler β€” pgbouncer.ini in transaction mode, DB_MIGRATION_HOST for Flyway in inventory's application.yaml; see Replication (connection pooling).
  • Add an index for a measured query, checked with EXPLAIN ANALYZE β€” V3__index_events.sql; see Data models & storage engines (reading EXPLAIN).
  • Know what a failover loses, and fence the old primary β€” asynchronous replication lost a hold that had answered 201; pg_rewind (rejoin-as-replica.sh) is the only way the old primary comes back; see Replication β€” failover.
  • Audit the invariant after every test β€” SeatQueries.doubleBookedSeats behind /inventory/audit/double-bookings; see Transactions β€” seat holds.

Clean up

REPLICA_DELAY=0 docker compose up -d db-replica             # if you left the artificial lag on
INVENTORY_LOCKING=pessimistic docker compose up -d inventory
bash scripts/reset-inventory.sh                             # all seats back on sale
docker compose down                                         # keep the volumes for lab 7

Keep everything: lab 7 adds order and Kafka on top of this stack and confirms holds through inventory.

Deliverable

  • services/inventory running behind the gateway; the gateway routes /inventory/** (GETs on /inventory/events/** open, holds protected).
  • The flash-sale table with the three strategies, including the audit result of each: at least one run of naive with double bookings > 0, and pessimistic/optimistic with 0.
  • db-replica streaming (pg_stat_replication shows it), reads routed to it, and the read-your-writes experiment with and without REPLICA_DELAY.
  • PgBouncer in front of the primary, with the connection counts.
  • EXPLAIN ANALYZE of the catalog listing before and after V3__index_events.sql, and the sweep after.
  • The failover of step 4: the synchronous-replication table, the lost hold, the split brain, and the stack back with db as primary and db-replica streaming.
  • results/lab6.md complete with the four conclusions.
  • Repository tagged lab6; quiz answered.

End state

changed in lab 6
πŸ“ boxoffice/
β”œβ”€β”€ YAML compose.yaml               # replaced: + db-replica (5433), + pgbouncer (6432), + inventory (8083)
β”œβ”€β”€ GNU Bash infra/postgres/10-replication.sh  infra/postgres/replica-entrypoint.sh     # new
β”œβ”€β”€ GNU Bash infra/postgres/rejoin-as-replica.sh                                       # new: pg_rewind after a failover
β”œβ”€β”€  infra/pgbouncer/pgbouncer.ini  infra/pgbouncer/userlist.txt               # new
β”œβ”€β”€ JavaScript load/lib.js  load/flash-sale.js  load/availability.js  load/read-your-writes.js   # new
β”œβ”€β”€ GNU Bash scripts/reset-inventory.sh                                                # new
β”œβ”€β”€ πŸ“ services/inventory/        # new: seats, holds, three locking strategies, replica routing
β”œβ”€β”€  services/catalog/src/main/resources/db/migration/V3__index_events.sql    # new
β”œβ”€β”€ YAML services/gateway/src/main/resources/application.yaml                     # replaced: + /inventory/** route
β”œβ”€β”€  services/gateway/src/main/java/boxoffice/gateway/SecurityConfig.java     # replaced: GET /inventory/events/** open
└── Markdown results/lab6.md

What breaks next

The seat is safe: naive sold seats twice (9 and 11 double bookings in runs for this lab), while pessimistic and optimistic always ended the audit at 0. But a hold is not a purchase yet, and nothing in this stack turns it into one. Checkout needs an order, a payment and a confirmation e-mail: called inside the request, the fan waits for the slowest of them, and an e-mail outage becomes an ordering outage. Locks also keep the seat safe, not the database alive: at the 10:00 on-sale 30 times more fans arrive than one PostgreSQL can serve, and they need a fair line instead of a race for row locks. Lab 7 β€” Async builds order, which confirms holds through inventory, publishes its events through a transactional outbox to Kafka and admits the crowd through a waiting room: class 8 β€” Messaging.


  1. Martin Kleppmann and Chris Riccomini, Designing Data-Intensive Applications, 2nd ed., O'Reilly β€” chapters on transactions and replication. ↩

  2. PostgreSQL documentation, Explicit Locking, Log-Shipping Standby Servers and Using EXPLAIN. ↩