Re: Btree indizes, FILLFACTOR, vacuum_freeze_min_age and CLUSTER

On Mon, May 11, 2009 at 12:20 AM, Philipp Marek
<philipp(dot)marek(at)emerion(dot)com> wrote:
> 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)"].

Try increasing autovacuum_vacuum_cost_delay to 20 or 30 milliseconds
and see if that helps during autovacuum.

> 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).

It will still have to eventually run, just less often.

> 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.

No, only vacuuming will solve it. It has to happen eventually. If
you put it off too far, and the database can't get the vacuum to reset
the txids to the magical frozentxid, then the db will shut down and
demand that you vacuum it in single user mode. Which will definitely
make it run slower than if autovacuum is doing the job.

> 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).

Not sure about all this part. Could it just be index bloat due to
updates and / or delete insert cycles?

> 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.

I'm not sure that's the issue here. Cluster doesn't return index
space. reindex returns index space. vacuum makes dead index space
available for reuse.