Skip to content

CORE-73 PostgreSQL edge-index cutover (proposed operator procedure)

Status: design only; no fleet barrier or production migration is implemented or authorized here. This procedure needs deployment-specific review and an authorized maintenance window. Do not run its DDL merely because the read-only preflight passes.

Why a barrier is required

The current default PostgresGraphBackend uses ON CONFLICT (tenant_id, source, target, relationship) and ensure_schema() creates idx_memory_edges_upsert on those four columns (source). A four-key writer loses its conflict arbiter when that index is dropped. Its startup can recreate the index: the generated scaffold calls ensure_schema() when resources open, and the production example calls it in lifespan startup.

The dark scoped writer on PR #345's feat/core73-phaseb-dark-writer branch uses a fifth key, COALESCE(scope_path, 'tenant' || chr(31) || tenant_id). Its explicit edge_upsert_mode="scoped" verifies the exact live five-key arbiter and refuses the old four-key index or another matching scoped arbiter. The default stays tenant_guarded; this is not automatic fleet activation. The disposable SQL contract demonstrates that the old index rejects a sibling tuple even when the scoped index permits it. These are code and scratch-database facts, not evidence that any deployed writer is drained.

Preconditions and evidence record

Record the target cluster, database, schema, deployment revision, owner, maintenance window, rollback owner, and an approved write-barrier mechanism. Resolve every process that can write memory_edges or call PostgresGraphBackend.ensure_schema(): web replicas, background jobs, workers, scheduled jobs, consoles, and older deployments. Include connection pools and in-flight transactions. Current fleet inventory, write-barrier capability, and authorization: UNKNOWN. The generated scaffold does not supply a fleet barrier, and a PostgreSQL connection cannot prove one.

Before scheduling a cutover, provide a separately reviewed way to prevent every startup from recreating the old index after its removal. PR #345's explicit scoped mode skips that index at bootstrap, but the current default mode still creates it, and an older binary cannot honor a new guard. Require a disposable PostgreSQL restart test showing the old index remains absent, plus fleet controls proving stale/default code cannot start during the window. Merely setting a scoped writer on one process is insufficient.

With POSTGRES_DSN supplied through the approved secret mechanism, run symfonic postgres-edge-preflight --schema public (replace public with the exact target schema) from a build containing this command, with the [postgres] extra installed. The CLI uses a read-only, repeatable-read inspection with timeouts; it returns only index metadata, NULL-scope count, duplicate effective-owner group count, and sibling-tuple group count. Require zero duplicate effective-owner groups, expected table/column identity and collations, and an inventory of all indexes on the table. A nonzero NULL-scope count is not itself a duplicate: NULL paths map to tenant root. Investigate unexpected or unsafe indexes, invalid indexes, RLS, missing visibility, or failed CLI exit code before proceeding. The CLI's fleet_writer_state: "UNKNOWN" and migration_authorized: false are intentional (preflight); they cannot be overridden by a green database report.

Minimal missing experiment: on a disposable database and the actual deployment topology, hold the proposed ingress/job barrier, start every configured writer class, and attempt an edge upsert from each. Show that all writes stop or queue, all pre-barrier in-flight transactions finish, no old revision can restart, and writes resume only after explicit release. Capture deployment revision/instance inventory and database session evidence. Repeat with a restart while the old index is absent and sibling tuples exist; the expected outcome is no old-index recreation and no stale four-key write. Until this passes, stop before DDL.

Authorized cutover sequence

Each numbered transition needs an operator decision recorded against the evidence above. Use the real, quoted schema; public below is an example, never an inferred production schema. Execute each concurrent-index command as its own autocommit statement, outside a transaction block or migration tool transaction.

  1. Authorize and engage the fleet write barrier. Stop new edge-producing work at every ingress and queue. Drain in-flight requests, workers, leases, and transactions; confirm no old process can restart and no outstanding edge write can commit. Keep the barrier until step 5 succeeds. Record the platform-specific proof and timestamp. pg_stat_activity can corroborate transaction drain but does not identify every application writer or queued job.
  2. Re-run read-only preflight under the barrier. Require zero duplicate effective-owner groups and inspect exact index names, definitions, indisvalid, and indisready. If the old index is absent unexpectedly, or a five-key/alternate unique arbiter is unexpected, stop and investigate. Check the target table and schema OIDs before any DDL.
  3. Authorize and build the new index. With the barrier held, run the reviewed statement (example schema):
CREATE UNIQUE INDEX CONCURRENTLY idx_memory_edges_scope_upsert
ON public.memory_edges (
    tenant_id, source, target, relationship,
    COALESCE(scope_path, 'tenant' || chr(31) || tenant_id)
);

Do not use IF NOT EXISTS: an invalid same-name index must not look like success. Check pg_stat_progress_create_index and database logs while it runs. After completion, inspect pg_index joined to pg_class/pg_namespace for the exact table and index OIDs, unique, btree, nonpartial, five key columns, expression, valid/ready/live flags, deterministic collations, and expected opclasses. The CLI exposes only part of this metadata; PR #345's src/symfonic/memory/backends/postgres_edge_scoped_writer.py supplies the scoped writer's catalog gate. Preserve the four-key index at this stage. 4. Authorize removal of the old index only after proving bootstrap suppression and continued drain. Run DROP INDEX CONCURRENTLY public.idx_memory_edges_upsert; as a separate autocommit statement. Confirm that the exact old index is absent and the new index remains valid/ready. Check for any alternate four-key unique index or unsafe scoped arbiter; the dark writer refuses them. A concurrent startup using default ensure_schema() can recreate the old index, so recheck after a controlled restart before activation. Any recreation is a stop condition, not a reason to repeat DROP blindly. 5. Authorize activation and release. Deploy only the reviewed revision/configuration that explicitly selects edge_upsert_mode="scoped" and suppresses legacy index bootstrap. Verify each instance's effective mode, startup result, and catalog check on its target database; the search_path-resolved memory_edges table OID must match the DDL target OID. Then release queued writes gradually. In an explicitly approved canary scope, check same-owner increments, root/NULL behavior, sibling inserts, unique-conflict refusals, and direct/cascade delete behavior against disposable-test expectations. Monitor write errors (42P10, 23505, ScopedEdgeIndexNotReady, ScopedEdgeWriteConflict), index state, queue depth, request failures, and unexpected old-index reappearance. No single successful write establishes fleet readiness.

Stop, recovery, and rollback boundary

Before the old index is dropped, retain the barrier and old writer/index if the new build or verification fails. CREATE INDEX CONCURRENTLY can leave an invalid index, possibly still enforcing uniqueness. Inspect its exact schema, table OID, definition, and indisvalid/indisready; after operator approval, remove only that confirmed failed index with DROP INDEX CONCURRENTLY, then resolve duplicate data or DDL cause and retry from preflight. Do not use DROP ... CASCADE, a transaction wrapper, or an automatic cleanup script.

After the old index is dropped but before any sibling tuple is committed, rollback to the old writer is possible only while the barrier is held, after rechecking that no four-key duplicates exist, recreating and validating the old index concurrently, and restoring a compatible bootstrap/revision. This is a new authorized DDL operation, not a simple application rollback. If a scoped sibling tuple has committed, the old four-key index cannot represent the data and the old writer is unsafe. Keep or re-engage the barrier, preserve data, and repair forward with the scoped writer; any data merge/delete needs a separate decision. If any prerequisite or state is UNKNOWN, keep writes stopped and escalate to the database and deployment owners.

This runbook does not establish CORE-71 parity, a completed cutover, or legacy retirement. Those require live deployment evidence and their own acceptance gates.