Build1 publisher3 min readPublished
ParkEase puts its no-double-booking rule inside a GiST exclusion constraint
ParkEase puts slot exclusivity in PostgreSQL 18, where no write path can skip it. The mechanism is a GiST exclusion constraint, and the cost lands on whichever booking request loses the race at insert time.
The Engineer · Build desk
What happened
- ParkEase's booking_slots table carries an exclusion constraint forbidding two rows that share space, vehicle type and slot index when their time ranges overlap, scoped to confirmed and active rows.
- The author rejected SELECT FOR UPDATE and advisory locks that every future write path must remember, SERIALIZABLE with retry loops spread through the code, and a Redis lock that pulls Redis into correctness.
- Allocation still runs a SELECT, which picks the lowest slot index that looks free for the requested window and throws SlotUnavailableError when no candidate comes back.
- When a concurrent insert wins, Postgres raises SQLSTATE 23P01 and the booking service turns that one error code into a SlotUnavailableError and an HTTP 409.
- The constraint depends on the btree_gist extension, created in the project's first migration, because GiST indexes cannot compare uuid, text or integer columns for equality without it.
Compiled by The EngineerSomething wrong?How this is made
Why it matters
- constraint Capacity has to be materialised before the database can police it: a space with three car slots needs three slot_index rows, because one rule capping three overlapping bookings per space cannot be written as an exclusion constraint.
- cost The loss under contention is visible to the user, since the code shown surrenders after a single exclusion violation and only a retry of the whole allocation would find the slot that is still free.
- decision Adding a hold-then-pay step forces a schema choice, because the status column defaults to a value the predicate does not cover.
- exposure Correctness now depends on an extension being present: in a database without btree_gist the constraint cannot be created at all, so every fresh environment and CI database inherits that dependency.
The rule is pairwise. The author's plain-English gloss on the DDL is that "no two rows can have the same space, vehicle type and slot index with overlapping time periods, among rows that are confirmed or active" [20]. A capacity rule capping the number of bookings allowed to overlap in a space is not expressible that way [1]. So three car slots in a space are not a number in a column. Each slot is a row in `space_slots`, and the allocation query walks them in `slot_index` order, taking the lowest that looks free for the requested window [11]. The slot number then travels into the constraint key, which tests four columns: three with `=` and one with `&&` [6][4].
Enforcement is the index. GiST does not support equality on `uuid`, `text` or `integer` without `btree_gist`, so the first migration creates the extension [7]. A conflicting insert comes back as SQLSTATE 23P01 [12]. "I wanted the rule to live in one place that no code path can skip. That's the database," the author wrote [14].
The same author wrote that "the SELECT picks which slot to try. It doesn't make the write safe. The constraint does" [13]. The trimmed `allocate()` bears that out in the unhappy path. The catch maps 23P01 to `SlotUnavailableError` and a 409, and it does not re-run the candidate query [12][2]. On a three-slot space under contention, the losing driver can be told the slot is unavailable while slot 2 is free [2]. The published excerpt breaks off mid-sentence at "If two requests r", before the trade-off promised in the TL;DR is named [16][15].
Two defaults here are worth copying. Every period is built as `tstzrange(start, end, '[)')`, so a 10:00-12:00 booking and a 12:00-14:00 booking do not overlap; with `[]`, back-to-back bookings would collide at exactly 12:00 [8]. Releasing a slot is a status update rather than a delete, so the row drops out of the partial index the moment the transaction commits and the history survives for disputes [9]. Primary keys come from `uuidv7()`, built into Postgres 18 with no extension [10].
For the predicate to be the truth about occupancy, everything that occupies a slot has to be inside it. `booking_slots` defaults `status` to `held`, and the predicate covers only `confirmed` and `active` [5][6], so a row written at the default status neither blocks nor is blocked [3]. This allocation path inserts `confirmed` directly, so the gap only opens when some later feature writes a row and leaves the status alone [12]. A hold-then-pay flow would have to add `held` to the predicate, or accept that a hold reserves nothing. ParkEase is a peer-to-peer parking marketplace for India, running NestJS on Fastify with Drizzle over PostgreSQL 18 and PostGIS, and it is still in development [1][2].
What to watch
- Whether the rest of the series names the trade-off the TL;DR promises, and whether it is the lost-race 409 or something else.
- Whether a hold-then-pay flow adds 'held' to the constraint predicate, which would change what releasing a slot has to do.
- Whether real contention rates once ParkEase leaves development justify retrying the whole allocation instead of returning 409 on the first exclusion violation.