mark reid wrote:
> I've been using pg_autovacuum for a while, and for the most part it's
> been great. There's one case in my system where it won't run on a
> particular type of table, even though the table apparently needs it.
>
> I have a table called "properties" that has key->value pairs. Usually
> there are only a handful of rows, some of which are updated relatively
> frequently compared to the number of rows (hundreds or thousands of
> times per day). The problem is that some of the rows have long
> strings for their value (on the order of a few hundred kilobytes), so
> if I understand correctly, the bulk of the data gets offloaded to a
> toast table.
> What I believe is happening is that the main table doesn't meet the
> minimum activity level for pg_autovacuum based on size / update
> frequency, but the toast table would, though it isn't specifically
> checked by pg_autovacuum. The result is that the toast table grows
> really big before triggering autovacuum (or until I manually vacuum
> the "properties" table). Not the end of the world, obviously, but
> might be a "gotcha" for some people with similar situations.
I don't think the problem has to do with toast, or pg_autovacuum missing
the fact that the toast table has been updated. Rather I think the
problem is that autovacuum believes that all updates are created equal.
That is 1 update is 1 update even though a single update may effect 1
page or thousands of pages. This is where FSM data needs to be
integrated into pg_autovacuum. This isn't going to happen soon (not for
8.1) but it is definatley planned.
With the inclusion of autovacuum into the backend for 8.1, you will at
least be able to set per table thresholds and set a more aggressive
threshold for this table.
Matt
