Re: strange table disk sizes

On Thu, Sep 1, 2011 at 4:32 AM, Rik Bellens <rik(dot)bellens(at)telin(dot)ugent(dot)be> wrote:
>
> Hello,
>
> I have two large tables in a database, one containing original data and the
> other one derived from the first table. The first table contains several
> columns and indexes, while the second table has less columns and only one
> index. Both tables have the same number of rows. Nevertheless, the second
> table is much larger in disk size than the first one. How can this be
> explained?

This is most likely due to table bloat. In PostgreSQL when you update
or delete a row, a dead version gets left behind. Vacuum eventually
comes along and reclaims the empty space to be reused. If you delete
/ update a LOT of rows at once, then you'll have a lot of dead rows
which can only be reused after vacuuming when you do more updates or
deletes later on.

A few salient questions. What version of PostgreSQL are you running?
Is autovacuum running? Do you do a LOT of bulk deletes / updates? If
you do a lot of bulk deletes on this table, and you delete everything,
can you switch to using the truncate command instead?