Time travel with PostgreSQL on Amazon RDS

At NearForm we prefer PostgreSQL for building relational databases. In a recent project, we built a feature to allow users to freely operate on data, whilst being able to see previous versions and, in case of necessity, rollback to a certain point in time.

There are multiple applications for this, e.g. think of a text editor that creates a snapshot every time you hit save. If you make a mistake or are unsatisfied with recent changes, you can navigate through all the previous versions and restore to a previous point in time.

Or just think of Git.

Wouldn't it be great to solve this problem directly at the database level? Maybe being able to query data like it was yesterday or last year?

Well, turns out that SQL:2011 , a relatively recent iteration of the sql standard, includes improved support for temporal databases, allowing the user to store historical data and easily query through it.

Unfortunately, no databases fully support the new standard yet.

Since we know and love PostgreSQL and it's our go-to choice for relational databases, we searched for an alternative supported by it and found the temporal_tables extension. It works through a trigger that on insert, update or delete stores a copy of the data in a separate table.

It's a very nice extension and works well but, unfortunately, it's not available on Amazon RDS or other hosted solutions.

So we decided to create a drop-in replacement written in PL/pgSQL to be fully portable to all PostgresSQL installations, and today we are proud to open source and share it with you: check it out on our github .

For a tutorial on using temporal_tables check the project readme or this article by Clark Dave If you need help building relational databases for your business, get in touch with us