a. Overview

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 pressure¹ — correctness first (transactions and locks), then scale (replicas, pooling, indexes), then survival (failover) — and about the trade-offs between them.

This lab builds chapter 6 of the story of the course, the database that every stateless service now depends on. It 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.

Before you start

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/
├──  docker-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/

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

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.

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)]

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

Steps

Measure it

Each step has its own experiment; collect the results in 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.

Troubleshooting

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

📁 boxoffice/
├──  docker-compose.yaml        # replaced: + db-replica (5433), + pgbouncer (6432), + inventory (8083)
├──  infra/postgres/10-replication.sh  infra/postgres/replica-entrypoint.sh     # new
├──  infra/postgres/rejoin-as-replica.sh                                       # new: pg_rewind after a failover
├──  infra/pgbouncer/pgbouncer.ini  infra/pgbouncer/userlist.txt               # new
├──  load/lib.js  load/flash-sale.js  load/availability.js  load/read-your-writes.js   # new
├──  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
├──  services/gateway/src/main/resources/application.yaml                     # replaced: + /inventory/** route
├──  services/gateway/src/main/java/boxoffice/gateway/SecurityConfig.java     # replaced: GET /inventory/events/** open
└──  results/lab6.md

Next: Lab 7 — Async, where order confirms holds through events.