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
autovacuum and 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