Обсуждение: What is the best way to create Primary Key on a large table in Postgresql 9.5?
What is the best way to create Primary Key on a large table in Postgresql 9.5?
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
Re: What is the best way to create Primary Key on a large table in Postgresql 9.5?
<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" <<a href="mailto:rtammineni@partner.aligntech.com">rtammineni@partner.aligntech.com</a>>:<blockquote> Ihave to create a primarykey on a large table (~100Million records). What is the best and fastest way to create pkey? This column is a sequencecolumn and i don't want to lock the table because this is highly transactional database.<br /><br /> Thanks<br /><br/> ravi</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.
Thanks
ravi
https://www.keithf4.com/cleaning-up-postgresql-bloat/
https://www.postgresql.org/docs/9.5/static/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY
Re: 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.
Re: What is the best way to create Primary Key on a large table in Postgresql 9.5?
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>:
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
Re: What is the best way to create Primary Key on a large table in Postgresql 9.5?
I have to add a new id column to the existing table,
Re: What is the best way to create Primary Key on a large table in Postgresql 9.5?
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.-
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>: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
Re: What is the best way to create Primary Key on a large table in Postgresql 9.5?
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.-
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>: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
Re: What is the best way to create Primary Key on a large table in Postgresql 9.5?
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