Is this Postgres EXCLUDE constraint equivalent to this UNIQUE constraint?
0
votes
1
answer
146
views
# The constraints
Currently, a table on a Postgres version 12 DB has the following
EXCLUDE
constraint:
ALTER TABLE ONLY example_table
ADD UNIQUE CONSTRAINT example_table_range_excl EXCLUDE USING gist
(service_id WITH =, customer_id WITH =, tstzrange(billing_start, billing_end) WITH &&);
This constraint is on a big table. When I run pg_restore
for the DB, this statement takes 5 hours to complete. On a replica test DB, I dropped the EXCLUDE
constraint and added this UNIQUE
constraint:
ALTER TABLE example_table
ADD CONSTRAINT uc_example_table UNIQUE (service_id, customer_id, billing_start, billing_end);
# My test case
I ran the following test with each constraint in place. In each test, I got the expected/desired constraint violation on the final UPDATE
. This leads me to believe the constraints are equivalent.
-- Step 1: Insert an initial row
INSERT INTO example_table
(customer_id, service_id, billing_start, billing_end) VALUES
(1, 1, '2023-10-04 00:00:00+00', '2023-10-05 00:00:00+00');
-- Step 2: Insert another row without a billing_end
INSERT INTO example_table
(customer_id, service_id, billing_start) VALUES
(1, 1, '2023-10-04 00:00:00+00') RETURNING id;
-- Step 3: Update the row from step 2 to have the same billing_end as the row from step 1
--...this should violate whichever constraint is currently applied
UPDATE example_table SET billing_end = '2023-10-05 00:00:00+00'
WHERE id = 590756786785677
Violation when the EXCLUDE
constraint is applied:
ERROR: conflicting key value violates exclusion constraint "example_table_range_excl"
DETAIL: Key (service_id, customer_id, tstzrange(billing_start, billing_end))
=(1, 1, ["2023-10-04 00:00:00+00","2023-10-05 00:00:00+00"))
conflicts with existing key
(service_id, customer_id, tstzrange(billing_start, billing_end))
=(1, 1, ["2023-10-04 00:00:00+00","2023-10-05 00:00:00+00")).
SQL state: 23P01
Violation when the UNIQUE
constraint is applied:
ERROR: duplicate key value violates unique constraint "uc_example_table"
DETAIL: Key (service_id, customer_id, billing_start, billing_end)
=(1, 1, 2023-10-04 00:00:00+00, 2023-10-05 00:00:00+00) already exists.
SQL state: 23505
# Performance?
If they are equivalent, would the UNIQUE
constraint be more performant than the EXCLUDE
constraint? It takes a minute to execute the UNIQUE
constraint on this replica DB and applying the EXCLUDE
constraint takes more than 10 minutes (I'm waiting for it to finish executing as I write this).
Asked by Trouble Bucket
(159 rep)
Oct 5, 2023, 06:37 PM
Last activity: Jul 16, 2025, 03:03 PM
Last activity: Jul 16, 2025, 03:03 PM