View historical data with this PostgreSQL extension

PostgreSQL temporal_tables for 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.

PostgreSQL to the rescue

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

Don’t miss a beat

Get all the latest NearForm news,
from technology to design.
Sign Up
View all posts  |  Technology  |  Business  |  Culture  |  Opinion  |  Design
Follow us for more information on this and other topics.
Published by Paolo Chiodi