Sample Header Ad - 728x90

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