[object Object]

← back to Homesonspec

db(homesonspec): guard TK-10878 index-drop with lock_timeout=3s; document CONCURRENTLY-vs-Prisma-txn tradeoff + concurrent-rebuild rollback (codex review)

63fa11854f94be95ac0e5f083a437bf6b890bffa · 2026-08-26 10:17:55 -0700 · Steve

Files touched

Diff

commit 63fa11854f94be95ac0e5f083a437bf6b890bffa
Author: Steve <steve@designerwallcoverings.com>
Date:   Wed Aug 26 10:17:55 2026 -0700

    db(homesonspec): guard TK-10878 index-drop with lock_timeout=3s; document CONCURRENTLY-vs-Prisma-txn tradeoff + concurrent-rebuild rollback (codex review)
---
 .../migration.sql                                             | 11 +++++++++--
 1 file changed, 9 insertions(+), 2 deletions(-)

diff --git a/packages/database/prisma/migrations/20260826165959_drop_unused_validationevent_ruleid_passed_idx/migration.sql b/packages/database/prisma/migrations/20260826165959_drop_unused_validationevent_ruleid_passed_idx/migration.sql
index 74a5bb8d..22070a09 100644
--- a/packages/database/prisma/migrations/20260826165959_drop_unused_validationevent_ruleid_passed_idx/migration.sql
+++ b/packages/database/prisma/migrations/20260826165959_drop_unused_validationevent_ruleid_passed_idx/migration.sql
@@ -2,6 +2,13 @@
 -- Evidence (TK-10878, pg_stat_user_indexes on homesonspec prod 2026-08-26):
 --   idx_scan = 0 over the table's lifetime; size ~508 MB on 51M rows.
 -- Nothing FK-references ValidationEvent; the hot @@index([stagedRecordId]) (679k scans) is retained.
--- DROP INDEX (non-concurrent) is a fast catalog op with only a brief lock; it does not scan the table.
--- Reversible: re-add @@index([ruleId, passed]) to the ValidationEvent model and migrate.
+-- DROP INDEX (non-concurrent) does NOT scan the table, but it briefly takes ACCESS EXCLUSIVE on
+-- ValidationEvent. To avoid queuing behind/ahead of live traffic we bound the lock wait: if the lock
+-- can't be acquired in 3s the migration fails cleanly (single DDL = no partial state) and we retry in
+-- a quieter moment. We deliberately do NOT use DROP INDEX CONCURRENTLY: Prisma wraps each migration in
+-- a transaction and CONCURRENTLY cannot run inside a transaction block. Run in a low-traffic window.
+-- Reversible: re-add @@index([ruleId, passed]) to the model and recreate MANUALLY with
+--   CREATE INDEX CONCURRENTLY "ValidationEvent_ruleId_passed_idx" ON "ValidationEvent" ("ruleId","passed");
+-- (never a plain non-concurrent rebuild on 51M rows).
+SET lock_timeout = '3s';
 DROP INDEX "ValidationEvent_ruleId_passed_idx";

← bd19db5f db(homesonspec): drop unused ValidationEvent(ruleId,passed)  ·  back to Homesonspec  ·  docs(homesonspec): runbook reflects measured reality — index 26657d75 →