Re: AS OF queries

On 26.01.2018 03:55, Bruce Momjian wrote:
> On Sat, Dec 23, 2017 at 11:53:19PM +0300, konstantin knizhnik wrote:
>> On Dec 23, 2017, at 2:08 AM, Greg Stark wrote:
>>
>>> On 20 December 2017 at 12:45, Konstantin Knizhnik
>>> <k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
>>>
>>>> It seems to me that it will be not so difficult to implement them in
>>>> Postgres - we already have versions of tuples.
>>>> Looks like we only need to do three things:
>>>> 1. Disable autovacuum (autovacuum = off)
>>> "The Wheel of Time turns, and Ages come and pass, leaving memories
>>> that become legend. Legend fades to myth, and even myth is long
>>> forgotten when the Age that gave it birth comes again"
>>>
>>> I think you'll find it a lot harder to get this to work than just
>>> disabling autovacuum. Notably HOT updates can get cleaned up (and even
>>> non-HOT updates can now leave tombstone dead line pointers iirc) even
>>> if vacuum hasn't run.
>>>
>> Yeh, I suspected that just disabling autovacuum was not enough.
>> I heard (but do no know too much) about microvacuum and hot updates.
>> This is why I was a little bit surprised when me test didn't show lost of updated versions.
>> May be it is because of vacuum_defer_cleanup_age.
> Well vacuum and single-page pruning do 3 things:
>
> 1. remove expired updated rows
> 2. remove deleted row
> 3. remove rows from aborted transactions
>
> While time travel doesn't want #1 and #2, it probably wants #3.
>
Rows of aborted transactions are in any case excluded by visibility checks.
Definitely skipping them costs some time, so large percent of aborted
transactions  may affect query speed.
But query speed is reduced in any case if in order to support time
travel we prohibit or postpone vacuum.

What is the expected relation of committed and aborted transactions? I
expected that it should be much bigger than one (especially if we take
in account
only read-write transaction which has really updated database). In this
case number of versions created by aborted transaction should be much
smaller than number of versions created by updated/delete of successful
transactions. So them should not have significant impact on performance.

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company