Re: Freeze avoidance of very large table.

On 4/20/15 2:09 PM, Bruce Momjian wrote:
> 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

Right, but do you have a proposal for how that would actually happen?

Perhaps I'm mis-understanding you, but it sounded like you were opposed 
to this patch because it doesn't do anything to avoid the need to 
freeze. My point is that no one has any good ideas on how to avoid 
freezing, and I think it's a safe bet that any ideas people do come up 
with there will be a lot more invasive than a FrozenMap is.

While not perfect, a FrozenMap is something we can do today, without a 
lot of effort, and it will provide definite value for any tables that 
have a "good" amount of frozen pages. Without performance testing, we 
don't know what "good" actually looks like, but we can't test without a 
patch (which we now have). Assuming performance numbers look good I 
think it would be folly to reject this patch in the hopes that 
eventually we'll have some way to avoid the need to freeze.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com