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

pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed

What is the best way to create Primary Key on a large table in Postgresql 9.5?
9+ messages / 7 participants
[nested] [flat]

* What is the best way to create Primary Key on a large table in Postgresql 9.5?
@ 2016-08-18 21:00 Ravi Tammineni <rtammineni@partner.aligntech.com>
  2016-08-18 21:05 ` Re: What is the best way to create Primary Key on a large table in Postgresql 9.5? Samed YILDIRIM <samed@reddoc.net>
  2016-08-18 21:14 ` Re: What is the best way to create Primary Key on a large table in Postgresql 9.5? Keith <keith@keithf4.com>
  0 siblings, 2 replies; 9+ messages in thread

From: Ravi Tammineni @ 2016-08-18 21:00 UTC (permalink / raw)
  To: pgsql-admin

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

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: What is the best way to create Primary Key on a large table in Postgresql 9.5?
  2016-08-18 21:00 What is the best way to create Primary Key on a large table in Postgresql 9.5? Ravi Tammineni <rtammineni@partner.aligntech.com>
@ 2016-08-18 21:05 ` Samed YILDIRIM <samed@reddoc.net>
  2016-08-18 21:16   ` Re: What is the best way to create Primary Key on a large table in Postgresql 9.5? David G. Johnston <david.g.johnston@gmail.com>
  2016-08-18 21:19   ` Re: What is the best way to create Primary Key on a large table in Postgresql 9.5? Ravi Tammineni <rtammineni@partner.aligntech.com>
  1 sibling, 2 replies; 9+ messages in thread

From: Samed YILDIRIM @ 2016-08-18 21:05 UTC (permalink / raw)
  To: Ravi Tammineni <rtammineni@partner.aligntech.com>; pgsql-admin

<p>Hi Ravi,<br /><br />You can use CREATE INDEX with CONCURRENTLY keyword. It will not lock your table during the process.<br /><br />Best regards.<br /><br /><br /><br />İyi çalışmalar.<br />Samed YILDIRIM<br /><br /><br />19.08.2016, 00:01, "Ravi Tammineni" &lt;<a href="mailto:rtammineni@partner.aligntech.com">rtammineni@partner.aligntech.com</a>&gt;:</p><blockquote> 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.<br /><br /> Thanks<br /><br /> ravi</blockquote>

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: What is the best way to create Primary Key on a large table in Postgresql 9.5?
  2016-08-18 21:00 What is the best way to create Primary Key on a large table in Postgresql 9.5? Ravi Tammineni <rtammineni@partner.aligntech.com>
  2016-08-18 21:05 ` Re: What is the best way to create Primary Key on a large table in Postgresql 9.5? Samed YILDIRIM <samed@reddoc.net>
@ 2016-08-18 21:16   ` David G. Johnston <david.g.johnston@gmail.com>
  1 sibling, 0 replies; 9+ messages in thread

From: David G. Johnston @ 2016-08-18 21:16 UTC (permalink / raw)
  To: Samed YILDIRIM <samed@reddoc.net>; +Cc: Ravi Tammineni <rtammineni@partner.aligntech.com>; pgsql-admin

On Thu, Aug 18, 2016 at 5:05 PM, Samed YILDIRIM <samed@reddoc.net> wrote:

> Hi Ravi,
>
> You can use CREATE INDEX with CONCURRENTLY keyword. It will not lock your
> table during the process.
>
> After which you would do:

ALTER TABLE tbl ADD PRIMARY KEY USING INDEX <index name from above>;

https://www.postgresql.org/docs/9.5/static/sql-altertable.html

See the note on the linked page advising this exact procedure.

David J.

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: What is the best way to create Primary Key on a large table in Postgresql 9.5?
  2016-08-18 21:00 What is the best way to create Primary Key on a large table in Postgresql 9.5? Ravi Tammineni <rtammineni@partner.aligntech.com>
  2016-08-18 21:05 ` Re: What is the best way to create Primary Key on a large table in Postgresql 9.5? Samed YILDIRIM <samed@reddoc.net>
@ 2016-08-18 21:19   ` Ravi Tammineni <rtammineni@partner.aligntech.com>
  2016-08-18 21:48     ` Re: What is the best way to create Primary Key on a large table in Postgresql 9.5? David G. Johnston <david.g.johnston@gmail.com>
  2016-08-19 07:20     ` Re: What is the best way to create Primary Key on a large table in Postgresql 9.5? Esteban Casuscelli <esteban.casuscelli@gmail.com>
  2016-08-19 07:22     ` Re: What is the best way to create Primary Key on a large table in Postgresql 9.5? Esteban Casuscelli <estebanc00@yahoo.com.ar>
  2016-08-24 12:08     ` Re: What is the best way to create Primary Key on a large table in Postgresql 9.5? Feike Steenbergen <feikesteenbergen@gmail.com>
  1 sibling, 4 replies; 9+ messages in thread

From: Ravi Tammineni @ 2016-08-18 21:19 UTC (permalink / raw)
  To: Samed YILDIRIM <samed@reddoc.net>; pgsql-admin

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


^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: What is the best way to create Primary Key on a large table in Postgresql 9.5?
  2016-08-18 21:00 What is the best way to create Primary Key on a large table in Postgresql 9.5? Ravi Tammineni <rtammineni@partner.aligntech.com>
  2016-08-18 21:05 ` Re: What is the best way to create Primary Key on a large table in Postgresql 9.5? Samed YILDIRIM <samed@reddoc.net>
  2016-08-18 21:19   ` Re: What is the best way to create Primary Key on a large table in Postgresql 9.5? Ravi Tammineni <rtammineni@partner.aligntech.com>
@ 2016-08-18 21:48     ` David G. Johnston <david.g.johnston@gmail.com>
  3 siblings, 0 replies; 9+ messages in thread

