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