Follow-up to #230 (partial-index safe slice shipped in migration 0019).
Context
#230 shipped the low-risk half: current-tier partial indexes (WHERE status='current') on edges.from_id, edges.to_id, nodes.project, plus superseded_at partial indexes. That keeps the hot index the size of live memory even as historical rows pile up — without touching table storage.
What's left
The rows themselves still share one heap. At very large history depth (tens of millions of superseded rows) the heap bloats, VACUUM cost rises, and sequential maintenance scans still walk history. The real fix is declarative partitioning: split nodes/edges into current vs historical partitions (or PARTITION BY LIST (status)), so historical rows live in their own physical table.
Why it's not in #230
Converting an existing populated table to a partitioned one requires a table rewrite (create partitioned table → copy → swap), or pg_partman. That is far too risky to auto-apply at boot inside store.Migrate — a failed rewrite mid-migration fails the deploy, and the expand-only/idempotent contract can't safely express a rewrite. Needs a deliberate, possibly out-of-band migration path with its own runbook.
Scope
- Design the partition key (
status, or a boolean is_current) and FK/unique-constraint implications (partitioned tables constrain what unique indexes are allowed — edges_current_uniq, nodes_identity_current_uniq need review).
- A one-time migration strategy that does NOT block boot (opt-in
kb partition command, or a documented maintenance-window migration).
- Keep it opt-in / no default behavior change — a single-user minimal deployment must not need it.
P2, area:store, scale.
🤖 Generated with Claude Code
Follow-up to #230 (partial-index safe slice shipped in migration 0019).
Context
#230 shipped the low-risk half: current-tier partial indexes (
WHERE status='current') onedges.from_id,edges.to_id,nodes.project, plussuperseded_atpartial indexes. That keeps the hot index the size of live memory even as historical rows pile up — without touching table storage.What's left
The rows themselves still share one heap. At very large history depth (tens of millions of superseded rows) the heap bloats, VACUUM cost rises, and sequential maintenance scans still walk history. The real fix is declarative partitioning: split
nodes/edgesintocurrentvshistoricalpartitions (orPARTITION BY LIST (status)), so historical rows live in their own physical table.Why it's not in #230
Converting an existing populated table to a partitioned one requires a table rewrite (create partitioned table → copy → swap), or
pg_partman. That is far too risky to auto-apply at boot insidestore.Migrate— a failed rewrite mid-migration fails the deploy, and the expand-only/idempotent contract can't safely express a rewrite. Needs a deliberate, possibly out-of-band migration path with its own runbook.Scope
status, or a booleanis_current) and FK/unique-constraint implications (partitioned tables constrain what unique indexes are allowed —edges_current_uniq,nodes_identity_current_uniqneed review).kb partitioncommand, or a documented maintenance-window migration).P2, area:store, scale.
🤖 Generated with Claude Code