Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id EE2F06325DB for ; Thu, 25 Feb 2010 19:41:23 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 40879-07 for ; Thu, 25 Feb 2010 23:41:07 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f216.google.com (mail-bw0-f216.google.com [209.85.218.216]) by mail.postgresql.org (Postfix) with ESMTP id 14EAB6322EA for ; Thu, 25 Feb 2010 19:41:12 -0400 (AST) Received: by bwz8 with SMTP id 8so1282283bwz.3 for ; Thu, 25 Feb 2010 15:41:11 -0800 (PST) MIME-Version: 1.0 Received: by 10.204.16.66 with SMTP id n2mr289954bka.67.1267141271192; Thu, 25 Feb 2010 15:41:11 -0800 (PST) In-Reply-To: References: <7965A9DCF12CC14984420BCC37B1608F25A9E2D038@Elzar.grant.co.uk> From: Jaime Casanova Date: Thu, 25 Feb 2010 18:40:55 -0500 Message-ID: <3073cc9b1002251540s8b91e7cw844b3bb6da2a0cd@mail.gmail.com> Subject: Re: Altering a column (increasing size) in Postgres takes long time To: "Tomeh, Husam" Cc: "pgsql-admin@postgresql.org" Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.882 tagged_above=-10 required=5 tests=AWL=-0.142, BAYES_20=-0.74 X-Spam-Level: X-Archive-Number: 201002/191 X-Sequence-Number: 33794 On Thu, Feb 25, 2010 at 6:04 PM, Tomeh, Husam wrot= e: > We have a huge table with hundred million records. We need to increase th= e > size of an existing column with varchar type, and it has been running for > more than 12 hours. > > > > We=E2=80=99re using: ALTER TABLE Property ALTER COLUMN "situs-number" TYP= E > varchar(30); > it's not better to have the field beign type text and don't worry about length but just in check constraints if really necesary? > > 1)=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Can you explain what=E2=80=99s hap= pening internally that make this a very > long process? Does the table get re-created? > yes, and all it's indexes rebuild not just the one you dropped and the FK's rechecked (don't think so but can't remember right now)? > > > 2)=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Assuming the Alter statement finis= hed successfully, And if I didn=E2=80=99t > drop the index (on that column), do I have to rebuild the index? Does the > index get invalidated for just alter the indexed column? > it's get rebuilt > > > 3)=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Some folks referred to directly up= dating Postgres internal tables > (pg_attribute) which takes seconds to make the column change happen. How > safe if this and would potentially cause any corruption? > no, that's insane > > 4)=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Is there a more practical and safe= method =C2=A0to alter a huge table > with reasonable amount of time? > > use text fields instead of varchar(n) --=20 Atentamente, Jaime Casanova Soporte y capacitaci=C3=B3n de PostgreSQL Asesor=C3=ADa y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157