2

I have a a table which has unique index:

CREATE UNIQUE INDEX IF NOT EXISTS tb_sha256_key_idx ON blob_props (sha256, key);

I want to change it into primary key during db migration and there are 2 ways:

  1. ALTER TABLE tb ADD CONSTRAINT tb_pkey PRIMARY KEY USING INDEX tb_sha256_key_idx;

  2. DROP INDEX tb_sha256_key_idx and then ALTER TABLE tb ADD PRIMARY KEY (sha256, key);

sha256 and key aren't null columns (so it should work fine).

I have some concerns regarding the two approaches and I'm debating which one is the right approach using during a db migration:

The second approach is better because by drop/create we'll dramatically improve index clustering factor, and its size because of fragmentation. It's preferable from performance point of view. But creating an index over large table would cause large temporary files (That's why we use REINDEX most of the time and create new indexes only as last resort).

Want to further the discussion and get some claims and/or more opinions regarding that scenario.

1 Answer 1

3

In production systems, downtime is more important. In most cases, you cannot completely block (for insert/update/delete and even select) a large table for several hours while a new btree is built. Therefore, changing the primary key is performed only using a previously created unique index.

-- make sure all columns in the new primary key are NOT NULL

CREATE UNIQUE INDEX CONCURRENTLY :new_primary_key_name ON :relname(:new_colname);

begin;
SET LOCAL statement_timeout TO '1s';
ALTER TABLE :relname DROP CONSTRAINT :primary_key_name;
ALTER TABLE :relname ADD CONSTRAINT :primary_key_name PRIMARY KEY USING INDEX :new_primary_key_name;
commit;

In this case, while building a new btree, we do not block normal select/insert/update/delete queries.

For small tables or when you don't care about execution time - it doesn't matter. Feel free to command DROP CONSTRAINT + ADD PRIMARY KEY for simplicity.


sha256 and key are nullable columns

All parts of the primary key must be not null. ADD PRIMARY KEY will try to add not null implicitly. Migration will return an error if this is not true for some tuple.

But creating an index over large table would cause large temporary files (That's why we use REINDEX most of the time and create new indexes only as last resort).

This doesn't make any sense. The reindex command uses the same index building code as the create index command. Effectively reindex index is create index + drop index in one command.

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.