DEV Community

Cover image for Postgres Won't Let Me Double-Book a Parking Slot: Exclusion Constraints in Practice
Onkar Deokate
Onkar Deokate

Posted on

Postgres Won't Let Me Double-Book a Parking Slot: Exclusion Constraints in Practice

#postgres #database #systemdesign #typescript

TL;DR: In ParkEase, my peer-to-peer parking marketplace, two drivers can tap "Book" on the same slot at the same moment. The app doesn't prevent a double booking with locks or retry loops. A single Postgres exclusion constraint makes an overlapping booking impossible to write. This post covers the constraint, the allocation code around it, and the one trade-off I chose to accept.

What I'm building

ParkEase is a peer-to-peer parking marketplace for India. Owners list unused parking spaces, and drivers find and book them by the hour, day, week or month. Valet and car-wash services sit on top.

The stack: NestJS on Fastify, PostgreSQL 18 + PostGIS, Drizzle ORM, pg-boss for background jobs, and an Expo app. It's still in development. This is the first post in a series about the decisions I'm making along the way.

The first hard problem is also the most boring-sounding one: don't sell the same parking slot twice.

Why "check, then insert" isn't enough

A space has a number of slots per vehicle type (say, 3 car slots). A booking holds one slot for a time window. The obvious code:

  1. SELECT to check whether a slot is free for 10:00–12:00
  2. If it is, INSERT the booking

Two requests can both pass step 1 before either reaches step 2. Both insert, and one slot is now sold twice.

The usual fixes all work, and all have a cost:

  • SELECT … FOR UPDATE / advisory locks: correct, but every future code path that writes bookings has to remember to take the lock.
  • SERIALIZABLE + retry loop: correct, but the retries end up everywhere.
  • Application-level mutex / Redis lock: now correctness depends on Redis too.

I wanted the rule to live in one place that no code path can skip. That's the database.

The constraint

Every booking gets one row in booking_slots, which records which slot it occupies and for what time range:

CREATE TABLE booking_slots (
  id          uuid PRIMARY KEY DEFAULT uuidv7(),
  booking_id  uuid NOT NULL REFERENCES bookings(id) ON DELETE CASCADE,
  space_id    uuid NOT NULL REFERENCES spaces(id),
  vehicle_type text NOT NULL,
  slot_index  integer NOT NULL,
  period      tstzrange NOT NULL,
  status      text NOT NULL DEFAULT 'held'
  -- ...
);

ALTER TABLE booking_slots
  ADD CONSTRAINT booking_slots_no_overlap
  EXCLUDE USING gist (
    space_id     WITH =,
    vehicle_type WITH =,
    slot_index   WITH =,
    period       WITH &&
  ) WHERE (status IN ('confirmed', 'active'));
Enter fullscreen mode Exit fullscreen mode

In plain English: no two rows can have the same space, vehicle type and slot index with overlapping time periods, among rows that are confirmed or active.

A few details that matter:

  • btree_gist is required. GiST indexes don't support = on uuid/text/int without it. It's one line in my first migration: CREATE EXTENSION IF NOT EXISTS btree_gist;
  • Half-open ranges [). I build every period as tstzrange(start, end, '[)'), so a 10:00–12:00 booking and a 12:00–14:00 booking don't overlap. With [], back-to-back bookings would collide at exactly 12:00.
  • The WHERE predicate means released or cancelled rows stop blocking. Releasing a slot is a status update, not a delete. The row drops out of the constraint, the slot becomes bookable the moment the transaction commits, and the history stays around for disputes.
  • uuidv7() is built into Postgres 18. It gives time-ordered IDs with no extension needed.

Allocating a slot

Here's the (lightly trimmed) allocation code from the booking service:

async allocate(tx: TxHandle, input: AllocateSlotInput): Promise<number> {
  const period = sql`tstzrange(${input.startsAt.toISOString()}::timestamptz,
                               ${input.endsAt.toISOString()}::timestamptz, '[)')`;

  // Pick the lowest slot index that looks free for this window.
  const candidates = await tx.execute<{ slot_index: number }>(sql`
    SELECT ss.slot_index
    FROM space_slots ss
    WHERE ss.space_id = ${input.spaceId}
      AND ss.vehicle_type = ${input.vehicleType}
      AND NOT EXISTS (
        SELECT 1 FROM booking_slots bs
        WHERE bs.space_id = ss.space_id
          AND bs.vehicle_type = ss.vehicle_type
          AND bs.slot_index = ss.slot_index
          AND bs.status IN ('confirmed', 'active')
          AND bs.period && ${period}
      )
    ORDER BY ss.slot_index
    LIMIT 1
  `);

  const candidate = candidates[0];
  if (candidate === undefined) throw new SlotUnavailableError();

  try {
    await tx.insert(bookingSlots).values({ /* ...same fields..., */ status: 'confirmed' });
  } catch (error) {
    // 23P01 = exclusion_violation: someone took this slot between our SELECT and INSERT.
    if (isPgError(error, PG_EXCLUSION_VIOLATION)) throw new SlotUnavailableError(); // → 409
    throw error;
  }
  return candidate.slot_index;
}
Enter fullscreen mode Exit fullscreen mode

