How do I answer "what did my data look like last month" in Postgres?

Approaches to Temporal Data

Sooner or later, someone asks: "What did this customer's plan look like when they signed up last February?" The current row won't tell you that; it's been edited three times since. This is the classic historical/temporal data problem: how do you manage changes to dimensional data over time so you can answer "row as of T" on demand.

This article is about the schema pattern that answers that question in one SELECT. There are several approaches to modeling and solving this problem, and I'll discuss some of those approaches below:

a) Event-driven / event sourcing

This has to do with when the domain naturally consists of discrete meaningful facts, e.g., sensor readings, prescriptions, stock movements, financial transactions. Here the change of records events is the natural state and the database is designed for recording these events. It is a bad fit when the domain thinks in records (customers, products, contracts) whose changes don't have natural event semantics.

b) Versioning in place (SCD Type 2)

This is what SCD Type 2 refers to, if you want to look it up - SCD explainer. The database table holds every version of every record with valid_from / valid_to denoting the change time; the domain still thinks in records. Getting the records state at a certain point in time requires filtering for versions in the valid from/to window. Great when versions have domain meaning. Bad fit when the app is already built; adding versioning in place means every existing query, view, and join needs a WHERE valid_to IS NULL filter. In greenfield the choice between this and a sibling history table (SCD Type 4) is close, and comes down to whether "one table with a filter convention" or "current and history split" fits the team's mental model.

c) Snapshotting a full copy on some cadence or event

Nightly snapshots into a separate table; or freeze a snapshot into a downstream entity when a business event happens (approval, invoice). This is efficient when the ask is anchored to those specific moments and storage is tight. When asking for arbitrary times ("what did this look like Tuesday at 15:37") and for records that change often, you get the values at snapshot time and miss a lot of the changes in between snapshots.

d) Slowly Changing Dimension (SCD) Type 4

In SCD Type 4, you solve by creating a history table in addition to your main table. The SQL below is Postgres; the pattern translates to any RDBMS that supports row-level triggers, just with different syntax for the trigger body and the point-in-time query.

Setting Up the History Table

So you originally have your main table:

CREATE TABLE records (
    record_id BIGSERIAL PRIMARY KEY,
    -- your app's existing columns
);

Alongside it, you create a history table that mirrors the same shape plus a few bookkeeping columns:

CREATE TABLE records_history (
    history_id BIGSERIAL PRIMARY KEY,
    valid_from TIMESTAMPTZ NOT NULL,
    valid_to TIMESTAMPTZ,
    operation TEXT NOT NULL,
    LIKE records INCLUDING DEFAULTS
);

CREATE INDEX ON records_history (record_id, valid_from DESC);

LIKE records INCLUDING DEFAULTS copies every column from records. The history table auto-inherits the schema. Only the currently-valid version of a record has valid_to = NULL; older versions have their valid_to filled in with the moment the next edit closed them out.

Keeping History in Sync with a Trigger

Every INSERT, UPDATE, and DELETE on records needs to be mirrored into records_history. A row-level trigger keeps them in sync so the app itself never has to write to two tables:

CREATE FUNCTION records_history_trg()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP IN ('UPDATE', 'DELETE') THEN
        UPDATE records_history
        SET valid_to = now()
        WHERE record_id = OLD.record_id
        AND valid_to IS NULL;
    END IF;

    IF TG_OP = 'DELETE' THEN
        INSERT INTO records_history
        SELECT nextval('records_history_history_id_seq'), now(), now(), 'DELETE', OLD.*;
        RETURN OLD;
    END IF;

    INSERT INTO records_history
    SELECT nextval('records_history_history_id_seq'), now(), NULL, TG_OP, NEW.*;
    RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER records_history_trg
AFTER INSERT OR UPDATE OR DELETE ON records
FOR EACH ROW
EXECUTE FUNCTION records_history_trg();

On every UPDATE or DELETE the previously-current version has its valid_to stamped to now(). On INSERT or UPDATE a fresh row is written with valid_from = now() and valid_to = NULL; the new current version. On DELETE we also write a tombstone row with valid_from = valid_to = now() so we can tell later that the record ceased to exist at exactly that moment.

Querying Historical State

With this in place, asking "what did the table look like on date T?" is one query:

SELECT DISTINCT ON (record_id) *
FROM records_history
WHERE valid_from <= :as_of
  AND (valid_to IS NULL OR valid_to > :as_of)
  AND operation != 'DELETE'
ORDER BY record_id, valid_from DESC;

For each record we pick the version whose validity window contains :as_of, and we skip records that were deleted before that moment. The DISTINCT ON (record_id) with ORDER BY record_id, valid_from DESC gives you one row per record, the most recent one that was valid at :as_of - which is exactly what a SELECT * FROM records would have returned if you'd run it at that moment.

Backfilling Existing Data

One thing to handle at install time: if rows existed before the trigger was added, they'll have no history entries yet. Backfill once:

INSERT INTO records_history (valid_from, valid_to, operation, ...)
SELECT now(), NULL, 'BOOTSTRAP', r.*
FROM records r;

Every existing record now has a BOOTSTRAP version starting at install. Point-in-time queries before that return empty (which is honest - you didn't have history yet). Anything on or after is queryable.

Handling Schema Drift

Two things to bake into ops before you ship. Schema drift. When you add a column to records, records_history doesn't gain it automatically. LIKE ... INCLUDING DEFAULTS copies the schema at CREATE time, not on future changes. The trigger's NEW.* copy will fail on the column-count mismatch until you ALTER TABLE records_history ADD COLUMN to match. Bake that ALTER into whatever automation runs your migrations so nobody has to remember.