Sample Header Ad - 728x90

Skip (or speedup) postgres unique index creation if I know beforehand that it's unique

2 votes
1 answer
315 views
I have a large Postgres table (dbeaver says it has about 3e9 rows and almost 4Tb of disk space) I need an UPSERT, but ON CONFLICT section fields don't contain PK and I can't know it without performing real select. There are other partial indexes over that columns, but they don't include this new INSERT. Talk is cheap, here is the code.
-- CURRENT STATE

CREATE TABLE test (
	id bigserial NOT NULL, -- PK
	client_id varchar NOT NULL,
	order_id varchar NOT NULL,
	typ varchar(255) NOT NULL,
	value integer
	CONSTRAINT test_pkey PRIMARY KEY (id)
);

CREATE UNIQUE INDEX idx1 ON test (client_id, order_id, typ) WHERE (typ in ['foo', 'bar']);

-- what's even more interesting, this non-unique index is actually unique
-- or at least behaves like unique
CREATE INDEX idx2 ON test (client_id, order_id);
-- WHAT I WANT

INSERT INTO test
(client_id, order_id, typ, value) 
VALUES
($1, $2, 'baz', $3) --  this typ is not present in the table!
ON CONFLICT (client_id, typ)
DO UPDATE SET
	value = value + EXCLUDED.value
-- how I am going to achieve this
CREATE UNIQUE INDEX idx3 ON test (client_id, typ) WHERE (typ = 'baz');
As far as I know, during index creation postgres will scan the full table, looking for a single baz, which is guaranteed not to be there. **The questions are:** 1) Is there a way to tell postgres to skip that index validation, because "I swear to god, it's unique" 2) (x-y problem) maybe I miss something and there is a simplier way to achieve this without index creation.
Asked by Alexander Dmitriev (121 rep)
Dec 7, 2022, 09:56 PM
Last activity: May 26, 2025, 02:02 AM