8

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?

Improve this question
2
  • 1
    I don't think there is a better way, currently. You might automate dropping and recreating FK constraints for convenience. Is concurrent read or write access of concern? Is (temporary) storage size of concern? Commented Aug 19, 2019 at 16:25
  • Thanks, I was hoping something would get me around the FK recreation. Automating this might make this easier, yes, that's a good point. Storage should be fine for the additional index and creating the new index concurrently, will probably make the IO okay. Commented Aug 19, 2019 at 23:57

1 Answer 1

Reset to default
7

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.

Improve this answer
2
  • 1
    True, I could define the new index before I delete the original one, which is probably a good idea in terms of data integrity. Unfortunately it doesn't get me around recreating all foreign key. Using CREATE INDEX CONCURRENTLY is certainly a good point, too. Commented Aug 19, 2019 at 23:53
  • 1
    I have revised my answer so that it actually works. You'd have to do without foreign keys for a while. Commented Aug 20, 2019 at 6:03

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.