Prevent Overlapping Bookings With a PostgreSQL Exclusion Constraint

Start with the overlap the product forbids

A booking rule sounds simple until two requests arrive together. Use one concrete case: room Cedar is free from 10:00 to 11:00, and two clients try to reserve 10:15 to 10:45 and 10:30 to 11:15. An application that checks for conflicts and then inserts has a gap between those operations. Both checks can see an empty schedule before either insert commits. The desired result is one accepted reservation and one explainable conflict, regardless of which request reaches the database first.

Write the business rule before the SQL. Name the resource key, time zone, precision, permitted duration, meaning of cancellation, whether adjacent reservations may touch, and whether maintenance blocks ordinary bookings. Decide how open-ended holds behave and whether tentative and confirmed records conflict. The database can enforce a declared predicate, but it cannot decide what the product means by overlap. Product and operations owners approve that meaning; the developer translates it into a reviewed schema and concurrent fixture.

Choose range bounds deliberately

PostgreSQL range types record lower and upper values together with inclusive or exclusive bounds. Half-open intervals such as [10:00,11:00) usually let one booking end exactly when the next begins. That convention must match the user interface and every importer. If one path stores an inclusive end while another assumes an exclusive end, the constraint will enforce a rule users did not agree to. Pick timestamp with or without time zone according to the application model, then test daylight-saving transitions where local schedules matter.

Normalize input before persistence. Reject an end before its start, decide whether an empty range is valid, and record how precision is rounded. Do not accept a text range from an untrusted client and treat successful parsing as product validation. Construct the range from validated fields on the server or in a typed database expression. Preserve the original user-facing zone separately when the product needs it. The constraint should compare canonical scheduling values, while the interface explains them in the agreed local context.

Express resource equality and time overlap together

An exclusion constraint can reject pairs of rows when all selected operator comparisons are true. For room bookings, the useful combination is equality on the room identifier and overlap on the time range. The btree_gist extension supplies GiST operator classes for ordinary scalar types that can sit beside the range overlap operator. This allows overlapping times in different rooms while preventing them for the same room. Review extension availability and ownership in the actual PostgreSQL service before relying on it.

Keep status semantics visible. A cancelled row may remain for audit without blocking new work, which can call for a partial constraint over active statuses. If several statuses conflict differently, one boolean predicate may hide too much product meaning. Model the states and transitions first. Changing a status from cancelled back to confirmed is a write that must pass the same overlap rule. Avoid a trigger that silently shifts times or cancels another booking to make the constraint pass; rejection should preserve both requests for an owner to resolve.

Prove concurrency with separate sessions

Build a disposable schema with room Cedar, room Maple, and synthetic reservations. Open two database sessions, begin both transactions, and hold them at a barrier before inserting overlapping Cedar ranges. Release them together and retain statement start, lock waits, commit order, SQLSTATE, final rows, and application response. Repeat with the arrival order reversed. A serial test that inserts one row after another proves the predicate but misses the race that motivated database enforcement.

Add controls: adjacent Cedar ranges, the same time in Maple, an exact duplicate, a range contained inside another, a range that contains another, an update that moves an existing booking into conflict, and two non-overlapping writes. Exercise cancellation and reactivation under concurrency. If transactions retry, prove the retry does not create duplicate notifications or audit events. Use a stable operation identifier and inspect database state after every case rather than assuming an error response means no other side effect occurred.

Handle the conflict as a product outcome

A constraint violation is expected competition, not necessarily a server fault. Map the named constraint or reviewed SQLSTATE path to a stable conflict response without exposing another customer’s booking details. The interface can say the slot is no longer available and fetch current availability. It should preserve the user’s search inputs and offer a deliberate next choice. Do not parse a localized database error string or return raw detail containing identifiers and ranges that the caller is not authorized to see.

Keep authorization ahead of conflict disclosure. A caller who cannot access Cedar should not learn that a particular time is occupied. Validation, resource lookup, permission checks, and the attempted write need an order that matches the application’s disclosure policy. Record allowed conflict, denied caller, missing room, malformed range, and dependency failure separately. Metrics should count safe classifications and constraint name, not booking contents. Product owners approve the recovery text; security owners approve what the response may reveal.

Plan migration and operational review

Adding the rule to an existing table requires evidence about historical overlaps. Inventory them with an approved query and classify their causes before changing data. An exclusion constraint cannot be introduced as NOT VALID in the same way as the staged CHECK and foreign-key pattern, so do not copy that migration plan. Review PostgreSQL version, table size, index build behavior, locks, write volume, maintenance window, backup posture, and rollback. Existing conflicts are product records, not debris a migration may delete automatically.

The handoff should include schema and extension versions, range convention, status predicate, exact constraint definition, historical-conflict report, two-session fixture, SQLSTATE mapping, authorization cases, migration rehearsal, lock observations, rollback, and named owners. An offshore developer can prepare the schema change, application mapping, tests, and evidence in an approved environment. Database and product owners decide historical correction and production timing. Bring the table shape, booking rule, volume, and one conflict example to a Developer Offshore discussion to scope the work precisely.

Questions about assessing Philippine developers