From: David G. Johnston @ 2016-08-18 21:48 UTC (permalink / raw)
  To: Ravi Tammineni <rtammineni@partner.aligntech.com>; +Cc: Samed YILDIRIM <samed@reddoc.net>; pgsql-admin

On Thu, Aug 18, 2016 at 5:19 PM, Ravi Tammineni <
rtammineni@partner.aligntech.com> wrote:

>  I have to add a new id column to the existing table,
>

You will have to write/create a brand new table file.  That will take
time.  I suspect that using some combination of views and triggers, and
object renaming, will allow you to put together a custom scheme to do this
with minimal impact on production (though you cannot avoid the I/O, you can
maybe spread it out).

A lot depends on the exact usage pattern of the table in question.

David J.

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: What is the best way to create Primary Key on a large table in Postgresql 9.5?
  2016-08-18 21:00 What is the best way to create Primary Key on a large table in Postgresql 9.5? Ravi Tammineni <rtammineni@partner.aligntech.com>
  2016-08-18 21:05 ` Re: What is the best way to create Primary Key on a large table in Postgresql 9.5? Samed YILDIRIM <samed@reddoc.net>
  2016-08-18 21:19   ` Re: What is the best way to create Primary Key on a large table in Postgresql 9.5? Ravi Tammineni <rtammineni@partner.aligntech.com>
@ 2016-08-19 07:20     ` Esteban Casuscelli <esteban.casuscelli@gmail.com>
  3 siblings, 0 replies; 9+ messages in thread

From: Esteban Casuscelli @ 2016-08-19 07:20 UTC (permalink / raw)
  To: pgsql-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
>

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: What is the best way to create Primary Key on a large table in Postgresql 9.5?
  2016-08-18 21:00 What is the best way to create Primary Key on a large table in Postgresql 9.5? Ravi Tammineni <rtammineni@partner.aligntech.com>
  2016-08-18 21:05 ` Re: What is the best way to create Primary Key on a large table in Postgresql 9.5? Samed YILDIRIM <samed@reddoc.net>
  2016-08-18 21:19   ` Re: What is the best way to create Primary Key on a large table in Postgresql 9.5? Ravi Tammineni <rtammineni@partner.aligntech.com>
@ 2016-08-19 07:22     ` Esteban Casuscelli <estebanc00@yahoo.com.ar>
  3 siblings, 0 replies; 9+ messages in thread

From: Esteban Casuscelli @ 2016-08-19 07:22 UTC (permalink / raw)
  To: pgsql-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
>

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: What is the best way to create Primary Key on a large table in Postgresql 9.5?
  2016-08-18 21:00 What is the best way to create Primary Key on a large table in Postgresql 9.5? Ravi Tammineni <rtammineni@partner.aligntech.com>
  2016-08-18 21:05 ` Re: What is the best way to create Primary Key on a large table in Postgresql 9.5? Samed YILDIRIM <samed@reddoc.net>
  2016-08-18 21:19   ` Re: What is the best way to create Primary Key on a large table in Postgresql 9.5? Ravi Tammineni <rtammineni@partner.aligntech.com>
@ 2016-08-24 12:08     ` Feike Steenbergen <feikesteenbergen@gmail.com>
  3 siblings, 0 replies; 9+ messages in thread

From: Feike Steenbergen @ 2016-08-24 12:08 UTC (permalink / raw)
  To: Ravi Tammineni <rtammineni@partner.aligntech.com>; +Cc: Samed YILDIRIM <samed@reddoc.net>; pgsql-admin

What we would do when having to update a large table is do it in multiple
transactions. Basically:

1. create sequence s1;
2. alter table t1 add column c1 integer default nextval('s1'); -- Ensure
new values don't need changing anymore
3. In batches of a significant but not huge amount of rows: update t1 set
c1=nextval('s1');
4. Create index
5. Convert the index into pk index

We use niceupdate (home brew tool) for this, it may suit your needs:
https://github.com/zalando/acid-tools/wiki/Niceupdate

Regards,

Feike

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: What is the best way to create Primary Key on a large table in Postgresql 9.5?
  2016-08-18 21:00 What is the best way to create Primary Key on a large table in Postgresql 9.5? Ravi Tammineni <rtammineni@partner.aligntech.com>
@ 2016-08-18 21:14 ` Keith <keith@keithf4.com>
  1 sibling, 0 replies; 9+ messages in thread

From: Keith @ 2016-08-18 21:14 UTC (permalink / raw)
  To: Ravi Tammineni <rtammineni@partner.aligntech.com>; +Cc: pgsql-admin

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

^ permalink  raw  reply  [nested|flat] 9+ messages in thread

end of thread, other threads:[~2016-08-24 12:08 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-08-18 21:00 What is the best way to create Primary Key on a large table in Postgresql 9.5? Ravi Tammineni <rtammineni@partner.aligntech.com>
2016-08-18 21:05 ` Samed YILDIRIM <samed@reddoc.net>
2016-08-18 21:16   ` David G. Johnston <david.g.johnston@gmail.com>
2016-08-18 21:19   ` Ravi Tammineni <rtammineni@partner.aligntech.com>
2016-08-18 21:48     ` David G. Johnston <david.g.johnston@gmail.com>
2016-08-19 07:20     ` Esteban Casuscelli <esteban.casuscelli@gmail.com>
2016-08-19 07:22     ` Esteban Casuscelli <estebanc00@yahoo.com.ar>
2016-08-24 12:08     ` Feike Steenbergen <feikesteenbergen@gmail.com>
2016-08-18 21:14 ` Keith <keith@keithf4.com>

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