Re: Unexplained growth of tables

After a long battle with technology, kraj(at)illumen(dot)com ("Benjamin Krajmalnik"), an earthling, wrote:
> Any assistance or guidance will be deeply appreciated. I am pulling
> hairs on this one.

It needs to be vacuumed a lot more aggressively. If you're using
pg_autovacuum, the defaults seem to be not quite aggressive enough.

Mind you, now that the table has grown to huge size, it won't be cut
down to size by a mere VACUUM ANALYZE.

To draw it back down to size, you need to run VACUUM FULL, which will
cut the number of tuples down to size, and then reindex the table,
which will "do a number" on the indices.

Alternatively, "CLUSTER [some index] on [table];" will reorganize the
whole thing at once, reshaping the table to conform with the specified
index, and regenerating all indices, at the cost that it (at present,
up to 8.1; I'm not sure about 8.2) doesn't follow MVCC and keep old,
but still-visible obsolete copies of tuples.

CLUSTER is the fast answer, assuming you haven't any old transactions
you need to keep in flight...
--
(format nil "~S(at)~S" "cbbrowne" "gmail.com")
http://linuxfinances.info/info/slony.html
The difference between a child and a hacker is the amount he flames
about his toys. -- Ed Schwalenberg