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
Last activity: May 26, 2025, 02:02 AM