From: Esteban Casuscelli <esteban.casuscelli@gmail.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: What is the best way to create Primary Key on a large table in Postgresql 9.5?
Date: Fri, 19 Aug 2016 09:20:47 +0200
Message-ID: <57B6B34F.5020604@googlemail.com> (raw)
In-Reply-To: <BN1PR02MB2318313D3F51BCC4B6B0CF280150@BN1PR02MB231.namprd02.prod.outlook.com>
References: <BN1PR02MB23134DCFEF3087FA7DAF0EC80150@BN1PR02MB231.namprd02.prod.outlook.com>
<569661471554327@web30h.yandex.ru>
<BN1PR02MB2318313D3F51BCC4B6B0CF280150@BN1PR02MB231.namprd02.prod.outlook.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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@reddoc.net]
> *Sent:* Thursday, August 18, 2016 2:05 PM
> *To:* Ravi Tammineni <rtammineni@partner.aligntech.com>;
> pgsql-admin@postgresql.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@partner.aligntech.com
> <mailto:rtammineni@partner.aligntech.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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: esteban.casuscelli@gmail.com
Subject: Re: What is the best way to create Primary Key on a large table in Postgresql 9.5?
In-Reply-To: <57B6B34F.5020604@googlemail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox