Re: What is the best way to create Primary Key on a large table in Postgresql 9.5?

[Keith <keith@xxxxxxxxxxx>]

On Thu, Aug 18, 2016 at 5:00 PM, Ravi Tammineni <rtammineni@xxxxxxxxxxxxxxxxxxxxx> wrote:

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

https://www.keithf4.com/cleaning-up-postgresql-bloat/

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

https://www.postgresql.org/docs/9.5/static/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY