Re: table size is bigger than expected

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