Re: Freeze avoidance of very large table.

On 4/6/15 1:28 PM, Josh Berkus wrote:
> 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

Same as VM.

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

Not everytime, just the first time if FM for a page was set. It would 
only be set by vacuum, just like VM.

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

Sure, which is why I think it might be interesting to either allow for 
more than one page per bit, or perhaps some form of compression. That 
said, I don't think it's worth worrying about too much because it's 
still a 64,000-1 ratio with 8k pages. If you use 32k pages it becomes 
256,000-1, or 4GB of FM for 1PB of heap.

> 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.

The thing is, I think in many workloads the paterns here will actually 
be radically different, in that it's way easier to get a page to be 
all-visible than it is to freeze it.

Perhaps there's something we can do here when we look at other ways to 
reduce space usage for FM (and maybe VM too), but I don't think now is 
the time to put effort into this.

> 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.

Aside from Alvaro's points, I think many users would find it useful as 
an easy way to ensure no one is writing to a table, which could be 
valuable for any number of reasons. As long as the patch isn't too 
complicated I don't see a reason not to do it.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com