How to design a hotel-reservation system

A hotel booking system enforces one invariant under concurrency: a room-night is sold exactly once. At single-digit writes/s this is a correctness problem, not a scale problem; every mechanism exists to make the decision and the write one step.

Sizing (why no sharding)

• 5,000 hotels x 200 rooms = 1M rooms; 70% occupancy, 3-night stay -> ~233K bookings/day = ~8 writes/s peak.
• Reads ~3,241/s peak -> 400:1 read:write. Reads may be stale; writes may not be wrong.
• Inventory table: (hotel, room type, date) grain, ~12.5M rows, ~900 MB -> fits in RAM. Reservations ~128 GB/5yr (partition by month).
• One Postgres primary handles thousands of writes/s: ~1,000x headroom. No shard/queue/NoSQL argument. If you must shard, shard on hotel_id (every txn touches one hotel).

Unit and interval

• Inventory unit is (hotel, room type, date), not physical room; hotel assigns the physical room at check-in.
• Every range is half-open [check_in, check_out): check-in night sold, checkout night not. 14th->17th = 3 nights.
• Predicate is always date >= check_in AND date < check_out. BETWEEN matches nights+1 rows and sells the checkout night twice with no concurrency involved.
• Guard the nights() function: reject check_out <= check_in, else a zero-night stay passes rows != wanted (0 != 0 is false) and charges the card for no inventory.

The double-booking race

• Default isolation READ COMMITTED permits the lost update: two txns both SELECT remaining=1, both UPDATE ... SET total_reserved = 11 (value computed in the app), both commit -> oversold.
• Root cause: the decision (read) and the write were not the same step. Any fix that re-unites them works.
• Isolation levels vs this trace:

Three fixes + the backstop (ship all four)

• Ceiling per row = 1/T; a booking txn T ≈ 5 ms -> 200 bookings/s per row. Hottest row runs at 0.056/s (~3,600x headroom), so choose on failure behavior, not throughput.

• Default to the conditional UPDATE: SET total_reserved = total_reserved + 1 WHERE ... AND total_reserved < total_inventory. Ranged form covers all N nights; rowcount != nights means a night sold out, rollback releases the rest.
• Use FOR UPDATE only when the decision reads other tables the UPDATE can’t mention; add ORDER BY date to avoid deadlock.
• Optimistic beats pessimistic under light load; inverts under a flash sale (switch that row to locking).
• Keep the CHECK (total_reserved <= total_inventory) in the schema forever; it’s the only guard against code you haven’t written.

Idempotency + payment (no double charge)

• A retried request is indistinguishable from a second booking. Exactly-once delivery doesn’t exist; use at-least-once + a client-chosen dedup key.
• R1: key is a canonical hash of request content, excluding volatile fields (timestamp, attempt, trace id).
• R2: namespace per operation (key:auth, key:capture, key:refund).
• R3: claim in one statement, INSERT ... ON CONFLICT DO NOTHING; rowcount is the answer, never SELECT-then-INSERT.
• R4: applies to every mutating call and every compensation.
• Keep the response body (24h TTL, ~426 MB) so a retry replays the same reservation id. TTL is about meaning, not storage.

Booking write structure

• Three phases; the slow payment call sits between two txns and inside neither.
• Txn 1: claim idempotency key + conditional inventory decrement + insert pending reservation + record saga step-2 row — all together.
• Payment authorize (not capture) outside any txn (~3s). Putting it inside takes T 5ms->3005ms, ceiling 200/s->0.33/s: a 601x collapse. This is the most common structural mistake.
• Txn 2: confirm on approval, or release_nights on decline; store response.
• Release is guarded by the status transition (WHERE status='pending'), not the decrement: total_reserved - 1 can’t know it already ran and would invent inventory the CHECK can’t catch (wrong direction).

Txn 1  claim key + take inventory + pending row + saga step-2
  |
  v
Authorize payment   (outside any txn, ~3s)
  |
  v
Txn 2  confirm  OR  release every night + store response

Cache invalidation

• Two staleness directions are asymmetric: stale-unavailable hides ~0.005% of inventory; stale-available walks users to the payment page and fails them (~17% on hot rows). ~3,600x worse.
• Stale-available never overbooks: the write re-checks the DB. Cache is a conversion hint, never a correctness participant.
• Invalidate on write, not TTL expiry (~24 deletes/s). Keep short TTL only as a backstop for a lost invalidation.
• Bias downward: within one room of sold-out, don’t cache, serve from a replica.
• Key on (hotel, room type, date), not on search results (a booking invalidates 1-3 entries, not thousands).
• Commit DB first, then delete cache. Deleting first lets a reader repopulate the old value.

Across services: saga, not 2PC

• 2PC holds every participant’s locks if the coordinator crashes after PREPARE (oversubscribes hottest row 1.67x through a 30s failover), and the payment gateway won’t enroll. Reject it across services; use it only inside one DB.
• Saga: local txns, each with a compensation that undoes it after the fact. Rules:
  • Order steps so the least reversible is last: void auth (no trace) -> refund (statement line) -> confirmation email (unsendable, so last).
  • Every compensation is safe to run twice and when the forward step never happened.
  • pending reservation is a semantic lock (enforced by column meaning), letting the txn commit in 5ms while the gateway takes 3s. Holds tax inventory (~2.2% of a night’s rooms at 15min); run the sweeper or a crash deletes inventory permanently.
  • Saga log is a durable work queue: write step before it runs, update after; a crash resumes.

Deliberate overbooking = one integer

• No-shows are predictable, so sell 200 + a rooms against 200. Walk someone when fewer than a guests no-show.
• Count is X ~ Binomial(200 + a, 0.05) (use 200 + a, not 200 — the common slip understates risk). Walk probability P(X <= a - 1).
• Normal approx: mean 10, sd (200*0.05*0.95)^0.5 = 3.08; 10 - 1.28*3.08 + 0.5 = 6.55 (with continuity correction) -> a = 7.
• Exact: a=6 -> 5.2%, a=7 -> 10.4%, a=8 -> 17.9%. Business accepts ~10% -> 7.
• Express it as total_inventory = 207 for that date: one honest integer, CHECK / conditional UPDATE / concurrency untouched. This is why two columns beat one remaining.

Gotchas

• Replica lag: a booking txn must never read a replica; a conditional UPDATE against second-old data is the race with a wider window. Route by intent, not by SELECT vs write.
• Two columns (total_inventory, total_reserved), not remaining: distinguishes sold-out from out-of-service, and expresses overbooking without lying.
• reservations.idempotency_key UNIQUE is the truth even if the idempotency layer has a bug.
• Sweeper must move an orphaned in_progress idempotency row to abandoned when it releases the hold, else every retry gets a permanent 409 for a re-sold room.