Re: strange table disk sizes

On Thu, Sep 1, 2011 at 6:38 AM, Rik Bellens <rik(dot)bellens(at)telin(dot)ugent(dot)be> wrote:
> Op 01-09-11 14:22, Scott Marlowe schreef:
>>
>> On Thu, Sep 1, 2011 at 6:05 AM, Rik Bellens<rik(dot)bellens(at)telin(dot)ugent(dot)be>
>>  wrote:
>>>
>>> Op 01-09-11 13:31, Scott Marlowe schreef:
>>>>
>>>> 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?
>>>
>>> I use version 8.3. I see the 'autovacuum launcher process' and
>>> 'autovacuum
>>> worker process' in the process list, so I suppose autovacuum is running.
>>>
>>> Rows in the measurement table are added once and never deleted or
>>> updated.
>>> Adding a row to this table triggers a function that adds a row to the
>>> stats_count table. Normally rows are added chronologically. So rows in
>>> the
>>> stats_count table are normally not updated either. If however, for some
>>> reason, a measurement is added from an older time, all rows of that
>>> device
>>> which come after this time, are updated, but I don't think this will
>>> happen
>>> very often.
>>>
>>> The table 'stats_count' was created in a later stage, so the first 45M
>>> rows
>>> were added at once and chronologically. However, because the function to
>>> initialize this table took a long time and the client application crashed
>>> a
>>> few times, I had to restart this function several times. Can it be that
>>> there is some trash left from running this function several times without
>>> finishing it? Would it be a solution to run 'VACUUM FULL' to reclaim some
>>> disk space?
>>
>> Yeah, could be.  Take a look at this page:
>> http://wiki.postgresql.org/wiki/Show_database_bloat and see if the
>> query there sheds some light on your situ.
>
> thanks for this answer
>
> if i run the query, I get 12433752064 wasted bytes on stats_count_pkey, so I
> suppose that is the reason

Sounds like it. Take a look here:
http://wiki.postgresql.org/wiki/Index_Maintenance