Hotel Room Booking System Design: Per-Night Inventory & Overbooking Prevention

1. Overview

The hotel room booking problem sounds simple at first. Check if rooms are available, then allow the booking. But when two guests simultaneously click “Book” for the last room on December 31st, your system can easily sell one room to two people.

This article walks through a production-grade design: requirements, per-night inventory model, APIs, the hold-then-confirm flow, how to prevent overbooking using PostgreSQL, and handling traffic spikes. After reading, you’ll be able to build the core booking logic using Spring Boot 3.x without unintended overbooking.

2. Requirements

2.1. Functional Requirements

• Find available rooms by hotel, room type, check-in date, and check-out date.
• Hold a room for 10 minutes while the guest completes payment.
• Confirm the booking after successful payment.
• Cancel a booking and return inventory.

2.2. Scale and Constraints

• Around 50,000 hotels. Each hotel has an average of 5 room types. The system opens bookings up to 365 days in advance.
• Read volume vastly exceeds write volume. The search-to-booking ratio is roughly 100:1.
• Around 50 bookings per second during peak hours. During a flash sale, this can spike 20x.
• Hard constraint: never sell more rooms than configured. Search results are allowed to lag by a few seconds.

Inventory row count comes to around 50,000 × 5 × 365 ≈ 91 million rows. A well-indexed PostgreSQL table can handle this.

3. Data Model

