0

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?

  • 1
    When a feautre (in this case exclusion constraints is designed as part of the database (or, in general, most systems), you can expect it to be more performant than doing something controlled from outside of it. And you have complete safety, even if someone tampers with your database not using your app, you continue to have the guarantees. In other terms: "don't reinvent the wheel" ;-) – joanolo Feb 7 '17 at 20:34
  • 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 gist.github.com/nathanl/f98450014f62dcaf0405394a0955e18e for a simple demo (about uniqueness, not overlap, but the same concept applies) – Nathan Long Feb 9 '17 at 14:58
  • 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. – Nathan Long Feb 14 '17 at 22:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.