A Slow Report, a Drifting Counter, and a Duplicate Seat
CityRail's reservation platform runs PostgreSQL 15. The reservations table holds about 40 million rows. The monthly reconciliation report joins five tables, filters on route_id, travel_date, and status, and sorts by travel_date; it takes 14 seconds against a p95 target of 2 seconds. The table has the primary key plus five separate single-column indexes, one per filter column. Separately, the trips table carries a denormalized seats_remaining column that the application decrements in a second transaction after the reservation commits. After the last two failovers it drifted three to nine seats below the true count, and the seat map on popular trips shows a hot row with roughly 400 updates per minute. Finally, during a fare promotion two customers were confirmed for the same seat; the code runs SELECT count(*) for that seat and then inserts.
1. Design the index for the reconciliation report and explain why the five existing single-column indexes do not solve it.
Read the worked answer
One composite B-tree does the work; the five single-column indexes do not.
- Order: equality columns first, then the range/sort column, then remaining filters -
(route_id, travel_date, status). Withroute_idfixed andtravel_dateas a range, the index entries arrive pre-sorted bytravel_date, so the sort node disappears. - Because reports almost always filter on confirmed rows, make it partial:
WHERE status = 'CONFIRMED'withstatusout of the key, which keeps the index small. - Add
INCLUDE (fare_cents, passenger_id)so the scan is covering and avoids heap fetches on the 40M-row table. - Why the existing five fail: the planner can bitmap-AND a few of them, but a bitmap scan returns rows in physical order and cannot satisfy the
ORDER BY, so the sort comes back.statushas few distinct values, so its index is nearly useless alone, and the plan is unstable as statistics drift. - Check for sargability first:
WHERE date(travel_date) = ...would disable the index entirely; rewrite as a half-open range. - Cost: every index taxes writes. Drop the redundant single-column indexes once the composite is in place.
2. The denormalized `seats_remaining` drifts after failovers. Evaluate the options and recommend one, distinguishing data used for display from data used for a booking decision.
Read the worked answer
The drift is structural: the counter is written in a second transaction, so any crash, failover, or rollback between the two leaves it wrong and nothing repairs it.
Options:
- (a) Update the counter in the same transaction as the reservation - consistent, but the popular-trip row becomes a serialization point at 400 updates/minute and risks deadlock if lock ordering differs from other writers.
- (b) A trigger - same contention, plus business logic hidden from the application and from code review.
- (c) Drop it and compute seats taken with
count(*)over the seat-map index for one trip - a few hundred rows, milliseconds, always correct. - (d) Periodic reconciliation - correct only as a repair job, never as the source of truth.
Recommendation: (c) for anything that gates a booking decision, because a derived value must never be the authority for an invariant. Keep a cached count only for search-result pages, with a short TTL, a repair job, and an explicit "approximate" label. Prefer recomputation from the base table over a counter whenever the recomputation is cheap.
3. Two customers received the same seat. Choose between a unique constraint and raising the isolation level to SERIALIZABLE, and justify.
Read the worked answer
Add a unique constraint; keep SERIALIZABLE for cases a constraint cannot express.
- Partial unique index on
(trip_id, seat_no) WHERE status IN ('HELD','CONFIRMED'). It holds regardless of pod count, isolation level, or which new code path someone adds next quarter. The application check becomes purely a UX affordance that produces a friendly message instead of an error. - Handle the duplicate-key error explicitly and translate it to "seat taken, choose another"; an unhandled constraint violation surfacing as a 500 is a bad outcome, not a good one.
- Why not SERIALIZABLE: it would prevent this write skew, but it converts the failure mode into serialization errors on every read-modify-write path, requiring retry logic throughout the application, and it reduces throughput sharply on exactly the hot trips that matter during a promotion. It also protects only operations that actually run inside the elevated transaction, so one forgotten
BEGINreopens the hole. - A constraint is permanent, self-documenting, and costs one index. It makes the impossible state unrepresentable rather than merely unlikely.