O3: prove SourceEvidence partitioning is feasible with no rewrite and no data-model change

d6400d4d0518a1d00396206fc1c566fdbc1c2cd6 · 2026-09-10 09:44:59 -0700 · Steve

Tested the ATTACH-based conversion end-to-end on a scratch PG 14.23 DB and
disproved the three blockers this work has assumed since 2026-08-26:

- pg_partman is NOT required (native PG14 declarative partitioning suffices)
- the conversion needs ~0 extra disk, NOT ~82GB of scratch for a rewrite
- no composite-PK / Prisma data-model change is needed, because a PK-less
  partitioned parent lets the legacy partition keep its own local PK

Verified on prod (read-only) that nothing has an incoming FK to
SourceEvidence.id, createdAt is NOT NULL, and the only constraints are the PK
and the StagedRecord FK. Verified in code that all four app call sites key on
stagedRecordId or entityType/entityId -- there is no findUnique by id anywhere,
which is also why SourceEvidence_pkey (11GB) shows 0 scans.

Scratch proof: 20k/20k rows preserved with the legacy heap unchanged (no
rewrite), insert routing correct, FK still enforcing, pruning working, both hot
indexes still used per partition, DETACH/re-ATTACH round-trips cleanly (the
payoff: cold archive without deleting history), and all four real app
operations plus cross-partition row movement pass.

Measured partition fan-out on the public-page query across 36 monthly
partitions: execution 0.4-0.6ms and planning within noise of the unpartitioned
baseline, so fan-out is not a concern at realistic partition counts.

Only real prod cost is one ~82GB sequential scan during VALIDATE CONSTRAINT,
held under SHARE UPDATE EXCLUSIVE, which does not block reads or writes.

Still deferred: feasibility is no longer the blocker, necessity is. Execute on
a TK-11363 trigger.

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01HpKbjp2febJ1r8BNTyZwvP

Files touched

A ops/o3-partition-PROVEN-runbook.md

Diff

commit d6400d4d0518a1d00396206fc1c566fdbc1c2cd6
Author: Steve <steve@designerwallcoverings.com>
Date:   Thu Sep 10 09:44:59 2026 -0700

    O3: prove SourceEvidence partitioning is feasible with no rewrite and no data-model change
    
    Tested the ATTACH-based conversion end-to-end on a scratch PG 14.23 DB and
    disproved the three blockers this work has assumed since 2026-08-26:
    
    - pg_partman is NOT required (native PG14 declarative partitioning suffices)
    - the conversion needs ~0 extra disk, NOT ~82GB of scratch for a rewrite
    - no composite-PK / Prisma data-model change is needed, because a PK-less
      partitioned parent lets the legacy partition keep its own local PK
    
    Verified on prod (read-only) that nothing has an incoming FK to
    SourceEvidence.id, createdAt is NOT NULL, and the only constraints are the PK
    and the StagedRecord FK. Verified in code that all four app call sites key on
    stagedRecordId or entityType/entityId -- there is no findUnique by id anywhere,
    which is also why SourceEvidence_pkey (11GB) shows 0 scans.
    
    Scratch proof: 20k/20k rows preserved with the legacy heap unchanged (no
    rewrite), insert routing correct, FK still enforcing, pruning working, both hot
    indexes still used per partition, DETACH/re-ATTACH round-trips cleanly (the
    payoff: cold archive without deleting history), and all four real app
    operations plus cross-partition row movement pass.
    
    Measured partition fan-out on the public-page query across 36 monthly
    partitions: execution 0.4-0.6ms and planning within noise of the unpartitioned
    baseline, so fan-out is not a concern at realistic partition counts.
    
    Only real prod cost is one ~82GB sequential scan during VALIDATE CONSTRAINT,
    held under SHARE UPDATE EXCLUSIVE, which does not block reads or writes.
    
    Still deferred: feasibility is no longer the blocker, necessity is. Execute on
    a TK-11363 trigger.
    
    Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
    Claude-Session: https://claude.ai/code/session_01HpKbjp2febJ1r8BNTyZwvP
---
 ops/o3-partition-PROVEN-runbook.md | 90 ++++++++++++++++++++++++++++++++++++++
 1 file changed, 90 insertions(+)

