Hello everybody,
we're using postgresql 8.3 for some logging framework.
There are several tables for each day (which are inherited from a common
base), which
- are filled during the day,
- after midnight the indizes are changed to FILLFACTOR=100, and
- the tables get CLUSTERed by the most important index.
- Some time much later the tables that aren't needed anymore are DROPped.
So far, so fine.
A few days before we found the machine much slower, because of the autovacuum
processes that were started automatically ["autovacuum: VACUUM ... (to prevent
wraparound)"].
After several days we killed that, and, as a quick workaround, changed
"autovacuum_freeze_max_age" to 1G and restarted the server, which worked as
before (and didn't ran the autovacuum processes).
As a next idea we changed the cluster/reindex script to set
"vacuum_freeze_min_age=0" before the CLUSTER call, hoping that this would
solve our transaction ID wraparound problem.
We don't know yet whether that's enough (is it?), but we're seeing another
problem - the btree indizes aren't cleaned up.
By this I mean that for two compareable tables (with about the same amount of
data, one done before the "vacuum_freeze_min_age=0" and one with that), have
about the same size for the GIST/GIN-, but about 30-50% difference for the
btree indizes (after the ALTER INDEX/CLUSTER).
So, as summary: "vacuum_freeze_min_age=0" seems to interfere with btree
indizes with FILLFACTOR=100 in some way, so that CLUSTER doesn't return space
to the filesystem.
Now I'd like to ask whether that's a known problem, and maybe even solved for
8.4 (which we'd like to use because of the "FOR UPDATE" across inherited
tables).
Regards,
Phil