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(at)alvh(dot)no-ip(dot)org>
To: Hackers <pgsql-hackers(at)postgresql(dot)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 -------