Best strategy I've come across so far since Postgres doesn't support system versioned tables like MariaDB and MS SQL Server?
• create your table with a timestamp range (tstzrange) type with a default of [clock_timestamp(), 'Infinity') • make sure your table has a primary key • create a history table that looks exactly like your main table • use the Postgres table inheritance to have the main table inherit from the history table (yes, I know that seems backwards) • add update, delete, and truncate triggers to your main table, copying OLD.* values (soon to be replaced) to the history table with the timestamp range end to be the range start of the NEW value • add a second delete trigger that adds the OLD.* values to the history only the range start and end are both the current timestamp (makes looking up deletions easier and distinct from updates)
Once you get it working like you want:
• create the history table and triggers automatically with event triggers on "CREATE TABLE" so that new tables are automatically system versioned
So inserts are free and updates/deletes are only about 2% slower when tested on AWS Aurora, but the app layer doesn't require any extra complexity and the database can keep relational integrity at all times. History taking up too much space? Just DELETE from the history using a timestamp cutoff.
SELECTing from the history table due to inheritance rules always shows the current entry as well as the complete history of a record while SELECTing from the main table just shows the current records as expected with no query overhead from the extra WHERE clause. App maintenance moving forward is MUCH easier at the expense of DDL changes taking a little more forethought. Adding and removing columns requires making policy decisions about your history: truncating history, archiving and bisecting, or adding NULLs to columns that didn't previously exist. This is a policy decision rather than a technical one though. Use what best fits your use case.
