Rails PostgreSQL Exclusion Constraints: Prevent Booking Overlaps with tsrange and btree_gist

A client called me last spring in a real panic. They ran an equipment rental platform on Rails 7.1, and a hedge-fund analyst had just discovered that two customers had both been charged for the same industrial 3D printer during the same three days. It had taken the customer support team six hours to figure out how it happened. It had taken them nine minutes to lose the second customer.

The bug was the one every booking system eventually ships: a race condition between find_by_availability and bookings.create!. Two requests arrived within twenty milliseconds of each other, both saw the printer as available, both saved. The application layer had a validation. The database had nothing. Whichever request committed first won, and the second request also won, because there was no one at the door checking.

Fifteen minutes after that call, we had a Rails PostgreSQL exclusion constraint in production. The race stopped. It has stopped every day since. This is the guide I wish I could have handed the founder before they wrote their first Booking model.

What Rails PostgreSQL Exclusion Constraints Actually Do

An exclusion constraint is a Postgres feature that generalizes the unique index. A unique index says “no two rows may have the same value in these columns.” An exclusion constraint says “no two rows may have values in these columns that satisfy this operator.” The operator can be equality (which recovers a unique index), or it can be && — the range overlap operator — which recovers the thing you actually want when you write a scheduling app.

Rails PostgreSQL exclusion constraints are the correct primitive for any problem shaped like “these two things must not overlap.” Room bookings. Equipment rentals. Doctor appointments. Meeting rooms. Court reservations. Delivery slots. Employee shifts. Any time you have a resource and a time window, this is the tool.

They are enforced by the database, inside the transaction, before commit. No amount of application-level cleverness — advisory locks, uniqueness validators, SERIALIZABLE isolation — is as simple, as fast, or as correct.

The Race Condition No Validator Can Fix

Here is the naive Rails code that runs in most booking apps I audit:

class Booking < ApplicationRecord
  belongs_to :resource

  validate :no_overlapping_bookings

  private

  def no_overlapping_bookings
    conflicts = Booking.where(resource_id: resource_id)
                      .where("starts_at < ? AND ends_at > ?", ends_at, starts_at)
                      .where.not(id: id)
    errors.add(:base, "overlaps with existing booking") if conflicts.exists?
  end
end

That validation runs at valid? time, which happens before INSERT. In between the SELECT and the INSERT, another request can do its own SELECT, see nothing, and also INSERT. Neither request sees the other. Both succeed. You have a double booking, and your on-call engineer has a Sunday morning to look forward to.

You cannot fix this with validates_uniqueness_of. You cannot fix it with a before_save callback. You cannot fix it with ActiveRecord::Base.transaction alone, because default isolation is READ COMMITTED and each transaction reads its own snapshot. You can fix it with SERIALIZABLE isolation plus retry loops, but you will pay for it in throughput and for the retry logic in engineering time forever.

Or you can add three lines of migration and let Postgres do it.

Setting Up btree_gist and Range Types

Postgres range types (tsrange, tstzrange, daterange, int4range) support the && overlap operator natively. But exclusion constraints also need to compare non-range columns for equality — you want “no two bookings for the same resource overlap,” not “no two bookings anywhere overlap.” That equality check on resource_id needs the btree_gist extension, because a standard GiST index cannot do equality on integers by itself.

Enable it in a migration:

class EnableBtreeGist < ActiveRecord::Migration[7.1]
  def change
    enable_extension "btree_gist"
  end
end

Now build the bookings table with a tstzrange column instead of separate starts_at and ends_at:

class CreateBookings < ActiveRecord::Migration[7.1]
  def change
    create_table :bookings do |t|
      t.references :resource, null: false, foreign_key: true
      t.references :customer, null: false, foreign_key: true
      t.tstzrange :period, null: false
      t.timestamps
    end

    add_index :bookings, :period, using: :gist

    execute <<~SQL
      ALTER TABLE bookings
      ADD CONSTRAINT bookings_no_overlap
      EXCLUDE USING gist (
        resource_id WITH =,
        period WITH &&
      );
    SQL
  end
end

That EXCLUDE USING gist clause is the whole game. Postgres now refuses, at the storage layer, to insert two bookings rows where the resource_id matches and the period ranges overlap. The check happens inside the same commit as the INSERT, and it uses the same GiST index for lookups, so it is fast.

Working with tstzrange in Rails

Rails has supported Postgres range types since Rails 4.2, but the API is quiet enough that most Rails developers have never used it. The model does not need any special declaration — Rails casts tstzrange columns to Ruby Range objects automatically:

class Booking < ApplicationRecord
  belongs_to :resource
  belongs_to :customer

  validates :period, presence: :true
end

booking = Booking.new(
  resource_id: printer.id,
  customer_id: acme.id,
  period: Time.zone.parse("2026-09-01 09:00")...Time.zone.parse("2026-09-04 17:00")
)
booking.save!

Note the three-dot range operator (...). This is important: it creates a range that excludes the end value, which in Postgres range terms is [start, end) — inclusive of the start, exclusive of the end. That matches how bookings actually work. A booking ending at 10:00 does not conflict with a booking starting at 10:00.

If you use two dots (..), you get [start, end], and two adjacent bookings will conflict on the boundary. I have seen production systems where teams could not figure out why “Meeting Room A” was unbookable between 10:00 and 10:00. It was .. in a Ruby range.

Query for conflicts using the overlaps? operator with a bit of SQL:

period = Time.zone.parse("2026-09-02 12:00")...Time.zone.parse("2026-09-02 14:00")

Booking.where(resource_id: printer.id)
       .where("period && tstzrange(?, ?, '[)')", period.begin, period.end)

What Happens When a Conflict Hits the Wall

When Postgres rejects an insert due to your Rails PostgreSQL exclusion constraint, it raises PG::ExclusionViolation, which ActiveRecord wraps as ActiveRecord::StatementInvalid. You want to catch that at the controller or service layer and translate it to a user-facing error:

class BookingsController < ApplicationController
  def create
    @booking = Booking.new(booking_params)

    begin
      @booking.save!
      redirect_to @booking, notice: "Booking confirmed"
    rescue ActiveRecord::RecordNotUnique, ActiveRecord::StatementInvalid => e
      if e.cause.is_a?(PG::ExclusionViolation)
        @booking.errors.add(:period, "conflicts with an existing booking")
        render :new, status: :conflict
      else
        raise
      end
    end
  end
end

For extra defense, keep the application-level validation as a first-pass check. It gives users a nicer error message on the 99.9% of requests where there is no race. The exclusion constraint is the safety net for the 0.1% where there is.

I usually wrap this in a small BookingCreator service object so the controller stays boring. If you already have a general approach to reliable writes, this pairs well with the transactional outbox pattern for downstream notifications.

Overlap With Deleted Rows: Use a Partial Constraint

Real booking systems have cancellations. You do not want cancelled bookings to block new ones. The tempting fix — deleting cancelled rows — is wrong; you need the audit trail. The right fix is a partial exclusion constraint that only applies to non-cancelled rows.

Postgres allows a WHERE clause on exclusion constraints:

class AddCancelledAtToBookings < ActiveRecord::Migration[7.1]
  def change
    add_column :bookings, :cancelled_at, :datetime

    execute <<~SQL
      ALTER TABLE bookings DROP CONSTRAINT bookings_no_overlap;

      ALTER TABLE bookings
      ADD CONSTRAINT bookings_no_overlap
      EXCLUDE USING gist (
        resource_id WITH =,
        period WITH &&
      ) WHERE (cancelled_at IS NULL);
    SQL
  end
end

Now a customer can cancel and rebook a slot they just released, and Postgres will happily let the new booking through. The old row still sits in the table for the finance team and for auditing. If you have to answer compliance or GDPR questions about who booked what and when, that history matters — see the notes in my post on audit logging with Papertrail.

