True declarative table partitioning for nodes/edges (current vs historical) — deferred from #385

[CheeryProgrammer]

Deferred from #385. kb compact (VACUUM ANALYZE + current/historical report) shipped as the safe interim bloat tool; this tracks the real table-partitioning rewrite, to revisit only when the historical tail is genuinely large (10M+ superseded rows).

Why deferred

• The real risk is correctness, not data size: converting a populated table to declarative partitioning is a rewrite (create partitioned table → copy → swap) that must faithfully recreate every index, unique constraint, and FK. A partitioned table also forces the partition key into every unique index — nodes_identity_current_uniq (scope_key, canonical_name) WHERE status='current' and edges_current_uniq need rework, and edges' from_id/to_id FKs to a partitioned nodes need re-establishing.
• Partitioning + current-only partial indexes + superseded_at index #230's current-tier partial indexes already keep the hot path small regardless of historical depth, so the practical benefit at current scale is marginal.
• Supersession flips status current→historical, which would move rows across partitions on every update (supported since PG 11, but with overhead).

Scope when picked up

• Design the partition key (status, or a bool is_current), and the unique-constraint / FK implications.
• A one-time migration that does not block boot — an explicit kb partition maintenance command (backup-first, --force), or a documented maintenance-window procedure. Never an auto-boot migration.
• Verify as-of (Temporal recall: view an entity's relationships as of a past date #200) and retention (Retention policy (auto-purge aged history) + at-rest encryption (follow-up to #272) #363) queries still work across partitions.
• Keep it opt-in; a single-user minimal deployment must not need it.

Meanwhile

kb compact reclaims superseded-row bloat safely (non-blocking VACUUM ANALYZE). Run it after kb sweep-retention.

P2, area:scale.

🤖 Generated with Claude Code