Re: Freeze avoidance of very large table.

On Mon, Apr 20, 2015 at 01:59:17PM -0500, Jim Nasby wrote:
> On 4/20/15 1:48 PM, Bruce Momjian wrote:
> >On Mon, Apr 20, 2015 at 04:45:34PM +0900, Sawada Masahiko wrote:
> >>Attached WIP patch adds Frozen Map which enables us to avoid whole
> >>table vacuuming even when full scan is required: preventing XID
> >>wraparound failures.
> >>
> >>Frozen Map is a bitmap with one bit per heap page, and quite similar
> >>to Visibility Map. A set bit means that all tuples on heap page are
> >>completely frozen, therefore we don't need to do vacuum freeze that
> >>page.
> >>A bit is set when vacuum(or autovacuum) figures out that all tuples on
> >>corresponding heap page are completely frozen, and a bit is cleared
> >>when INSERT and UPDATE(only new heap page) are executed.
> >
> >So, this patch avoids reading the all-frozen pages if it has not been
> >modified since the last VACUUM FREEZE? Since it is already frozen, the
> >running VACUUM FREEZE will not modify the page or generate WAL, so is it
> >really worth maintaining a new per-page bitmap just to avoid the
> >sequential scan of tables every 200MB transactions? I would like to see
> >us reduce the need for VACUUM FREEZE, rather than go in this direction.
>
> How would you propose we do that?
>
> I also think there's better ways we could handle *all* our cleanup
> work. Tuples have a definite lifespan, and there's potentially a lot
> of efficiency to be gained if we could track tuples through their
> stages of life... but I don't see any easy ways to do that.

See the TODO list:

https://wiki.postgresql.org/wiki/Todo
o Avoid the requirement of freezing pages that are infrequently
modified

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +