PolarDB:temporal_tables (temporal tables)

Example setup

1. Create the main table.

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

2. Add the system period column.

   ALTER TABLE employees ADD COLUMN sys_period tstzrange NOT NULL;

3. Create the history table. The LIKE clause is the simplest approach and copies the column definitions automatically.

   The history table does not need to match the main table exactly. It can include extra columns or omit some, as long as it satisfies two requirements: it must include a system period column with the same name and data type (tstzrange), and any column that exists in both tables must have the same data type.

   CREATE TABLE employees_history (LIKE employees);

4. Create the versioning trigger.

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

Example

Insert three rows on August 8, 2006.

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);
INSERT INTO employees (name, department, salary)
VALUES ('Helmholtz Watson', 'College of Emotional Engineering', 18500);

Query the main table:

SELECT * FROM employees;

name             | department                       | salary   | sys_period
-----------------+----------------------------------+----------+-----------------------------
Bernard Marx     | Hatchery and Conditioning Centre | 10000.00 | ["2006-08-08 00:00:00+00",)
Lenina Crowne    | Hatchery and Conditioning Centre |  7000.00 | ["2006-08-08 00:00:00+00",)
Helmholtz Watson | College of Emotional Engineering | 18500.00 | ["2006-08-08 00:00:00+00",)
(3 rows)

Query the history table:

SELECT * FROM employees_history;

name | department | salary | sys_period
------+------------+--------+------------
(0 rows)

Inserts write only to the main table. The history table is empty until the first UPDATE or DELETE.

Example

Update Bernard Marx's salary on February 27, 2007.

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

Main table after update:

SELECT * FROM employees;

name             | department                       | salary   | sys_period
-----------------+----------------------------------+----------+-----------------------------
Lenina Crowne    | Hatchery and Conditioning Centre |  7000.00 | ["2006-08-08 00:00:00+00",)
Helmholtz Watson | College of Emotional Engineering | 18500.00 | ["2006-08-08 00:00:00+00",)
Bernard Marx     | Hatchery and Conditioning Centre | 11200.00 | ["2007-02-27 00:00:00+00",)
(3 rows)

History table after update:

SELECT * FROM employees_history;

name         | department                       | salary   | sys_period
-------------+----------------------------------+----------+-----------------------------------------------------
Bernard Marx | Hatchery and Conditioning Centre | 10000.00 | ["2006-08-08 00:00:00+00","2007-02-27 00:00:00+00")
(1 row)

Example

Delete Helmholtz Watson on December 24, 2012.

DELETE FROM employees WHERE name = 'Helmholtz Watson';

Main table after deletion:

SELECT * FROM employees;

name          | department                       | salary   | sys_period
--------------+----------------------------------+----------+-----------------------------
Lenina Crowne | Hatchery and Conditioning Centre |  7000.00 | ["2006-08-08 00:00:00+00",)
Bernard Marx  | Hatchery and Conditioning Centre | 11200.00 | ["2007-02-27 00:00:00+00",)
(2 rows)

History table after deletion:

SELECT * FROM employees_history;

name             | department                       | salary   | sys_period
-----------------+----------------------------------+----------+-----------------------------------------------------
Bernard Marx     | Hatchery and Conditioning Centre | 10000.00 | ["2006-08-08 00:00:00+00","2007-02-27 00:00:00+00")
Helmholtz Watson | College of Emotional Engineering | 18500.00 | ["2006-08-08 00:00:00+00","2012-12-24 00:00:00+00")
(2 rows)

Set a custom system time

By default, the trigger uses CURRENT_TIMESTAMP. Override this to backfill historical data—for example, when migrating from a system that recorded its own timestamps.

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

Reset to the default:

SELECT set_system_time(NULL);

The custom time persists until the end of the session if the transaction commits. If the transaction is rolled back, the change is reverted.

Create a system-period temporal table using inheritance

As an alternative to the LIKE clause, use PostgreSQL inheritance to define the history table first, then derive the main table from it.

Create the history table explicitly:

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

Create the main table as a child of the history table:

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

Maintain the history table

History tables grow continuously. Use one of the following strategies to keep storage under control.

Delete old rows by age:

-- Remove history rows older than one year
DELETE FROM employees_history
WHERE upper(sys_period) < now() - interval '1 year';

Delete old rows when the source row is deleted — add a trigger on the history table that prunes versions beyond a retention count:

-- Retain only the 5 most recent versions of each row
DELETE FROM employees_history
WHERE name = 'Bernard Marx'
  AND sys_period NOT IN (
    SELECT sys_period FROM employees_history
    WHERE name = 'Bernard Marx'
    ORDER BY lower(sys_period) DESC
    LIMIT 5
  );

Use partitioning — partition the history table by time range and detach old partitions when they are no longer needed. Detached partitions can be archived or dropped independently.

Move to cold storage — configure a separate tablespace for the history table to move it to lower-cost storage. For details, see Cold data tiered storage.

Use system-period temporal tables for data audit

Extend the setup with additional triggers to capture which user made each change.

-- Track the user who modified a 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();

-- Track the user whose action caused a row to be archived
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();