Skip to content

CORE-199 claim record index rollout

Native PostgreSQL claimed discard deletes every claim generation of each discarded row from memory_pending_claims through the index idx_pending_claims_record_c, keyed by the exact owner (tenant_id, record_id, scope_path). Until a valid index of exactly that shape exists, discard refuses with CORE-199 claim record index missing or invalid before it changes anything. Flush does not need this index. Application startup never creates it. This page is the operator procedure. It is not a deployment record, and it makes no staging latency claim.

Artifact: scripts/migrations/core199_claim_record_index.sql, which follows the pattern of scripts/migrations/core199_pending_index.sql.

Why the index is required

The claim ledger keeps published and discarded generations. Without a record-keyed index, the generation delete costs the ledger, not the rows discarded. On a disposable PostgreSQL 16, discarding 8,192 rows against 2,100,000 claims in one tenant, 100,000 of them in the discarded rows' scope:

  • custom plan: a sequential scan of the tenant's ledger, 0.62 s;
  • forced generic plan: one (tenant_id, scope_path) index scan per row, each rejecting 99,999 rows, 50.8 s and 8.24 million buffers.

With the index, the same delete runs as one index probe per row under either plan: 18.6 ms and 49,152 buffers, with no row rejected by a filter. The lifecycle also disables sequential scans, hash joins and merge joins for its own transaction only. Those settings steer the planner toward index probes; they do not forbid other plans, and PostgreSQL may still choose a scan of history when no usable index path exists.

Pre-validation

  1. Confirm the target schema, and that the role owns memory_pending_claims and holds CREATE on the schema and TEMP on the database.
  2. Look for any existing index with the name: SELECT indisvalid, pg_get_indexdef(indexrelid) FROM pg_index WHERE indexrelid = to_regclass('<schema>.idx_pending_claims_record_c'); If one exists and is invalid or has a different shape, stop. The script refuses it too and never drops it.
  3. Look for long transactions. CREATE INDEX CONCURRENTLY waits for every snapshot older than the build: SELECT pid, xact_start, state FROM pg_stat_activity WHERE xact_start < now() - interval '5 minutes';
  4. Check that there is disk headroom. Unlike the pending index, this index is not partial: it holds one entry per claim generation, so it grows with the ledger.

Run

psql "$POSTGRES_DSN" -X -v scope_schema=public -f scripts/migrations/core199_claim_record_index.sql

Do not pass -1 or wrap the script in a transaction: CONCURRENTLY refuses to run inside one. ON_ERROR_STOP is set, so the first error ends the run. The script verifies the index shape before and after the build. Running it again is a no-op that keeps the same index.

Post-validation

  • The script exits 0. Its final verifier checks validity, readiness, liveness, the btree access method, the (tenant_id, record_id, scope_path) keys in that order with "C" collation and text_ops, that the index is neither unique nor partial, and that it has no expressions.
  • Monitor pg_stat_progress_create_index during the build.
  • After ANALYZE memory_pending_claims, an EXPLAIN of the discard generation delete names idx_pending_claims_record_c and reports no rows removed by filter.

Failure and rollback

  • If the build is interrupted (cancelled, killed, or out of disk), PostgreSQL can leave an invalid index under the name. Discard refuses it. Investigate the cause, run DROP INDEX CONCURRENTLY <schema>.idx_pending_claims_record_c, and re-run the script.
  • To roll back, run DROP INDEX CONCURRENTLY <schema>.idx_pending_claims_record_c. Native claimed discard then refuses again, fail closed. Rollback never turns on a scan over claim history.
  • No row data changes during the rollout or the rollback.

Scope

The index bounds the claimed discard of the private native graph-only path. The row-at-a-time cascade in postgres_native_node_delete (per-row claimed discard, taken when recall is configured or a batch spans tenants, and native cascading node deletes) deletes claim generations by the same key; it also requires the index and runs as index probes. Per-row discard refuses before it locks a row. A node delete refuses when it reaches the claim-generation delete, so cascading a node that does not exist still succeeds. That delete, and so the refusal, runs only when a memory_pending_claims table resolves on the connection's search_path; without one, a node delete neither checks nor needs the index. Rolling the index back therefore makes native cascading node deletes refuse only where that table resolves. Public native lifecycle admission stays closed, and the 1 s lifecycle deadline still applies.