Create a Primary Key on a large table (6Million records) without locking the table in PostgreSQL

Question

I want to create a Primary Key on a table with 6 Million records, but when I execute this:

ALTER TABLE schema_name.table_name ADD CONSTRAINT pkey_name PRIMARY KEY (field_pkey_name);

It's locking my table and alter table does not finish executing...

If it doesn't ever finish, then it must be blocked by something else holding a conflicting lock. You will probably need to resolve this before any method is going to work. Laurenz's method only needs a strong lock for a fraction of a second, but it still does need to take a strong lock. — jjanes, Jan 07 '21 at 16:44

Laurenz Albe · Accepted Answer · 2023-03-23T18:40:39.483

8

Try to do it in two steps:

CREATE UNIQUE INDEX CONCURRENTLY pkey_name
   ON schema_name.table_name (field_pkey_name);

ALTER TABLE schema_name.table_name
   ADD CONSTRAINT pkey_name PRIMARY KEY USING INDEX pkey_name;

It will still take a long time (even longer), but the table will not be locked for a long time.

edited Mar 23 '23 at 18:40

answered Jan 07 '21 at 08:28

Laurenz Albe

209,280
17
206
263

1

Actually, the table will be locked. However, for a very short time as the index is already built. – Arthur Del Esposte Mar 23 '23 at 18:29

Create a Primary Key on a large table (6Million records) without locking the table in PostgreSQL

1 Answers1