Re: Per-table freeze limit proposal

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 <alvherre@alvh.no-ip.org>
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 -------