CYBERTEC PostgreSQL Logo

Implementing “AS OF”-queries in PostgreSQL

02.2019 / Category: / Tags: |

Over the years many people have asked for “timetravel” or “AS OF”-queries in PostgreSQL. Oracle has provided this kind of functionality for quite some time already. However, in the PostgreSQL world “AS OF timestamp” is not directly available. The question now is: How can we implement this vital functionality in user land and mimic Oracle functionality?

Implementing “AS OF” and timetravel in user land

Let us suppose we want to version a simple table consisting of just three columns: id, some_data1 and some_data2. To do this we first have to install the btree_gist module, which adds some valuable operators we will need to manage time travel. The table storing the data will need an additional column to handle the validity of a row. Fortunately, PostgreSQL supports “range types”, which allow to store ranges in an easy and efficient way. Here is how it works:

Attention!

Mind the last line here: “EXLUDE USING gist” will ensure that if the “id” is identical the period (“valid”) must not overlap. The idea is to ensure that the same “id” only has one entry at a time. PostgreSQL will automatically create a Gist index on that column. The feature is called “exclusion constraint”. If you are looking for more information about this feature consider checking out the official documentation (https://www.postgresql.org/docs/current/ddl-constraints.html).

How can I filter?

If you want to filter on some_data1 and some_data2 consider creating indexes. Remember, missing indexes are in many cases the root cause of bad performance:

By creating a view, it should be super easy to extract data from the underlying tables:

For the sake of simplicity I have created a view, which returns the most up to date state of the data. However, it should also be possible to select an old version of the data. To make it easy for application developers I decided to introduce a new GUC (= runtime variable), which allows users to set the desired point in time. Here is how it works:

Then you can create a second view, which returns the old data:

It is of course also possible to do that with just one view. However, the code is easier to read if two views are used (for the purpose of this blog post). Feel free to adjust the code to your needs.

If you are running an application you usually don't care what is going on behind the scenes - you simply want to modify a table and things should take care of themselves in an easy way. Therefore, it makes sense to add a trigger to your t_object_current table, which takes care of versioning. Here is an example:

The trigger will take care that INSERT, UPDATE, and DELETE is properly taken care of.

Finally: Timetravel made easy

It is obvious that versioning does have an impact on performance. You should also keep in mind that UPDATE and DELETE are more expensive than previously. However, the advantage is that things are really easy from an application point of view. Implementing time travel can be done quite generically and most applications might not have to be changed at all. What is true, however, is that foreign keys will need some special attention and might be easy to implement in general. It depends on your applications whether this kind of restriction is in general a problem or not.

 


In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.

6 responses to “Implementing “AS OF”-queries in PostgreSQL”

  1. "Oracle has provided this kind of functionality for quite some time already"
    Also DB2 LUW. DB2's implementation of bi temporal tables is probably the best.

  2. Hi,
    I tried to implement this solution but trigger doesn't works on run PostgreSQL said: tables cannot have triggers INSTEAD OF
    Other question what is correct solution to implement Validation-Time time travel?

    I need that my users to be able to edit field value from now to infinte or from dataA to dateB and split the record if already existing A o B in record range date
    Ex
    in DB there is a record with
    valid = ['2019-01-01', 'infinity')
    name = "pippo";

    user do an update with:
    name = "pluto"
    valid = ['2019-06-01', '2019-08-01')

    DB must have this 3 records
    valid = ['2019-08-01', 'infinity') name = "pippo";
    valid = ['2019-06-01', '2019-08-01') name = "pluto";
    valid = ['2019-08-01', '2019-06-01') name = "pippo";

    Thanks a lot if someone helps me

  3. Hans-Jürgen, does this time travel also support joins (ie given time correct results when joining tables on a "past date"?

Leave a Reply

Your email address will not be published. Required fields are marked *

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram