Suppose I have a system for reserving vacation properties. One of the tables is
reservations. Each reservation has a
start_date, and an
I want to prevent conflicting reservations. Eg, if a property is reserved from June 15-20, nobody can reserve it on June 17, or from June 19-22, or any other overlapping period.
I can see two ways to guarantee this:
Option 1: Lock the table for writes, run a
SELECT and check the existing reservations in application code, and if it will not create a conflict,
INSERT a reservation or
UPDATE the dates of an existing one.
Option 2: Use an
ALTER TABLE reservations ADD CONSTRAINT no_overlapping_rentals EXCLUDE USING gist (property_id WITH =, daterange("start_date", "end_date", '') WITH &&);
I'm fairly sure that the second option is more performant overall, because PostgreSQL doesn't have to wait while the application code examines the existing reservations.
But at the database level, what's the performance difference between these two options? And are there any other factors I should consider?