I have to create a primary key on a large table (~100Million records) in Postgtres database. What is the best and fastest way to create pkey? This column is a sequence column and i don't want to lock the table because this is highly transactional database.

2 Answers 2


You can create a unique index with the option concurrently which will allow read and write access to the table while the index is created. However, building an index concurrently will take much longer then adding the index without that option.

create unique index concurrently unique_id on the_big_table (id);

Once the index is created, you can use that as a primary key:

alter table the_big_table
   add primary key using index unique_id;

That will only lock the the table for a very short time.

  • is is the answer to accept @rkt Jan 21, 2022 at 10:04
  • "That will only lock the the table for a very short time.": No, as the primary key is a clustered index that define how the table is physically organized, even this will need to rebuild the whole table.
    – Kedare
    Jun 23, 2022 at 8:21
  • @Kedare: Postgres does not have a "clustered index". Neither creating the index nor creating the PK constraint will "physically reorganize" the table
    – user1822
    Jun 23, 2022 at 8:43

You will have to lock the table if you want to add a Primary Key.

However (and this will require some storage space) ...

  • Create a new table with the same schema (and indexes, foreign keys, check constraints, etc.), and add the new Primary Key as well.

  • Create a view that selects (with UNION ALL) from both tables, and in a quiet period (or a maintenance window), rename the old table to something else, give the view the same name as the old table, and then your users and applications shouldn't know the difference (selecting from the view instead of the table).

  • Create a batch process that deletes x number of rows out of the old table at a time, and inserts these into the new table.

  • In another maintenance window, drop the view, rename the new table so that it gets the original table name (and it already has all the indexes, foreign keys, check constraints and so on), and then drop the old table, which should now be empty.

It will take some time to run this, depending how many rows you move in each batch, but it keeps your table (which is now part of the view) online. You will generate a lot of logs, and it will require a fair amount of disk space, but that's how I'd do it.

  • Planning to do this.. ALTER TABLE table1 ADD COLUMN "id" INTEGER; CREATE SEQUENCE "table1_id_seq"; UPDATE foo SET id = nextval('"table1_id_seq"'); ALTER TABLE table1 ALTER COLUMN "id" SET DEFAULT nextval('"table1_id_seq"'); ALTER TABLE table1 ALTER COLUMN "id" SET NOT NULL; CREATE UNIQUE INDEX CONCURRENTLY my_pkey_idx ON table1 (id_column); ALTER TABLE table1 ADD CONSTRAINT my_pkey PRIMARY KEY USING INDEX my_pkey_idx; Please let me know if that is the right solution or not.
    – rkt
    Aug 18, 2016 at 22:28
  • Every time you ALTER TABLE, it will need to take a lock. If that's not what you want, then this is not going to work, and you should look at my proposed solution. Aug 18, 2016 at 22:32

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.