On Wed, Mar 09, 2011 at 10:52:25AM +0000, Michael Graham wrote: > I have a database with a number of tables that are partitioned monthly, > after that the tables are mostly read only (on rare occasions we may > delete from a table but normally we just drop the partitions). Recently > I've noticed that we have a lot of these tables are vacuumed around the > same time, after a little big of digging I've realised that postgres is > vacuuming them to stop xaction wrap around. So for example in a few > million xactions (later today) postgres is going to want to vacuum 37 > tables for just this reason. > > I know I can fiddle autovacuum_freeze_max_age and vacuum_freeze_min_age > to change how regularly the tables have this occur, and I can do this on > a per table basis in pg_autovacuum (yes this means I'm running an old > version, version 8.2) but what I'm wondering is how other people are > breaking this synchronisation? > > Should I add a random value to the freeze_max_age for all the old tables > when I start a new month? I haven't tried such a thing, but that seems like a great plan. You wouldn't need to change all old tables every month or actually make it random. Each time you create a monthly table, decrease the last-used-value by some fixed interval and use that for the new table. (You'll probably need to subtract, not add, because per-table freeze_max_age cannot usefully exceed the global autovacuum_freeze_max_age.) > Or do the same with the freeze_min_age? This should work about as well, perhaps slightly better. Reducing freeze_max_age can force freeze_min_age down, but not vice-versa. > Perhaps I should just force a vacuum on some of the tables the break it? That strategy does work. However, if you ever run a full-database manual VACUUM (in PostgreSQL < 8.4, anyway), they'd get resynchronized. Fiddling the per-table freeze_max_age or freeze_min_age does not have that liability. nm -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
