Design a Hotel Reservation System

A hotel reservation system lets guests search hotels, view room availability for a date range, and book rooms. Read traffic (search and browsing) far exceeds write traffic (bookings), but the writes carry the correctness requirement: a room type must never be sold beyond its inventory for any night.

Requirements

Functional

• Hotel staff add and update hotels, room types, prices, and inventory.
• Guests search hotels by location and dates and see availability and price.
• Guests reserve a room type for a date range, then pay, then receive confirmation.
• Guests cancel a reservation; inventory is released.
• Overbooking is optionally allowed by a configured percentage (hotels often sell slightly above capacity to offset cancellations).

Non-functional

• No double booking of the last available room (strong consistency on inventory).
• Idempotent reservation requests: a retry after a timeout does not create a second reservation.
• Availability of search is more important than perfectly fresh results; a search result may be seconds stale as long as the booking step validates.
• Moderate scale; concurrency is high only for popular hotels and dates.

Estimation

Assumptions: 5,000 hotels, 1,000 rooms each on average (5 million rooms), 70% occupancy, average stay 3 nights.

• Occupied rooms per night: 5,000,000 x 0.70 = 3.5 million. Each reservation covers 3 nights, so about 3.5 M / 3 = 1.2 million new reservations per day. Take 1 million.
• Reservation QPS: 1,000,000 / 86,400 is about 12 per second. Peak at 10x is about 120 per second.
• Funnel: assume 10 searches per hotel-page view, 10 page views per booking-form start, and 10 form starts per completed booking. Then searches are about 1,000 times bookings: about 12,000 per second average, tens of thousands at peak.
• Inventory rows: room types (say 5 per hotel, so 25,000 room types) x 2 years x 365 days = about 18 million rows. At 100 bytes each, under 2 GB. This fits in one relational database node.
• Reservation storage: 1,000,000 per day x 500 B = 500 MB per day, about 180 GB per year.

The write path is small enough for a single relational primary with replicas. Search is the traffic that needs caching and read scaling.

What each number implies:

• 120 bookings/s at peak: one primary per shard suffices for throughput, so sharding is about isolation and hot hotels, not volume.
• About 2 GB of inventory: the whole table fits in the buffer pool of one node, so availability updates are memory-speed and the cost is lock contention, not I/O.
• 12,000+ searches/s: the primary must not serve them. A search over 50 hotels x 3 nights touches about 150 inventory rows, so unaided it would generate roughly 1.8 million row reads per second. Serve it from a cache and a search index.
• 180 GB/year of reservations: partition by start_date and archive past stays; only recent and future rows need to be hot.

API design

GET  /v1/hotels?lat=&lng=&radius=&check_in=&check_out=&guests=   -> hotels with price and availability
GET  /v1/hotels/{hotel_id}/rooms?check_in=&check_out=            -> room types, availability, price
POST /v1/reservations
     { hotel_id, room_type_id, check_in, check_out, guests, idempotency_key } -> { reservation_id, status: PENDING }
POST /v1/reservations/{id}/confirm        (after payment)
DELETE /v1/reservations/{id}              (cancel)
GET  /v1/reservations/{id}

# admin
POST/PUT /v1/hotels, /v1/hotels/{id}/room-types, /v1/room-types/{id}/inventory

The client generates idempotency_key (a UUID) when the booking page loads. The server enforces uniqueness on it.

Data model

Relational storage fits: the data is structured, relationships are clear, and the core invariant needs multi-row ACID transactions (see ACID and isolation levels).

