A temporal EAV entity store in Postgres — Part 5: Maintenance and migration

Parts 1–4 built and tested the store. This part is about living with it: the Postgres metrics that tell you how the six tables are actually behaving, the conflict queries that stand guard over the invariants in production, the Prometheus wiring that pages before users notice — and the in-house migration tool, because a store that promised "no DDL ever again" still has to evolve its schema. It just does it as data.

Goal

Two duties every temporal EAV deployment inherits. Maintenance: the design multiplies rows (entities × fields × versions) and moved every invariant out of the database — so growth must be watched per family, and the invariants must be re-checked continuously, in production, by queries. Migration: the virtual schema still changes — a family gains a field, a field changes meaning — and since there is no ALTER TABLE, schema evolution becomes controlled data change: an in-house tool with the same discipline (registry, ordering, idempotency, verification) that DDL migrations get for free elsewhere.

Maintenance

What to watch, per table

Metric	Table(s)	Why it matters here
total size + growth rate	sm_entity_attr	The multiplicative table. Its growth rate is the honest version count: a sudden slope change means some writer stopped diffing (Part 1's same-value caveat) — find it before the disk does.
n_dead_tup / autovacuum lag	sm_entity_attr, sm_entity_rel	Every temporal close is an UPDATE (sets active_to) — this design is update-heavy by construction, so dead tuples accumulate faster than in insert-only stores. Autovacuum falling behind bloats the exact table every read joins.
seq_scan vs idx_scan	all six	A rising seq_scan count on sm_entity_attr means some new query bypasses the window indexes — at this table's size that's a production incident on a delay timer.
open rows (active_to IS NULL) per family	sm_entity_attr	The "current state" working set — what every point-in-time read filters down to. Tracks real product growth; diverging from entity count means field creep or missed closes.
versions per entity, per family	sm_entity_attr	The history-vs-churn ratio. Some families should version often (mileage), some almost never (attr definitions). A family off its baseline names the misbehaving writer.
transaction age	cluster-wide	Part 3 holds transactions open across external syncs — long-running transactions are the design's known pressure point, and they also block autovacuum from reclaiming anything.

Usage metrics — one query over pg_stat_user_tables

SELECT relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       pg_size_pretty(pg_relation_size(relid))       AS heap_size,
       n_live_tup,
       n_dead_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup, 0), 2) AS dead_ratio,
       seq_scan,
       idx_scan,
       round(idx_scan::numeric / NULLIF(seq_scan + idx_scan, 0), 2) AS idx_share,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_user_tables
WHERE relname IN ('sm_entity', 'sm_entity_attr', 'sm_entity_attr_name',
                  'sm_entity_rel', 'sm_entity_family', 'sm_customer_entity')
ORDER BY pg_total_relation_size(relid) DESC;

-- reading it:
--   dead_ratio > ~0.2 on sm_entity_attr  -> autovacuum is losing; check xact age
--   idx_share falling on sm_entity_attr  -> a new query path is seq-scanning
--   last_autovacuum hours old on a hot table -> tune per-table autovacuum settings

Growth and shape — open rows and versions per family

SELECT f.name                                            AS family,
       count(DISTINCT a.fk_sm_entity_uuid)               AS entities,
       count(*) FILTER (WHERE a.active_to IS NULL)       AS open_rows,
       count(*)                                          AS total_rows,
       round(count(*)::numeric
             / NULLIF(count(DISTINCT a.fk_sm_entity_uuid), 0), 1) AS versions_per_entity
FROM sm_entity_attr a
JOIN sm_entity e        ON e.uuid = a.fk_sm_entity_uuid
JOIN sm_entity_family f ON f.id   = e.fk_sm_entity_family_id
GROUP BY f.name
ORDER BY total_rows DESC;

-- versions_per_entity climbing fast on one family = a writer that stopped
-- diffing before writing. The family name points at the team.

The conflict queries — invariants on patrol

Part 4 called these states "impossible" and asserted them in CI. Production deserves the same assertions, continuously: each query below must return zero rows, always — a non-zero result is a corruption alarm, not a statistic. They are cheap enough to run on a schedule and feed straight into Prometheus.

Conflict 1 — overlapping windows (two values active at once)

-- the production version of the E2E "only one active" impossible-check:
-- any two rows of the same entity+field whose [from, to) windows intersect
SELECT a1.fk_sm_entity_uuid AS entity,
       an.name              AS field,
       a1.uuid              AS row_a,
       a2.uuid              AS row_b,
       a1.active_from, a1.active_to,
       a2.active_from, a2.active_to