The most important decision in this design: guests don’t book a specific room (like #302), but a room type across a range of nights. The system assigns a specific room number only at check-in. Therefore, inventory is keyed by (hotel, room_type, night).

CREATE TABLE room_inventory (
    hotel_id      BIGINT  NOT NULL,
    room_type_id  BIGINT  NOT NULL,
    stay_date     DATE    NOT NULL,
    total_rooms   INT     NOT NULL,
    reserved      INT     NOT NULL DEFAULT 0,
    PRIMARY KEY (hotel_id, room_type_id, stay_date),
    CONSTRAINT chk_not_oversold CHECK (reserved >= 0 AND reserved <= total_rooms)
);

CREATE TABLE booking (
    id               BIGSERIAL PRIMARY KEY,
    idempotency_key  VARCHAR(64) NOT NULL UNIQUE,
    customer_id      BIGINT      NOT NULL,
    hotel_id         BIGINT      NOT NULL,
    room_type_id     BIGINT      NOT NULL,
    check_in         DATE        NOT NULL,
    check_out        DATE        NOT NULL,
    quantity         INT         NOT NULL,
    status           VARCHAR(16) NOT NULL, -- HOLD, CONFIRMED, CANCELLED, EXPIRED
    expires_at       TIMESTAMPTZ NOT NULL
);

CREATE INDEX idx_booking_expiry ON booking (expires_at) WHERE status = 'HOLD';

Key points explained:

• A stay from Dec 10 to Dec 13 occupies 3 nights: the 10th, 11th, and 12th. The checkout date is not counted. Every query uses the half-open range [check_in, check_out).
• CHECK (reserved <= total_rooms) is the final safeguard. If there’s a bug in the code, the database will reject an oversale rather than silently allowing it.
• The partial index idx_booking_expiry only contains bookings with status HOLD. A cleanup job scans it quickly without the index growing from historical data.

Note: Many hotels intentionally overbook by 3–5% because they know some guests will cancel. Express this policy by setting total_rooms higher than the physical number of rooms. Don’t loosen the overbooking prevention logic itself.

4. API

GET  /hotels/{hotelId}/availability?checkIn=2026-12-10&checkOut=2026-12-13
POST /bookings                      (Header: Idempotency-Key)  -> 201, status=HOLD
POST /bookings/{id}/confirm         (called after successful payment)
POST /bookings/{id}/cancel

Request body for creating a booking:

public record HoldRequest(long customerId, long hotelId, long roomTypeId,
                          LocalDate checkIn, LocalDate checkOut, int quantity) {
    public HoldRequest {
        if (!checkOut.isAfter(checkIn)) throw new IllegalArgumentException("checkOut must be after checkIn");
        if (quantity < 1) throw new IllegalArgumentException("quantity >= 1");
    }

    public int nights() {
        return (int) ChronoUnit.DAYS.between(checkIn, checkOut);
    }
}

The Idempotency-Key header is generated by the client (UUID). When the network is flaky, the client can retry freely without creating duplicate bookings.

5. Processing Flow

Guest ──► POST /bookings ──► deduct inventory for all nights (1 transaction) ──► booking HOLD, expires in 10'
      ──► payment ──► POST /confirm ──► HOLD → CONFIRMED
                   └► no payment within 10' ──► cleanup job: HOLD → EXPIRED, restore inventory

Inventory is deducted immediately when a hold is created, not when payment succeeds. This ensures that once a guest reaches the payment page, rooms are guaranteed available. In return, the system must have a mechanism to restore rooms when a hold expires.

6. Hard Problem 1: Preventing Overbooking

6.1. Why “read-then-write” is unsafe

The naive approach is to SELECT the remaining rooms, check in Java, then UPDATE. Two transactions can both read reserved = 9, total = 10 and both write reserved = 10. Result: two guests holding the same room. This is the classic lost-update anomaly.

6.2. Lock in order, then update with a condition

Add the dependency:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

Repository with native queries:

public interface InventoryRepository extends Repository<RoomInventory, InventoryId> {

    @Query(value = """
        SELECT stay_date FROM room_inventory
        WHERE hotel_id = :hotelId AND room_type_id = :roomTypeId
          AND stay_date >= :checkIn AND stay_date < :checkOut
        ORDER BY stay_date
        FOR UPDATE
        """, nativeQuery = true)
    List<LocalDate> lockNights(long hotelId, long roomTypeId, LocalDate checkIn, LocalDate checkOut);

    @Modifying
    @Query(value = """
        UPDATE room_inventory SET reserved = reserved + :qty
        WHERE hotel_id = :hotelId AND room_type_id = :roomTypeId
          AND stay_date >= :checkIn AND stay_date < :checkOut
          AND total_rooms - reserved >= :qty
        """, nativeQuery = true)
    int reserve(long hotelId, long roomTypeId, LocalDate checkIn, LocalDate checkOut, int qty);

    @Modifying
    @Query(value = """
        UPDATE room_inventory SET reserved = reserved - :qty
        WHERE hotel_id = :hotelId AND room_type_id = :roomTypeId
          AND stay_date >= :checkIn AND stay_date < :checkOut
        """, nativeQuery = true)
    int release(long hotelId, long roomTypeId, LocalDate checkIn, LocalDate checkOut, int qty);
}

Service to create a hold:

@Service
public class BookingService {

    private static final Duration HOLD_TTL = Duration.ofMinutes(10);
    private final InventoryRepository inventory;
    private final BookingRepository bookings;
    private final Clock clock;

    public BookingService(InventoryRepository inventory, BookingRepository bookings, Clock clock) {
        this.inventory = inventory;
        this.bookings = bookings;
        this.clock = clock;
    }

    @Transactional
    public Booking hold(HoldRequest req, String idempotencyKey) {
        Optional<Booking> existing = bookings.findByIdempotencyKey(idempotencyKey);
        if (existing.isPresent()) return existing.get();

        inventory.lockNights(req.hotelId(), req.roomTypeId(), req.checkIn(), req.checkOut());
        int updated = inventory.reserve(req.hotelId(), req.roomTypeId(),
                req.checkIn(), req.checkOut(), req.quantity());

        if (updated != req.nights()) {
            throw new RoomUnavailableException(req.hotelId(), req.checkIn(), req.checkOut());
        }
        return bookings.save(Booking.hold(req, idempotencyKey, Instant.now(clock).plus(HOLD_TTL)));
    }
}

Here’s how each step works:

1. lockNights locks the inventory rows in order by stay_date. Suppose guest A books the 10th–13th and guest B books the 11th–14th. If locking doesn’t follow a consistent order, A locks the 10th and waits for the 11th, B locks the 12th and waits for the 11th, and both may deadlock. When all transactions lock in the same order, this doesn’t happen.
2. reserve only increments reserved for nights where there’s available capacity. It returns the number of rows updated.
3. If the row count is less than the number of nights, it could mean a night is fully booked or not yet open for sale (no row exists). The service throws RoomUnavailableException. Since this is a RuntimeException, @Transactional rolls back the entire operation, including any nights already incremented.

When two requests race for the last room:

T1: lockNights (acquires lock) → reserve = 3 rows → INSERT booking → COMMIT
T2: lockNights (waits for T1)  → reserve = 0 rows → RoomUnavailableException → ROLLBACK → HTTP 409

You don’t need to raise isolation to SERIALIZABLE. READ COMMITTED with row locking is sufficient.

6.3. Idempotency with concurrent retries

Two requests with the same Idempotency-Key might both pass the findByIdempotencyKey check. The UNIQUE constraint will block the second one with DataIntegrityViolationException. Catch this at the controller layer, fetch the booking by key again, and return the old result. The losing transaction rolls back, so any inventory it deducted is also restored.

7. Hard Problem 2: Hold Expiry and Confirmation Races

A cleanup job runs every 30 seconds:

@Component
public class HoldExpiryJob {

    private final BookingRepository bookings;
    private final InventoryRepository inventory;

    public HoldExpiryJob(BookingRepository bookings, InventoryRepository inventory) {
        this.bookings = bookings;
        this.inventory = inventory;
    }

    @Scheduled(fixedDelay = 30_000)
    @Transactional
    public void expireHolds() {
        for (Booking b : bookings.lockExpiredHolds(500)) {
            inventory.release(b.getHotelId(), b.getRoomTypeId(), b.getCheckIn(), b.getCheckOut(), b.getQuantity());
            b.markExpired();
        }
    }
}

FOR UPDATE SKIP LOCKED lets you run multiple instances without processing the same booking twice. Each batch fetches only 500 rows to keep transactions short.

Remember to enable @EnableScheduling on a @Configuration class. When releasing inventory, also call release in the same night order as lock to prevent deadlocks with the hold thread.

The most dangerous race condition is: the guest completes payment just as the job expires the hold. You prevent this with a conditional status UPDATE:

UPDATE booking SET status = 'CONFIRMED'
WHERE id = :id AND status = 'HOLD' AND expires_at > now();

If this returns 0 rows, the hold has expired and the room may already belong to someone else. At this point, the service must refund the guest, or retry the hold before confirming. Never transition from EXPIRED to CONFIRMED without re-checking and potentially re-holding the inventory.

8. Hard Problem 3: Traffic Spikes

Since read volume is 100x write volume, search queries must never contend with row locks. You can cache room availability on Redis with a short TTL:

@Service
public class AvailabilityCache {

    private final StringRedisTemplate redis;

    public AvailabilityCache(StringRedisTemplate redis) {
        this.redis = redis;
    }

    public Optional<Integer> get(long hotelId, long roomTypeId, LocalDate night) {
        String v = redis.opsForValue().get(key(hotelId, roomTypeId, night));
        return Optional.ofNullable(v).map(Integer::valueOf);
    }

    public void put(long hotelId, long roomTypeId, LocalDate night, int available) {
        redis.opsForValue().set(key(hotelId, roomTypeId, night), String.valueOf(available), Duration.ofSeconds(30));
    }

    private String key(long h, long rt, LocalDate d) {
        return "avail:%d:%d:%s".formatted(h, rt, d);
    }
}

Principles to maintain:

• Redis only serves reads. PostgreSQL is the source of truth. If a search shows “1 room left” but the hold step fails, the guest just sees HTTP 409 with a message “just sold out”. No erroneous bookings are created.
• After each hold, release, or cancel, write an event to an outbox table within the same transaction. A relay pushes this event to a Kafka topic inventory-changed. A consumer invalidates the Redis key or updates the search index. The outbox ensures no event is lost if the app crashes right after commit.
• During a flash sale, row locks concentrate on a few “hot” hotels. You should rate-limit by hotelId at the API gateway and set lock_timeout around 2 seconds for the booking transaction. This way, requests fail fast instead of exhausting the connection pool.

9. Testing for Overbooking

The most important test is having many threads race for the last room. You need real PostgreSQL; H2 doesn’t emulate locking correctly. Add the dependencies:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-testcontainers</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>postgresql</artifactId>
    <scope>test</scope>
</dependency>

@SpringBootTest
@Testcontainers
class BookingServiceConcurrencyTest {

    @Container
    @ServiceConnection
    static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:16");

    @Autowired BookingService bookingService;
    @Autowired JdbcTemplate jdbc;

    @Test
    void onlyOneGuestGetsTheLastRoom() throws Exception {
        for (int d = 10; d <= 12; d++) {
            jdbc.update("INSERT INTO room_inventory VALUES (1, 1, ?, 1, 0)", LocalDate.of(2026, 12, d));
        }
        var req = new HoldRequest(42L, 1L, 1L, LocalDate.of(2026, 12, 10), LocalDate.of(2026, 12, 13), 1);

        AtomicInteger success = new AtomicInteger();
        try (var pool = Executors.newVirtualThreadPerTaskExecutor()) {
            List<Future<?>> futures = IntStream.range(0, 20)
                .mapToObj(i -> pool.submit(() -> {
                    try {
                        bookingService.hold(req, "key-" + i);
                        success.incrementAndGet();
                    } catch (RoomUnavailableException ignored) { }
                }))
                .toList();
            for (Future<?> f : futures) f.get();
        }

        assertThat(success.get()).isEqualTo(1);
        assertThat(jdbc.queryForList("SELECT reserved FROM room_inventory", Integer.class))
            .containsOnly(1);
    }
}

Twenty virtual threads all call hold with different keys. The test asserts that exactly one thread succeeds, and all three nights show reserved = 1. No night gets over-incremented due to a missed rollback. Tables are created with a schema.sql script from section 3, or via Flyway migration.

10. Common Mistakes

• Counting the checkout day as a night. This bug causes guests to lose an extra night’s inventory. Always use the range [check_in, check_out) everywhere.
• Checking inventory in Java after an unlocked SELECT. This causes lost updates. The condition must be in the UPDATE clause or come after FOR UPDATE.
• Not comparing the number of updated rows to the number of nights. If a night has no row yet, UPDATE still “succeeds” for the other nights.
• Calling a payment API inside a transaction holding locks. The lock is held for seconds, and the entire hotel gets bottlenecked. Instead, separate the steps: hold (commit), pay, then confirm.
• Using Redis DECR as the source of truth. When Redis failover loses data, you lose inventory too. This approach is only worth considering if load far exceeds the database’s capacity, and even then, you still need periodic reconciliation with the DB.

Note: The per-room-type count model doesn’t work when guests choose a specific room number (e.g., a private villa or homestay). In that case, use a room_night(room_id, stay_date) table with a UNIQUE constraint, or PostgreSQL’s exclusion constraint on a daterange.

11. Conclusion

The core of a hotel booking system is an inventory table keyed by night. On top of it sits a short transaction: lock nights in order, update conditionally, and check the affected row count. A CHECK constraint provides the final safety net. Holds have expiration times, paired with a cleanup job using SKIP LOCKED, which solves the problem of guests who abandon payment. A conditional status UPDATE resolves races between confirm and expire.

The read side is offloaded to Redis and synced via an outbox table with Kafka, so searches can handle large traffic without weakening write consistency. You can start with this design, and only shard by hotel_id if a single PostgreSQL node truly can’t keep up.