Design a Hotel Reservation System: Inventory, Double Booking, Holds and Overbooking

One booking, from Book to confirmed

Follow one guest booking a King room at hotel 42 for the nights of Oct 11 and Oct 12. This is a good order to explain it in an interview.

1. 1

   Browse

   The guest picks dates. The availability service reads the cache and shows King rooms as free, with the price per night. Nothing is reserved yet.

2. 2

   Send with a key

   The guest presses Book. The app creates one random Idempotency-Key for this checkout and sends POST /reservations with hotel 42, room type King, check-in Oct 11, check-out Oct 13.

3. 3

   Route

   The reservation service hashes hotel_id 42 to find its shard, the database server that holds hotel 42's rows.

4. 4

   Check the key

   Inside one transaction, it inserts the key into the idempotency table. If the key is already there, this is a retry, and the service returns the stored reply without touching inventory.

5. 5

   Hold the nights

   A guarded UPDATE adds 1 to reserved for King on Oct 11 and Oct 12, only where reserved + 1 stays within the limit. If it changes 2 rows, every night had room. If it changes fewer, the transaction rolls back and the guest is told the King is sold out for those dates.

6. 6

   Write the reservation

   Still in the transaction, it inserts the reservation with status HELD, the price, and hold_expires_at 10 minutes from now. It saves the reply next to the key and commits.

7. 7

   Authorize the card

   The service asks the payment provider to authorize the total, with the reservation id as the payment's own idempotency key. Authorize means the money is reserved on the card but not taken.

8. 8

   Confirm

   On success, it updates the reservation to CONFIRMED, only WHERE status is still HELD. It writes a "confirmed" event to the outbox inside that transaction. Email and the hotel's systems receive it from the queue.

9. 9

   Or expire

   If payment never finishes, the expiry job marks the reservation EXPIRED, only WHERE it is still HELD and its time has passed, and subtracts 1 from both nights. The King is bookable again.

10. 10

    Stay

    At check-in the hotel assigns a room number. At check-out the money is captured, and the reservation becomes CHECKED_OUT.

These are the conversations the interviewer is steering you toward. Practice each one until you can talk through it without notes.

Inventory: one row per hotel, room type and night

The first design choice decides everything after it. Guests do not book room 512. They book "a King room for two nights", and the hotel picks the room number later. So the system should count rooms of each type for each night, instead of tracking each physical room. The table is room_type_inventory, with one row per hotel, room type and date. Each row holds total_inventory, the number of rooms of that type that exist, and total_reserved, the number already held or confirmed. A stay from Oct 11 to Oct 13 covers the nights of Oct 11 and Oct 12, so it touches two rows. The check-out day is not a night of the stay. Counting per type has three benefits. Each night is one number to check, not a hundred rows to search. The number fits a database constraint, a rule the database itself enforces on every write. And hotels already think and sell this way. A CHECK constraint is that rule here: total_reserved may never pass the limit. Even if a bug in the application skips a check, the database refuses the write. Per-room rows make sense in a different product, such as a meeting-room booking tool where the user picks a specific room. For that case PostgreSQL offers an exclusion constraint on a date range, which refuses two overlapping bookings of one room.

CREATE TABLE room_type_inventory (
  hotel_id        BIGINT NOT NULL,
  room_type_id    BIGINT NOT NULL,
  stay_date       DATE   NOT NULL,
  total_inventory INT    NOT NULL,           -- rooms of this type
  total_reserved  INT    NOT NULL DEFAULT 0, -- held + confirmed
  overbook_pct    INT    NOT NULL DEFAULT 0, -- 5 = sell up to 105%
  PRIMARY KEY (hotel_id, room_type_id, stay_date),
  CHECK (total_reserved * 100 <= total_inventory * (100 + overbook_pct))
);

UPDATE room_type_inventory
SET    total_reserved = total_reserved + 1
WHERE  hotel_id = 42 AND room_type_id = 7
  AND  stay_date >= '2026-10-11' AND stay_date < '2026-10-13'
  AND  (total_reserved + 1) * 100 <= total_inventory * (100 + overbook_pct);
-- 2 rows changed: book it. Fewer than 2: ROLLBACK, sold out.

The double-booking race

