From: Steve Crawford <scrawford@pinpointresearch.com>
To: Jian Shi <jshi@unitrends.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: table size is bigger than expected
Date: Thu, 04 Aug 2011 16:47:19 -0700
Message-ID: <4E3B2F87.9010506@pinpointresearch.com> (raw)
In-Reply-To: <68B6AA6E4AA4D840865FAB5BEC6707C108183089@UNIATLEX2010.unitrends.com>
References: <68B6AA6E4AA4D840865FAB5BEC6707C108183089@UNIATLEX2010.unitrends.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: scrawford@pinpointresearch.com, jshi@unitrends.com
Subject: Re: table size is bigger than expected
In-Reply-To: <4E3B2F87.9010506@pinpointresearch.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox