Querying Historical Data in Postgres with the SCD Type 4 Pattern
A practical Postgres pattern using a history table, triggers, and valid_from/valid_to columns to answer point-in-time queries about record state in a single SELECT.
Engineers frequently need to answer questions like "what did this record look like on a given date," but a mutable current row can't provide that on its own. This piece briefly compares the main strategies—event sourcing, in-place versioning (SCD Type 2), periodic snapshots, and SCD Type 4—before focusing on the SCD Type 4 pattern as the most practical retrofit for existing, already-running systems.
The approach adds a history table mirroring the main table's schema, extended with valid_from/valid_to columns. A row-level trigger automatically mirrors every INSERT, UPDATE, and DELETE from the main table into the history table, so application code never has to write to two places. Deletes also produce a tombstone row marking exactly when a record ceased to exist.
Once in place, retrieving the table's state as of any given timestamp becomes a single SELECT using DISTINCT ON. The article also covers two operational essentials: a one-time backfill for pre-existing rows so point-in-time queries behave honestly, and the need to bake ALTER TABLE steps into migration automation, since the history table doesn't automatically pick up new columns added to the main table.