Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bbu7T-0001bQ-Q8 for pgsql-admin@arkaria.postgresql.org; Mon, 22 Aug 2016 18:38:48 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bbu7T-0007Fm-Cx for pgsql-admin@arkaria.postgresql.org; Mon, 22 Aug 2016 18:38:47 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bae6w-0001fP-2m for pgsql-admin@postgresql.org; Fri, 19 Aug 2016 07:21:02 +0000 Received: from mail-wm0-x22d.google.com ([2a00:1450:400c:c09::22d]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1bae6s-0006xM-R0 for pgsql-admin@postgresql.org; Fri, 19 Aug 2016 07:21:01 +0000 Received: by mail-wm0-x22d.google.com with SMTP id o80so27197281wme.1 for ; Fri, 19 Aug 2016 00:20:58 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=from:subject:to:references:message-id:date:user-agent:mime-version :in-reply-to; bh=PjLMbRWNUTerWdsc8t/qaAXrV+FYLQVJDDlN5OgCVik=; b=K4+ViVGM2OAcZfWTAtaZGh3T7sjJZR3S/gdRaO27LxqKHMkJFEVmgPpO9vLrVWL4QL 3jDBp2WSy6G9QuPam1egw2bBOzOdbVXz7sYLy1u0/jRcbBc5YO0RAZ3dkB5YbLabz7WJ gRd7DzsHGhU67Epe0UHd/8ZdYxAByRn77i48d1tuaXDQQpSJCvPi85OfgR/+L7SgD4Hs nc6xQxe033TBtI1j/B4r/ojQaYheGy/Ms6sUCMDrmQGJIqvqoyzarJOblkvxZnmK267Y 4uJ8js301SXB/xu58V5C2/c95AsuOzTd7wsLHHJlMj84eyAgTtxzG4Dp/98/2g6CUdgH iOGQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:from:subject:to:references:message-id:date :user-agent:mime-version:in-reply-to; bh=PjLMbRWNUTerWdsc8t/qaAXrV+FYLQVJDDlN5OgCVik=; b=fCdo03nhmGOYu/Y6SNxtfCWfi6bACTgvmI/6z/ZXW6H8Zrfdkz8Ocha67rh6Vb5BMH +l6J8AuTawYwi4RdJV7gabFC+Xaf1AaMc14GYnpS0bejzGrH9XMu0W+y/42WG98ZuwUY wtXTcUBAwKEViJAeRKfBA1lWbGbbjLPIZc6xf070jAnWlidNUDOKi1XVt2+h2SCKFudT M91VctSpT0YwlChUelwkshpo7U5uaCODCMHGZewhF3IAaU4kmao14eaA1cRT5kxLXgE9 Vx7BWSENCHabLtN6WptC9d/DbD3dfZxcCHlth5v7ErSiXwppooRjvZAaEOlZEO82m9r0 ++rQ== X-Gm-Message-State: AEkoouuIlLBYLGGpgKogL6z0EtOWJovKTjw9162DXmGvdnzrS8/duSIWTgtKpJF9BDQj3w== X-Received: by 10.194.110.229 with SMTP id id5mr5002188wjb.23.1471591256969; Fri, 19 Aug 2016 00:20:56 -0700 (PDT) Received: from hera.site (188-143-3-91.pool.digikabel.hu. [188.143.3.91]) by smtp.googlemail.com with ESMTPSA id va3sm5713926wjb.18.2016.08.19.00.20.55 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Fri, 19 Aug 2016 00:20:55 -0700 (PDT) From: Esteban Casuscelli X-Google-Original-From: Esteban Casuscelli Subject: Re: What is the best way to create Primary Key on a large table in Postgresql 9.5? To: "pgsql-admin@postgresql.org" References: <569661471554327@web30h.yandex.ru> Message-ID: <57B6B34F.5020604@googlemail.com> Date: Fri, 19 Aug 2016 09:20:47 +0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101 Thunderbird/38.7.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------060708030604030102060005" X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a multi-part message in MIME format. --------------060708030604030102060005 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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 ; > 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" >: > > 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 > --------------060708030604030102060005 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit 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>:

 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


--------------060708030604030102060005--