Runbook — `facts.fact_revision` partition adoption

Applies to: the Vulnerabilities service (stellaops_vuln), migration 011_fact_revision_range_partitioning.sql. Status: current. Raised by SPRINT_20260830_001 P13-10.

What the migration does

facts.fact_revision has been PARTITION BY RANGE (changed_at) since the 001 baseline and had exactly one child — fact_revision_default. Migration 011 gives it a real retention mechanism: a monthly runway, a 24-month window, and a pruner that drops whole partitions.

The rows are not moved. On a populated database the migration:

takes ACCESS EXCLUSIVE on the parent, so the span it measures cannot change under it;
measures the default partition’s first and last month;
DETACHes the default (metadata-only, instant);
renames it to fact_revision_legacy_pre_<YYYY_MM>;
re-ATTACHes it under bounds covering the span it already holds (one verification scan, no rewrite);
creates a fresh, empty fact_revision_default as the catch-all;
opens a 12-months-back / 12-months-ahead runway.

On a fresh database, or one whose default is already empty, step 2 finds nothing and the adoption returns without touching anything.

Expected cost on first boot after deploy. One aggregate scan plus one validation scan of whatever the default holds, inside the startup migration transaction. Measured on the lab estate 2026-09-17: 5,444 MB / 17,546,474 rows. Budget a first-boot stall on the order of a couple of minutes and do not restart the service through it — a restart rolls the transaction back and the next boot starts again from the same place, which is safe but wastes the window.

Verifying afterwards

SELECT c.relname, pg_get_expr(c.relpartbound, c.oid)
FROM pg_inherits i JOIN pg_class c ON c.oid = i.inhrelid
WHERE i.inhparent = 'facts.fact_revision'::regclass
ORDER BY 1;

SELECT count(*) AS stranded FROM facts.fact_revision_default;   -- expect 0

doctor.vulnerabilities.retention.fact-revision-runway reports both numbers and warns below three months of forward runway, or on any row stranded in the default.

The two refusals, and what to do about them

Both raise SQLSTATE 55000 and roll the whole migration back. Neither can be resolved by re-running; they mean the database is in a shape the migration deliberately does not guess at.

`the DEFAULT partition holds rows in [a, b), which overlaps the already-attached partition …`

A ranged child already covers months the default also holds rows for. Adoption cannot attach an overlapping range, and PostgreSQL would refuse anyway.

This normally means a previous adoption was interrupted after its ATTACH but before its CREATE … DEFAULT, and writes then landed in a default that was re-created by hand. Decide per estate which child should own the overlapping months, then move the smaller side with an explicit INSERT … SELECT / DELETE under a window before re-running. Do not widen the existing child’s bounds in place; PostgreSQL does not support it and the workaround (detach/re-attach) is the same operation with fewer checks.

`facts.<name> already exists while the DEFAULT partition still holds N rows`

The rename target is taken. Either a previous adoption was interrupted between its RENAME and its ATTACH, or something else in the database already owns that name.

Check first whether the existing relation is already an attached child of facts.fact_revision with the bounds the migration wants. If it is, the previous run got further than it looks and the remaining work is only to re-create the empty default:

CREATE TABLE facts.fact_revision_default PARTITION OF facts.fact_revision DEFAULT;

If it is a detached heap, confirm its contents against the default before deciding which to keep. Do not drop either side to make the migration pass — both hold revision history, and facts.fact_revision has no foreign key to facts.fact_current, so nothing else in the schema will tell you the rows are gone.

What the pruner will and will not drop

facts.prune_fact_revision_partitions(interval) selects children by their measured upper bound, read from pg_get_expr(relpartbound), not by rebuilding a name from a month. This is deliberate: the adopted legacy child spans several months, so a name-keyed pruner would never match it and its rows would be retained forever.

It never drops fact_revision_default. The default is the catch-all that makes an out-of-runway mirror import recoverable instead of lost; anything that settles there is retained until an operator moves it into a ranged child. That is what the Doctor check’s default_rows measurement is for.

Runbook — facts.fact_revision partition adoption