Bi-temporal Data Models in Postgres with Range Types & GIST Indexes
Beyond the Audit Log: True Point-in-Time State with Bi-temporality
In mature systems, especially in regulated domains like finance, insurance, or healthcare, a simple updated_at column is dangerously insufficient. It tells you when a row was last touched, but it erases the past. A typical first-level enhancement is a separate audit log or history table, usually populated by triggers. This is a uni-temporal model: it tracks the history of the data as it was recorded. You can answer the question, "What was the customer's address in our database on June 1st?"
But you cannot answer the more critical question: "On July 15th, what did we believe the customer's address was back on June 1st?" This distinction—between when an event happened in the real world (valid time) and when it was recorded in our system (transaction time)—is the crux of bi-temporal modeling.
Implementing this pattern from scratch with discrete start_date and end_date columns is a notorious source of complex application logic, off-by-one errors, and nightmarish queries riddled with BETWEEN clauses that perform poorly. Fortunately, modern PostgreSQL provides a set of powerful, native tools that make bi-temporal modeling not just feasible, but elegant and performant: tsrange (timestamp range) types and GIST (Generalized Search Tree) indexes.
This article is a blueprint for implementing a production-grade bi-temporal model. We will not cover the basics. We assume you understand SQL, PostgreSQL, and the fundamental problem space. We will focus on:
tsrange and integrity constraints.1. The Bi-temporal Schema: Atomicity in Time
A bi-temporal entity needs to track two distinct timelines. Let's model an employees table where salary history is critical.
valid_time (vt): A tsrange representing the interval during which a specific record's data (e.g., salary) was true in the real world. This is the "business time" or "effective time."transaction_time (tt): A tsrange representing the interval during which this record was considered the current state within our database. This is the "system time" or "record time."Here is the complete DDL for our employees_bitemporal table.
-- Ensure the btree_gist extension is available for creating exclusion constraints on scalar types combined with ranges.
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE employees_bitemporal (
-- A surrogate primary key for the row itself.
id BIGSERIAL PRIMARY KEY,
-- A stable identifier for the employee entity across all its temporal versions.
employee_id UUID NOT NULL,
-- The employee's data attributes.
position VARCHAR(255) NOT NULL,
salary NUMERIC(10, 2) NOT NULL,
-- Bi-temporal timestamp ranges.
-- vt: Valid Time - When this fact was true in the real world.
valid_time TSRANGE NOT NULL,
-- tt: Transaction Time - When this fact was present in the database.
transaction_time TSRANGE NOT NULL,
-- An exclusion constraint is the heart of our data integrity.
-- It prevents any two records for the same employee from having overlapping
-- valid_time AND transaction_time ranges. This makes logically impossible states unrepresentable.
CONSTRAINT employees_bitemporal_exclusion
EXCLUDE USING GIST (employee_id WITH =, valid_time WITH &&, transaction_time WITH &&)
);
-- Create indexes AFTER the table is defined.
-- A GIST index is crucial for performant range-based queries.
CREATE INDEX employees_bitemporal_vt_idx ON employees_bitemporal USING GIST (valid_time);
CREATE INDEX employees_bitemporal_tt_idx ON employees_bitemporal USING GIST (transaction_time);
-- A composite GIST index is even better for queries that filter on the entity and a time range.
CREATE INDEX employees_bitemporal_employee_vt_tt_idx ON employees_bitemporal USING GIST (employee_id, valid_time, transaction_time);Key Design Decisions:
* employee_id vs id: We need two identifiers. id is a meaningless surrogate key for the physical row. employee_id is the business key that uniquely identifies an employee across all their historical versions. All temporal queries will be anchored to employee_id.
TSRANGE: We use tsrange which is a range of timestamp without time zone. For production systems, it is highly* recommended to use tstzrange (range of timestamp with time zone) to avoid a universe of pain. For this article's clarity, we'll stick to tsrange but consider this a critical production requirement.
The EXCLUDE Constraint: This is the most important part of the DDL. A standard UNIQUE constraint is insufficient for temporal data. This constraint ensures that for any given employee_id, there can be no row whose valid_time and transaction_time both* overlap with another row. The && operator is the "overlaps" operator for ranges. The btree_gist extension is required to use a scalar type (employee_id with =) inside a GIST-based exclusion constraint.
2. Core Bi-temporal Operations: Encapsulated Logic
Directly manipulating bi-temporal data with ad-hoc INSERT and UPDATE statements is a recipe for disaster. The logic must be encapsulated in stored procedures (or functions in PostgreSQL) to ensure atomicity and correctness.
Let's define a helper function to get the current timestamp, which we'll use as our now().
CREATE OR REPLACE FUNCTION get_current_time() RETURNS timestamp AS $$
SELECT '2023-10-27 10:00:00'::timestamp;
$$ LANGUAGE sql IMMUTABLE;Note: In a real system, you would use NOW() or transaction_timestamp(). We use a fixed function here to make our examples deterministic and testable.
A. Initial Record Creation
When a new employee is hired, we insert their first record. Both its valid time and transaction time start now and extend indefinitely into the future.
CREATE OR REPLACE FUNCTION bitemporal_create_employee(
p_employee_id UUID,
p_position TEXT,
p_salary NUMERIC
) RETURNS void AS $$
DECLARE
v_now TIMESTAMP := get_current_time();
BEGIN
INSERT INTO employees_bitemporal (
employee_id,
position,
salary,
valid_time,
transaction_time
) VALUES (
p_employee_id,
p_position,
p_salary,
tsrange(v_now, 'infinity', '[)'),
tsrange(v_now, 'infinity', '[)')
);
END;
$$ LANGUAGE plpgsql;Example Usage:
-- Hire Alice
SELECT bitemporal_create_employee(
'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1',
'Software Engineer',
90000.00
);B. Standard Update (e.g., Promotion)
This is the most common and complex operation. When an employee's salary changes, we are not performing an UPDATE in the traditional SQL sense. Instead, we are:
valid_time and transaction_time to now().valid_time and transaction_time begin at now() and extend to infinity.This must happen atomically.
CREATE OR REPLACE FUNCTION bitemporal_update_employee(
p_employee_id UUID,
p_position TEXT,
p_salary NUMERIC
) RETURNS void AS $$
DECLARE
v_now TIMESTAMP := get_current_time();
v_current_record_id BIGINT;
BEGIN
-- Find the ID of the current record for this employee.
-- The current record is the one whose valid_time and transaction_time are both open-ended.
SELECT id INTO v_current_record_id
FROM employees_bitemporal
WHERE employee_id = p_employee_id
AND upper(valid_time) = 'infinity'
AND upper(transaction_time) = 'infinity';
IF NOT FOUND THEN
RAISE EXCEPTION 'No current record found for employee_id %', p_employee_id;
END IF;
-- 1. Terminate the current record's validity and transaction time.
UPDATE employees_bitemporal
SET
valid_time = tsrange(lower(valid_time), v_now, '[)'),
transaction_time = tsrange(lower(transaction_time), v_now, '[)')
WHERE id = v_current_record_id;
-- 2. Insert the new version of the record.
INSERT INTO employees_bitemporal (
employee_id,
position,
salary,
valid_time,
transaction_time
) VALUES (
p_employee_id,
p_position,
p_salary,
tsrange(v_now, 'infinity', '[)'),
tsrange(v_now, 'infinity', '[)')
);
END;
$$ LANGUAGE plpgsql;Example Usage:
-- Let's advance time for our next operation.
CREATE OR REPLACE FUNCTION get_current_time() RETURNS timestamp AS $$
SELECT '2024-01-15 12:00:00'::timestamp;
$$ LANGUAGE sql IMMUTABLE;
-- Promote Alice
SELECT bitemporal_update_employee(
'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1',
'Senior Software Engineer',
120000.00
);After this operation, the table for Alice contains two rows:
valid_time and transaction_time ranges ending on 2024-01-15.2024-01-15 and extending to infinity.C. Logical Deletion
We never DELETE from a bi-temporal table. A deletion is just another type of update where we terminate the record's validity.
CREATE OR REPLACE FUNCTION bitemporal_terminate_employee(
p_employee_id UUID
) RETURNS void AS $$
DECLARE
v_now TIMESTAMP := get_current_time();
v_current_record_id BIGINT;
BEGIN
SELECT id INTO v_current_record_id
FROM employees_bitemporal
WHERE employee_id = p_employee_id
AND upper(valid_time) = 'infinity'
AND upper(transaction_time) = 'infinity';
IF NOT FOUND THEN
RAISE EXCEPTION 'No current record found for employee_id %', p_employee_id;
END IF;
-- We only terminate the valid_time. The transaction remains open because
-- the fact that this employee is *not* currently employed is the current state of our database.
UPDATE employees_bitemporal
SET valid_time = tsrange(lower(valid_time), v_now, '[)')
WHERE id = v_current_record_id;
END;
$$ LANGUAGE plpgsql;D. Retroactive Correction (The True Power)
This is where bi-temporality shines. Imagine we discovered that Alice's initial salary was entered incorrectly. It should have been $95,000, not $90,000. We need to correct this without losing the fact that we previously thought it was $90,000.
This operation involves only the transaction_time.
- Find the incorrect historical record(s).
transaction_time to mark them as superseded as of now.valid_time, but with a new transaction_time that starts now.CREATE OR REPLACE FUNCTION bitemporal_correct_history(
p_employee_id UUID,
p_correction_valid_time TSRANGE,
p_new_position TEXT,
p_new_salary NUMERIC
) RETURNS void AS $$
DECLARE
v_now TIMESTAMP := get_current_time();
v_incorrect_record RECORD;
BEGIN
-- Find the record(s) that were valid during the specified period
-- and are still considered the current version in the transaction timeline.
FOR v_incorrect_record IN
SELECT * FROM employees_bitemporal
WHERE employee_id = p_employee_id
AND valid_time && p_correction_valid_time -- The valid times overlap
AND upper(transaction_time) = 'infinity' -- It's a current belief
LOOP
-- 1. Terminate the transaction time of the incorrect record.
-- This preserves it for auditing but removes it from the 'current belief' timeline.
UPDATE employees_bitemporal
SET transaction_time = tsrange(lower(transaction_time), v_now, '[)')
WHERE id = v_incorrect_record.id;
-- 2. Re-insert the corrected record.
-- It has the SAME valid_time as the original, but a NEW transaction_time.
INSERT INTO employees_bitemporal (
employee_id,
position,
salary,
valid_time,
transaction_time
) VALUES (
p_employee_id,
p_new_position,
p_new_salary,
v_incorrect_record.valid_time, -- CRITICAL: Use the original valid_time
tsrange(v_now, 'infinity', '[)')
);
END LOOP;
END;
$$ LANGUAGE plpgsql;Example Usage:
-- Let's advance time again.
CREATE OR REPLACE FUNCTION get_current_time() RETURNS timestamp AS $$
SELECT '2024-02-01 09:00:00'::timestamp;
$$ LANGUAGE sql IMMUTABLE;
-- Correct Alice's starting salary. Her initial employment period started on 2023-10-27.
SELECT bitemporal_correct_history(
'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1',
tsrange('2023-10-27 10:00:00', '2024-01-15 12:00:00', '[)'),
'Software Engineer', -- Position was correct
95000.00 -- The corrected salary
);Now, our database contains a complete history. We have a record showing that from Oct 27 to Jan 15, we used to think her salary was $90k. And we have a new record, created on Feb 1, showing that for that same period, her salary was actually $95k.
3. Advanced Querying Patterns
The complexity of the write operations pays off in the flexibility of our reads. The @> (contains) operator for ranges is our primary tool.
A. "As-Is" Query: What is true right now?
This is the most common query: show me the current state of all employees.
SELECT employee_id, position, salary
FROM employees_bitemporal
WHERE valid_time @> get_current_time()
AND transaction_time @> get_current_time();This can be encapsulated in a view for simplicity:
CREATE OR REPLACE VIEW employees_current AS
SELECT employee_id, position, salary, lower(valid_time) as effective_from
FROM employees_bitemporal
WHERE valid_time @> NOW()
AND transaction_time @> NOW();B. "As-Of" Query: What was true in the real world on a specific date?
"Show me Alice's salary on December 1st, 2023."
SELECT position, salary
FROM employees_bitemporal
WHERE employee_id = 'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1'
-- The state of the world on this date:
AND valid_time @> '2023-12-01 00:00:00'::timestamp
-- According to our most recent records:
AND transaction_time @> get_current_time();C. The Full Bi-temporal Query: "As We Knew It On... As Of..."
This is the ultimate query that only a bi-temporal model can answer.
Question: "On January 20th, 2024, what did we believe Alice's salary was for the date December 1st, 2023?"
Remember, we corrected her salary on February 1st, 2024. So on Jan 20th, we still believed the incorrect salary.
SELECT position, salary
FROM employees_bitemporal
WHERE employee_id = 'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1'
-- The state of the world on this date:
AND valid_time @> '2023-12-01 00:00:00'::timestamp
-- As we believed it to be on this date:
AND transaction_time @> '2024-01-20 00:00:00'::timestamp;This query would correctly return $90,000.00. If we run the same query but change the transaction time to a date after the correction, like 2024-02-02, it will correctly return $95,000.00.
4. Performance, Indexing, and Edge Cases
Without proper indexing, the above queries will result in full table scans and abysmal performance as the table grows.
The Critical Role of GIST Indexes
A standard B-Tree index is optimized for scalar value lookups and sorting (=, >, <). It is completely ineffective for range operators like @> (contains) or && (overlaps).
GIST (Generalized Search Tree) indexes are designed for indexing complex data types, including geometric data and ranges. A GIST index on a tsrange column builds a tree structure where parent nodes represent a range that fully contains the ranges of its children. This allows the query planner to very quickly discard huge portions of the tree that could not possibly satisfy a containment or overlap condition.
Let's prove it. Imagine our table has grown to millions of rows.
Query without GIST index:
EXPLAIN ANALYZE
SELECT * FROM employees_bitemporal
WHERE employee_id = 'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1'
AND valid_time @> '2023-12-01 00:00:00'::timestamp;Predicted EXPLAIN Output:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Seq Scan on employees_bitemporal (cost=0.00..56250.00 rows=250 width=60) (actual time=0.015..350.123 rows=1 loops=1)
Filter: ((employee_id = 'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1'::uuid) AND (valid_time @> '2023-12-01 00:00:00'::timestamp))
Rows Removed by Filter: 999999
Planning Time: 0.110 ms
Execution Time: 350.200 msThis is a Sequential Scan. The database had to read the entire table and check every single row.
Now, let's use the composite GIST index we created earlier (employees_bitemporal_employee_vt_tt_idx).
Query with GIST index:
EXPLAIN ANALYZE
SELECT * FROM employees_bitemporal
WHERE employee_id = 'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1'
AND valid_time @> '2023-12-01 00:00:00'::timestamp;Predicted EXPLAIN Output:
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Index Scan using employees_bitemporal_employee_vt_tt_idx on employees_bitemporal (cost=0.42..8.44 rows=1 width=60) (actual time=0.035..0.036 rows=1 loops=1)
Index Cond: ((employee_id = 'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1'::uuid) AND (valid_time @> '2023-12-01 00:00:00'::timestamp))
Planning Time: 0.150 ms
Execution Time: 0.055 msThe difference is staggering: 350ms vs 0.055ms. The Index Scan immediately located the relevant records. For any serious application of this pattern, a composite GIST index on (entity_id, valid_time, transaction_time) is mandatory.
Edge Case: Contiguous Time Ranges
Our EXCLUDE constraint prevents overlaps, but it doesn't enforce contiguity. It's possible to terminate an employee's record and then create a new one for them a week later, leaving a gap in their valid_time history. This may be valid business logic (e.g., re-hiring). If gaps are not allowed, you would need to add application-level checks or more complex database constraints to ensure the lower() bound of a new record is exactly equal to the upper() bound of the previous one.
Edge Case: Timezones (`tstzrange`)
As mentioned, using tsrange (timestamp without time zone) is brittle. All timestamps are interpreted relative to the server's timezone setting. If that setting changes, or if clients in different timezones connect, your data's meaning can shift. Using tstzrange (timestamp with time zone) is the robust solution. All inputs are normalized to UTC for storage, and they can be converted back to the client's session timezone on retrieval, ensuring consistency.
Conclusion
Bi-temporal modeling is a powerful but complex discipline. By leveraging PostgreSQL's native tsrange types, GIST indexing, and exclusion constraints, we can move the majority of this complexity into the database layer. This provides several profound advantages:
* Data Integrity: The EXCLUDE constraint makes invalid temporal states impossible to represent, a guarantee that application-level logic can never truly provide.
* Performance: GIST indexes allow for highly efficient querying of temporal data, avoiding the performance pitfalls of manual start_date/end_date solutions.
* Atomicity and Reusability: Encapsulating write logic in PL/pgSQL functions ensures that complex temporal state transitions are atomic and consistent across all parts of your application.
* Unimpeachable Audit: The resulting data structure is a complete, immutable history of both reality and your system's knowledge of it, enabling powerful analytics and satisfying the strictest audit requirements.
While not a pattern to be used lightly, for systems where historical accuracy is a core business requirement, the native bi-temporal capabilities of PostgreSQL offer a superior, robust, and performant foundation.