Re: New feature request: FlashBack Query

Tom Lane wrote:
> August Zajonc <augustz(at)augustz(dot)com> writes:
>> The key is how lightweight the setup could be, which matters because
>> clients are not always willing to pay for a PITR setup. The low overhead
>> would mean you'd feel fine about setting guc to 1hr or so.
>
> This would have exactly the same performance consequences as always
> having an hour-old open transaction. I'm afraid that describing it
> as "low overhead" is mere wishful thinking: it would cripple vacuuming
> of high-update tables and greatly increase the typical load on pg_clog
> and pg_subtrans. We already know that pg_subtrans contention can be a
> source of context-swap storms, with the size of the window back to
> GlobalXmin being the controlling factor for how bad it gets.
>
> It's possible that this last could be addressed by separating the
> concept of "old enough to be vacuumed" from GlobalXmin, but it's
> certainly not a trivial thing.

Isn't globalxmin for open transactions? I thought the idea was that
everything goes as usual, but you can flip a knob and say that vacuum
doesn't vacuum anything more recent then GlobalXmin less x transactions.

Then you can look at your transactions per second and get a rough window
to work within. Or if there are timestamps on commits, that would switch
to a time interval more user friendly.

You end up simply delaying when 1hrs worth of transactions gets
vacuumed. For folks doing nightly cron job vacuums, not too bad.
Autovacuum isn't on by default :)

Of course, this will be clumsy if not per database.

But the thought might be to take advantage of the flashback data already
present under the MVCC model as long as vacuum hasn't hit things (and
being willing to stop activity on a database etc). Given that you are
delaying a vacuum rather then being more aggressive, and know you can
already vacuum up to a more recent transaction xmin, I dunno... Does
anything depend (other then performance) on vacuum actually vacuuming as
far as it can?

- August