FROM sm_entity_attr a1
JOIN sm_entity_attr a2
  ON  a1.fk_sm_entity_uuid          = a2.fk_sm_entity_uuid
  AND a1.fk_sm_entity_attr_name_uuid = a2.fk_sm_entity_attr_name_uuid
  AND a1.uuid < a2.uuid
  AND a1.active_from < COALESCE(a2.active_to, 'infinity'::timestamptz)
  AND a2.active_from < COALESCE(a1.active_to, 'infinity'::timestamptz)
JOIN sm_entity_attr_name an ON an.uuid = a1.fk_sm_entity_attr_name_uuid;
-- MUST be empty. A row here means later writes on that field will start
-- failing "Object schema is invalid" (Part 1's matrix) — fix before users hit it.

Conflict 2 — timestamp drift (field older than its entity)

-- Part 2's caveat #1, checked in production: no attr may predate its entity
SELECT e.uuid AS entity, an.name AS field,
       a.active_from AS attr_from, e.active_from AS entity_from
FROM sm_entity_attr a
JOIN sm_entity e            ON e.uuid  = a.fk_sm_entity_uuid
JOIN sm_entity_attr_name an ON an.uuid = a.fk_sm_entity_attr_name_uuid
WHERE a.active_from < e.active_from;
-- MUST be empty. Non-empty means some write path let the database (or a
-- second clock read) mint a timestamp.

Conflict 3 — duplicate field definitions (the lazy-creation race)

SELECT name, count(*), array_agg(uuid)
FROM sm_entity_attr_name
GROUP BY name
HAVING count(*) > 1;
-- MUST be empty (the unique index should make it structurally so —
-- this query is the audit that the index is actually there and valid).

Conflict 4 — lock waits and long transactions

-- who is blocked on whom, right now
SELECT blocked.pid            AS blocked_pid,
       left(blocked.query, 60)  AS blocked_query,
       blocking.pid           AS blocking_pid,
       left(blocking.query, 60) AS blocking_query,
       now() - blocked.query_start AS waiting_for
FROM pg_stat_activity blocked
JOIN pg_locks bl  ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks gl  ON gl.granted
                 AND gl.locktype = bl.locktype
                 AND gl.relation      IS NOT DISTINCT FROM bl.relation
                 AND gl.transactionid IS NOT DISTINCT FROM bl.transactionid
JOIN pg_stat_activity blocking ON blocking.pid = gl.pid
WHERE blocked.wait_event_type = 'Lock';

-- transactions old enough to be a sync-before-commit stuck on a slow
-- external system (Part 3's known pressure point)
SELECT pid, now() - xact_start AS xact_age, state, left(query, 80) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND now() - xact_start > interval '5 seconds'
ORDER BY xact_age DESC;

Prometheus

All of the above becomes automatic through postgres_exporter custom queries: the conflict counts and growth gauges are exported as metrics, and alerting stops being a human remembering to run SQL. The rule of thumb: invariant metrics alert on any non-zero value; capacity metrics alert on slope, not absolute size.

postgres_exporter custom queries + alert rules

# queries.yaml (postgres_exporter)
smentity_open_rows:
  query: |
    SELECT f.name AS family,
           count(*) FILTER (WHERE a.active_to IS NULL) AS open_rows,
           count(*) AS total_rows
    FROM sm_entity_attr a
    JOIN sm_entity e        ON e.uuid = a.fk_sm_entity_uuid
    JOIN sm_entity_family f ON f.id   = e.fk_sm_entity_family_id
    GROUP BY f.name
  metrics:
    - family:     { usage: LABEL }
    - open_rows:  { usage: GAUGE, description: "Active attr rows per family" }
    - total_rows: { usage: GAUGE, description: "All attr versions per family" }

smentity_invariants:
  query: |
    SELECT
      (SELECT count(*) FROM sm_entity_attr a1
        JOIN sm_entity_attr a2
          ON  a1.fk_sm_entity_uuid           = a2.fk_sm_entity_uuid
          AND a1.fk_sm_entity_attr_name_uuid = a2.fk_sm_entity_attr_name_uuid
          AND a1.uuid < a2.uuid
          AND a1.active_from < COALESCE(a2.active_to,'infinity'::timestamptz)
          AND a2.active_from < COALESCE(a1.active_to,'infinity'::timestamptz)
      ) AS overlaps,
      (SELECT count(*) FROM sm_entity_attr a JOIN sm_entity e
          ON e.uuid = a.fk_sm_entity_uuid
        WHERE a.active_from < e.active_from) AS drifted,
      (SELECT count(*) - count(DISTINCT name)
         FROM sm_entity_attr_name)           AS duplicate_names
  metrics:
    - overlaps:        { usage: GAUGE }
    - drifted:         { usage: GAUGE }
    - duplicate_names: { usage: GAUGE }

# alert rules (Prometheus)
- alert: SmentityInvariantBroken
  expr: smentity_invariants_overlaps > 0
     or smentity_invariants_drifted > 0
     or smentity_invariants_duplicate_names > 0
  for: 0m          # zero tolerance — this is corruption, page now
- alert: SmentityVersionExplosion
  expr: rate(smentity_open_rows_total_rows[6h])
        > 4 * rate(smentity_open_rows_total_rows[6h] offset 1d)
  for: 1h          # a writer stopped diffing — find it tomorrow morning
- alert: PgLongTransaction
  expr: pg_stat_activity_max_tx_duration > 30
  for: 2m          # a sync-before-commit is stuck on an external system

Migration — evolving a schema that has no DDL

The store's founding promise (Part 1: "no DDL operations ever again") has a fine print: the tables never change, but the virtual schema does. Adding a field to fieldsMeta changes only entities created after the deploy — every existing entity of the family reads that field as undefined ("never set"), which breaks the moment anything treats the field as required. The main case, and the reason the tool exists: a family gains a new field with a default, and every already-created entity must be aligned with it. That's not a schema migration or a data fix — it's a third thing, an entity migration, and it deserves the same machinery DDL migrations get: a registry, a defined order, idempotency, and verification.

The in-house tool

A small runner, deliberately shaped like TypeORM's migration runner so nobody has to learn a second mental model. Entity migrations are files with a name and an up(); applied names are recorded in their own registry table; the runner executes pending ones in order, batched, and refuses to finish if verification fails.

The shape — registry, migration file, runner contract

CREATE TABLE sm_entity_migration (
  id          serial PRIMARY KEY,
  name        varchar UNIQUE NOT NULL,   -- '2025-01-10-car-add-fuel-type'
  applied_at  timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
);

export class CarAddFuelType implements EntityMigration {
  name = '2025-01-10-car-add-fuel-type';

  async up(ctx: EntityMigrationContext): Promise<void> {
    await ctx.backfillField({
      familyId: SM_ENTITY_CAR_FAMILY,
      field:    'fuelType',
      type:     TYPE_STR,
      value:    'petrol',            // the default for pre-existing cars
      batchSize: 500,
    });
  }

  async verify(ctx: EntityMigrationContext): Promise<boolean> {
    return (await ctx.countEntitiesMissingField(SM_ENTITY_CAR_FAMILY, 'fuelType')) === 0;
  }
}

// runner: load pending (files minus registry), run in name order,
// one migration = its own transaction per batch, record on success,
// abort the deploy pipeline if verify() returns false.

The main cases

Case	What the migration does	The decision inside it
New field with default	Backfill: one new attr row per existing entity of the family, value = default. The headline case.	Timestamp policy: stamp rows at the migration instant (history says the field appeared now — honest) or backdate to each entity's own `active_from` (history says it always existed). Migration instant is the default; backdating is a business claim, not a technical choice.
Rename a field	Rename the `sm_entity_attr_name` row in place — every historical value follows automatically because rows reference the uuid, not the name.	API compatibility: keep the old name readable via `dtoField` mapping for one release, or break clients at once.
Change a field's type	New attr-name of the new type; per entity: parse the old value, open a new typed row, close the old one — all at one migration instant. Old rows stay as history.	What to do with unparseable values: fail the migration (strict) or route them to a report and default them (pragmatic, logged).
Retire a field	Close all open rows of the field at the migration instant. Nothing is deleted — Part 1's rule survives migrations too.	Whether reads should still expose it historically (usually yes: point-in-time reads before the retirement must not change).
Tighten a validator	Dry-run first: scan for values the new rule would reject, produce the report, fix the data (itself a migration), then ship the validator.	Never ship the validator first — Part 2's rule that every write passes validation means one legacy value bricks that entity's updates.

The backfill, precisely

backfillField — the algorithm

Resolve (or create) the attr-name row for the new field — through the same guarded path as Part 2, not a bare insert.
Mint one timestamp for the whole migration — every backfilled row across every batch carries it. The single-timestamp rule (Part 2, caveat #1) applies to migrations exactly as it applies to writes; a per-batch timestamp would make "when did this field appear?" have 40 answers.
Select target entities: all entities of the family with no active row for the field — which is precisely what makes re-runs idempotent: already-backfilled entities simply don't match.
Process in batches (500): each batch is one short transaction inserting the default rows. Short transactions keep locks polite to live traffic and autovacuum (see Maintenance) — one giant transaction for a million entities is how a migration becomes an outage.
Between batches: yield, and re-check a circuit breaker (error rate, replication lag) — a backfill must be pausable and resumable, and idempotency (step 3) is what makes resume trivial.
Verify: the count of family entities missing the field is zero, and the conflict queries above still return zero rows. Record the migration in the registry only after verification passes.

backfillField — the SQL underneath

-- step 3: entities of the family still missing an active 'fuelType'
SELECT e.uuid
FROM sm_entity e
LEFT JOIN sm_entity_attr a
       ON  a.fk_sm_entity_uuid = e.uuid
       AND a.fk_sm_entity_attr_name_uuid = $attrNameUuid
       AND a.active_to IS NULL
WHERE e.fk_sm_entity_family_id = $familyId
  AND a.uuid IS NULL
LIMIT 500;

-- step 4: one batch, one transaction
INSERT INTO sm_entity_attr
  (uuid, val_str, active_from, fk_sm_entity_uuid, fk_sm_entity_attr_name_uuid)
SELECT uuid_generate_v4(), 'petrol', $migrationTs, e.uuid, $attrNameUuid
FROM   (/* the SELECT above */) e;

-- step 6: verification — MUST return 0
SELECT count(*) FROM (/* the step-3 SELECT without LIMIT */) missing;

Worth naming the alternative that avoids the backfill entirely: teach the read path to treat a missing value as the fieldsMeta default at view-assembly time. It's cheaper — and it means "what was the value on March 3rd?" silently changes answer when the default ships, which breaks the one guarantee this store exists to give. We backfill: history should record when a fact started being true.

Caveats

Backfills fight the same locks as traffic. Batch small, commit often, run in off-peak windows, and watch the lock-wait query live while it runs. The Prometheus gauges are the migration's progress bar and its alarm.
Fast path vs safe path. Backfilling through the repository primitives is correct by construction but slow (per-entity queries); raw SQL is fast but must re-honor the invariants by hand — single timestamp, no overlaps, right val_* column. We use raw SQL for volume and run the conflict queries as the safety net; picking speed without the net is how invariants die quietly.
The attr-name cache lies during migrations. Part 2's 2-second lookup cache means app instances may not see a just-created field definition immediately — create the attr-name first, then backfill; never interleave with traffic that races the same name.
Version metrics spike by design during backfills. A million backfilled rows looks exactly like a runaway writer to the SmentityVersionExplosion alert. Silence the slope alerts for the migration window — deliberately, with an end time — or on-call learns to ignore them, which is worse.
Idempotency is the rollback plan. Temporal stores don't undo — a "down" migration would rewrite history. The recovery story for a bad backfill is: stop, fix the migration, re-run (idempotent), and if values were wrong, write the correction as another versioned change. The registry records what ran; history records what happened. Both are append-only, on purpose.

Before you operate: the decision checklist

Backfill or read-time default. Align old entities with new fields by writing rows, or by defaulting at read time? (my choice — backfill: read-time defaults silently rewrite historical answers, which is the one thing this store must never do)
Backfill timestamp policy. Migration instant, or backdated per entity? (my choice — one migration-instant timestamp for the whole run; backdating only on an explicit business decision that the fact "was always true")
Primitives or raw SQL for volume. (my choice — raw SQL batches with the conflict queries as a mandatory post-run gate; the primitives for small, surgical migrations)
Batch and transaction scope. (my choice — 500 per batch, one transaction per batch, pausable between batches; never one transaction per migration)
When migrations run. Deploy-pipeline hook or operator-triggered? (my choice — registry check runs on every deploy and fails the pipeline if pending; the backfill itself is operator-triggered inside a window, because data volume — unlike DDL — has no predictable duration)
Invariant monitoring cadence. (my choice — the conflict gauges scrape every minute and alert on any non-zero value with zero tolerance; capacity gauges alert on slope, weekly review of the per-family shape query)
Who owns a family's baseline. Version-per-entity baselines drift per family — someone must own "what normal looks like". (my choice — the team that owns the family's writers owns its baseline; the shape query in the weekly review makes it visible)

No DDL was never "no migrations" — it was a promise to migrate as data, with history intact. The registry says what ran; the conflict queries say it ran clean; Prometheus says it stayed clean.

Series

Part 1 — Storage concept · Part 2 — The abstract repository · Part 3 — Services and the API edge · Part 4 — E2E testing · Part 5 — this one: keeping it alive, and letting it change.

A temporal EAV entity store in PostgresPart 5 — Maintenance and migration