Sometimes you need to find out what a record looked like at some point in the past. This is known as the Slowly Changing Dimension problem. Most database models - by design - don't keep the history of a record when it's updated. But there are plenty of reasons why you might need to do this, such as:
- audit/security purposes
- implementing an undo functionality
- showing a model's change over time for stats or comparison
There are a few ways to do this in PostgreSQL, but this article is going to focus on the implementation provided by the SQL:2011 standard, which added support for temporal databases. It's also going to focus on actually querying that historical data, with some real-life use cases.
PostgreSQL doesn't (yet?) support these features natively, but this temporal tables extension does the trick. This requires PostgreSQL 9.2 or higher, as that was the first version with support for a timestamp range data type.
Before you dig in, it's important to note that this extension does not provide complete support for the 2011 standard. Specifically it lacks any support for the new syntaxes provided for querying across historical tables, such as the
AS OF SYSTEM TIME keyword.
These are generally conveniences though - the
temporal tables extension takes care of the updating, and you'll at least be adopting a standard used by other databases, as opposed to rolling your own solution or using something application-specific.