Re: Unreasonable size of table pg 8.2.5

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.

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.

--
Bill Moran
http://www.potentialtech.com