Hotel Booking System Design: Search and No Double Bookings

Two guests get the same room for the same night, and one of them is turned away at the door. It happens when the app checks that a room is free and saves the booking as two separate steps. Closing that gap, while keeping date search fast, is the heart of hotel booking system design.

Picture a common setup. A booking app lists about 1,400 homestays and guesthouses in hill towns such as Mussoorie, Dhanaulti and Lansdowne, with 3,800 rooms in all. The setup is made up, but the problem is real.

On the Diwali weekend, two families drove up to the same cottage in Dhanaulti. Both had paid Rs 4,200 for the night of 7 November, and the owner had one room. One family slept in their car until a nearby guesthouse found space.

The team traced it to three causes. Each one is a classic mistake, and this guide fixes all three on this one app.

• The booking code read the calendar, decided the room was free, then wrote the booking. Two requests 40 milliseconds apart both passed the read.
• Search results came from a copy of the calendar rebuilt every 15 minutes. On a busy weekend, rooms sold out long before the copy noticed.
• The cottage was also listed on two other booking sites, whose calendars reached the app only every few hours.

Hotel booking system design is planning the software that lets guests search rooms by place and dates, see a price, and book a stay nobody else can take. Interviewers often ask it as design Airbnb or design a hotel booking site.

It looks like an ordinary online shop with one twist. You are not selling a room; you are selling a room for certain nights. One room can be free on Monday, taken on Tuesday and free again on Wednesday.

A picture to keep in mind: the dharamshala register

Picture the manager of a dharamshala, a pilgrims' rest house, in Haridwar during a big festival. He keeps a thick register for every room. Here is how it maps to the system.

• A page in the register is a room, and each column on the page is one night of that room.
• Writing a family's name in a blank box, in ink, is a booking. His golden rule is to write only in a blank box, one family at a time.
• Scanning the pages for blank columns on your dates is search.

Where the picture breaks: the manager serves one family at a time for the whole register. A database makes bookings wait only when they want the same room and night, so bookings for different rooms run side by side.

The requirements, in two groups

Functional requirements are the features the system must offer. Non-functional requirements say how well those features must work, and they shape this design the most.

• Guests search by town or map area, dates and number of guests, then filter by price and features such as parking.
• Guests see a property page with photos, house rules and the total price for their exact nights.
• Guests book and pay, and get a confirmation they can trust. Guests and hosts can cancel under the property's rules.
• Hosts open or block nights, set nightly prices and see their bookings.

Now the non-functional side. Search should answer within half a second (a made-up target). Booking must never sell one night twice, even if that makes it slower. Search may use data a few seconds old; booking may not.

The simplest safe model is one row per room per night, holding either nothing or the booking that owns that night. Booking claims every night row of the stay; search looks for rooms whose rows are all empty.

Call the table room_nights. It is the dharamshala register as a table: room 17, night of 7 November, booking 88412. An empty booking column means the night is free.

Get the dates right first. A stay is a half-open range: it includes the check-in night and excludes the check-out day. A guest staying from 10 to 12 October uses the nights of the 10th and 11th, so another guest can check in on the 12th.

Is a row per night too much data? Opening 3,800 rooms for 365 nights gives 1,387,000 rows a year, about 1.4 million. That is a small table for a relational database, one that stores data in tables of rows and columns.

Hotels with identical rooms use a variant. If a hotel has 20 identical deluxe rooms and the guest does not mind which one, keep one row per room type per night. It holds a total and a booked count, and you book only while booked is below total.

A second common model keeps one row per booking with its date range. PostgreSQL, a popular open-source database, has a built-in daterange type for this. It stores ranges in half-open form, which matches how stays work.

This app picks night rows because hosts set different prices for weekends and festivals. A price on each night row keeps that simple, and the checkout total is the sum of those rows.

Make the check and the claim one step inside the database, so only one booking can win. Never read availability in your code, decide, and then write in a second step.

The old code had a race condition, a bug where the result depends on which of two requests runs first. Both requests read the calendar, both saw 7 November free, and both wrote a booking.

