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
Messenger wants to send you on a trip.
Enter today.