The key idea: the SELECT picks which slot to try. It doesn't make the write safe. The constraint does.

If two requests race for slot 0, both SELECTs return 0 and both try to INSERT. Postgres lets exactly one of them commit. The other gets SQLSTATE 23P01, which I turn into a friendly 409 Slot unavailable. There's no advisory lock, no FOR UPDATE and no retry loop.

The trade-off I accepted

This design has a known downside, and I wrote it down in the code instead of hiding it:

With three slots, a driver who loses the race on slot 0 gets a 409 even though slot 1 was free.

I could add a retry that tries the next index. I chose not to, for now:

  • Losing the race needs two people booking the same space in the same few milliseconds, which is rare.
  • The failure is instant and clearly worded.
  • "Try again" is a new request with a fresh idempotency key, and it gets the next free index.

A retry loop is easy to add later. Adding correctness to a system that doesn't have it is much harder.

Extensions get the same protection for free

Extending a booking updates the range in place:

const moved = await tx.update(bookingSlots)
  .set({ period: sql`tstzrange(lower(${bookingSlots.period}), ${newEndsAt.toISOString()}::timestamptz, '[)')` })
  .where(eq(bookingSlots.bookingId, bookingId))
  .returning({ id: bookingSlots.id });

if (moved.length !== 1) {
  throw new Error(`Extension touched ${moved.length} occupancy rows; expected exactly 1`);
}
Enter fullscreen mode Exit fullscreen mode

Nothing in the app checks whether the later window is free. The same constraint answers that, and a collision with the next driver's booking becomes an ExtensionConflictError.

The moved.length !== 1 check is there for a quieter bug. In SQL, an UPDATE that matches zero rows succeeds. If that ever happened, the booking's ends_at would move while the occupancy row didn't, and the space would be sold for a window the constraint no longer protects. So the transaction fails instead of committing half an extension.

Keeping the app and the constraint in agreement

Search also has to decide which slots count as "taken". If search and the constraint ever disagreed, drivers would see slots that the database then refuses to book. So discovery uses a constant with a comment pointing back at the constraint:

/** Deliberately the same set as booking_slots_no_overlap's WHERE clause. */
export const LIVE_SLOT_STATUSES = ['confirmed', 'active'] as const;
Enter fullscreen mode Exit fullscreen mode

One commit for everything

Creating a booking writes four things in one transaction: the booking row, the slot row, balanced ledger entries, and outbox messages (the payment-expiry job, the "booking created" event and a reminder). If the exclusion constraint fires, none of the other writes ever happened. There's never a moment where a booking exists without its slot, or a slot exists without its ledger entry.

I'm writing this series as I build ParkEase. Next up is the geo-search layer and a pagination bug that hides inside a location cache.

Question for you: have you used exclusion constraints in production, or do you handle overlap with locks? I'd like to hear where each approach broke down for you.

Top comments (1)

Collapse
 
mihai_leanzero profile image
Mihai Perdum •

Putting the invariant in the exclusion constraint instead of a lock is the right instinct, and the WHERE clause scoping it to confirmed/active is what makes it usable, since a naive version without that predicate would keep dead rows blocking a slot forever.

One thing I'm curious about: the table default is status 'held', but the constraint only protects 'confirmed' and 'active'. So during the hold window, before payment resolves, two drivers could both land on 'held' for the same slot_index, and the collision only surfaces later when one of them tries to flip to 'confirmed' and eats the 23P01. Is that intentional, i.e. hold is meant to be a soft, app-level reservation that's allowed to race, with the real guarantee only kicking in at confirm time? If so I'd guess the UX cost shows up as someone getting all the way through checkout before hitting the 409, which is a worse place to lose the race than at slot-selection time. Did you consider giving 'held' a shorter-lived overlap protection too, maybe a separate partial exclusion constraint, or is the rarity argument from the slot-0-loses-anyway trade-off enough to just not bother?