temporal_tables

temporal_tables : temporal tables

Overview

Packages

Source

pig build pkg temporal_tables;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

pig repo add pgsql -u   # add both repo and update cache

Install this extension with pig:

pig install temporal_tables;		# install via package name, for the active PG version

pig install temporal_tables -v 18;   # install for PG 18
pig install temporal_tables -v 17;   # install for PG 17
pig install temporal_tables -v 16;   # install for PG 16
pig install temporal_tables -v 15;   # install for PG 15
pig install temporal_tables -v 14;   # install for PG 14

Create this extension with:

CREATE EXTENSION temporal_tables;

Usage

temporal_tables: System-period temporal tables for PostgreSQL

A temporal table is a table that records the period of time when a row is valid. The system period is a column (or a pair of columns) with a system-maintained value that contains the period of time when a row is valid from a database perspective. When you insert a row into such table, the system automatically generates the values for the start and end of the period. When you update or delete a row from a system-period temporal table, the old row is archived into another table, which is called the history table.

There is a fantastic tutorial on using and querying temporal tables in PostgreSQL with this extension.

Creating a System-Period Temporal Table

The extension uses a general trigger function to maintain system-period temporal table behaviour:

versioning(<system_period_column_name>, <history_table_name>, <adjust>)

First, create a table and add a system period column:

CREATE TABLE employees (
  name text NOT NULL PRIMARY KEY,
  department text,
  salary numeric(20, 2)
);

ALTER TABLE employees ADD COLUMN sys_period tstzrange NOT NULL;

Then create a history table:

CREATE TABLE employees_history (LIKE employees);

A history table must contain a system period column with the same name and data type as in the original one. If both tables contain a column, the data type must be the same.

Finally, create a trigger to link it with the history table:

CREATE TRIGGER versioning_trigger
BEFORE INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW EXECUTE PROCEDURE versioning('sys_period',
                                          'employees_history',
                                          true);

Inserting Data

Inserting data into a system-period temporal table is similar to inserting data into a regular table:

INSERT INTO employees (name, department, salary)
VALUES ('Bernard Marx', 'Hatchery and Conditioning Centre', 10000);

INSERT INTO employees (name, department, salary)
VALUES ('Lenina Crowne', 'Hatchery and Conditioning Centre', 7000);

The start of sys_period column represents the time when the row became current, generated by CURRENT_TIMESTAMP.

Updating Data

When a user updates rows, the trigger inserts a copy of the old row into the history table. If a single transaction makes multiple updates to the same row, only one history row is generated:

UPDATE employees SET salary = 11200 WHERE name = 'Bernard Marx';

The history table now contains the previous version:

Update Conflicts and Time Adjustment

Update conflicts can occur when multiple transactions update the same row. When the adjust parameter is set to true, the start of sys_period is adjusted by adding a small delta (typically 1 microsecond) to avoid failures with SQLSTATE 22000.

Deleting Data

When a user deletes data, the trigger adds rows to the history table:

DELETE FROM employees WHERE name = 'Helmholtz Watson';

Advanced Usage

You can set a custom system time for versioning triggers, useful for creating a data warehouse from a system that recorded timestamps:

SELECT set_system_time('1985-08-08 06:42:00+08');

To revert to the default behaviour:

SELECT set_system_time(NULL);

If issued within a transaction that is later aborted, all changes are undone. If committed, changes persist until the end of the session.

Examples and Hints

Using Inheritance for History Tables

CREATE TABLE employees_history (
  name text NOT NULL,
  department text,
  salary numeric(20, 2),
  sys_period tstzrange NOT NULL
);

CREATE TABLE employees (PRIMARY KEY(name)) INHERITS (employees_history);

Pruning History Tables

History tables are always growing. Several pruning strategies:

1. Periodically delete old data from a history table.
2. Use partitioning and detach old partitions from a history table.
3. Retain only the latest N versions of a row.
4. Prune rows when a corresponding row is deleted from the temporal table.
5. Prune rows that satisfy specified business rules.

You can also set another tablespace for a history table to move it on cheaper storage.

Data Audit

You can add triggers to save the user that modified or deleted the current row:

CREATE FUNCTION employees_modify()
RETURNS TRIGGER AS $$
BEGIN
  NEW.user_modified = SESSION_USER;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER employees_modify
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW EXECUTE PROCEDURE employees_modify();

CREATE FUNCTION employees_delete()
RETURNS TRIGGER AS $$
BEGIN
  NEW.user_deleted = SESSION_USER;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER employees_delete
BEFORE INSERT ON employees_history
FOR EACH ROW EXECUTE PROCEDURE employees_delete();