2

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.
3
  • Thinking more clearly, with the unique index you propose (ON test (client_id, typ) WHERE (typ = 'baz')), there will be no scan anyway. If there is no row with typ='baz', the index will be empty, so no scan either. Dec 7, 2022 at 23:42
  • Probably I was not clear enough. I would like to skip full scan during index creation. Dec 8, 2022 at 1:10
  • I don't think you can. You can create an index CONCURRENTLY though so there are minimal locks and other operations are not blocked while the index is created. Dec 8, 2022 at 1:41

1 Answer 1

0

Is there a way to tell postgres to skip that index validation, because "I swear to god, it's unique"

There would be no point, because without properly creasing three index next time you insert or update a row in the table the db would have to scan the table to check for a conflict with the unique constraint because the index was not created. Similarly when changing data in a table with a foreign key referring that index. This would have to happen for every relevant data change.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.