Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id E6D7165016C for ; Thu, 10 Jul 2008 04:53:17 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 28242-04-2 for ; Thu, 10 Jul 2008 04:53:06 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from wdscexfe02.sc.wdc.com (wdscexfe02.sc.wdc.com [129.253.170.52]) by postgresql.org (Postfix) with ESMTP id CE64D650131 for ; Thu, 10 Jul 2008 04:53:05 -0300 (ADT) Received: from wdmyexbe03.my.asia.wdc.com ([129.253.105.55]) by wdscexfe02.sc.wdc.com with Microsoft SMTPSVC(6.0.3790.1830); Thu, 10 Jul 2008 00:52:36 -0700 Received: from 129.253.110.56 ([129.253.110.56]) by wdmyexbe03.my.asia.wdc.com ([129.253.105.55]) with Microsoft Exchange Server HTTP-DAV ; Thu, 10 Jul 2008 07:52:33 +0000 Received: from neuromancer by 129.253.105.55; 10 Jul 2008 15:53:00 +0800 Subject: Altering a column type - Most efficient way From: Ow Mun Heng To: Pgsql performance Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Date: Thu, 10 Jul 2008 15:53:00 +0800 Message-Id: <1215676380.15319.2.camel@neuromancer.home.net> Mime-Version: 1.0 X-Mailer: Evolution 2.22.0 X-OriginalArrivalTime: 10 Jul 2008 07:52:36.0289 (UTC) FILETIME=[EB075310:01C8E261] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200807/85 X-Sequence-Number: 30685 =EF=BB=BFIs there any quick hacks to do this quickly? There's around 20-30million rows of data.=20 I want to change a column type from varchar(4) to varchar() table size is ~10-15GB (and another 10-15G for indexes) What would be the preferrred way of doing it? SHould I be dropping the indexes 1st to make things faster? Would it matter? The punch line is that since the databases are connected via slony, this makes it even harder to pull it off. My last try got the DDL change completed in like 3 hours (smallest table of the bunch) and it hung everything.