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

Have you considered the following:

1) create a new table with all you need.
2) load the new table from the original table
3) keep updating the new table from the original table until a time that
you do not have users
4) rename original table
5) rename new table

You need to consider all additional variables from your production
environment that I do not know and test it but it is another option.

Thanks
esteban.-

On 08/18/2016 11:19 PM, Ravi Tammineni wrote:
>
> Hi Samed,
>
> Thanks for the response.
>
> But my question is, I have to add a new id column to the existing
> table, populate the data and then convert that column into primary key
> column. I am running the following command but its taking forever.
>
> ALTER TABLE tblclinchecklog ADD COLUMN id serial primary key;
>
> Is there a better way? Like
>
> 1>Add column
>
> 2>Create sequence
>
> 3>Update the table
>
> 4>Create index
>
> 5>Convert the index into pk index
>
> Thanks
>
> ravi
>
> *From:*Samed YILDIRIM [mailto:samed(at)reddoc(dot)net]
> *Sent:* Thursday, August 18, 2016 2:05 PM
> *To:* Ravi Tammineni <rtammineni(at)partner(dot)aligntech(dot)com>;
> pgsql-admin(at)postgresql(dot)org
> *Subject:* Re: [ADMIN] What is the best way to create Primary Key on a
> large table in Postgresql 9.5?
>
> Hi Ravi,
>
> You can use CREATE INDEX with CONCURRENTLY keyword. It will not lock
> your table during the process.
>
> Best regards.
>
>
>
> İyi çalışmalar.
> Samed YILDIRIM
>
>
> 19.08.2016, 00:01, "Ravi Tammineni" <rtammineni(at)partner(dot)aligntech(dot)com
> <mailto:rtammineni(at)partner(dot)aligntech(dot)com>>:
>
> 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
>