Implement Range Overlap Searches and EXCLUDE-Style Rules Without GiST

Jim Knicely

PostgreSQL provides an elegant way to handle problems such as overlapping reservations, duplicate-event windows, or effective-date ranges. A typical design uses a PostgreSQL range type, the && overlap operator, a GiST index, and sometimes an EXCLUDE USING GIST constraint.

For example, PostgreSQL might enforce a rule such as:

  • For the same business key, two rows cannot have overlapping time windows.

A typical PostgreSQL implementation could look like:

				
					CREATE EXTENSION btree_gist;

ALTER TABLE event_data
ADD CONSTRAINT no_overlapping_window
EXCLUDE USING GIST (
    category WITH =,
    subtype WITH =,
    amount WITH =,
    event_window WITH &&
);

YSQL supports PostgreSQL range types and operators, but YugabyteDB-backed distributed tables do not currently support GiST indexes or EXCLUDE constraints. That means the business rule is portable, but the PostgreSQL implementation needs to change. YugabyteDB’s documentation currently lists GiST indexes and exclusion constraints as unsupported for YugabyteDB-backed tables.

Fortunately, we can preserve much of the expressive SQL while building the access path around YugabyteDB’s distributed LSM indexes.

What’s Supported?

Feature YugabyteDB What to Do
Range types such as int4range, tsrange, tstzrange Supported Use range operators such as && normally.
GiST on temporary PostgreSQL-local tables Can work Useful for local temporary processing, but not representative of distributed YugabyteDB storage.
GiST index on YugabyteDB-backed tables Not currently supported Use an LSM-friendly data model and index instead.
btree_gist Don’t design around it It is not listed among YugabyteDB’s supported extensions, and the underlying distributed GiST requirement remains.
EXCLUDE constraints Not currently supported Use an indexed lookup plus a trigger when enforcement is required.

YugabyteDB supports PostgreSQL’s built-in range types, but its range documentation notes that range-type columns cannot currently participate directly in YugabyteDB indexes. The current supported-extension list also does not include btree_gist.

Why a GiST Test on a Temporary Table Can Be Misleading

Consider:

				
					CREATE TEMP TABLE gist_probe (
    r int4range
);

CREATE INDEX gist_probe_r_idx
ON gist_probe
USING gist (r);

Then:

				
					INSERT INTO gist_probe
VALUES
    ('[1,10)'),
    ('[5,20)'),
    ('[100,200)');

An overlap query can use the GiST index:

				
					EXPLAIN
SELECT *
FROM gist_probe
WHERE r && int4range(6,7);

and produce a plan such as:

				
					Bitmap Heap Scan on gist_probe
  Recheck Cond: (r && '[6,7)'::int4range)
  -> Bitmap Index Scan on gist_probe_r_idx
       Index Cond: (r && '[6,7)'::int4range)

So GiST appears to work.

The important detail is that this is a temporary table. YugabyteDB’s CREATE INDEX documentation explains that YugabyteDB-backed tables use the distributed lsm access method by default, while non-YugabyteDB relations such as temporary tables use PostgreSQL-local index implementations.

⚠️ Temporary-table success does not imply distributed-index support.
A PostgreSQL access method may work on a temporary relation because that object lives in the local PostgreSQL layer. Always repeat compatibility tests using a normal YugabyteDB-backed table before choosing the production design.

What Happens on a Distributed Table?

Now create a normal YugabyteDB table:

				
					CREATE TABLE gist_probe (
    r int4range
);

The range predicate itself works:

				
					SELECT *
FROM gist_probe
WHERE r && int4range(6,7);

but without an appropriate distributed index, the query needs to scan the table:

				
					Seq Scan on gist_probe
  Filter: (r && '[6,7)'::int4range)

Range semantics are supported. The challenge is providing a distributed access path for them.

An LSM-Friendly Range-Overlap Pattern

One way to preserve convenient overlap syntax is to represent the range internally as two ordinary scalar values and let YugabyteDB index those values.

First define a small composite type:

				
					CREATE TYPE int_range_t AS (
    lo int,
    hi int
);