Here is the bug most first designs have. The code reads the count, checks it in the application, then writes the new count. Two guests want the last King on Oct 11, with 99 of 100 taken. Guest A reads 99. Guest B reads 99. A sees room and writes 100. B also saw room and writes 100. Two guests now hold one room, and the stored count of 100 even looks correct, so nothing alerts anyone. This is called a race condition: the result depends on the timing of two requests that run at once. Adding a cache lock or a check in the app does not fix it, because the check and the write are still two separate steps that another request can get between. The fix is to make the check and the write one step that the database itself protects. PostgreSQL's default isolation level (the rules for how transactions running at once see each other's changes) is called Read Committed, and in it a guarded UPDATE does this. When B's UPDATE finds a row that A has already changed but not committed (made final), B waits. When A commits, PostgreSQL re-checks B's WHERE clause on the new version of the row. 100 + 1 is more than 100, so B's update changes 0 rows, and the service tells B that the room is gone. A booking for several nights must change one row per night. If the count of changed rows is short, one night was full, and the whole transaction is rolled back so no partial hold is left.

Three ways to stop it: lock, version, or guarded write

Interviewers usually ask you to compare the options, so know all three. The first is a pessimistic lock: assume a clash will happen and lock first. SELECT ... FOR UPDATE reads the inventory rows and locks them. Any other transaction that tries to update or lock those rows waits until this one commits or rolls back. The application checks the counts at leisure, then updates. It is simple to reason about, and it suits a hotel where many guests fight for one night. Lock the nights in date order every time. If one booking locked Oct 12 then Oct 11 while another locked Oct 11 then Oct 12, each could wait for the other forever, which is called a deadlock. The second is an optimistic version check: assume clashes are rare and detect them at write time. Each row has a version number. The app reads the row and its version, then writes WHERE version is still the one it read. If someone changed the row in between, 0 rows change, and the app reads again and retries. No locks are held while the guest's request is being processed, which is good when collisions are rare, and wasteful when they are common, because every loser retries. DynamoDB's conditional writes work this way: the write succeeds only if a condition on the item is still true. The third is the guarded write from the previous section: put the business rule itself in the WHERE clause. The loser gets 0 rows and a clear answer, sold out, with no retry needed. Back all three with the CHECK constraint, so a bug cannot oversell. A fourth option, raising the isolation level to Serializable, also works in PostgreSQL, but the database then aborts one of two clashing transactions with a serialization error, and the application must retry the whole transaction. The course's Serializability lesson walks through pessimistic locking and optimistic validation side by side, with this retry rule.

-- 1. Pessimistic: lock the nights, then check and write
BEGIN;
SELECT stay_date, total_inventory, total_reserved, overbook_pct
FROM   room_type_inventory
WHERE  hotel_id = 42 AND room_type_id = 7
  AND  stay_date >= '2026-10-11' AND stay_date < '2026-10-13'
ORDER  BY stay_date          -- same order every time: no deadlock
FOR UPDATE;                  -- other bookings for these rows wait
-- the app checks every night has room, then UPDATE ... and INSERT ...
COMMIT;                      -- locks released

-- 2. Optimistic: write only if nobody changed the row since we read it
UPDATE room_type_inventory
SET    total_reserved = 88, version = version + 1
WHERE  hotel_id = 42 AND room_type_id = 7 AND stay_date = '2026-10-11'
  AND  version = 13;         -- 0 rows: read again and retry

An idempotent booking API

Networks fail in an annoying way: the app cannot tell whether its request was lost or only the reply was lost. If the reply was lost, the booking already happened. A retry without protection would hold a second room and, later, charge the card twice. An operation is idempotent when doing it twice has the effect of doing it once. To make booking idempotent, the app generates a random key, such as a UUID, when the guest starts checkout, and sends it in an Idempotency-Key header with the booking and with every retry of it. The server stores the key, a hash of the request body and the reply. Stripe's API works this way: it saves the status code and body of the first request for each key and returns them for later requests with that key, it compares the parameters and returns an error if they differ, and keys can be removed once they are at least 24 hours old. The key must be written inside the database transaction that makes the hold. Then a hold without its key, or a key without its hold, cannot exist. If two copies of the request arrive at once, the primary key lets only one of them insert the key. The other is treated as a retry; if the first has not finished yet, it gets a "try again shortly" reply. Use the reservation id as the idempotency key for the call to the payment provider too, so a retried authorization does not reserve the money twice. The course's Idempotency Keys lesson replays this lost-reply case step by step.

CREATE TABLE idempotency_keys (
  guest_id       BIGINT NOT NULL,
  idem_key       TEXT   NOT NULL,   -- made by the app, once per checkout
  request_hash   TEXT   NOT NULL,   -- same key, other body: reject
  reservation_id BIGINT,
  response_code  INT,
  response_body  JSONB,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (guest_id, idem_key)
);

