How to Prevent Equipment Double-Booking with PostgreSQL (A Race Condition Fix)

Imagine your company has one expensive camera available for Friday afternoon.

Two employees open the booking page at almost the same time.

Both see Available.

Both click Reserve.

And both receive a confirmation.

Now two people think they've booked the same camera.

This isn't necessarily a frontend bug. It's a concurrency problem that can happen even when your availability-checking code looks perfectly reasonable.

Let's walk through a simple solution using PostgreSQL and Node.js.

The Problem: Checking Availability Isn't Enough

A typical booking API follows this process:

1. Receive the equipment ID and requested dates.
2. Search for an overlapping reservation.
3. If none exists, create a booking.
4. Return a success response.

Something like:

const existing = await db.query( `SELECT id FROM bookings WHERE asset_id = $1 AND starts_at < $3 AND ends_at > $2`, [assetId, startsAt, endsAt] ); if (existing.rowCount === 0) { // Create booking } 

This works when requests arrive one after another.

But consider two simultaneous requests:

Both requests passed the availability check before either booking was committed.

This is a classic race condition, specifically a check-then-insert problem.

Wrapping those two statements in a normal transaction doesn't automatically solve it under PostgreSQL's default Read Committed isolation level.

We need the database to enforce the rule.

Step 1: Design the Booking Table

For this example, every booking belongs to one physical asset.

We'll store timestamps using TIMESTAMPTZ to represent actual instants rather than assuming every employee is in the same timezone.

CREATE EXTENSION IF NOT EXISTS btree_gist; CREATE TABLE assets ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE bookings ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, asset_id BIGINT NOT NULL REFERENCES assets(id), starts_at TIMESTAMPTZ NOT NULL, ends_at TIMESTAMPTZ NOT NULL, status TEXT NOT NULL DEFAULT 'confirmed' CHECK (status IN ( 'confirmed', 'checked_out', 'completed', 'cancelled' )), CHECK (ends_at > starts_at) ); 

Notice two decisions.

First, a booking must have an end time later than its start time.

Second, we distinguish active reservations from completed or cancelled ones.

That distinction becomes useful when enforcing booking conflicts.

Step 2: Understand Overlapping Time Ranges

Suppose an employee reserves a laptop from 10:00 to 12:00.

Another employee wants it from 11:00 to 13:00.

Those reservations overlap.

But a booking from 12:00 to 14:00 should normally be allowed because the first reservation has ended.

PostgreSQL has a built-in range type called tstzrange for timestamp ranges with time zones.

We can represent the booking interval like this:

tstzrange(starts_at, ends_at, '[)') 

The [) means the start is included and the end is excluded.

This makes back-to-back bookings possible.

PostgreSQL also has the overlap operator:

&& 

For example:

SELECT tstzrange( '2026-11-13 10:00+00', '2026-11-13 12:00+00', '[)' ) && tstzrange( '2026-11-13 11:00+00', '2026-11-13 13:00+00', '[)' ) AS overlaps; 

Result:

overlaps -------- true 

That gives us the foundation for a database-enforced booking rule.

Step 3: Add an Exclusion Constraint

This is the important part.

ALTER TABLE bookings ADD CONSTRAINT no_overlapping_active_bookings EXCLUDE USING gist ( asset_id WITH =, tstzrange(starts_at, ends_at, '[)') WITH && ) WHERE (status IN ('confirmed', 'checked_out')); 

Let's break it down.

asset_id WITH =

Two bookings can conflict only when they concern the same asset.

tstzrange(...) WITH &&

Two bookings conflict when their time intervals overlap.

WHERE (status IN (...))

Only confirmed or checked-out bookings block another reservation.

Cancelled and completed bookings don't participate in this specific constraint.

The btree_gist extension lets PostgreSQL combine equality comparisons on an ordinary scalar asset ID with range-overlap comparisons in a GiST exclusion constraint.

Now PostgreSQL itself rejects conflicting active bookings, including concurrent attempts.

No matter how many API instances you run, they share the same database rule.

One caveat: this example assumes a completed booking no longer reserves future time. If your application has pending approvals, late returns, or temporary booking holds, design the status rules to represent those cases correctly.

Step 4: Test the Constraint

Create one asset:

INSERT INTO assets (name) VALUES ('Camera A'); 

Create its first reservation:

INSERT INTO bookings ( asset_id, starts_at, ends_at ) VALUES ( 1, '2026-11-13 10:00+00', '2026-11-13 12:00+00' ); 