Create an immutable overlap function:

				
					CREATE FUNCTION int_range_overlaps(
    int_range_t,
    int_range_t
)
RETURNS boolean
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT
        $1.lo < $2.hi
        AND
        $2.lo < $1.hi;
$$;

Now expose it using the familiar && operator:

				
					CREATE OPERATOR && (
    LEFTARG = int_range_t,
    RIGHTARG = int_range_t,
    PROCEDURE = int_range_overlaps,
    COMMUTATOR = &&
);

Finally, add a convenience constructor:

				
					CREATE FUNCTION int_range_t(int, int)
RETURNS int_range_t
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT ROW($1, $2)::int_range_t;
$$;

These objects only need to be created once per database.

Store the Bounds as Normal Columns

Create the underlying table:

				
					CREATE TABLE range_probe_b (
    lo int,
    hi int
);

Then create a range-sharded LSM index:

				
					CREATE INDEX range_probe_b_idx
ON range_probe_b (
    lo ASC,
    hi ASC
);

The explicit ASC is important because the first column needs ordered access for a predicate such as:

				
					lo < 7

rather than hash equality lookup.

Now expose the composite range through a view:

				
					CREATE VIEW range_probe AS
SELECT
    lo,
    hi,
    ROW(lo, hi)::int_range_t AS r
FROM range_probe_b;

Because this is a simple view, you can still insert through it:

				
					INSERT INTO range_probe (lo, hi)
VALUES
    (1, 10),
    (5, 20),
    (100, 200);

The application can query using the expressive overlap syntax:

				
					SELECT *
FROM range_probe
WHERE r && int_range_t(6,7);

With this design, YugabyteDB can simplify the overlap expression into scalar comparisons and use the underlying LSM index:

				
					Index Only Scan using range_probe_b_idx
  Index Cond: ((lo < 7) AND (hi > 6))
  Heap Fetches: 0
  Storage Index Rows Scanned: 2

The query returns:

				
					.lo | hi  |   r
----+-----+--------
  1 |  10 | (1,10)
  5 |  20 | (5,20)

So the application retains:

				
					r && int_range_t(6,7)

while YugabyteDB executes:

				
					lo < 7
AND hi > 6

against an ordinary distributed index. This pattern produced an Index Only Scan in YugabyteDB 2026.1.

💡 Preserve the SQL semantics, change the access path.
The application can still express an overlap using &&, while the optimizer reduces that expression to scalar bounds that a YugabyteDB LSM index can service efficiently.

What About EXCLUDE USING GIST?

Now consider a more interesting requirement:

  • For the same category, subtype, and amount, don’t allow two events whose 7-day windows overlap.

In PostgreSQL, this is an excellent candidate for:

				
					EXCLUDE USING GIST

YugabyteDB currently reports:

				
					ERROR: EXCLUDE constraint not supported yet

The documented workaround is to use a trigger that checks for conflicts and an index that makes that lookup efficient.

GitHub issue #3944 continues to track native EXCLUDE constraint support.

Build the Lookup Around the Actual Access Pattern

Create a generic event table:

				
					CREATE TABLE transaction_event (
    id         BIGINT PRIMARY KEY,
    category   TEXT NOT NULL,
    subtype    TEXT NOT NULL,
    amount     NUMERIC(18,2) NOT NULL,
    created_at TIMESTAMP NOT NULL
);

Instead of storing and indexing a range value, index the equality columns plus the timestamp:

				
					CREATE INDEX transaction_event_dedup_idx
ON transaction_event (
    (category, subtype, amount) HASH,
    created_at ASC
);

That gives YugabyteDB an efficient access pattern for:

				
					WHERE category = ?
  AND subtype = ?
  AND amount = ?
  AND created_at > ?
  AND created_at < ?

For fixed 7-day windows, two rows overlap when an existing timestamp falls within seven days on either side of the new timestamp:

				
					created_at > NEW.created_at - INTERVAL '7 days'
AND
created_at < NEW.created_at + INTERVAL '7 days'

This avoids needing an indexed tsrange column altogether.

Don’t Rely on FOR UPDATE Alone

A tempting trigger implementation is to search the target table using:

				
					SELECT 1
