On 9/30/05,
Jim C. Nasby <
jnasby@pervasive.com> wrote:
Looks like it's definately an issue with index bloat. Note that it's
normal to have some amount of empty space depending on vacuum and update
frequency, so 15G -> 20G isn't terribly surprising. I would suggest
using pg_autovacuum instead of the continuous vacuum; it's very possible
that some of your tables need more frequent vacuuming than they're
getting now. If you go this route, you might want to change the default
settings a bit to make pg_autovacuum more agressive.
actually i have a very bad experience with autovaccum - of course it is because i dont know how to setup it correctly, but for me it's just easier to setup continuos vacuums. and i know which tables are frequently updated, so i setup additional vacuums on them.
Also, I'd suggest posting to -hackers about the index bloat. Would you
be able to make a filesystem copy (ie: tar -cjf database.tar.bz2
$PGDATA) available? It might also be useful to keep an eye on index size
in pg_class.relpages and see exactly what indexes are bloating.
i'm watching it right now (which indices are bloating), but i cannot send copy of pgdata - it contains very sensitive information.
depesz