Querying the Past: Temporal Tables in SQL Server and PostgreSQL

[Lomanu4]

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

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		No (manual only)
Manual triggers	Not needed	Required
Indexable history		(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

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.