Re: table size is bigger than expected

Steve Crawford <scrawford@xxxxxxxxxxxxxxxxxxxx> · Thu, 04 Aug 2011 16:47:19 -0700



    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