Deferrable Constraints for Bulk Rescheduling

There is one case where the immediate check hurts: rescheduling many bookings inside a single transaction. If you move Booking A to 10:00-11:00 and Booking B to 11:00-12:00 in the same transaction, and B previously overlapped where A is going, the intermediate state may briefly overlap.

Mark the constraint DEFERRABLE INITIALLY IMMEDIATE, and then use SET CONSTRAINTS ALL DEFERRED inside the transaction to defer the check to commit time:

execute <<~SQL
  ALTER TABLE bookings
  ADD CONSTRAINT bookings_no_overlap
  EXCLUDE USING gist (
    resource_id WITH =,
    period WITH &&
  ) WHERE (cancelled_at IS NULL)
  DEFERRABLE INITIALLY IMMEDIATE;
SQL

Then in the service that reshuffles:

Booking.transaction do
  ActiveRecord::Base.connection.execute("SET CONSTRAINTS bookings_no_overlap DEFERRED")

  booking_a.update!(period: new_period_a)
  booking_b.update!(period: new_period_b)
end

The check runs once at COMMIT and sees only the final state. This is one of those Postgres features that feels like cheating the first time you use it.

Performance: The GiST Index Does the Work

The GiST index on period is what makes this fast. Every insert issues a range && period lookup for the given resource_id, and GiST handles that in logarithmic time. On a booking table with 4M rows across 20K resources, I measured single-row insert latency going from 0.4 ms (no constraint) to 0.6 ms (with the exclusion constraint). Two hundred microseconds is a fair price for correctness.

Range queries against the same index are also fast — you get to reuse it for “show me all bookings in this week” without adding a second index. If your dashboards are lagging, pair this with the numbers from the PgHero dashboard to confirm the index is being used.

When Not to Use Exclusion Constraints

They are not right for every non-overlap problem. Two cases where I reach for something else:

Cross-database or cross-service resources. If the “resource” lives in another service, Postgres cannot see it, and the constraint cannot help. Use a saga, an idempotency key, or a workflow engine.

Very high write throughput on the same resource. Ten thousand writes per second against the same resource_id will contend on the GiST leaf pages. This is very rare in booking systems (people do not book meeting rooms ten thousand times per second), but if you are building a high-frequency trading matching engine, look elsewhere.

For 95% of Rails apps that deal with schedules, reservations, or availability, this is the right tool.

Frequently Asked Questions

How do exclusion constraints differ from unique indexes in Rails PostgreSQL?

A unique index only checks for equality — it prevents duplicate values in a set of columns. An exclusion constraint generalizes this to arbitrary operators. In particular, using the && overlap operator on a tstzrange column combined with = on a resource_id gives you “no two rows have the same resource and overlapping time,” which unique indexes cannot express.

Do Rails PostgreSQL exclusion constraints work with SQLite in test?

No. Exclusion constraints are Postgres-specific. If you run tests on SQLite (which I strongly recommend against for any app that uses Postgres in production), the constraint will not exist and race conditions will not be caught. Run your test suite on Postgres locally and in CI. Docker Compose makes this trivial.

Why do I need the btree_gist extension for rails exclusion constraints?

Because GiST indexes handle range types natively but do not know how to compare integers, strings, or UUIDs for equality out of the box. The btree_gist extension adds equality operator classes for scalar types so you can mix resource_id WITH = and period WITH && in the same GiST index. Without it, the migration fails with “data type integer has no default operator class.”

Can I use Rails PostgreSQL exclusion constraints with UUID primary keys?

Yes. btree_gist covers UUIDs. The migration is identical — Rails serializes UUIDs into the constraint expression the same way it does integers. This works well with the patterns in my post on ActiveRecord encryption for PII if you are already on UUIDs for tenant isolation.

Need help hardening a Rails booking, scheduling, or availability system before your next incident? TTB Software specializes in Rails and Postgres correctness under real production load. We have been doing this for nineteen years, and we have seen every race condition the framework can hide.