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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