FROM transaction_event
WHERE ...
FOR UPDATE;

If an existing conflicting row is found, FOR UPDATE locks it.

But there is an important concurrency hole: you cannot lock a row that doesn’t exist.

Imagine two transactions attempting to insert the first conflicting row for the same logical business key:

  • ● Transaction A: checks for an existing row → none exists.
  • ● Transaction B: checks for an existing row → none exists.
  • ● Transaction A: inserts.
  • ● Transaction B: inserts.

Both checks succeeded because there was nothing to lock.

⚠️ A row lock cannot lock a row that doesn’t exist.
SELECT ... FOR UPDATE helps when a conflicting row already exists, but by itself it does not close the empty-result race between concurrent inserts. If the trigger is replacing constraint-like enforcement, create a deterministic serialization point.

Add a Guard Row

One way to serialize checks for the same logical key is to maintain a small guard table:

				
					CREATE TABLE transaction_event_guard (
    category TEXT NOT NULL,
    subtype  TEXT NOT NULL,
    amount   NUMERIC(18,2) NOT NULL,

    PRIMARY KEY (
        (category, subtype, amount) HASH
    )
);

The trigger first ensures that a guard row exists:

				
					INSERT INTO transaction_event_guard (
    category,
    subtype,
    amount
)
VALUES (
    NEW.category,
    NEW.subtype,
    NEW.amount
)
ON CONFLICT DO NOTHING;

Then it locks that deterministic row:

				
					PERFORM 1
FROM transaction_event_guard
WHERE category = NEW.category
  AND subtype = NEW.subtype
  AND amount = NEW.amount
FOR UPDATE;

Now concurrent inserts for the same business key serialize on the same guard row, while unrelated keys can continue concurrently.

Create the Trigger

The complete trigger function looks like this:

				
					CREATE OR REPLACE FUNCTION check_7day_duplicate()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    -- Ensure a serialization point exists for this business key.
    INSERT INTO transaction_event_guard (
        category,
        subtype,
        amount
    )
    VALUES (
        NEW.category,
        NEW.subtype,
        NEW.amount
    )
    ON CONFLICT DO NOTHING;

    -- Serialize concurrent checks for this business key.
    PERFORM 1
    FROM transaction_event_guard
    WHERE category = NEW.category
      AND subtype = NEW.subtype
      AND amount = NEW.amount
    FOR UPDATE;

    -- Check for an overlapping 7-day window.
    IF EXISTS (
        SELECT 1
        FROM transaction_event
        WHERE category = NEW.category
          AND subtype = NEW.subtype
          AND amount = NEW.amount
          AND created_at >
              NEW.created_at - INTERVAL '7 days'
          AND created_at <
              NEW.created_at + INTERVAL '7 days'
          AND id <> NEW.id
    ) THEN
        RAISE EXCEPTION
            'duplicate event within 7-day window';
    END IF;

    RETURN NEW;
END;
$$;

Attach it to the table:

				
					CREATE TRIGGER transaction_event_7day_check
BEFORE INSERT
ON transaction_event
FOR EACH ROW
EXECUTE FUNCTION check_7day_duplicate();

The flow is now conceptually:

  • Lock logical business key → perform indexed overlap check → insert or reject

What About UPDATE?

If the application can modify category, subtype, amount, or created_at, the same rule also needs to be enforced for updates.

The trigger can therefore be defined as:

				
					CREATE TRIGGER transaction_event_7day_check
BEFORE INSERT OR UPDATE
ON transaction_event
FOR EACH ROW
EXECUTE FUNCTION check_7day_duplicate();

For more advanced implementations where an update moves a row between two logical business keys, lock the old and new guard keys in a deterministic order. That avoids two concurrent updates acquiring the same pair of locks in opposite order and creating a deadlock.

What About a Generated Range Column?

YugabyteDB now supports stored generated columns, provided that the generation expression uses immutable functions.

For example, you might model a window as:

				
					ALTER TABLE transaction_event
ADD COLUMN event_window TSRANGE
GENERATED ALWAYS AS (
    tsrange(
        created_at,
        created_at + INTERVAL '7 days',
        '[)'
    )
) STORED;

