Postgresql – Add INCLUDE term to primary key index in Postgres 11

constraintindexindex-tuningpostgresql

I work with Postgres 11 and would like to change the index that backs the primary key of a large table (~750 million rows). The primary key is the bigint column id and I would like to include an extra column using the INCLUDE term. This needs to happen without a table rewrite (i.e. no new/changed columns).

Dropping the current PRIMARY KEY constraint isn't convenient, because a lot of other tables reference the target table. I suppose it would be possible to drop the FOREIGN KEY constraints on all those tables, then drop the PRIMARY KEY constraint, recreate using a new index and then recreate the FOREIGN KEY constraints. Is there a better way to do it?

Best Answer

Without messing with the catalogs (which is not commendable), the only option I can think of would require you to do without foreign keys for a while:

You can define a second UNIQUE index on the table that contains the appropriate INCLUDE clause.

If you use the CONCURRENTLY clause of CREATE INDEX, that shouldn't be disruptive.

Then you can delete the original primary key constraint and all dependent foreign keys using DROP INDEX ... CASCADE.

Then use ALTER TABLE ... ADD CONSTRAINT ... USING INDEX to turn the unique index into a primary key constraint.

Now you can re-create the foreign key constraints.

Disclaimer

This is experimental and only tested rudimentarily. Proceed at your own risk. I would not use it myself and just drop / recreate constraints with standard DDL commands. If you break entries in the catalog tables you could easily mess up your database.

For all I know, there are only two differences between a PRIMARY KEY and a UNIQUE constraint in the catalog tables (the index itself is identical):

pg_index.indisprimary:
For PRIMARY KEY constraint ... TRUE
For UNIQUE constraint ... FALSE

pg_constraint.contype:
PRIMARY KEY constraint ... 'p'
UNIQUE constraint ... 'u'

You could convert constraint and index in place, from PRIMARY KEY constraint to UNIQUE constraint, my_idx being the (optionally schema-qualified) index name:

UPDATE pg_index SET indisprimary = FALSE WHERE indexrelid = 'my_idx'::regclass
UPDATE pg_constraint SET contype = 'u' WHERE conindid = 'my_idx'::regclass;

Or upgrade from UNIQUE to PRIMARY KEY:

UPDATE pg_index SET indisprimary = TRUE WHERE indexrelid = 'my_idx'::regclass;
UPDATE pg_constraint SET contype = 'p' WHERE conindid = 'my_idx'::regclass;

Best Answer

Related Solutions

Sql-server – How does SQL Server choose an index key for a foreign key reference

Postgresql – What happens to the index of a primary key after a DROP CONSTRAINT

Disclaimer

Related Question