Re: Freeze avoidance of very large table.

On 04/06/2015 10:07 AM, Jim Nasby wrote:
> Actually, I would start simply with ReadOnly and ReadWrite.
>
> As I understand it, the goal here is to prevent huge amounts of periodic
> freeze work due to XID wraparound. I don't think we need the Freeze
> state to accomplish that.
>
> With a single bit per page in the Frozen Map, checking a 800GB table
> would require reading a mere 100MB of FM. That's pretty tiny, and
> largely accomplishes the goal.
>
> Obviously it would be nice to eliminate even that 100MB read, but I
> suggest you leave that for a 3rd patch. I think you'll find that just
> getting the first 2 accomplished will be a significant amount of work.
>
> Also, note that you don't really even need the ReadOnly patch. As long
> as you're not actually touching the table at all the FM will eventually
> read as everything is frozen; that gets you 80% of the way there. So I'd
> suggest starting with the FM, then doing ReadOnly, and only then
> attempting to add the Frozen state.

+1

There was some reason why we didn't have Freeze Map before, though;
IIRC these were the problems:

1. would need to make sure it gets sync'd to disk and/or WAL-logged

2. every time a page is modified, the map would need to get updated

3. Yet Another Relation File (not inconsequential for the cases we're
discussing).

Also, given that the Visibility Map necessarily needs to have the
superset of the Frozen Map, maybe combining them in some way would make
sense.

I agree with Jim that if we have a trustworthy Frozen Map, having a
ReadOnly flag is of marginal value, unless such a ReadOnly flag allowed
us to skip updating the individual row XIDs entirely. I can think of
some ways to do that, but they have severe tradeoffs.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com