Performance impact of adding unique constraint to existing postgres index

Question

We have a fairly large table in a postgres and during some refactoring process we realized there was an Index on an existing UUID column but it was lacking a unique constraint.

Does anyone have any experience applying an ALTER TABLE ... ADD CONSTRAINT ala https://www.postgresql.org/docs/9.4/indexes-unique.html and what the runtime impact is?

Trying to evaluate the runtime impact of doing this live vs. taking downtime. We've tested it on db copies but it difficult to simulate production traffic load and just looking for something to check if anybody had experience.

Answer 1

To do it without downtime, you should do it in two steps:

1.  CREATE UNIQUE INDEX CONCURRENTLY idx_name ON table_name (id);
   

2.  ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE(id) 
    USING INDEX idx_name;