[mulander]

Funny historical and architecture fact about PostgreSQL. It actually can do this, for all tables without special features. Unfortunately the facility to perform a query like this is no longer exposed but it shouldn't be impossible to re-add in a more modern way.

Essentially PostgreSQL has copy-on-write semantics, so historical records exist unless a vacuum marks them as no longer needed and subsequent insert/updates overwrite the values.

In the past when PostgreSQL had the postquel language (before SQL was added) there was special syntax to access data at specific points in time:

This is nicely outlined in "THE IMPLEMENTATION OF POSTGRES" by Michael Stonebraker, Lawrence A. Rowe and Michael Hirohama[1]. Go ahead open the PDF and search for "time travel" or read the quotes below.

> The second benefit of a no-overwrite storage manager is the possibility of time travel. As noted earlier, a user can ask a historical query and POSTGRES will automatically return information from the record valid at the correct time.

Quoting the paper again:

> For example to find the salary of Sam at time T one would query:

    retrieve (EMP.salary)
    using EMP [T]
    where EMP.name = "Sam"

> POSTGRES will automatically find the version of Sam’s record valid at the correct time and get the appropriate salary.

[1] - https://dsf.berkeley.edu/papers/ERL-M90-34.pdf

[jarym]

Really nice background, thanks for sharing! I knew Postgres did CoW internally and always wondered why the SQL standard for time-travel queries was not implemented.

I am using triggers and audit tables which works but my data requirements are relatively small so I won't face any challenges that way. However, re-using the old rows like this would lead to a far more efficient approach if it were supported natively.