Querying the Past: Temporal Tables in SQL Server and PostgreSQL

Querying the Past: Temporal Tables in SQL Server and PostgreSQL "Time travel isn’t just science fiction — in SQL, it’s a feature." In modern data systems, auditing and tracking changes over time is essential for: Compliance (e.g., GDPR, HIPAA) Historical reporting Slowly changing dimensions (SCD Type 2) Rollback and debugging Temporal tables enable you to query your data as it was in the past, without building custom history-tracking logic. This post walks through how to use temporal features in: SQL Server (system-versioned tables) PostgreSQL (triggers + audit tables) Step 1: Define Your Temporal Schema Let’s create an Employees table with full change history tracking. SQL Server (Native) CREATE TABLE Employees ( ID INT PRIMARY KEY, Name NVARCHAR(100), Title NVARCHAR(100), ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START, ValidTo DATETIME2 GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) ) WITH (SYSTEM_VERSIONING = ON); ✅ SQL Server automatically maintains a history table. PostgreSQL (Manual) CREATE TABLE employees ( id SERIAL PRIMARY KEY, name TEXT, title TEXT ); CREATE TABLE employees_history ( id INT, name TEXT, title TEXT, valid_from TIMESTAMP, valid_to TIMESTAMP ); Step 2: Log Changes in PostgreSQL Use a trigger to manually track changes: CREATE OR REPLACE FUNCTION log_employee_update() RETURNS TRIGGER AS $$ BEGIN INSERT INTO employees_history SELECT OLD., now(), now(); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trg_emp_update BEFORE UPDATE ON employees FOR EACH ROW EXECUTE FUNCTION log_employee_update(); ✅ Triggers create audit trails that mimic temporal behavior. Step 3: Query Past States SQL Server -- Snapshot at a specific time SELECT FROM Employees FOR SYSTEM_TIME AS OF '2024-01-01T00:00:00'; -- Range of time SELECT * FROM Employees FOR SYSTEM_TIME BETWEEN '2024-01-01' AND '2024-03-01'; PostgreSQL SELECT * FROM employees_history WHERE valid_from '2023-12-01'; Use Case: Auditing Role Changes Let’s say we want to audit role changes for an employee named Eve: SELECT * FROM employees_history WHERE name = 'Eve' ORDER BY valid_from; ✅ See exactly how her title changed over time. Trade-offs Feature SQL Server PostgreSQL Native support ✅ Yes ❌ No (manual only) Manual triggers ❌ Not needed ✅ Required Indexable history ✅ Yes ✅ Yes (manually) Easy querying ✅ FOR SYSTEM_TIME ⚠️ Requires logic Best Practices Use consistent ValidFrom / ValidTo columns Automate trigger logic in functions for maintainability Periodically archive old history Protect history from user tampering Final Thoughts: Build a Timeline in SQL Temporal tables give your database memory — letting you: See what changed, when, and by whom Roll back to known-good snapshots Track history without bloating production tables "Your data deserves a memory. With temporal tables, it has one." #SQL #TemporalTables #Auditing #History #DataGovernance #SQLServer #PostgreSQL

May 12, 2025 - 21:31

Querying the Past: Temporal Tables in SQL Server and PostgreSQL

"Time travel isn’t just science fiction — in SQL, it’s a feature."

In modern data systems, auditing and tracking changes over time is essential for:

Compliance (e.g., GDPR, HIPAA)
Historical reporting
Slowly changing dimensions (SCD Type 2)
Rollback and debugging

Temporal tables enable you to query your data as it was in the past, without building custom history-tracking logic.

This post walks through how to use temporal features in:

SQL Server (system-versioned tables)
PostgreSQL (triggers + audit tables)

Step 1: Define Your Temporal Schema

Let’s create an Employees table with full change history tracking.

SQL Server (Native)

CREATE TABLE Employees (
  ID INT PRIMARY KEY,
  Name NVARCHAR(100),
  Title NVARCHAR(100),
  ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
  ValidTo DATETIME2 GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON);

✅ SQL Server automatically maintains a history table.

PostgreSQL (Manual)

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT,
  title TEXT
);

CREATE TABLE employees_history (
  id INT,
  name TEXT,
  title TEXT,
  valid_from TIMESTAMP,
  valid_to TIMESTAMP
);

Step 2: Log Changes in PostgreSQL

Use a trigger to manually track changes:

CREATE OR REPLACE FUNCTION log_employee_update()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO employees_history
  SELECT OLD.*, now(), now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_emp_update
BEFORE UPDATE ON employees
FOR EACH ROW EXECUTE FUNCTION log_employee_update();

✅ Triggers create audit trails that mimic temporal behavior.

Step 3: Query Past States

SQL Server

-- Snapshot at a specific time
SELECT *
FROM Employees
FOR SYSTEM_TIME AS OF '2024-01-01T00:00:00';

-- Range of time
SELECT *
FROM Employees
FOR SYSTEM_TIME BETWEEN '2024-01-01' AND '2024-03-01';

PostgreSQL

SELECT * FROM employees_history
WHERE valid_from <= '2024-01-01' AND valid_to > '2023-12-01';

Use Case: Auditing Role Changes

Let’s say we want to audit role changes for an employee named Eve:

SELECT *
FROM employees_history
WHERE name = 'Eve'
ORDER BY valid_from;

✅ See exactly how her title changed over time.

Trade-offs

Feature	SQL Server	PostgreSQL
Native support	✅ Yes	❌ No (manual only)
Manual triggers	❌ Not needed	✅ Required
Indexable history	✅ Yes	✅ Yes (manually)
Easy querying	✅ FOR SYSTEM_TIME	⚠️ Requires logic

Best Practices

Use consistent ValidFrom / ValidTo columns
Automate trigger logic in functions for maintainability
Periodically archive old history
Protect history from user tampering

Final Thoughts: Build a Timeline in SQL

Temporal tables give your database memory — letting you:

See what changed, when, and by whom
Roll back to known-good snapshots
Track history without bloating production tables

"Your data deserves a memory. With temporal tables, it has one."

#SQL #TemporalTables #Auditing #History #DataGovernance #SQLServer #PostgreSQL