diff --git a/ops/o3-partition-PROVEN-runbook.md b/ops/o3-partition-PROVEN-runbook.md
new file mode 100644
index 00000000..31c78b5f
--- /dev/null
+++ b/ops/o3-partition-PROVEN-runbook.md
@@ -0,0 +1,90 @@
+# O3 — SourceEvidence partitioning: PROVEN runbook (ATTACH, no rewrite)
+
+- **Ticket:** TK-11363 (successor to TK-10878) · **Authored:** 2026-09-10 · **Status:** feasibility PROVEN on a scratch DB; prod execution still GATED.
+- **Supersedes the cost assumptions in** `ops/o3-partition-and-disk-plan.md` and the TK-11363 opening comment.
+
+## TL;DR — three blockers we believed in are now disproven
+
+| Previously believed | Measured reality |
+|---|---|
+| Needs `pg_partman` (an apt install on prod) | **False.** Native PG14 declarative partitioning is sufficient. No extension, no package install. |
+| Needs ~82 GB scratch to rewrite the table (blocked: only ~44 GB free) | **False.** The ATTACH path does **zero** table rewrite. Extra disk needed ≈ **0**. |
+| Forces `@@id` → composite `(id, createdAt)`, a Prisma data-model change | **False.** Use a **PK-less partitioned parent**; the legacy partition keeps its own local PK. No schema semantics change. |
+
+O3 is therefore **much cheaper than this ticket has assumed since 2026-08-26** — a mostly-online operation whose only heavy step is one sequential scan under a non-blocking lock.
+
+## Why the PK-less parent is the crux
+PostgreSQL only enforces *"the partition key must be a member of every UNIQUE/PK constraint"* **if you declare a PK on the partitioned parent**. If the parent has no PK, each partition keeps its own local PK on `id`. Uniqueness becomes per-partition rather than global — acceptable here because `id` is a **cuid** (globally unique by construction), and, decisively, **nothing in the codebase looks a row up by `id`** (see below). Prisma keeps `@id` in `schema.prisma` as a client-level identifier; it does not require a database PK to exist.
+
+## Prod topology verified (read-only, 2026-09-10)
+- **Incoming FKs referencing `SourceEvidence.id`: NONE.** Nothing breaks.
+- Outgoing FK `SourceEvidence_stagedRecordId_fkey` → `StagedRecord` — supported on partitioned tables (PG12+), verified still enforcing after conversion.
+- `createdAt` is `NOT NULL` — valid partition key.
+- Only two constraints exist on the table: the PK and that FK.
+
+## Complete app surface (4 call sites — none use `id`)
+| Call site | Operation | Key | Partition behavior |
+|---|---|---|---|
+| `apps/workers/src/pipeline.ts:165` | `deleteMany` | `stagedRecordId` | per-partition index scan ✓ |
+| `apps/workers/src/pipeline.ts:166` | `createMany` | — | routes on `createdAt` ✓ |
+| `packages/publisher/src/index.ts:262` | `updateMany` | `stagedRecordId` | per-partition index scan ✓ |
+| `apps/web/src/app/homes/[id]/page.tsx:40` | `findMany` | `entityType`,`entityId` | per-partition index scan ✓ |
+
+**No `findUnique({where:{id}})` anywhere** — which is also why `SourceEvidence_pkey` (11 GB) shows 0 scans.
+
+## Scratch-DB proof (PG 14.23 local; prod is 14.24, same partitioning semantics)
+All green:
+1. Conversion preserved 20,000/20,000 rows; legacy heap unchanged at 4640 kB (**no rewrite**).
+2. INSERT routes to the correct partition.
+3. FK still enforced — a bad `stagedRecordId` is correctly rejected.
+4. Partition pruning works on a `createdAt`-scoped query (legacy partition skipped entirely).
+5. `stagedRecordId` and `(entityType,entityId)` indexes still used, per partition.
+6. **DETACH → 20,000 rows retained in the detached table → re-ATTACH restores them.** This is the payoff: cold-archive without deleting a single row of history.
+7. All four real app operations pass, plus cross-partition row movement on a partition-key UPDATE.
+
+**Fan-out cost: measured, not a concern.** Public-page query across 36 monthly partitions: execution 0.4–0.6 ms, planning within noise of the unpartitioned baseline (cold runs are non-monotonic — 36-monthly measured *cheaper* than 12-quarterly — so partition count is not the driver at these scales).
+
+## The one real prod cost
+`VALIDATE CONSTRAINT` performs **one sequential scan of the ~82 GB table**. It holds `SHARE UPDATE EXCLUSIVE`, which does **not** block reads or writes. `ATTACH PARTITION` afterwards is near-instant because a valid matching constraint lets it skip its own scan. Budget I/O time for the scan; expect no user-visible downtime.
+
+## Procedure (GATED — prod DDL, do not auto-fire)
+```sql
+-- STEP 1 (long, online, non-blocking): bound constraint on the existing table
+ALTER TABLE "SourceEvidence"
+  ADD CONSTRAINT se_legacy_bound CHECK ("createdAt" < '2026-10-01'::timestamptz) NOT VALID;
+ALTER TABLE "SourceEvidence" VALIDATE CONSTRAINT se_legacy_bound;   -- ~82GB seq scan, SHARE UPDATE EXCLUSIVE
+
+-- STEP 2 (fast, ACCESS EXCLUSIVE — run in a low-traffic window)
+BEGIN;
+SET LOCAL lock_timeout = '5s';
+ALTER TABLE "SourceEvidence" RENAME TO "SourceEvidence_p_legacy";
+ALTER INDEX "SourceEvidence_pkey"                    RENAME TO "SourceEvidence_p_legacy_pkey";
+ALTER INDEX "SourceEvidence_entityType_entityId_idx" RENAME TO "SE_p_legacy_entity_idx";
+ALTER INDEX "SourceEvidence_stagedRecordId_idx"      RENAME TO "SE_p_legacy_staged_idx";
+
+CREATE TABLE "SourceEvidence" (
+  LIKE "SourceEvidence_p_legacy" INCLUDING DEFAULTS INCLUDING STORAGE
+) PARTITION BY RANGE ("createdAt");          -- deliberately NO PRIMARY KEY
+
+ALTER TABLE "SourceEvidence"
+  ADD CONSTRAINT "SourceEvidence_stagedRecordId_fkey"
+  FOREIGN KEY ("stagedRecordId") REFERENCES "StagedRecord"(id);
+CREATE INDEX "SourceEvidence_entityType_entityId_idx" ON "SourceEvidence" ("entityType","entityId");
+CREATE INDEX "SourceEvidence_stagedRecordId_idx"      ON "SourceEvidence" ("stagedRecordId");
+
+ALTER TABLE "SourceEvidence" ATTACH PARTITION "SourceEvidence_p_legacy"
+  FOR VALUES FROM (MINVALUE) TO ('2026-10-01'::timestamptz);
+CREATE TABLE "SourceEvidence_p_2026_10" PARTITION OF "SourceEvidence"
+  FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
+COMMIT;
+```
+Then create each forward month ahead of time (a small monthly job, or quarterly bounds to keep partition count low).
+
+## Rollback
+Before STEP 2 — just `ALTER TABLE "SourceEvidence" DROP CONSTRAINT se_legacy_bound;`. After STEP 2 — DETACH the legacy partition, drop the parent, rename the legacy table back. No data is ever copied or deleted, so rollback is catalog-only.
+
+## Prisma drift
+Ship STEP 2 as a **raw-SQL Prisma migration**. `schema.prisma` keeps `@id` on `SourceEvidence` (client-level only). `prisma migrate deploy` applies pending migrations and does not revert drift; avoid `migrate dev` against prod. Same manual-checksum pattern used for the TK-10878 step-1 index drop.
+
+## Why this is still DEFERRED
+Feasibility is no longer the blocker — **necessity** is. Post-TK-11125 growth is 3.19 GB/wk with ~13 weeks of runway and the canary at PASS. Execute when a TK-11363 trigger fires (T1 growth >10 GB/wk sustained, T2 runway <8wk with the DB as primary consumer, or T3 an available maintenance window and Steve wants the architecture landed).

← 82fadf19 ops: preflight now VALIDATES the rollback dump instead of su · back to Homesonspec · O3: add prod apply script for the partition conversion a15775f6 →