BEGIN;
INSERT INTO idempotency_keys (guest_id, idem_key, request_hash)
VALUES (9001, 'c1f7e2...', 'sha256:ab12...')
ON CONFLICT (guest_id, idem_key) DO NOTHING;
-- 0 rows inserted: a retry. ROLLBACK and return the stored reply.
-- 1 row: guarded UPDATE on inventory, INSERT reservation,
--        UPDATE idempotency_keys SET reservation_id, response_...
COMMIT;

The reservation state machine and hold expiry

A guest who presses Book still has to pay, and that can take minutes. If the room is only counted after payment, two guests can both reach the payment page for the last room, and one pays for nothing. So the booking first creates a hold: the nights are counted as reserved and the reservation gets status HELD with hold_expires_at set, here 10 minutes ahead. A state machine lists every state a reservation can be in and the only moves allowed between them. HELD becomes CONFIRMED when the card is authorized in time, EXPIRED when the timer runs out, or CANCELLED when the card is declined. CONFIRMED becomes CANCELLED, NO_SHOW or CHECKED_IN. The rule that makes this safe is simple: every move is an UPDATE that names the state it expects to leave, such as WHERE status = 'HELD'. Now consider the nasty case. The payment succeeds at 9 minutes 59 seconds, but the success message reaches the service at 10 minutes 2 seconds, after the expiry job ran. The expiry job's update matched first, so the confirm update finds no HELD row and changes 0 rows. The service then cancels the card authorization so the guest is not charged, and tells the guest to try again. Without the guarded update, both jobs would win: the room would be given back and also confirmed. Expired holds give their nights back inside the transaction that marks them EXPIRED, so the count and the status can never disagree. Several expiry workers can run at once, because the guarded update lets only one of them win for each reservation.

-- Expiry job, for one reservation
BEGIN;
UPDATE reservations SET status = 'EXPIRED'
WHERE  reservation_id = 555 AND status = 'HELD'
  AND  hold_expires_at < now();
-- 0 rows: it was confirmed in time. COMMIT and stop.
-- 1 row: give the nights back
UPDATE room_type_inventory
SET    total_reserved = total_reserved - 1
WHERE  hotel_id = 42 AND room_type_id = 7
  AND  stay_date >= '2026-10-11' AND stay_date < '2026-10-13';
COMMIT;

-- Payment succeeded
UPDATE reservations SET status = 'CONFIRMED'
WHERE  reservation_id = 555 AND status = 'HELD';
-- 0 rows: the hold already expired. Cancel the card authorization.

Payment: authorize at booking, capture later

A card payment can happen in two steps. Authorize reserves the amount on the guest's card; capture actually takes it. Stripe's documentation uses hotels as its example: a hotel often authorizes the full amount before the guest arrives and captures it at check-out. An authorization does not last forever. For most card networks, an online authorization is valid for 7 days. Some networks allow an extended authorization of up to 30 days for hotels and lodging. So for a stay booked three months ahead, the system cannot simply authorize at booking and capture at check-out. It has two common choices. For a prepaid rate, capture at booking and refund under the cancellation policy. For pay later, save the card with the provider at booking, check it, and authorize closer to arrival. The reservation service treats the provider as slow and unreliable. Every call carries an idempotency key. Results also arrive by webhook, and the webhook handler applies them with a guarded state change, so a result that arrives twice, or late, does no harm. A job looks for authorizations that are about to expire, and for reservations that are CONFIRMED with no capture after check-out. Money moves are recorded in the payments table, so support can answer "was I charged?" from our own data. The course's Design a Payment System capstone covers the ledger and reconciliation side in depth.

Overbooking on purpose

Hotels know that some booked guests will not come. A no-show is a guest with a confirmed booking who never arrives. Others cancel too late for the room to be sold again. An empty room that night earns nothing, so many hotels deliberately sell a few more rooms than they have. SiteMinder, which sells software to hotels, says hotels may overbook by a percentage "such as 2%, 3%, or even 10%", and that many set it at or just below their historical no-show and cancellation rate. So 10% is an upper example, and the real number differs by hotel and by season. The cost of getting it wrong is real. When more guests arrive than there are rooms, the hotel "walks" a guest: it arranges and pays for a room at another hotel, plus transport and often some compensation. In the system, overbooking is just one more number per room type, overbook_pct, set by the hotel's revenue team. The CHECK constraint and the guarded UPDATE use total_inventory x (100 + overbook_pct) / 100 as the limit. For 100 King rooms at 5%, that is 105. Use whole-number math, as in the code, so 100 x 1.05 never becomes 104.99999 in floating point. The system does not choose the percentage. It stores it, enforces it exactly, and reports how often guests were walked, so the hotel can tune it.

