Suppose I have a system for reserving vacation properties. One of the tables is reservations. Each reservation has a property_id, a start_date, and an end_date.
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 EXCLUDE constraint.
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?
There actually is a third way - use transactions with serializable isolation, and within the transactions, SELECT for conflicting reservations, and if you find none, INSERT your new one. But 1) that also requires the db waiting for the application code to check the results and 2) it can give false positives - see https://gist.github.com/nathanl/f98450014f62dcaf0405394a0955e18e for a simple demo (about uniqueness, not overlap, but the same concept applies)
Oh, and another thing about serializable isolation - I think the fact that you have to retry on a false positive means that the time to insert becomes unbounded, as you may have to keep getting in the back of the retry line.
