PostgreSQL, Add Unique Constraint to Existing Table without Downtime/Locking

Question

I am trying to figure out the best way to go about adding a new varchar column to an existing table in a PostgreSQL database that doesn't acquire a table lock or require downtime.

I know that I can add an index concurrently which will prevent a lock.

CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS "my_unique_index" ON "my_table" USING ("my_column", varchar_pattern_ops)

What I am not sure of, and can't deduce from the docs is if the constraint operation will acquire a lock on the table if the unique index already exists.

ALTER TABLE "my_table" ADD CONSTRAINT "my_unique_constraint" UNIQUE USING INDEX "my_unique_index";

I saw in the docs that the NOT VALID option is not useful for unique constraints.

Any recommendations would be great.

Answer 1

Your method of first creating a unique index CONCURRENTLY and then adding a constraint based on that index is good. The ALTER TABLE will require an ACCESS EXCLUSIVE lock, but that will only be held for a very short time. That won't be a problem unless you always have long running transactions in your database – but in that case you already have a problem.