Bitemporal Data Patterns

Prerequisite: The Ledger (three timestamps)

Your regulatory report for Q1 shipped on April 10. On April 22, a correction arrives: a wire posted on March 28 had the wrong beneficiary account. You fix the entry. Now your current balance is correct, but the Q1 report — which an examiner is reviewing — reflects a state that never existed. The report showed $4.2M in that account on March 31. The corrected ledger shows $3.8M. Neither your current database nor the report tells the full truth. The database knows the correction but lost the original. The report knows the original but lacks the correction.

Bitemporal data solves this by tracking two independent time axes: when something happened in reality (valid time) and when your system learned about it (transaction time). With both axes, you can answer two distinct questions. “What was the balance on March 31?” gives you the valid-time answer. “What did we believe the balance was on March 31 as of April 10?” gives you the transaction-time answer. The first is the corrected truth. The second is what the report said — and proving both is what the auditor needs.

Two Time Axes

Every fact in a financial system has two temporal dimensions.

Valid time (also called business time or effective time) records when the fact was true in the real world. A wire that settles on March 28 has a valid time of March 28, regardless of when your system processes it.

Transaction time (also called system time or knowledge time) records when your system recorded the fact. If you process that March 28 wire on March 29, the transaction time is March 29. If you correct it on April 22, the correction’s transaction time is April 22.

Dimension	Records	Set by	Can change?
Valid time	When it happened in reality	Business logic	Yes — corrections update valid time
Transaction time	When the system recorded it	Database	No — always append-only

Transaction time is immutable. Your system cannot retroactively claim it knew something earlier than it did. Valid time is mutable in the sense that corrections can establish a new valid-time fact that supersedes a prior one — but the prior fact remains in the history, stamped with its own transaction-time range.

See The Ledger. The handbook’s three timestamps — value time, booking time, settlement time — are all flavors of valid time. Bitemporal modeling adds the orthogonal system-time axis that tracks when your ledger learned about each of these.

The Four Query Types

Combining the two axes produces four distinct query types. Each answers a different question, and conflating them produces wrong answers.

Query type	Valid time	Transaction time	Question answered
Current	Now	Now	What is the state right now?
As-of (valid)	Specific date	Now	What is the corrected state at that date?
As-was (transaction)	Now	Specific date	What did we believe the state was at that date?
As-of-as-was	Specific date	Specific date	What did we believe the state was at valid date V, as of knowledge date T?

The Q1 report scenario requires the as-of-as-was query: “What was the balance on March 31 (valid time) as we understood it on April 10 (transaction time)?” This returns the $4.2M figure that matched the shipped report — verifiable, auditable, and distinct from the corrected $3.8M.

Schema Design in Postgres

A bitemporal table adds four columns to every row: valid_from, valid_to, txn_from, and txn_to. Each row represents a fact that was true during the valid-time range and known during the transaction-time range.

CREATE TABLE entries_bitemporal (
  entry_id      BIGINT NOT NULL,
  account_id    BIGINT NOT NULL,
  amount        NUMERIC(19,4) NOT NULL,
  currency      CHAR(3) NOT NULL,
  valid_from    TIMESTAMPTZ NOT NULL,
  valid_to      TIMESTAMPTZ NOT NULL DEFAULT 'infinity',
  txn_from      TIMESTAMPTZ NOT NULL DEFAULT now(),
  txn_to        TIMESTAMPTZ NOT NULL DEFAULT 'infinity',
  EXCLUDE USING gist (
    entry_id WITH =,
    tstzrange(valid_from, valid_to) WITH &&,
    tstzrange(txn_from, txn_to) WITH &&
  )
);

The GiST exclusion constraint prevents overlapping facts for the same entry in both time dimensions simultaneously. This is the structural guarantee: no two rows can claim to be the current truth for the same entry at the same valid time and transaction time.

An as-of-as-was query filters on both ranges.

SELECT SUM(amount)
FROM entries_bitemporal
WHERE account_id = $1
  AND valid_from <= $2 AND valid_to > $2     -- valid at date V
  AND txn_from  <= $3 AND txn_to  > $3;      -- known at date T

This query returns the balance for account_id at valid time $2 as it was understood at transaction time $3. Changing $3 to now() gives the corrected as-of balance. Changing $2 to now() gives the current as-was balance.

Corrections Without Deletion

When a correction arrives, you do not update or delete the original row. You close the original’s transaction-time range and insert a new row with the corrected valid-time data.

BEGIN;
-- Close the old fact's transaction-time range
UPDATE entries_bitemporal
SET txn_to = now()
WHERE entry_id = $1
  AND txn_to = 'infinity';

-- Insert the corrected fact
INSERT INTO entries_bitemporal
  (entry_id, account_id, amount, currency, valid_from, valid_to, txn_from)
VALUES
  ($1, $2, $3, $4, $5, 'infinity', now());
COMMIT;

After this transaction, the original fact still exists — its txn_to is no longer infinity, marking it as superseded. The new row has txn_from = now() and txn_to = infinity, making it the currently-known version. Both rows coexist. An as-was query at the old transaction time returns the original. An as-of query at the same valid time returns the correction.

This is the mechanism that makes Q1 reports reproducible after corrections. The data that supported the report is never lost — it is timestamped out of currency, not erased.

Updating txn_to is the only mutation allowed in a bitemporal table. Updating amount, valid_from, or any business field violates the append-only invariant and makes historical queries unreproducible.

SQL:2011 Temporal Tables

The SQL:2011 standard added native temporal table support. Postgres does not implement it natively (as of version 17), but MariaDB 10.3+, IBM Db2, and Oracle 12c do. SQL Server provides a similar feature under the name “system-versioned temporal tables.”

