Make Double-Booking Impossible in the Database

To prevent double-booking in PostgreSQL, enforce the reservation rule at the write: a unique key for a fixed slot or seat, an exclusion constraint for overlapping time ranges. Keep the availability check for the customer, but let the database settle competing reservations. The choice depends on what you sell.

Two people grab the same slot. Both get a confirmation email. Now someone has to apologize, refund, or rebook, and your product looks unreliable for a reason that has nothing to do with your product. The fix everyone reaches for first is an application-level check. Read the calendar, see if the slot is free, then write the booking. It reads like correct code. It fails the moment two people click at once.

The practical question is where the final decision belongs. A calendar can look available and still lose the race.

The short version: Model the thing that cannot be sold twice. Fixed slots need one consistent slot identity; flexible appointments need a no-overlap rule. Catch the losing reservation before sending confirmation, offer an alternative, and test concurrent requests against the actual booking path.

Why app-level availability checks fail under load

The flaw is the gap between the read and the write.

Picture the standard approach. A request comes in, your code queries the bookings table, sees the 3pm slot is open, and inserts a new row. Perfectly reasonable. Now picture two requests arriving in the same handful of milliseconds:

Request A  --read--> "3pm is free"
Request B  --read--> "3pm is free"      (A hasn't written yet)
Request A  --write-> books 3pm          OK
Request B  --write-> books 3pm          OK  <- double-booked

Both reads happened before either write. The check passed for both. The slot is now booked twice, and nothing in your code did anything “wrong” in isolation. Each request followed the rules. The rules just had a hole in them.

Lower latency only makes the race less frequent. A transaction by itself does not fix it under every isolation level. You can close the race with a database constraint, appropriate locking, or serializable isolation with retry handling. The important distinction is between a naive pre-check and a concurrency-safe database protocol.

That is why I put the final guard on the write. The pre-check can help a customer choose; it should not be the promise.

Unique constraint or exclusion constraint?

For fixed inventory, PostgreSQL’s unique constraints enforce one row per combination of keys. Make the booking keys non-null. Choose the combination that represents the inventory:

What the customer reserves	Candidate rule	What the model must establish
One resource in a predefined slot	UNIQUE (resource_id, slot_id)	Slot identities are canonical and cannot overlap for that resource
One seat at one event	UNIQUE (event_id, seat_id)	A seat belongs to the intended event inventory
One resource for an arbitrary interval	Exclude equal resources with overlapping ranges	Duration, boundaries, and resource identity match the reservation

A unique start time is insufficient for flexible appointments: 15:00–16:00 UTC and 15:30–16:30 UTC on the same day have different starts but compete for the same room. PostgreSQL’s range documentation shows the exclusion pattern for this case.

Decide what cancellation means before selecting either rule. A retained cancelled row should not silently block inventory forever. The schema below treats every row as occupying time; it does not implement reservation statuses, expiring holds, or cancellation history.

Pushing the rule into the database

PostgreSQL has a feature built for this rule: the exclusion constraint. Instead of relying on a separate availability query, you declare which row combinations may not coexist. PostgreSQL’s constraint documentation explains that an exclusion constraint guarantees that not all specified comparisons can be true for two rows.

First you need the building blocks. A booking has a resource (the room, the person, the rental) and a time range, and you want the btree_gist extension so the constraint can mix an equality check with a range-overlap check:

create extension if not exists btree_gist;

create table bookings (
  id          bigint generated always as identity primary key,
  resource_id bigint not null,
  during      tstzrange not null,

  -- No two bookings for the same resource can overlap in time.
  constraint no_overlap
    exclude using gist (
      resource_id with =,
      during      with &&
    )
);

That exclude using gist (...) line is the whole game. It tells Postgres: for any two rows that share the same resource_id, their during ranges must not overlap (&& is the range-overlap operator). This is not “the app should check.” The database will refuse to write the second row.

Now an insert is just intent. The database is the one that says yes or no:

-- Books resource 42 from 15:00 to 16:00 UTC:
insert into bookings (resource_id, during)
values (42, '[2026-06-01 15:00+00, 2026-06-01 16:00+00)');

-- A second, overlapping booking for the same resource:
insert into bookings (resource_id, during)
values (42, '[2026-06-01 15:30+00, 2026-06-01 16:30+00)');
-- ERROR: conflicting key value violates exclusion constraint "no_overlap"

The example uses explicit UTC offsets. Its half-open interval includes the start and excludes the end, so adjacent appointments can meet at 16:00 without overlapping. Add setup or cleanup time to the occupied interval if the resource needs a buffer. Validate that submitted ranges have finite bounds and positive duration; this small schema only demonstrates overlap protection.

What this changes about what you have to trust

This is the part that matters more than the syntax. Moving the rule into the database changes what you are forced to trust.

With an app-level check, you are trusting that every code path that ever writes a booking remembered to run the check first. The web handler, the admin tool, the bulk importer, the cron job, an AI caller booking a lead mid-conversation, the migration script someone runs by hand at midnight. Miss the check in any one of them and the hole reopens. You are also trusting that two requests will never truly arrive at once, which is a bet you lose the day you get popular.

With the constraint, you trust one thing: the rule on the table. Every path that writes a booking obeys it whether it meant to or not. The app expresses what it wants to happen. The database refuses anything that breaks the rule. Your handler still catches the error and shows the user a friendly “that slot was just taken, here are nearby times” message, but that is presentation, not protection. Delete that handling tomorrow and the data is still correct.

Put the rule where the data lives

This is a habit, not a one-off trick. The pattern shows up across everything I build: put the rule where the data lives, not where the code happens to touch it.

A few examples of the same idea:

The rule	The weak way (in code)	The strong way (on the data)
Emails must be unique	SELECT before INSERT to check	UNIQUE constraint
Bookings can’t overlap	Read the calendar, then write	EXCLUDE constraint
A status value is allowed	A comment listing valid states	A CHECK constraint
A child row needs a parent	Hope the app inserts in order	FOREIGN KEY

A CHECK validates the row being written. It does not compare the previous status with the next one. To restrict transitions, use a suitable trigger with OLD and NEW, or a transactional update design that enforces the allowed prior state across every writer. A list of valid states is not a transition policy.

Making overlapping rows invalid

The schema makes an overlapping booking row invalid for the resource and range modeled by the constraint. That is a precise guarantee. It still depends on modeling status, range boundaries, time zones, buffers, resource identity, and any administrative override correctly.

The distinction is the whole point. A pre-check estimates availability before the write. A constraint decides whether the row may exist at the write. The application can still show available times first, then catch a conflict and offer alternatives if another request wins the slot.

When a client asks how double-booking is prevented, the useful answer names the enforced invariant: the database rejects overlapping reservations for the same modeled resource. Then explain the boundaries and the conflict experience.

I built this rule into a booking system with database-enforced overlap protection.

Frequently asked questions

What causes a double-booking bug?

A common cause is a race condition between reading availability and writing the booking. Two requests check the same slot at nearly the same moment, both see it as free because neither has written yet, and both then insert a booking. The check passed for both, so the slot ends up booked twice.

Why doesn’t an application-level availability check prevent double-booking?

Because a separate read and write can race under concurrency. Lower latency does not guarantee correctness. A constraint, appropriate locking, or a correctly retried serializable transaction can make the final decision safely.

What is a PostgreSQL exclusion constraint?

It is a table-level rule that prevents rows from coexisting if they conflict according to operators you define. For booking systems you typically combine = on the resource with && (range overlap) on a time range, so Postgres rejects any new booking that overlaps an existing one for the same resource. This bigint resource example uses btree_gist to support the equality check alongside range overlap.

Do I still need to handle the error in my application code?

Yes. Catch the conflict and show a useful message such as “that time was just booked” with nearby alternatives. The constraint protects the modeled data invariant, while application handling protects the customer experience and preserves expected transaction behavior.

Does a database constraint hurt booking performance?

Exclusion constraints create an index for the specified access method, but performance depends on table size, write volume, range distribution, and query patterns. Benchmark representative concurrent inserts and availability queries before setting capacity assumptions.