This can be useful for readability or application access.

However, it does not solve the indexing problem because YugabyteDB currently documents that range-type columns cannot participate directly in a YugabyteDB index.

For the enforcement query, scalar columns still give the distributed LSM index a much better access path.

💡 A generated range can still be useful for modeling.
You don’t necessarily need to eliminate PostgreSQL range types from the schema. The important distinction is that the distributed lookup should be driven by scalar columns that YugabyteDB can index efficiently.

Be Careful with timestamptz in Generated Expressions

Generated-column expressions must be immutable. That matters when working with timezone-aware timestamps because some timestamp operations depend on session timezone settings and therefore are not immutable.

If you need a generated window, test the exact expression against the target YugabyteDB release rather than assuming that a timestamptz expression is valid.

A common pattern is to keep the timezone-aware timestamp as the application’s source of truth while using a normalized scalar representation for indexing and enforcement.

Verify the Distributed Access Path

Whenever you replace a PostgreSQL index design with a YugabyteDB-specific one, verify the resulting distributed plan:

				
					EXPLAIN (ANALYZE, DIST, COSTS OFF)
SELECT 1
FROM transaction_event
WHERE category = 'A'
  AND subtype = 'B'
  AND amount = 100.00
  AND created_at >
      TIMESTAMP '2026-09-01 00:00:00'
  AND created_at <
      TIMESTAMP '2026-09-15 00:00:00';

Look beyond just Index Scan. YugabyteDB’s distributed statistics can show whether the access path is actually selective:

				
					Storage Index Read Requests
Storage Index Rows Scanned
Storage Rows Scanned
Storage Read Execution Time
Execution Time

The goal is to confirm that the trigger’s conflict check performs a targeted distributed lookup rather than scanning large numbers of rows.

PostgreSQL Design vs. YugabyteDB Design

PostgreSQL Pattern YugabyteDB-Friendly Pattern
Range value Range value or scalar lower/upper values
&& overlap operator && at the SQL layer, reduced to scalar comparisons where useful
GiST index Distributed LSM index on scalar lookup columns
btree_gist Composite HASH key plus ordered range column where appropriate
EXCLUDE USING GIST Indexed trigger check plus explicit serialization when concurrent writes matter

Final Takeaway

PostgreSQL range types and overlap operators work well in YSQL, but the exact GiST + btree_gist + EXCLUDE implementation does not currently map directly onto YugabyteDB-backed distributed tables.

Instead, preserve the business rule and redesign the physical access path for YugabyteDB:

  • Range semantics at the SQL layer → scalar bounds for retrieval → distributed LSM index → trigger for enforcement → explicit serialization when concurrent writes must be protected.

For read-only overlap searches, a custom operator and simple view can even preserve expressive syntax such as:

				
					WHERE r && int_range_t(6,7);

while YugabyteDB turns the work into ordinary indexed conditions such as:

				
					lo < 7
AND hi > 6

For EXCLUDE-style write enforcement, use an indexed conflict check, but don’t assume FOR UPDATE alone closes every race. If two transactions can simultaneously insert the first row for a logical key, introduce a deterministic guard row so they have something real to serialize on.

💡 Preserve the rule, not necessarily the PostgreSQL implementation.
When a PostgreSQL feature depends on an index access method that isn’t distributed in YugabyteDB, identify the actual predicates behind the feature and model those predicates around YugabyteDB’s LSM indexes. You can often preserve the application’s semantics while giving the distributed storage layer a much better access path.
🙌 Acknowledgment
Special thanks to Dan Farrell, Senior Pre-Sales Engineer at YugabyteDB, for sharing the detailed guidance and examples that helped shape this tip.

Have Fun!

After years of debating whether an expensive Vitamix was really worth it, we finally pulled the trigger today when we found one on sale at Crate & Barrel!

First creation: a simple strawberry, banana, and honey smoothie. 🍓🍌🍯 Delicious!

Tomorrow morning, things apparently get a little healthier: spinach, pineapple, lemon… and whatever else she can find in the fridge. 😂🥬🍍🍋

I have a feeling this thing is about to get a workout!