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
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.
-
-
"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.– KedareJun 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– user1822Jun 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.– rktAug 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