Now try another booking for the same asset:

INSERT INTO bookings ( asset_id, starts_at, ends_at ) VALUES ( 1, '2026-11-13 11:00+00', '2026-11-13 13:00+00' ); 

PostgreSQL rejects it with an exclusion-constraint violation.

The SQLSTATE error code is:

23P01 

However, a reservation for a different asset at the same time is allowed.

A back-to-back booking for the same asset is also allowed under our [) interval rule.

That's exactly the behavior we want for this simplified reservation model.

Step 5: Handle Conflicts in a Node.js API

Now the API needs to translate the database error into something the user can understand.

Here's a minimal example using Express and pg:

import express from 'express'; import pg from 'pg'; const app = express(); const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL }); app.use(express.json()); app.post('/bookings', async (req, res) => { const { assetId, startsAt, endsAt } = req.body; if ( !Number.isSafeInteger(assetId) || assetId <= 0 || typeof startsAt !== 'string' || typeof endsAt !== 'string' ) { return res.status(400).json({ error: 'Invalid booking input' }); } const start = new Date(startsAt); const end = new Date(endsAt); if ( !Number.isFinite(start.getTime()) || !Number.isFinite(end.getTime()) || end <= start ) { return res.status(400).json({ error: 'Invalid booking time range' }); } try { const result = await pool.query( `INSERT INTO bookings (asset_id, starts_at, ends_at) VALUES ($1, $2, $3) RETURNING id, asset_id, starts_at, ends_at`, [assetId, start.toISOString(), end.toISOString()] ); return res.status(201).json(result.rows[0]); } catch (error) { if (error.code === '23P01') { return res.status(409).json({ error: 'This equipment is already booked for that time.' }); } if (error.code === '23503') { return res.status(404).json({ error: 'Asset not found' }); } console.error('Booking creation failed', error); return res.status(500).json({ error: 'Unable to create booking' }); } }); 

This is an illustrative endpoint, not a complete production API. You still need authentication, tenant-scoped authorization, stricter timestamp validation, request limits, and operational error handling.

The essential point is that the API doesn't rely on a preliminary SELECT to guarantee availability.

It attempts the insert and lets PostgreSQL enforce the invariant.

An HTTP 409 Conflict is a sensible response when the requested time is unavailable.

Step 6: Handle the Real-World Cases

Blocking overlapping reservations solves one important problem.

It doesn't solve every operational problem around shared equipment.

Consider these additional rules.

Booking cancellations

A cancelled booking should release its reserved time.

With our partial exclusion constraint, changing the status to cancelled removes that booking from the conflict rule.

Equipment returned late

An overdue camera may still be physically unavailable even after the scheduled end time.

You may need to update the booking interval, block the asset, or create a separate operational availability rule.

The database must enforce whichever policy your application chooses.

Turnaround buffers

Cameras may need charging, inspection, or cleaning between reservations.

Two back-to-back bookings may therefore be undesirable.

Model turnaround time explicitly, ideally as part of the interval being reserved.

Duplicate API requests

A double-click or network retry can submit the same reservation twice.

The overlap constraint will reject a duplicate active booking, but that isn't the same as implementing idempotency.

For production APIs, consider an idempotency key that lets a repeated request safely retrieve its original result.

Concurrent changes

The exclusion constraint prevents conflicting stored intervals.

If your workflow also updates equipment state, approval records, or inventory counts, those related operations may require additional transaction boundaries and locking.

What About PostgreSQL 18?

PostgreSQL 18 also supports temporal uniqueness syntax using WITHOUT OVERLAPS.

That offers another way to express non-overlapping time ranges.

For this tutorial, I chose the traditional exclusion constraint because it explicitly demonstrates the overlap operator and allows a partial condition for active booking statuses.

It's also applicable to older supported PostgreSQL versions.

The Bigger Lesson

The mistake in many booking systems isn't that developers forget to check availability.

They do check.

The problem is assuming that a successful availability check guarantees the equipment will still be available a moment later.

It doesn't.

When correctness depends on preventing conflicting records, it's often better to enforce that invariant in the database.

A good booking interface tells users what's available.

A good booking database makes sure two users can't reserve the same asset for overlapping times.

That's the difference between a reservation system that works in a demo and one that can handle simultaneous requests.

Have you handled booking conflicts with exclusion constraints, row-level locks, or another approach? What trade-offs did you encounter?