Re: Unreasonable size of table pg 8.2.5

6 dec 2007 kl. 15.25 skrev Bill Moran:

> Henrik <henke(at)mac(dot)se> wrote:
>>
>> Hello list,
>>
>> I have a table with 135 rows and it still takes up about 360MB with
>> only small columns. Details below.
>>
>> db=# vacuum full tbl_archive;
>> VACUUM
>> db=# select * from
>> pg_size_pretty(pg_total_relation_size('tbl_archive'));
>> pg_size_pretty
>> ----------------
>> 360 MB
>> (1 row)
>>
>> db=# select * from pg_size_pretty(pg_relation_size('tbl_archive'));
>> pg_size_pretty
>> ----------------
>> 16 kB
>> (1 row)
>>
>>
>> Looks like we have a very bloated index.
>> After reindex
>> db=# select * from
>> pg_size_pretty(pg_total_relation_size('tbl_archive'));
>> pg_size_pretty
>> ----------------
>> 80 kB
>> (1 row)
>>
>> I thought that reindex should not be necessary in 8.2.5?
>
> VACUUM FULL tends to bloat indexes, which is one of the reasons that
> it's
> not recommended for regular maintenance.
>
> Use plain VACUUM instead. If you feel the need to run a VACUUM
> FULL, always
> do a REINDEX afterward.
I usually only do normal vacuum but its good to know that reindex
should be ran after vacuum full.
>
>
> Even still, there are apparently some corner cases around that cause
> index
> bloat. If it turns out that you've found one, you may want to
> document it
> so the developers can look into possible solutions.
>
I maybe have an idea why its get this big but I'll do some more
testing first!
Thanks,
Henke
> --
> Bill Moran
> http://www.potentialtech.com
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/