The fix is a conditional update: one UPDATE statement that claims every night of the stay, but only rows that are still free. Then you count the rows it actually changed. Here it is in SQL, the language databases are queried in, run in psql, PostgreSQL's command-line client.

CREATE TABLE room_nights (room_id int, night date, booking_id bigint,
  held_until timestamptz, PRIMARY KEY (room_id, night));
INSERT INTO room_nights (room_id, night)
  SELECT 17, d::date FROM generate_series('2026-11-01'::date, '2026-11-30', '1 day') d;
UPDATE room_nights
SET booking_id = 88412
WHERE room_id = 17
  AND night >= '2026-11-07' AND night < '2026-11-09'
  AND booking_id IS NULL;
UPDATE room_nights
SET booking_id = 88413
WHERE room_id = 17
  AND night >= '2026-11-08' AND night < '2026-11-10'
  AND booking_id IS NULL;

psql printed this. The first guest claimed both nights. The second guest wanted 8 to 10 November, found the 8th taken, and claimed only the 9th.

CREATE TABLE
INSERT 0 30
UPDATE 2
UPDATE 1

That UPDATE 1 is the danger sign. A two-night stay that claims one night is not a booking, so the app must undo it. That is why the real code runs inside a transaction, a group of changes that succeed or fail together, and rolls back when the count is short.

Why is this safe when two requests arrive together? In PostgreSQL, an UPDATE locks each row it changes until its transaction ends. At the default settings, a second UPDATE on the same night waits, then re-checks its condition, sees the night taken, and claims nothing.

Plain reads are not blocked by these row locks. Search keeps working during a booking and sees the last committed, meaning finally saved, state.

It filters a search index by place, guests and features, drops rooms booked on any of your nights, and ranks the rest. Then it confirms the short list against the booking database before showing it.

Running every search on the booking database would put thousands of browsing guests on the one machine that must settle bookings. So the app adds a search index, a separate copy of listing data arranged for fast filtering. This app uses Elasticsearch, a popular open-source search engine.

Each bookable room becomes one record, which Elasticsearch calls a document, holding its town, map point, guest limit, features, price range and booked nights. Map search uses Elasticsearch's geo_distance query, which matches locations within a set distance of a point. A guest can drop a pin near Kempty Falls and ask for stays within 5 kilometres.

Back to the dharamshala. A chai-wallah at the gate tells pilgrims which rooms looked free this morning. He answers in seconds but can be hours behind, so the manager still checks the ink before handing over a key.

The register is the source of truth, the one place whose answer always wins. Search works the same way, in two passes: the index proposes rooms, and the database confirms them.

The index asks for three times as many rooms as one page needs. A few stale hits then cannot leave the page half empty, and the database checks only 60 rooms instead of a whole town.

Only approaches where the database makes the final decision survive. The table compares five common ones. Each verdict was tested on PostgreSQL 16.15 below, except the cache lock, which comes from a well-known analysis of lock expiry.

A fencing token is a number that goes up each time the lock is granted. The storage rejects writes carrying an older number, so a holder whose lock expired cannot write.

A booking function you can run

Payment can take a minute or two, so the app first holds the nights for ten minutes, a design choice rather than a standard. Paying confirms the hold. The code uses psycopg2 2.9.13, a common Python driver for PostgreSQL.

With psycopg2, leaving a with conn block commits the transaction, and an error inside it rolls the transaction back. It does not close the connection.

class RoomTaken(Exception):
    pass

class HoldLapsed(Exception):
    pass

CLAIM = """
UPDATE room_nights
SET booking_id = %s, held_until = now() + interval '10 minutes'
WHERE room_id = %s AND night >= %s AND night < %s
  AND (booking_id IS NULL OR held_until < now())
"""

CONFIRM = """
UPDATE room_nights SET held_until = NULL
WHERE booking_id = %s
  AND (held_until IS NULL OR held_until >= now())
"""

