I have to create a primary key on a large table (~100Million records). 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.
Thanks
ravi
You can create a unique index concurrently and then create a primary key using that previously made unique index. Still requires a lock, but it should be much shorter since it doesn't have to verify the constraint again. I have an example of this at the link below for when you need to recreate a primary key due to it being bloated
It will not be as fast as if you created the primary key outright and lock the table. If this table has a very high write rate, creating a new index concurrently could take quite a long time. But it should not lock the table. See the documentation for caveats when creating concurrent indexes