PostgreSQL Overlapping Hotel Bookings

Booking a hotel room is not just a matter of running SELECT and INSERT.
The real problem appears when two users try to book the same room at nearly the same time.

If your application only checks whether a room is available before inserting the booking, both requests could potentially pass the check and create overlapping reservations.
PostgreSQL can prevent this directly at the database level using an EXCLUDE constraint.

---

How to Prevent Overlapping Bookings in PostgreSQL​

The basic idea is simple:
The same room cannot have two bookings whose time ranges overlap.

For example, you can create an exclusion constraint like this:

CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE bookings
ADD CONSTRAINT no_overlapping_bookings
EXCLUDE USING gist (
room_id WITH =,
tstzrange(starts_at, ends_at, '[)') WITH &&
);

This tells PostgreSQL to reject a booking when both conditions are true:

• The room_id is the same.
• The booking time ranges overlap.

The && operator checks whether two ranges overlap, while [) means the start time is included and the end time is excluded.
So a booking from 2:00 PM to 4:00 PM does not overlap with another booking starting exactly at 4:00 PM.

---

Why Use an EXCLUDE Constraint?​

A common approach is to check availability in application code first:

SELECT *
FROM bookings
WHERE room_id = 10
AND tstzrange(starts_at, ends_at, '[)')
&& tstzrange('2026-10-10 14:00', '2026-10-10 16:00', '[)');

Then the application inserts the new booking if no existing reservation is found.
The problem is that two requests can perform this check at almost the same time.
Both requests may see the room as available before either one inserts a booking.
This is a classic race condition.
An EXCLUDE constraint moves the rule into the database itself, so PostgreSQL can enforce it even when multiple requests arrive concurrently.

---

What Does EXCLUDE USING GIST Mean?​

The important part of the constraint is:

EXCLUDE USING gist (
room_id WITH =,
tstzrange(starts_at, ends_at, '[)') WITH &&
)

Each part defines a condition that PostgreSQL must prevent from being true for two rows.
room_id WITH = means the two bookings must have the same room.
tstzrange(starts_at, ends_at, '[)') WITH && means their time ranges must overlap.
If both conditions match, PostgreSQL rejects the new row.
In other words:
Same room + overlapping time = booking rejected.

---

Why Is btree_gist Required?​

If room_id is an integer or another data type that normally uses B-tree comparison operators, you may need the btree_gist extension to use that equality comparison inside a GiST exclusion constraint.
Enable it with:

CREATE EXTENSION IF NOT EXISTS btree_gist;

The exact requirements depend on the data types used by your table, but this extension is commonly needed for hotel and reservation systems where the room identifier is an integer.

---

Example Bookings​

Imagine the table contains these bookings:

Room	Start	End
10	10:00	12:00
10	14:00	16:00
11	10:00	12:00

A new booking for room 10 from 11:00 to 13:00 would be rejected because it overlaps with the existing 10:00–12:00 reservation.
A booking for room 10 from 12:00 to 14:00 would be allowed when using the [) range because the previous booking ends exactly when the new one starts.
A booking for room 11 at the same time could also be allowed because it belongs to a different room.

---

Why This Is Better Than Application-Only Validation​

Application-level validation is still useful for providing a good user experience.
You can check availability before attempting the booking and show the user a helpful message.
But the database should still enforce the rule.

This gives you two layers of protection:

• Application: Check availability and provide a clear response to the user.
• Database: Enforce the rule and prevent invalid overlapping bookings.

This is especially important for systems handling concurrent requests.

---

Handling a Rejected Booking​

When PostgreSQL detects an overlap, the INSERT or UPDATE operation fails because it violates the exclusion constraint.
Your application should catch the database error and tell the user that the room is no longer available.

The important point is that the application should not assume that a previous availability check guarantees that the booking will succeed.
The database constraint is the final authority.

---

The Key Idea​

For a reservation system, the rule can be expressed very simply:
The same room must not have overlapping time ranges.

PostgreSQL's EXCLUDE USING gist constraint allows you to enforce that rule directly in the database instead of relying entirely on application logic.

This makes it a powerful solution for hotel bookings, room reservations, appointments, equipment rentals, and other systems where time ranges must not overlap.

---

Frequently Asked Questions​

Can PostgreSQL prevent double bookings?​

Yes. An EXCLUDE USING gist constraint can prevent two rows from using the same resource during overlapping time ranges.

What does the && operator do?​

For PostgreSQL range types, && checks whether two ranges overlap.

What does '[)' mean?​

It creates a range that includes the starting value but excludes the ending value. This is useful for bookings because one reservation can end exactly when another begins.

Is an EXCLUDE constraint better than checking availability in application code?​

For enforcing the database rule, yes. Application checks are useful for user experience, but the database constraint protects against race conditions and concurrent requests.