Re: Freeze avoidance of very large table. - Mailing list pgsql-hackers

On 4/20/15 4:13 PM, Bruce Momjian wrote:
> On Mon, Apr 20, 2015 at 03:58:19PM -0500, Jim Nasby wrote:
>>>> 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.
>
> Didn't you think any of the TODO threads had workable solutions?  And

I didn't realize there were threads there.

The first three are discussion around the idea of eliminating the need 
to freeze based on a page already being all visible. No patches.

http://www.postgresql.org/message-id/CA+TgmoaEmnoLZmVbb8gvY69NA8zw9BWpiZ9+TLz-LnaBOZi7JA@mail.gmail.com 
has a WIP patch that goes the route of using a tuple flag to indicate 
frozen, but also raises a lot of concerns about visibility, because it 
means we'd stop using FrozenXID. That impacts a large amount of code. 
There were some followup patches as well as a bunch of discussion of how 
to make it visible that a tuple was frozen or not. That thread died in 
January 2014.

The fifth thread is XID to LSN mapping. AFAICT this has a significant 
drawback in that it breaks page compatibility, meaning no pg_upgrade. It 
ends 5/14/2014 with this comment:

"Well, Heikki was saying on another thread that he had kind of gotten
cold feet about this, so I gather he's not planning to pursue it.  Not
sure if I understood that correctly.  If so, I guess it depends on
whether someone else can pick it up, but we might first want to
establish why he got cold feet and how worrying those problems seem to
other people." - 
http://www.postgresql.org/message-id/CA+TgmoYoN8LzSuaffUaEkyV8Mhv1wi=ZLBXQ3VOfEZNO1dbw9Q@mail.gmail.com

So work was done on two alternative approaches, and then abandoned. Both 
of those approaches might still be valid, but seem to need more work. 
They're also higher risk because they're changing MVCC at a very 
fundamental level.

As I mentioned, I think there's a lot better stuff we could be doing 
about tuple lifetime, but there's no easy fixes to be had. This patch 
solves a problem today, using a concept that's now well proven 
(visibility map). If we had something more sophisticated being developed 
then I'd be inclined not to pursue this patch, but that's not the case.

Perhaps others can elaborate on where those two patches are at...

> don't expect adding an additional file per relation will be zero cost
> --- added over the lifetime of 200M transactions, I question if this
> approach would be a win.

Can you elaborate on this? I don't see how the number of transactions 
would come into play, but the overhead here is not large; the FrozenMap 
would be the same size as the VM map, which is 1/64,000th as large as 
the heap. So a 64G table means a 1M FM. That doesn't seem very expensive.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com