TL;DR: An availability read describes the intervals visible when it ran. An exclusivity constraint protects the stored reservation when claims compete. Define which resource and states consume time, choose interval boundaries consciously, and handle a rejected write as a booking decision with clear recovery.
A user at an invented recording studio sees Booth Two available from noon until one. Another user sees the same opening a moment later. Each submits a claim after an availability check. The studio has one physical booth. It needs its confirmed records to reflect that fact even when both requests ran reasonable queries.
The reservation dispute below revolves around a database invariant. For an eligible pair of bookings, the same booth cannot be occupied over intersecting time ranges. PostgreSQL's range documentation gives a concrete approach using an exclusion constraint with equality for resource identity and overlap for a range. A suitable GiST operator class is needed for each comparison, with the btree_gist extension useful for supported scalar identity types. The examples are invented and the database semantics come from PostgreSQL 18 documentation. PostgreSQL range constraints, PostgreSQL btree_gist.
The reservation clerk presents a pair of claim slips
Claim A covers Booth Two from noon to one. Claim B covers the same booth from twelve thirty to one thirty. The two occupants would share the room for thirty minutes under their proposed bookings. The earliest read in both sessions can truthfully return no current conflict. That observation describes an earlier state. It gives neither claimant a right to an interval the database has yet to record.
The service can do an availability read to communicate options and explain likely conflicts. It should also let the authoritative write detect changes that happened in the meantime. A concurrency control that every writer participates in can enforce the property. In PostgreSQL a correctly scoped exclusion constraint expresses it centrally, including writes from an admin console and import integrations that use the same constrained relation.
The particular statement concerns active rows. If two rows refer to the same booth and their applicable ranges overlap, they cannot coexist under the chosen constraint. For two different booths, the room equality comparison differs, and overlap is permitted. That qualification is the difference between enforcing a studio-wide capacity of one and enforcing one occupant per individually exclusive resource.
Two reservations that need the same equipment cart may have a second conflict relationship even when their main rooms differ. The booking model can use resource allocation rows for every item it must claim, subject to the same exclusion rule. Saving the group in one transaction avoids a booking that owns a booth and lacks the essential cart. A business requirement for a queue of shared fungible equipment has different capacity semantics from pairwise exclusion and deserves an explicit model.
An organization may identify rooms by tenant and a reused local room number. The constraint needs the relevant identity dimension. If globally unique booth ids already exist and are enforced, a scalar equality comparison may cover it. If room two in two separately operated studios shares the same stored local id, a namespace column or stable composite relationship prevents unrelated schedules from blocking each other. The plan describes resource identity before naming an index.
The dispute at one o'clock changes the definition of occupied
A new slip covers one until two. The noon booking's right edge coincides with the second booking's left edge. If a room can pass directly from one session to the next, use a lower-inclusive, upper-exclusive interval. Noon belongs to the first booking and one belongs to the second. Both intervals meet without overlap. If the studio needs fifteen minutes to reset the booth, the allocated resource ranges must account for that requirement.
The public appointment and claimed interval can have different endpoints. A studio renter may pay for noon to one. A fifteen-minute preparation buffer and another ten minutes after use produce an occupancy interval from eleven forty-five to one ten. Displaying the appointment alone to other users can imply space that the room owner cannot release. The schedule can show the real available next start while preserving the advertised time on the user's ticket.
Suppose the next requested appointment starts at one twenty, with its own fifteen-minute preparation. Its allocation starts at one five. The prior occupant requires the room until one ten. The constraint correctly sees a five-minute overlap, even though appointments appear twenty minutes apart on the customer's wall clock. The room manager can shorten a buffer only through an approved policy. Silently weakening desk-side checks leaves the physical conflict unresolved.
Intervals deserve validation independent of the overlap rule. The intended product requires finite start and end values, a strictly positive duration and defined upper bounds on length. A duration of zero can form an empty range that consumes no time. An absent bound can form a range spanning unbounded time in one direction. Neither represents a normal booth claim and both should be handled deliberately. Checks for validity belong at the same authority as the allocation wherever possible.
The time shown to people is civil time. The booked occupancy can be represented as an interval of instants once ambiguous input is resolved. A daylight saving transition can repeat or skip a local wall-clock reading depending on locale. Preserve the displayed time zone and applicable conversion choice where that matters for communication. The booking invariant sees instants and enforces physical overlap, while a separately owned schedule policy resolves a user's chosen civil time.
A large event may need continuous occupancy for several nonadjacent windows. It can request separate allocation rows linked to the same reservation. If releasing one part of the booking is permitted, the product decides how pricing and equipment are affected. A set of legal rows can still be incomplete as a business transaction. The application's group state and the database's local overlap invariant support different protections.
Mark the slip that can still claim the booth
A canceled booking must remain in the history, and its interval no longer consumes time. A finished historical booking might retain its allocation record permanently. A nonconfirmed calendar suggestion never consumes time. Each state needs a definition that expresses whether the row participates in the same exclusivity constraint. The operational decision can stay simple by keeping authoritative allocations in a dedicated relation and deriving display records separately.
PostgreSQL also supports a predicate on an exclusion constraint as described by its table definition documentation. A design can apply the conflict rule to a stored set of active states. State changes that move a row into that set go through normal constraint checking. Code coupled to the original create path might forget updates and imports. A stored condition at the authoritative write protects both creation and state transitions. PostgreSQL CREATE TABLE and exclusion constraints.
A hold gives a customer a short opportunity to finish a request. The requested range concerns when they will use the booth. The hold deadline concerns when the right to confirm expires. These clocks may be weeks apart. A fresh competing request cannot take a used hold just because the client believes its deadline passed. The service can reclaim it with a transaction that verifies the persisted expiry and changes the stored state. It should then insert the new allocation under the same invariant.
Think through a race near expiration. A confirmation request and an expiry worker each see a hold. The application defines which committed transition can win given the authoritative timestamps and state, using consistent transaction and update conditions. A worker that releases a state read earlier without confirming its current identity could delete an allocation that has since been confirmed. Rechecking under the selected database mechanism makes losing evidence explicit.
If a cleanup worker falls behind, expired holds may keep scarce intervals unavailable. That behavior is visible through the cleanup watermark and the remaining held count. An instant business-clock predicate should not be assumed to update stored index membership on its own. A concrete reclamation state makes it possible to reconcile what the database currently enforces and which customer claims remain eligible for confirmation.
A temporarily failed payment is another hold policy decision. The receipt can record whether the allocation remains held until a deadline, whether the transaction recorded payment intent, and who owns a refund or retry. Confirmation of a physical room and completion of a remote payment can proceed through a service workflow with compensation rules, instead of conflating the events with one database assertion. The allocation invariant stays meaningful throughout that workflow.
The database hears both sides at the final write
The team prepares a concurrent test with two controlled transactions. Each must reach its read before either can rely on a new committed row. The first inserts its claim. The other attempts the overlapping insert under the exclusion rule. Once the interleaving resolves, the invariant should permit at most one of the competing claims to remain committed. The test records which outcome each client received and does not assume that starting first determines all schedules.
There are several legitimate failure points. A transaction can fail because the inserted range conflicts, because another statement failed, or because the connection interrupted the client before it saw an outcome. PostgreSQL documents the SQLSTATE for an exclusion violation as 23P01. Applications can use the structured code and, when available, constraint identity to select a specific recovery. Checking a human-readable error fragment can unintentionally group unrelated failures. PostgreSQL error code appendix.
After a failed insertion the connection must respect the transaction's resulting state. A client can roll back the failed transaction, or use an intentionally prepared savepoint when preserving independent work is necessary. It should avoid reporting a nearby slot from a stale availability view as definitely confirmed. A response can offer to search fresh alternatives with suitable data about the current room schedule.
The place of the constraint matters. If availability is cached on a read replica, the replica can be behind a successful claim. That is acceptable for indicative browsing with a bounded product contract. Sending two teams to the room because a read replica is lagging is an authority error. Keep the final acceptance on the writable state that enforces the allocation. The interface can acknowledge a booking only when the definitive outcome is established.
Idempotency handles a different duplicate. A mobile client retries an identical request after a connection drops. It may be the same customer and intended reservation rather than two unrelated claims with overlapping intervals. The application can bind a request identifier to that customer and the approved request parameters. Returning the prior committed outcome avoids requesting the user to choose a throwaway replacement for a room they already booked. The exclusion rule remains applicable to two independent requests even if the client assigns both fresh retry identities.
Posting a notification before a committed result creates another false receipt. A service can record the intended notification with the transaction and deliver it after commit using the product's normal reliable delivery mechanism. The booking reference names the confirmed allocation, or the requested status if it remains held. If the notification service retries, the message policy can suppress accidental duplication independently of room ownership.
Two valid claims can still contend on a bundle
One requester books Booth Two with the shared cart. A second renter books the same cart and needs Booth Three. Each booking separately consumes a room and a cart. If both participating transactions reserve resources in inconsistent orders, more complex cases can hold different resources while waiting for the other transaction to release one. The team can choose a consistent resource-acquisition order and keep the critical transaction small. It still treats a transaction-level deadlock or serialization failure as a different recovery case from an authoritative overlap.
Suppose a technician moves an existing bundle from one booth to another and changes which cart it uses. The replacement should either commit completely or leave the existing bundle available according to the documented service policy. Holding a new room row before releasing the old may be appropriate. The chosen ordering and transaction boundaries determine whether intermediate work can block other requests and whether a failed attempt releases the temporary claim. A concurrent test can exercise the old and new resources together.
A free-standing availability system serving several stores can give plausible but disconnected answers. Moving each booth to another partition, database or service only preserves exclusivity if each physical resource has one authoritative allocator for the relevant time. An audit can identify where replicated calendars become writable or where a tenant migration leaves two active allocators. An individual database constraint covers its participating rows. The migration needs a service-level transfer protocol before both systems are allowed to make a final commitment.
The cutover can make ownership explicit with a dated resource routing record and a fenced interval for old writes. Replay current allocations as identified records, reconcile holds and pending transitions, and enable the new allocator only after the old route stops accepting independent claims. A replica can keep serving approximate search if its response names the relevant scope and does not confirm bookings. An engineer should test a client with cached pre-cutover routing, since that client may keep trying the old path after the transfer.
The cart and booth also provide a useful design counterexample. If a room has three interchangeable chairs, the studio can identify and reserve each chair or use a capacity rule that accounts for aggregate units over time. Applying the room's pairwise exclusion to one broad chairs resource would reduce usable capacity to one. Removing all conflict protection permits more than three at once. A product still has to decide whether an order names an individual item or requests units from a pool. Model that requirement before borrowing the range constraint.
A blocked room can be a valid decision
The studio manager needs to close a booth for maintenance. If maintenance occupies exactly the same physical resource as a reservation, it can enter the allocation model as a claim with its own reason and authorized owner. The constraint then prevents a worker from scheduling maintenance over a confirmed event unnoticed. A dashboard can highlight the conflict when the manager considers the closure.
Sometimes the manager needs to override a booking deliberately. The new closure must still end in a state where the accepted allocations obey the room rule. A controlled workflow releases or reschedules the affected appointment under its product authority and then accepts the maintenance block. It carries communication and payment implications as needed. Turning off the constraint for an operation deprives the resulting records of the fact the case was meant to protect.
Room moves need a similar decision. Shifting an appointment from Booth Two to Booth Three may leave alternate allocation rows in both places during a transaction if the explicit purpose is to reserve the replacement while giving up the first. The committed reservation should reflect the promised result. If the destination is already occupied, the application can preserve the original booking and report that the move was not accepted. A failed move should not leave the customer without the earlier allocation.
A recurring schedule can eventually produce two conflicting concrete claims. A calendar expansion service must decide which horizons it protects with material allocations and what future rule changes can affect later occurrences. The database invariant guards rows that actually exist. A future appointment inferred from a recurrence needs its own reservation promise. This makes displayed future availability an explicit contract, especially when equipment is normally allocated only a few months forward.
An institutional import often contains cases that already violate the new rule. It can surface them during a migration without quietly declaring a winner from insertion order. A reviewer can identify valid alternatives such as a moved class, duplicate input or real approved closure. Reconcile existing states before relying on the constraint for release, and guard new writes during the relevant transition. A test containing only a brand-new empty table cannot show what historical corrections the workflow needs.
Separate calendar hints from evidence of ownership
A monthly view can contain ranges painted in advance according to a business schedule. An operator can still request a booth outside the normal schedule when a manager authorizes it. The overlap invariant does not encode every opening-hour condition. Eligibility checks can combine policy such as service hours and renter permission with the allocation write, under the boundaries the service supports. Independent obligations help the UI return reasons a person can act on.
Consider a bookable window of twenty minutes with a setup requirement of fifteen minutes for each claim and a minimum appointment of ten. A green span chosen purely from unlabeled schedule emptiness offers no feasible appointment in that span if the setup must occur inside it. Computing candidate starts from actual allocation policy reduces futile requests. The final response still combines that help with the concurrency protection for competing writes.
Tests need both accepted and rejected neighbors. Same booth with overlapping positive ranges should conflict. Different booths with overlapping ranges can coexist. Same booth with an exact boundary touch can succeed when the chosen occupancy convention allows it. Add cases for same customer retry, first and last instants of a hold, overlapping buffer periods, and transitions between active and canceled state. Each expected outcome names which owner made the decision.
A service metrics record can distinguish expected races from defects. Count conflict responses by operation and reason, including administrative moves, ordinary competition, and duplicate retries. Observe the time a stale hold spends occupying inventory after expiry. A rise in legitimate conflicts during a popular recurring booking release can be a demand observation. A steady rise for rooms customers can see as free may indicate a stale data source or omitted occupancy buffer.
A booking screen can preserve the rejected request long enough to offer real alternatives, without automatically assuming one is acceptable. A few neighboring start times could fill the same goal while respecting the user's original location and duration. A customer who needs the same physical booth has a different preference from someone who mainly needs any open room at noon. The application can keep those values attached to the original attempt and display a reason for considering a change.
The final clerk reads two separate receipts. One says which customer owns a range and provides its confirmed reference. The other explains that the earlier view showed open space, the competing claim could not commit, and the available alternatives come from a later observation. That is a fair explanation to both people and it remains true no matter how close together the two requests arrived.
The acceptance review stores a reconciliation of confirmed, tentative and released work for each resource. A maintenance closing and a customer reservation can have different operational owners while appearing in the same physical inventory. The comparison lets the manager verify both occupancy and explanation during routine audits.