def hold_room(conn, room_id, check_in, check_out, booking_id):
    nights = (check_out - check_in).days   # check-out day is not a night
    if nights < 1:
        raise ValueError("a stay needs at least one night")
    with conn:                              # commit, or roll back on an error
        cur = conn.cursor()
        cur.execute(CLAIM, (booking_id, room_id, check_in, check_out))
        if cur.rowcount != nights:
            raise RoomTaken(room_id)        # rolls back every night claimed
    return booking_id

def confirm_hold(conn, booking_id, nights):
    with conn:
        cur = conn.cursor()
        cur.execute(CONFIRM, (booking_id,))
        if cur.rowcount != nights:
            raise HoldLapsed(booking_id)    # refund, or try to hold again

Three details carry the design. A night can be claimed if it is empty or its hold has lapsed, so expiry works without any background clean-up job. Confirming clears held_until, and the claim condition never matches a night with a booking and no expiry.

The third detail: a night the host never opened has no row, so it counts as taken. A stay of zero nights is refused before any SQL runs.

confirm_hold runs after payment. Confirming twice is safe, so a repeated payment callback changes nothing. If the hold lapsed first, it raises HoldLapsed even when nobody took the room, and the app refunds or holds again.

Pause and predict: how often do both guests win?

A test script started two threads at the same instant, each calling hold_room for the same room and 7 to 9 November. It repeated this on 150 fresh rooms. Then it ran 150 more races with overlapping stays, 1 to 3 against 2 to 4 November.

Guess how many of those 300 races had two winners before reading on.

None. Every race had exactly one winner, and no room ended with nights owned by two bookings. Which thread won varied from run to run, as races do.

What just happened? A step-by-step test shows the mechanism. While the first transaction was open, the second UPDATE showed wait type Lock in pg_stat_activity, PostgreSQL's view of running sessions. After the first committed, the second claimed 0 rows.

A plain SELECT from a third session returned at once and still showed the night free, because the first booking had not committed.

The mechanism is documented. An isolation level is the setting for how much parallel transactions see of each other. At Read Committed, PostgreSQL's default, a waiting UPDATE re-checks its WHERE clause on the new row version, where booking_id IS NULL is now false.

Stricter isolation levels and several statements

At Repeatable Read, the waiting guest gets an error instead of zero rows once the first guest commits. On PostgreSQL 16.15 the test printed: could not serialize access due to concurrent update. The error code was 40001.

The documentation says to retry the whole transaction on that error. In the test, the retry found the night taken and raised RoomTaken, so the guest sees the same answer.

If you claim nights in separate statements, claim them in date order. When two transactions locked the same two nights in opposite orders, PostgreSQL found a deadlock, each waiting for a row the other held. It cancelled one with error 40P01.

Why a locking read misses bookings that do not exist yet

Locking reads lock only rows that exist. With the booking-range model, a free room has no overlapping booking rows, so SELECT ... FOR UPDATE finds nothing to lock.

The test ran that check in two sessions at once. Both found 0 rows, neither waited, and both inserted. The table ended with two overlapping bookings for one room. Night rows avoid this because every night exists before anyone books it.

That was PostgreSQL at Read Committed, and Repeatable Read also saved both bookings. At Serializable, the strictest level, one session failed with error 40001 and one booking was saved. Locking the room's own row in a rooms table first also worked: the second session waited, then found the overlap.

MySQL's InnoDB engine behaves differently. Its documentation says that at its default level, Repeatable Read, locking reads also lock the gaps between index rows. That stops phantom rows, new rows another session slips into a range you already checked.

Letting the database refuse overlaps

With the booking-range model, PostgreSQL can enforce the rule itself. An exclusion constraint is a table rule that rejects a new row if it clashes with an existing one. The documentation's own example rejects overlapping reservations for the same meeting room.

The constraint is backed by a GiST index, a PostgreSQL index type that can search ranges. Comparing a plain room id inside it needs the btree_gist extension, an add-on module supplied with PostgreSQL. Without it, PostgreSQL 16.15 refused to create the table with error 42704, saying integer has no default operator class for gist.

You can check this yourself. Save the lines below as reader_check.sql and run psql -d scratch -f reader_check.sql, where scratch is a throwaway database and your user may create extensions. Expect one error and two successful inserts.

CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE stays (
  room_id int,
  stay daterange,
  EXCLUDE USING gist (room_id WITH =, stay WITH &&)
);
INSERT INTO stays VALUES (17, '[2026-10-10,2026-10-12)');
INSERT INTO stays VALUES (17, '[2026-10-11,2026-10-13)');
INSERT INTO stays VALUES (17, '[2026-10-12,2026-10-13)');
SELECT daterange('2026-10-10', '2026-10-11', '[]');

CREATE EXTENSION
CREATE TABLE
INSERT 0 1
psql:reader_check.sql:8: ERROR:  conflicting key value violates exclusion constraint "stays_room_id_stay_excl"
DETAIL:  Key (room_id, stay)=(17, [2026-10-11,2026-10-13)) conflicts with existing key (room_id, stay)=(17, [2026-10-10,2026-10-12)).
INSERT 0 1
        daterange
-------------------------
 [2026-10-10,2026-10-12)
(1 row)

What just happened? The second stay overlapped the night of the 11th, so it failed. The third stay starts on the first stay's check-out day, so the ranges only touch and the insert succeeded.

The last line shows PostgreSQL turning an inclusive range into half-open form: 10 to 11 October inclusive became [2026-10-10,2026-10-12). The bracket includes the date and the parenthesis excludes it.

Concurrency is covered too. When two sessions inserted overlapping stays at once, the second waited for the first. It failed with error 23P01 if the first committed, and succeeded if the first rolled back.

Rooms sold by type: a counter that cannot go over

For 20 identical deluxe rooms, the same pattern works on a counter. The claim is UPDATE ... SET booked = booked + 1 WHERE booked < total, backed by a CHECK (booked <= total) rule.

In a test with total set to 2, three guests raced for the last rooms. The updates changed 1, 1 and 0 rows, and booked ended at 2. A careless update without the condition hit the CHECK rule and failed with error 23514.

Give every booking attempt an idempotency key, a unique id the app creates once and sends with every retry. If the server has already seen the key, it returns the first result instead of holding or charging again.

Mountain networks are patchy, and guests tap Pay twice. Store the key under a unique constraint, inserted first in the same transaction as the hold. In a test, a second copy of one request waited for the first, then failed with error 23505, which ends that copy's transaction before it can hold anything.

Hold expiry, late payments and rushes for one seat are covered in depth by the ticket booking design guide. Hotel rooms usually see less fighting over one item than concert seats, so short holds are often enough.

Because search reads a copy of availability that lags behind real bookings. A room sold after the copy last updated still looks free until the next update reaches the index.

The old index was rebuilt every 15 minutes. On a festival weekend, a popular room could sell out and look free for most of that window. Guests tapped it, filled in their details and got an error at the end.

The fix is an event-driven update. Each time the booking service confirms, cancels or releases nights, it records a small message called an event. An indexer worker reads the events and updates that room's document.

Holds stay out of the index, because a hold that lapses sends no event. The index marks only confirmed nights as booked, and the database pass drops rooms on hold.

The events travel through Kafka, an open-source system that stores streams of events durably. Events are not deleted when they are read; each topic keeps them for a retention period you set. If the indexer crashes, the events are still there when it restarts, as long as that is within the retention period.

Elasticsearch adds its own small delay. A change becomes searchable after the next refresh, which by default runs every second on indices that received a search in the last 30 seconds.

• Write each event to an outbox table in the same transaction as the booking. A relay then publishes it, so a booking can never be saved without its event.
• Make the indexer safe to run twice on one event. The relay may publish a message more than once, so the consumer must be idempotent.
• Run a nightly job that compares the index with the database and repairs any room document that has drifted.

The confirming query from the second search pass is below. It returns only rooms free on every night of the stay, and it counts a lapsed hold as free, exactly like hold_room. Here index is a stand-in for the Elasticsearch client, and conn is a psycopg2 connection.