rooms, no_show = 100, 0.08
for pct in (0, 5, 10):
    sold = rooms * (100 + pct) // 100   # the limit the CHECK enforces
    arrive = sold * (1 - no_show)       # expected, on an average night
    print(pct, sold, round(arrive, 1), round(rooms - arrive, 1))
# 0   100   92.0   8.0    8 rooms empty
# 5   105   96.6   3.4    about 3 empty
# 10  110  101.2  -1.2    about 1 guest walked

Read path: fast and a little stale. Write path: always right

About 100 people look for every one who books, so the read path must be cheap. The availability service reads rooms left from a cache, keyed by hotel, room type and night. A cache is a fast copy of data kept in memory. The cache is updated from the database's change feed, and every entry also expires after a short time, so if an update is ever missed the entry heals itself. Hotel pages and photos are served from read replicas, copies of the database that take reads off the primary. The cost is staleness. A guest may see "2 rooms left" when one was booked a second ago. That is acceptable for one reason: the booking step never trusts the cache. It runs the guarded UPDATE on the primary, and if the room is gone, the guest sees a clear message and the cache entry is refreshed. The opposite design, reading the primary for every search, would put 20,000 reads a second on the servers that must also lock rows for bookings, and slow down the one path that must not be slow. One more source of change: hotels also sell their rooms on other websites. A channel manager is software that sends a hotel's rooms and prices to every website it sells on, and updates them all when one of them makes a sale. If those updates are slow, two websites can sell the last room, so the integration should push changes as they happen.

Sharding by hotel and the hot row

Sharding means splitting the data across several database servers, each called a shard. The shard key decides where each row goes. Here the key is hotel_id. A hash function turns the id into a number, and that number picks the shard. Inventory rows, reservations, idempotency keys and outbox events for one hotel all go to one shard. So a booking, which only ever touches one hotel, is one ordinary transaction on one server, and never needs a distributed transaction across servers. If you sharded reservations by guest instead, a booking would touch two shards, and you would need two-phase commit (a protocol where a coordinator asks every server to prepare, then to commit) or a saga (a chain of steps, each with an undo step). Both are slower and harder to get right. A guest's "my trips" page then needs a lookup by guest, served from a separate index table or a search copy, kept up to date from the outbox. Sharding does not fix the hot row. Hotel 42's King on New Year's Eve is one row, and every booking for it must wait for that row's lock. If each booking holds the lock for about 5 milliseconds, that row takes at most 1000 / 5 = 200 bookings a second, however many shards there are. That is far more than any real hotel sells in a second. For a flash sale of thousands of discounted rooms at once, put a waiting room or a queue in front, so requests arrive at the row at a pace it can handle. Keep the booking transaction short: no call to the payment provider while the lock is held.

How a Hotel Reservation System actually does it

Every mechanism on this page is documented by the system that provides it. PostgreSQL's documentation says FOR UPDATE locks the selected rows so that other transactions trying to update, delete or lock them wait until the current transaction ends. It also explains that in Read Committed mode, a second updater waits for the first, then re-evaluates its WHERE clause on the updated row, which is why the guarded update is safe. The PostgreSQL range-types page even shows a room_reservation table with an exclusion constraint that refuses two overlapping bookings of one room. Stripe's idempotent requests guide describes saving the first response for a key, comparing parameters on retries, and removing keys once they are at least 24 hours old. Brandur Leach published a full Postgres design for idempotency keys, with a unique constraint on (user_id, idempotency_key) and a stored response. Stripe's guide to placing a hold uses hotels as its example, and its extended authorization page lists 30-day windows for hotel and lodging merchants on some card networks. Amazon's DynamoDB guide shows the optimistic version: a conditional write succeeds only if the item still matches an expected value. On the business side, SiteMinder describes overbooking of "2%, 3%, or even 10%", set at or just below a hotel's own no-show and cancellation rate, and explains walking a guest to another hotel. Its channel manager page explains how one hotel's rooms are kept in step across many booking websites. For scale, Booking Holdings reported 1,144 million room nights booked in 2024, about 3.1 million a day, which is why the estimates above use a few million room nights a day as the upper end.

Frequently asked questions