I have a heavily partitioned DW type database that has over 5,000 tables in it.
Data is only ever inserted, read and then truncated after some period of time.
Once the table is truncated, the constraints are changed and the table is
reused. This works well until Postgres hits the autovacuum_freeze_max_age,
which I currently have set @ 1billion). Since these tables are only ever
inserted to and truncated, they are not normally vacuumed(which is what I want,
since data is typically going to be truncated before needing to be vacuumed).
Unfortunately, since truncate does not change the relfrozenxid, once the
autovacuum_freeze_max_age is reached, suddenly all tables in the schema need
vacuuming at once. When this occurs, the autovacuum process gives priority to
vacuuming all the tables(2TB+ of data), and query performance degenerates. More
significantly, as new data comes into emptied partition tables they are not
analyzed in a timely fashion and very poor query plans result. The DB remains
in this vacuuming state for up to 3 weeks.
What I would like to do, is just vacuum the table when the truncate code is
executed(This is currently done using PGSql functions), but I can't issue the
vacuum call from within a transaction. Given that, I was wondering if it is
"safe" to update pg_class directly for the table being truncated with a query
like:
UPDATE pg_class SET relfrozenxid = ( select relfrozenxid from pg_class where
age(relfrozenxid) in (select min(age(relfrozenxid)) from pg_class where relkind
= 'r') limit 1) WHERE relname = '<table being truncated>';
Is there a better way of doing this?
For that matter, would it be reasonable to have the relfrozenxid reset on a
successful truncate?
I am running Postgres 8.2.4
Regards...Mark Sherwood
_________________________________________________________________
