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

On Thu, Aug 18, 2016 at 5:00 PM, Ravi Tammineni <
rtammineni(at)partner(dot)aligntech(dot)com> 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