FREE_EVERY_NIGHT = """
SELECT room_id FROM room_nights
WHERE room_id = ANY(%s) AND night >= %s AND night < %s
  AND (booking_id IS NULL OR held_until < now())
GROUP BY room_id
HAVING count(*) = %s
"""

def search_stays(index, conn, town, check_in, check_out, guests, page=20):
    hits = index.find(town=town, guests=guests, free_from=check_in,
                      free_to=check_out, limit=page * 3)  # spare hits
    nights = (check_out - check_in).days
    with conn:
        cur = conn.cursor()
        cur.execute(FREE_EVERY_NIGHT,
                    ([h.room_id for h in hits], check_in, check_out, nights))
        free = {row[0] for row in cur.fetchall()}
    return [h for h in hits if h.room_id in free][:page]

In a test with a stand-in index of 60 hits, 7 rooms booked on the 9th and one never opened were dropped. A room with a lapsed hold stayed in, and the page still held 20 rooms.

Prices need the same care. The price on a search card is a hint from the index. Checkout recalculates the total from the night rows, so the guest pays the real price for the real nights.

Keep one calendar as the master and sync every other site to it as fast as their connection allows. Across separate companies you can only shrink the window in which two sites sell the same night, not close it.

Many homestay hosts list one room on several sites. Each site has its own calendar, and none can lock another's. That is how the Dhanaulti cottage sold twice: one family booked on this app and the other on a second site, minutes apart.

The simplest link is a calendar feed in iCalendar format, often called iCal, a standard text format for calendar events. Each site publishes a link listing booked dates, and the others fetch it now and then.

Feeds are slow by design. As of October 2026, Airbnb's help centre says its calendar updates every 3 hours from connected calendars, and hosts can press Refresh to check sooner. That is fine on a quiet Tuesday and risky on a festival weekend.

• Where a partner offers a direct connection that pushes each change at once, use it instead of a feed.
• Publish your own feed, so other sites learn about your bookings the next time they fetch it.
• Agree in advance what happens after a cross-site clash: who moves the guest, and who pays for the new room.

The last rule matters most. A clear rescue plan for the unlucky guest is part of the design, not an apology after it.

Scale search and booking separately. Search is almost all of the traffic, so it gets index copies and caching. Booking is a small share but must be exactly right, so it stays on a carefully split database.

Here is the rough maths with made-up figures. A normal day brings about 6 searches a second and 40 bookings an hour. The Diwali weekend brings 60 searches a second and 400 bookings an hour.

Check the ratio yourself: 60 searches a second is 216,000 searches an hour, and 216,000 divided by 400 bookings is 540. The normal day gives the same 540 searches for every booking.

• Search: add Elasticsearch replica shards, copies of the index that can also answer searches, and more search servers behind the load balancer, the router that spreads requests across servers.
• Property pages: serve photos from a content delivery network, servers near the guest that keep copies of files, and cache descriptions and rules.
• Booking: keep every night of one property in one database. Split properties across databases by property id only when one database gets too busy.

Splitting data across databases by a key is called sharding. Property id is a good key because a stay touches only one property, so each booking transaction stays inside one shard.

Follow the guest's two paths in the overview: the busy search path, and the narrow booking path to the database. The event queue connects them.

Explain it as two systems sharing one source of truth: a fast, slightly stale search path, and a strict booking path that settles every race in the database.

1. 1

   Ask about the product

   Unique homes or hotels with room types? Instant booking or host approval? Is the property listed on other sites too?

2. 2

   State the two requirements

   Search must be fast and may be slightly old. Booking must never sell one night twice, even at peak.

3. 3

   Draw the data model

   Show one row per room per night with half-open date ranges. Offer the booking-range table with an exclusion constraint as the alternative.

4. 4

   Make booking one atomic step

   Claim all nights in one transaction, only where they are free, and check the row count. Add a short self-expiring hold and an idempotency key.

5. 5

   Design search in two passes

   A search index filtered by place, dates and features, kept fresh by booking events from an outbox. Then confirm the short list in the database.

6. 6

   Scale and handle the edges

   Index replicas and caching for search, sharding by property id for booking. Finish with calendar sync and the rescue plan for a cross-site clash.