Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 51829B5DBC1 for ; Thu, 4 Aug 2011 20:47:27 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 02632-06 for ; Thu, 4 Aug 2011 23:47:21 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from polaris.pinpointresearch.com (cerberus.pinpointresearch.com [66.7.238.130]) by mail.postgresql.org (Postfix) with ESMTP id D8BB9B5D800 for ; Thu, 4 Aug 2011 20:47:20 -0300 (ADT) Received: from betelgeuse.pinpointresearch.com (betelgeuse.pinpointresearch.com [192.168.1.179]) by polaris.pinpointresearch.com (Postfix) with ESMTP id A43FEE00EC83; Thu, 4 Aug 2011 16:47:19 -0700 (PDT) Message-ID: <4E3B2F87.9010506@pinpointresearch.com> Date: Thu, 04 Aug 2011 16:47:19 -0700 From: Steve Crawford User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.2.18) Gecko/20110617 Lightning/1.0b2 Thunderbird/3.1.11 MIME-Version: 1.0 To: Jian Shi CC: "pgsql-performance@postgresql.org" Subject: Re: table size is bigger than expected References: <68B6AA6E4AA4D840865FAB5BEC6707C108183089@UNIATLEX2010.unitrends.com> In-Reply-To: <68B6AA6E4AA4D840865FAB5BEC6707C108183089@UNIATLEX2010.unitrends.com> Content-Type: multipart/alternative; boundary="------------040503090002030401090103" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.703 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RP_MATCHES_RCVD=-0.804 X-Spam-Level: X-Archive-Number: 201108/85 X-Sequence-Number: 44532 This is a multi-part message in MIME format. --------------040503090002030401090103 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit On 08/04/2011 11:56 AM, Jian Shi wrote: > > Hey, > > I'm a new user of PostgreSQL. I found one of my tables is taking > unexpectedly large space:... > > I did vaccum, reindex, the size is still the same. Is there anything > else that I can do? > > Did you try CLUSTER? A basic vacuum only identifies space as reusable, it doesn't actually shrink on-disk size. If you have workloads that update or delete a small number of tuples per transaction, the autovacuum process should keep things reasonably under control. But if you run transactions that do bulk updates or deletes, you may need to intervene. The CLUSTER statement will completely rewrite and reindex your table (and will physically reorder the table based on the selected index). Note: CLUSTER requires an exclusive lock on the table. Cheers, Steve --------------040503090002030401090103 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit On 08/04/2011 11:56 AM, Jian Shi wrote:

Hey,

I’m a new user of PostgreSQL. I found one of my tables is taking unexpectedly large space:...

I did vaccum, reindex, the size is still the same. Is there anything else that I can do?

Did you try CLUSTER? A basic vacuum only identifies space as reusable, it doesn't actually shrink on-disk size.

If you have workloads that update or delete a small number of tuples per transaction, the autovacuum process should keep things reasonably under control. But if you run transactions that do bulk updates or deletes, you may need to intervene. The CLUSTER statement will completely rewrite and reindex your table (and will physically reorder the table based on the selected index). Note: CLUSTER requires an exclusive lock on the table.

Cheers,
Steve

--------------040503090002030401090103--