-- SQL Server system-versioned temporal table
CREATE TABLE entries (
  entry_id    BIGINT PRIMARY KEY,
  account_id  BIGINT NOT NULL,
  amount      DECIMAL(19,4) NOT NULL,
  valid_from  DATETIME2 GENERATED ALWAYS AS ROW START,
  valid_to    DATETIME2 GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.entries_history));

The database manages valid_from and valid_to automatically. Updates and deletes move the old row to the history table with the correct timestamps. Temporal queries use the FOR SYSTEM_TIME clause.

-- As-of query in SQL:2011 syntax
SELECT SUM(amount) FROM entries
FOR SYSTEM_TIME AS OF '2025-03-31T23:59:59'
WHERE account_id = $1;

SQL:2011 temporal tables handle transaction time (system time) automatically. They do not natively support valid time as a separate axis. Full bitemporality — both axes independently managed — requires application-level logic on top of system-versioned tables, or a purpose-built temporal database.

DynamoDB Implementation

DynamoDB lacks range queries on temporal columns, so the bitemporal pattern uses a different key design. The partition key encodes the entity, and the sort key encodes the time axis.

PK: ENTRY#<entry_id>
SK: TXN#<txn_timestamp>#VALID#<valid_from>

A current-state query uses SK begins_with TXN#9999 (a sentinel for active rows) with a filter on valid_from. A historical query specifies the transaction-time prefix. DynamoDB’s key-value model makes as-of-as-was queries possible but expensive — they require a Query with filtering rather than a single scan with range predicates.

Operation	Postgres	DynamoDB
As-of query	Range predicate on index — 2-8ms	Query + filter — 5-15ms
As-of-as-was query	Double range predicate — 5-15ms	Query + double filter — 10-30ms
Correction (close + insert)	Two statements in one txn — 3-5ms	Conditional write + put — 8-12ms
Ad-hoc historical analysis	SQL with window functions	Export to analytics layer

DynamoDB’s strength is predictable latency at scale. Its weakness is the inability to run ad-hoc temporal queries without exporting data to a SQL-based analytics layer. For regulatory reporting — where as-of-as-was queries run infrequently but must be correct — the export approach works. For real-time temporal queries at low latency, Postgres with proper indexing is the better fit.

Late-Arriving Corrections

The most operationally painful scenario is a correction that arrives after the reporting period closes. A wire posted with the wrong amount on March 28, corrected on April 22, after the Q1 close on March 31.

Without bitemporal data, you have two options: restate the Q1 report (expensive, requires regulatory notification) or carry the correction forward into Q2 (creates a Q2 entry that doesn’t reflect Q2 activity). Both are wrong in different ways.

With bitemporal data, the correction records cleanly.

Axis	Original entry	Corrected entry
Valid time	March 28	March 28
Transaction time	March 29 → infinity	April 22 → infinity
Original txn_to	infinity → April 22	—
Amount	$400,000	$380,000

The Q1 report, when regenerated with txn_time <= April 10, produces the same $4.2M figure it shipped with. The Q1 report regenerated with txn_time = now() produces the corrected $3.8M. The auditor can see both: what was reported, what is correct, and when the correction occurred.

This eliminates the restatement-vs-carry-forward dilemma. The original report is reproducible. The correction is visible. The delta is traceable.

Indexing for Temporal Queries

Bitemporal queries are range queries on two dimensions. Without targeted indexes, performance degrades quickly as the table grows.

For Postgres, a GiST index on the temporal ranges is the starting point.

CREATE INDEX idx_entries_temporal ON entries_bitemporal
USING gist (
  account_id,
  tstzrange(valid_from, valid_to),
  tstzrange(txn_from, txn_to)
);

For current-state queries (the most common), a partial index on active rows eliminates scanning superseded facts.

CREATE INDEX idx_entries_current ON entries_bitemporal (account_id, valid_from)
WHERE txn_to = 'infinity';

On a table with 50 million rows and a 10:1 ratio of superseded to active rows, the partial index covers 5 million rows instead of 50 million. Current-state queries hit the partial index and complete in under 2ms. As-of-as-was queries hit the GiST index and complete in 5-15ms depending on the temporal range.

Query type	Index used	Rows scanned (50M table)	Latency
Current balance	Partial (active rows)	~500 per account	<2ms
As-of (valid time)	GiST temporal	~5,000 per account	5-8ms
As-of-as-was	GiST temporal	~5,000 per account	8-15ms

Storage Growth and Retention

Bitemporal tables grow faster than non-temporal tables because corrections create new rows without removing old ones. A table with a 5% monthly correction rate accumulates 60% more rows per year than a non-temporal equivalent.

Correction rate	Row growth per year	Storage multiplier at 5 years
1%	~12%	1.6x
5%	~60%	4x
10%	~120%	8.5x

Retention policies must balance storage cost against regulatory requirements. Most financial regulations require 5-7 years of transaction history. The transaction-time axis means you cannot simply delete old rows — a row superseded on April 22, 2025 might be needed to reproduce a report from April 10, 2025 until the retention window for that report expires.

Partitioning by txn_from enables efficient archival. Rows with txn_to before the oldest required reporting date can be moved to cold storage. Postgres partitioned tables with monthly ranges allow dropping entire partitions when the retention window passes, converting a delete-heavy operation into a metadata change.

The storage cost of bitemporality is the cost of provable history. The alternative — regenerating historical states from a non-temporal ledger — requires either full event replay (expensive at scale) or accepting that historical reports may not be reproducible (unacceptable to regulators).