Re: AS OF queries

On 20/12/17 14:48, Konstantin Knizhnik wrote:
>
>
> On 20.12.2017 16:12, Laurenz Albe wrote:
>> Konstantin Knizhnik wrote:
>>> I wonder if Postgres community is interested in supporting time travel
>>> queries in PostgreSQL (something like AS OF queries in Oracle:
>>> https://docs.oracle.com/cd/B14117_01/appdev.101/b10795/adfns_fl.htm).
>>> As far as I know something similar is now developed for MariaDB.
>> I think that would be a good thing to have that could make
>> the DBA's work easier - all the requests to restore a table
>> to the state from an hour ago.
>
> Please notice that it is necessary to configure postgres in proper way 
> in order to be able to perform time travels.

     This makes sense. BTW, I believe this feature would be an amazing 
addition to PostgreSQL.


> If you do not disable autovacuum, then old versions will be just 
> cleaned-up.
> If transaction commit timestamps are not tracked, then it is not 
> possible to locate required timeline.
>
> So DBA should make a decision in advance whether this feature is 
> needed or not.
> It is not a proper instrument for restoring/auditing existed database 
> which was not configured to keep all versions.
>
> May be it is better to add special configuration parameter for this 
> feature which should implicitly toggle
> autovacuumand track_commit_timestamp parameters).

     Downthread a "moving xid horizon" is proposed. I believe this is 
not too user friendly. I'd rather use a timestamp horizon (e.g. "up to 2 
days ago"). Given that the commit timestamp is tracked, I don't think 
this is an issue. This is the same as the undo_retention in Oracle, 
which is expressed in seconds.


>
> The obvious drawbacks of keeping all versions are
> 1. Increased size of database.
> 2. Decreased query execution speed because them need to traverse a lot 
> of not visible versions.

     In other words, what is nowadays called "bloat". I have seen in the 
field a lot of it. Not everybody tunes vacuum to keep up to date. So I 
don't expect this feature to be too expensive for many. While at the 
same time an awesome addition, not to fire a new separate server and 
exercise PITR, and then find the ways to move the old data around.


     Regards,

     Álvaro


-- 

Alvaro Hernandez


-----------
OnGres