Add composite primary key on existing PostgreSQL table

I have a table on PostgresSQL with millions of rows. The table has columns a and b. I already have a unique index on a and b:

CONSTRAINT a_b UNIQUE (a, b)

I want to add a composite primary key on columns a and b.

Will there be any downtime if I do a

ALTER TABLE table_name ADD PRIMARY KEY (a, b);

Will there be any downtime?

While CREATE INDEX only blocks concurrent writes and still allows reads, you need ALTER TABLE to add the PRIMARY KEY constraint, and that takes an ACCESS EXCLUSIVE lock on the table, blocking reads, too. So, yes, if the table is essential, I would call that "downtime".

However, there are options to alleviate the pain.

First, both index columns must be NOT NULL. While a UNIQUE constraint / index allows null values, the PK does not. If that's not the case, fix it first. Else, the later ALTER TABLE will do it for you, extending the downtime. The manual:

If [...] the index's columns are not already marked NOT NULL, then
this command will attempt to do ALTER COLUMN SET NOT NULL against
each such column. That requires a full table scan to verify the
column(s) contain no nulls. In all other cases, this is a fast
operation.

So, first (after fixing any null values):

ALTER TABLE public.demo
  ALTER COLUMN a SET NOT NULL
, ALTER COLUMN b SET NOT NULL;

Then, the question is: unique index or constraint? An index can be repurposed in a very fast "metadata only" operation. The same is not possible for a constraint, unfortunately. One might think it should be: it's still the same index. But it's just not implemented. Too rare a use-case that anyone would have bothered. You can probably hack the system catalogs. But I am not going there. One false move and you can break the entire database. There was a similar request in the past, and I didn't go there, either:

• Drop primary key without dropping an index

So, if it's a constraint, build a second, otherwise identical, unique index first. Use a temporary name, it will later be thrown out the window. We have the non-blocking CREATE INDEX CONCURRENTLY for the task since Postgres 8.2 (almost forever now).

CREATE UNIQUE INDEX CONCURRENTLY a_b_temp ON demo (a,b);

Finally, drop the constraint, and add the PK building on the existing or new index:

ALTER TABLE public.demo
  DROP CONSTRAINT a_b  -- if it was a constraint
, ADD  CONSTRAINT demo_pkey PRIMARY KEY USING INDEX a_b_temp;  -- "a_b" if it was an index

This takes said ACCESS EXCLUSIVE lock. But all the hard work has already been done, so it should be a matter of milliseconds - as soon as concurrent transactions let it take the lock.

fiddle

StackExchange Database Administrators Q#324803, answer score: 3