Re: Freeze avoidance of very large table.

On Wed, Apr 22, 2015 at 8:55 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> On Wed, Apr 22, 2015 at 06:36:23PM -0500, Jim Nasby wrote:
>> On 4/22/15 6:12 PM, Bruce Momjian wrote:
>> >My point is that for the life of 200M transactions, you would have the
>> >overhead of an additional file per table in the file system, and updates
>> >of that. I just don't know if the overhead over the long time period
>> >would be smaller than the VACUUM FREEZE. It might be fine --- I don't
>> >know. People seem to focus on the big activities, while many small
>> >activities can lead to larger slowdowns.
>>
>> Ahh. This wouldn't be for the life of 200M transactions; it would be
>> a permanent fork, just like the VM is.
>
> Right. My point is that either you do X 2M times to maintain that fork
> and the overhead of the file existance, or you do one VACUUM FREEZE. I
> am saying that 2M is a large number and adding all those X's might
> exceed the cost of a VACUUM FREEZE.

I agree, but if we instead make this part of the visibility map
instead of a separate fork, the cost is much less. It won't be any
more expensive to clear 2 consecutive bits any time a page is touched
than it is to clear 1. The VM fork will be twice as large, but still
tiny. And the fact that you'll have only half as many pages mapping
to the same VM page may even improve performance in some cases by
reducing contention. Even when it reduces performance, I think the
impact will be so tiny as not to be worth caring about.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company