Bitemporal composite indexes

Every bitemporal table queried by the latest-version-per-entity-id pattern needs a composite index covering the inner correlated subquery. Single-column indexes on eid, effective_as_of, and recorded_as_of separately leave Postgres no choice but a sequential scan or a costly index-merge.

The query shape

Bitemporal persistence emits this SQL for “latest version of each entity at an asOf coordinate” reads:

SELECT bt.*
FROM <bitemporal_table> bt
WHERE bt.id IN (
  SELECT sq.id
  FROM <bitemporal_table> sq
  WHERE <user-condition>                          -- e.g. tenant_id = ?, item_reference_entity_id = ?
    AND sq.effective_as_of <= <asOf.effective>
    AND sq.recorded_as_of  <= <asOf.recorded>
    AND bt.eId = sq.eId                           -- correlated to outer row
    AND bt.retired = FALSE
  ORDER BY sq.effective_as_of DESC, sq.recorded_as_of DESC
  LIMIT 1
)
ORDER BY bt.recorded_as_of DESC, bt.effective_as_of DESC, bt.id ASC
OFFSET ? LIMIT ?

The inner subquery is the dominant cost. Plan quality is decided entirely by whether an index supports the predicate + sort.

Index shapes

Two composite indexes per bitemporal table:

#	Index	Rationale
1	`(eid, effective_as_of DESC, recorded_as_of DESC)`	Supports the correlated subquery’s `bt.eId = sq.eId` join + `ORDER BY` for the latest-version lookup. Used by ad-hoc reads without a tenant filter (history, audit, replay).
2	`(tenant_id, <discriminator>, eid, effective_as_of DESC, recorded_as_of DESC)`	Supports tenant-scoped queries with an additional discriminator column (e.g. `item_reference_entity_id` on `kanban_card`). Provides an index-only scan on the inner subquery for the dominant workload.

The second index’s column order is (tenant_id, discriminator, eid, …) — tenant_id first because every Arda query is tenant-scoped (enforced by ScopedTable’s universal condition), discriminator second because most application queries filter on that, eid third to complete the correlated-subquery join, then the two temporal columns DESC for the ORDER BY ... LIMIT 1 shape.

Case-folded equality needs an expression index

eqI and neI fold both sides of the comparison, but not in the same place: SQL lower() is applied to the column, while the value is lowered before it is bound. A filter for Acme therefore reaches the database already folded, with one function for the planner rather than two:

WHERE lower(item_name) = 'acme'

That is a comparison a plain b-tree index on the column cannot serve — the index stores Acme, the query asks about acme. A query filtering on a case-folded term will fall back to a sequential scan however well the column is otherwise indexed.

What serves it is an expression index on the lowered column:

CREATE INDEX CONCURRENTLY idx_item_name_lower ON item (lower(item_name));

Add one when a case-folded filter on that column becomes hot, not pre-emptively for every text column — an expression index carries the same write cost as any other, and most columns are never filtered this way.

Why not a case-insensitive column type

The alternative is to give the column a case-insensitive collation or citext. That was considered and rejected: it needs a database extension and a migration per column, and it silently changes what ordinary equality means for that column — a much larger claim than a single filter term should make. Folding in the query keeps the change local to the callers that asked for it.

No partial-index predicate on `retired`

A natural first instinct is to add WHERE retired = FALSE to the second index, since the inner subquery always carries that predicate. Don’t. Two reasons:

Audit and replay tooling reads retired rows. A partial index on retired = FALSE cannot serve those queries; Postgres would fall back to a sequential scan. A plain (full-table) index serves both live and historical reads.
Storage cost is small. retired = TRUE rows are a small fraction of the table on every Arda bitemporal table inspected. Doubling the index entry count to gain partial-index selectivity isn’t a worthwhile trade.

The workbook decision record for PDEV-490 (T17 — no partial-index predicate) elaborates on the alternatives weighed.

DESC matters

The temporal columns are indexed DESC because the inner subquery is ORDER BY sq.effective_as_of DESC, sq.recorded_as_of DESC LIMIT 1. Postgres can scan a DESC index forward and stop at the first row, avoiding a sort step. An ASC index forces either a full read with sort or a backwards scan (which is permitted in Postgres but slightly less efficient than a forward scan on a DESC index).

Online deployment via CONCURRENTLY

Every bitemporal-index migration uses CREATE INDEX CONCURRENTLY so deploys do not block writes on the affected table. This requires:

A Flyway sidecar .conf file with executeInTransaction=false next to the .sql migration. CREATE INDEX CONCURRENTLY cannot run inside a transaction.
common-module’s DbMigration is configured with mixed=true and a session-level PostgreSQL advisory lock (PostgreSQLConfigurationExtension.setTransactionalLock(false)). Without the latter, Flyway’s transactional advisory lock would deadlock against CONCURRENTLY (PostgreSQL’s snapshot protocol makes CONCURRENTLY wait for all earlier transactions). This is the documented Flyway escape valve for CONCURRENTLY migrations.

See Flyway is authoritative for database indexes for the general convention.

Inspecting indexes in a live database

SELECT indexname, indexdef
FROM   pg_indexes
WHERE  schemaname = 'public'
  AND  tablename = '<bitemporal_table>'
ORDER BY indexname;

EXPLAIN ANALYZE is the right tool for verifying the planner uses the composite index:

EXPLAIN ANALYZE
SELECT bt.*
FROM <bitemporal_table> bt
WHERE bt.id IN (
  SELECT sq.id FROM <bitemporal_table> sq
  WHERE sq.tenant_id = '<tenant-uuid>'
    AND sq.<discriminator> = '<value>'
    AND sq.effective_as_of <= NOW()
    AND sq.recorded_as_of  <= NOW()
    AND bt.eId = sq.eId
    AND bt.retired = FALSE
  ORDER BY sq.effective_as_of DESC, sq.recorded_as_of DESC
  LIMIT 1
);

Expected plan: Index Scan using <tenant_+_eid_+_temporal_composite> on the inner subquery. A sequential scan or a generic single-column index lookup means the composite is missing or the column order doesn’t match.