Hotel Reservation System

We are building the booking backend for a hotel chain of roughly 5,000 hotels and 1 million rooms. Guests browse hotel and room pages, see prices for their dates, reserve a room type, pay, and may cancel. Hotel staff use an internal tool to manage rooms and prices. Prices are dynamic: the rate for a room type can differ every night depending on expected demand, so price is a function of hotel, room type and date rather than a static attribute.

Two business rules shape the design. First, the chain deliberately allows 10% overbooking, because some fraction of guests always cancel or fail to show up, and an empty room is lost revenue. Second, guests reserve a room type, not a specific room number; the concrete room is assigned at check-in. That second rule is a gift to the data model, because we only need counters per room type rather than a calendar per physical room.

Assume 70% occupancy and an average stay of 3 nights. Rooms turning over per day is then 1,000,000 x 0.7 / 3 ≈ 233,000 reservations per day, which we round to about 240,000. Dividing by 86,400 seconds gives roughly 3 reservations per second. That is tiny in absolute terms, which tells us the hard part is not raw throughput but correctness under contention, especially during flash sales or holiday peaks when bursts can be orders of magnitude above the mean.

Traffic higher up the funnel is larger. If about 10% of users move from one step to the next, then 3 QPS on the final reserve call implies around 30 QPS on the order-confirmation page and around 300 QPS on the room-detail view. Inventory data is also small: 5,000 hotels x 20 room types x 730 days (two years of bookable dates) is about 73 million rows, comfortably within one well-provisioned relational database, with sharding as a later option rather than a day-one necessity.

The API splits into three resource families: hotel endpoints (GET /v1/hotels/{id}, plus admin create/update/delete), room endpoints for room types, and reservation endpoints (GET /v1/reservations, POST /v1/reservations, DELETE /v1/reservations/{id}). The create call carries hotel ID, room type, start and end dates, and a reservationID generated by the client before submission. That ID is the idempotency key: if the same request arrives twice, the second insert collides on the primary key and the client receives the original result instead of a duplicate booking.

A relational database is the natural fit. Reads far outnumber writes, the schema is stable, and above all we need ACID transactions so that decrementing inventory and inserting a reservation succeed or fail together. The central table is room_type_inventory, keyed by (hotel_id, room_type_id, date) with columns total_inventory and total_reserved. Each row represents one room type on one night. A reservation for a three-night stay touches three rows, and an availability check simply verifies that every night in the range still has headroom.

The book-style design uses microservices, each owning a narrow responsibility: a hotel service for static hotel and room information (heavily cacheable), a rate service for nightly prices, a reservation service that owns booking requests and inventory, a payment service that charges the guest and updates reservation status, and a hotel management service used only by staff. A public API gateway handles authentication, rate limiting and routing, while services talk to each other through internal APIs that are not exposed to the internet.

Why split at all for a 3 TPS workload? Mostly for team and deployment independence and because hotel and rate data have very different caching profiles from inventory. The important caveat, covered later, is that splitting reservation and inventory across separate databases would turn one local transaction into a distributed one, so in practice the pragmatic answer is to keep both in the same database.

The simplest concurrency bug is a guest who double-clicks Book or retries after a timeout. Disabling the button on the client helps the common case but is easily bypassed by a flaky network, a refreshed page or a script. The robust fix is server-side idempotency: the client obtains a unique reservation_id when the order page is rendered, and that ID is the primary key of the reservation row. A second submission violates the unique constraint, so the database itself rejects the duplicate, and the API returns the existing reservation.

This pattern generalises: any operation that might be retried should carry a caller-generated key that the server can deduplicate on. The key should be minted before the first attempt, not on each attempt, otherwise retries look like new requests.

The harder race is two different guests booking the last available room type. Under typical read-committed isolation, both transactions read total_reserved = 99 against an inventory of 100, both conclude there is space, and both write 100, overselling. Three standard remedies exist. Pessimistic locking uses SELECT ... FOR UPDATE so the second transaction waits until the first commits; it is simple and correct but serialises contending requests, risks deadlocks when stays span multiple rows, and scales poorly under heavy contention.

Optimistic locking adds a version column: each writer reads the version, then updates with WHERE version = :seen, and retries if zero rows changed. It is fast when conflicts are rare, which matches our low average QPS, but degrades badly during bursts because most writers fail and retry. A database constraint such as CHECK (total_reserved <= total_inventory * 1.1) lets the database reject any oversell outright, which is concise and race-free, though constraint semantics vary across engines and the logic lives outside version-controlled application code.

If the system grows to serve a whole travel marketplace, the database becomes the bottleneck. Nearly every query filters by hotel_id, so it is the obvious shard key: hash(hotel_id) % N spreads load while keeping each booking local to one shard. Availability reads can be served from a Redis cache of inventory counters keyed by hotel, room type and date, with the database remaining the source of truth. The cache may briefly show a room that is gone; the authoritative check still happens in the database transaction, so the worst case is a failed booking, not an oversell. Changes are propagated from DB to cache asynchronously with change data capture, for example tailing the binlog with a tool like Debezium.

If reservation and inventory lived in separate services with separate databases, a booking would need a distributed transaction. Two-phase commit provides atomicity but blocks when the coordinator fails and couples every participant's availability. A Saga runs a chain of local transactions with compensations on failure, which is non-blocking but only eventually consistent and requires careful compensation logic. Both add serious complexity, which is why many real systems simply keep reservation and inventory tables in one relational database and accept a less pure service boundary.

Failures cluster around payment and retries. Reservations start as pending and move to paid only after the payment service confirms; a timer or reconciliation job expires pending holds so abandoned checkouts release inventory. Because every create call is idempotent, clients can retry safely after timeouts. Cancellations decrement total_reserved inside the same transaction that changes reservation status. The takeaway: tiny QPS does not mean an easy problem. Model inventory as counters per room type and night, make writes idempotent, pick a concurrency strategy that fits your contention profile, and resist splitting data that must change atomically.