erDiagram
    HOTEL ||--o{ ROOM_TYPE : has
    ROOM_TYPE ||--o{ ROOM_TYPE_INVENTORY : "per date"
    HOTEL ||--o{ RESERVATION : receives
    ROOM_TYPE ||--o{ RESERVATION : books
    GUEST ||--o{ RESERVATION : makes
    HOTEL {
        bigint hotel_id PK
        string name
        float lat
        float lng
    }
    ROOM_TYPE {
        bigint room_type_id PK
        bigint hotel_id FK
        string name
        int capacity
    }
    ROOM_TYPE_INVENTORY {
        bigint hotel_id PK
        bigint room_type_id PK
        date date PK
        int total_inventory
        int total_reserved
    }
    RESERVATION {
        uuid reservation_id PK
        bigint hotel_id FK
        bigint room_type_id FK
        bigint guest_id FK
        date start_date
        date end_date
        string status
        timestamp created_at
        uuid idempotency_key UK
    }

Design choice: inventory is stored per room type per date, not per physical room. Guests book a type ("double, city view"), and the hotel assigns a physical room at check-in. This makes availability a counter comparison (total_reserved < total_inventory x overbooking_factor, where overbooking_factor is a per-hotel setting such as 1.0 for no overbooking or 1.1 for 10% overbooking) rather than an interval-overlap search over individual rooms.

The reservation row also stores created_at, used by the expiry job. Reservation status values: PENDING, PAID (confirmed), CANCELED, REFUNDED, REJECTED. Indexes: primary key on (hotel_id, room_type_id, date) for inventory; unique index on idempotency_key; index on (guest_id, start_date) for the guest's booking list.

High-level design

flowchart LR
    U["Guest (web, mobile)"] --> GW["API gateway"]
    GW --> HS["Hotel service (search, details)"]
    GW --> RS["Reservation service"]
    GW --> AS["Admin / hotel management"]
    HS --> C[("Cache: hotel and rate data")]
    HS --> HDB[("Hotel DB replicas")]
    HS --> ES[("Search index")]
    RS --> RDB[("Reservation + inventory DB (primary)")]
    RS --> PS["Payment service"]
    RS --> Q[["Event bus"]]
    Q --> NS["Notification service"]
    Q --> HS
    AS --> HDB

• Hotel service. Read-mostly: hotel descriptions, photos, room types, prices. Served from cache and read replicas; static content goes to a CDN.
• Search. Location and filter queries run against a search index (or a geospatial index, see geospatial indexing). Availability shown in search results may come from a cached copy of inventory.
• Reservation service. Owns inventory and reservation state, and enforces the no-overbooking invariant.
• Payment service. External integration (see payment system).
• Event bus. Publishes reservation events for notifications, analytics, and cache refresh.

Deep dive

1. Preventing overbooking under concurrency

Two guests book the last room of a type for the same night concurrently. Both read total_reserved = 9, total_inventory = 10, both write 10, and the system sells 11 rooms if the check and update are separate. Options:

A safe implementation combines a single transaction, a conditional update per night, and a check constraint. The date range is half-open: check_in is the first night, check_out is the departure day and is not booked.

BEGIN;
  -- for each night from start_date to end_date (exclusive)
  UPDATE room_type_inventory
     SET total_reserved = total_reserved + 1
   WHERE hotel_id = ? AND room_type_id = ? AND date = ?
     AND total_reserved + 1 <= total_inventory * overbooking_factor;
  -- if rows affected is 0 for any night: ROLLBACK, return "sold out"
  INSERT INTO reservation (..., status = 'PENDING', idempotency_key = ?);
COMMIT;

Locks are taken on all date rows in a consistent order (ascending by date) to avoid deadlocks between overlapping bookings.

The following Java sketch shows the multi-night check-and-reserve with all-or-nothing behavior.

public class Main {
    static final class Inventory {
        private final int[] total;
        private final int[] reserved;

        Inventory(int nights, int roomsPerNight) {
            total = new int[nights];
            reserved = new int[nights];
            java.util.Arrays.fill(total, roomsPerNight);
        }

        /** Reserves nights from `from` (inclusive) up to `to` (exclusive). All nights or none. */
        synchronized boolean reserve(int from, int to) {
            checkRange(from, to);
            // check every night first, then apply: all-or-nothing
            for (int n = from; n < to; n++) {
                if (reserved[n] >= total[n]) {
                    return false;
                }
            }
            for (int n = from; n < to; n++) {
                reserved[n]++;
            }
            return true;
        }

        /** Releases a reservation made with the same range. */
        synchronized void release(int from, int to) {
            checkRange(from, to);
            for (int n = from; n < to; n++) {
                if (reserved[n] == 0) {
                    throw new IllegalStateException("night " + n + " has no reservation");
                }
            }
            for (int n = from; n < to; n++) {
                reserved[n]--;
            }
        }

        synchronized int reservedOn(int night) {
            return reserved[night];
        }

        private void checkRange(int from, int to) {
            if (from < 0 || to > total.length || from >= to) {
                throw new IllegalArgumentException("invalid range " + from + ".." + to);
            }
        }
    }

    public static void main(String[] args) {
        var inv = new Inventory(5, 1);
        System.out.println("book nights 0..2: " + inv.reserve(0, 2));
        System.out.println("book nights 1..3: " + inv.reserve(1, 3)); // night 1 is full
        System.out.println("book nights 2..4: " + inv.reserve(2, 4));
        inv.release(0, 2);
        System.out.println("after release, book 0..2: " + inv.reserve(0, 2));
    }
}

book nights 0..2: true
book nights 1..3: false
book nights 2..4: true
after release, book 0..2: true

Nights 0 to 1 are booked, so the second request fails on night 1 and changes nothing: night 2 stays free, which lets the third request succeed. reserve and release are synchronized, so the check and the increments are one atomic step, the in-memory counterpart of the conditional update. A release makes the nights bookable again.

In the database, the transaction provides this atomicity; the sketch only shows the logic.

2. Idempotent reservations and the payment step

A booking involves the user, the reservation database, and an external payment provider. The flow:

sequenceDiagram
    participant G as Guest
    participant R as Reservation service
    participant DB as Database
    participant P as Payment service
    G->>R: POST /reservations (idempotency_key K)
    R->>DB: transaction: reserve inventory, insert PENDING (unique K)
    DB-->>R: ok
    R-->>G: reservation_id, PENDING
    G->>P: pay
    P-->>R: payment succeeded (webhook)
    R->>DB: status PAID
    R-->>G: confirmed

• The unique constraint on idempotency_key makes retries safe: a duplicate insert fails, and the service returns the existing reservation.
• Inventory is held while the reservation is PENDING. A reservation not paid within a time limit (for example 10 minutes) is expired by a background job that sets status CANCELED and decrements total_reserved. This bounds how long an abandoned checkout blocks inventory.
• Payment confirmation arrives asynchronously and may be duplicated, so the PENDING to PAID transition is idempotent and checks the current status.
• Cancellation decrements total_reserved in the same transaction that sets CANCELED, so a double cancel cannot release inventory twice (the status check guards it).

Concrete thresholds and choices:

• Isolation level: Read Committed is enough with the conditional update, because the WHERE clause and the row lock make check-and-increment atomic. Serializable is not needed and would add retries.
• Why not SELECT ... FOR UPDATE: it holds locks across application round trips (read, decide, write). The conditional update holds the lock only for the statement and commit, typically a few milliseconds.
• Hot-row ceiling: if a transaction holds a row lock for about 3 ms, one row sustains roughly 300 updates per second. A 7-night stay locks 7 rows in ascending date order. Ordinary hotels never approach this; a festival night at one hotel can. Past about 200 bookings/s on one hotel, route that hotel's bookings through a queue partitioned by hotel_id (consumers apply them in order) and answer asynchronously.
• Alternative rejected: a Redis counter as the source of truth is faster but adds a second store that can disagree with the reservation rows; keep it only as a cache.

Hold duration. With about 12 new reservations per second and a 10-minute hold, roughly 7,200 PENDING reservations exist at any moment. If 50% abandon checkout, those 3,600 rooms-nights are unavailable for up to 10 minutes each, which is why the hold is short and the expiry job runs every minute. If a payment arrives after expiry, re-run the conditional update; if inventory remains, confirm, otherwise refund automatically.

Consistency by path: bookings are strongly consistent (single-shard ACID). Search availability is eventually consistent with a 30 to 60 second TTL or event-driven refresh. Hotel content (descriptions, photos) uses a TTL of hours with invalidation on admin update.

3. Search and availability at read scale

Searches are about 1,000 times more frequent than bookings, so they must not query the booking primary.

• Hotel and room-type data change rarely: cache aggressively (see caching strategies) with invalidation on admin updates.
• Availability changes with every booking. A cached availability view refreshed from reservation events (or short TTL) is sufficient; the booking transaction is the authority. If a user sees a room that is sold out, the booking fails with a clear message.
• Geographic search uses an index over hotel coordinates; filters (price, amenities) run in a search engine (inverted index).

4. Scaling the inventory database

Total data is small, but high-demand events (a city-wide festival) concentrate traffic on a few hotels and dates. Options:

• Shard by hotel_id. All inventory rows and reservations for a hotel live on one shard, so a booking stays a single-shard transaction (see sharding). Cross-hotel queries are search-side, not transactional.
• Read replicas serve reservation history and guest lookups.
• If a single popular hotel-date row becomes a hot row, queue booking requests per hotel through a partitioned message queue so they serialize without lock contention. This trades latency for throughput.
• A distributed transaction across the inventory database and an external payment provider is not possible; the PENDING state plus expiry and compensation replaces it (see saga pattern).

Failure modes

• Primary fails. With a synchronous replica, promotion takes tens of seconds. Bookings fail with a retryable error during that window; search keeps working from cache. No confirmed booking is lost. With asynchronous replication, the last few commits could be lost, which is unacceptable for paid bookings.
• Payment webhook lost or delayed. Before expiring a PENDING reservation, query the payment provider for its status. Cancelling a reservation the guest has paid for is the worst outcome, so expiry checks payment first.
• Expiry job down. Inventory stays held longer and rooms look sold out, but no overbooking occurs because the booking transaction remains the authority. Alert on the age of the oldest PENDING row.
• Cache or search index down. Reads fall to replicas, which can be overwhelmed at 12,000 searches/s. Shed load by returning cached-stale or hotel-level results without per-night availability.
• Drift. Bugs or manual fixes can desynchronize total_reserved; the reconciliation job repairs it.
• At 10x scale: about 1,200 bookings/s at peak and 180 million inventory rows (about 18 GB). Shard by hotel_id becomes mandatory for connection and lock capacity, search needs a horizontally scaled index (about 120,000 queries/s), and the hot-hotel queue becomes routine rather than exceptional.

Bottlenecks and scaling

• Read path: cache, CDN, replicas, search index.
• Write path: single primary per shard is sufficient at about 120 bookings per second peak; the hot spot is row contention, not throughput.
• Expiry job: scans PENDING reservations by creation time; needs an index on (status, created_at).
• Failure of the payment provider: reservations stay PENDING and expire; users retry. Do not confirm without payment evidence.
• Availability of the primary: replicate synchronously or use a consensus-based database for the inventory shard so failover does not lose confirmed bookings (see replication).
• Reconciliation: a periodic job compares total_reserved with the count of active reservations per night and reports drift.

Trade-offs and follow-up questions

• Room type inventory versus physical room assignment: counters are simpler, but late room assignment can fail if rooms go out of service. Assigning specific rooms at booking needs interval-overlap constraints.
• Overbooking factor: increases revenue against the risk of relocating guests; configured per hotel and date.
• Locking versus constraint-based approaches: a check constraint gives a safety net regardless of the application path.
• How to support dynamic pricing? A price column per room type per date in the inventory table, with the price captured on the reservation at booking time.
• How to support multiple data centers? A single writer region per hotel avoids cross-region write conflicts; see multi-region.
• What happens if the user closes the browser after paying but before the confirmation page? The webhook drives the state change independently of the browser session.
• Two users click the last room at the same instant? Both run the conditional update; the row lock serializes them, the second sees total_reserved at the limit, affects 0 rows, rolls back, and gets "sold out".
• A hotel lowers total_inventory below total_reserved (rooms under repair)? Reject the change if it would go below reserved, or accept it and flag the excess as oversold for staff to relocate; never silently cancel guests.
• How do you book 3 rooms at once? Increment by 3 in the same conditional update (total_reserved + 3 <= limit) so the group is all-or-nothing.
• How do you show "3 rooms left" in search without hammering the primary? Store a denormalized availability per hotel, room type, and date in the cache, and display the minimum across the requested nights.
• Why not a distributed lock (Redis or ZooKeeper) around booking? The database already provides the atomicity; an external lock adds a failure mode (expiry mid-transaction) without adding safety.