while you are at it, can you put in some audit timestamps as to when the vacuum occurred (full vs not full).
---------- Original Message ----------- From: Alvaro Herrera <[EMAIL PROTECTED]> To: Hackers <pgsql-hackers@postgresql.org> Sent: Wed, 14 Sep 2005 22:14:23 -0400 Subject: [HACKERS] Per-table freeze limit proposal > Hackers, > > As you've probably heard too many times already, I'm thinking in > improving vacuum, so we can keep track of the freeze Xid on a table > level, rather than database level. Hopefully this will eliminate the > need for database-wide vacuums. > > In fact this seems pretty easy to do. Add a field to pg_class, tell > VACUUM to update it using the determined freezeLimit, and that's it. > (Note that if we ever implement partial vacuum, it won't be able to > update the freeze point. But that was true before anyway.) > > We also need to teach autovacuum to update pg_database.datfreezexid, > using the minimum from pg_class. (I don't think it's a good idea to > seqscan pg_class to find out the minimum on each VACUUM call.) So, an > autovacuum iteration would issue all needed VACUUM/ANALYZE calls, then > get the minimum freezexid from pg_class to update pg_database. This > way, GetNewTransactionId can continue checking pg_database.datfreezexid > as the hard limit for issuing warnings for Xid wraparound. > > Does anyone see a need for anything other than the autovacuum process to > be updating pg_database.datfreezexid? Of course, if autovacuum is not > in use, things would continue as now, that is, manual database-wide > VACUUM calls updating pg_database.datfreezexid. But note that you can > mark all tables as disabled on pg_autovacuum, issue your manuals VACUUM > calls as needed (from cron or whatever), and use autovacuum to set > pg_database.datfreezexid -- so autovacuum would in fact do nothing > except set the freeze limit. > > The problem is, this seems so awfully simple that I fear I am missing > something ... Otherwise, does this sound like a plan? > > -- > Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com > The easiest way to resolve [trivial code guidelines disputes] is to fire > one or both of the people involved. (Damian Conway) > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq ------- End of Original Message ------- ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
