Title: perf(db): drop 0287's two zero-row scans from the ACCESS EXCLUSIVE hold by waleedlatif1 · Pull Request #6609 · simstudioai/sim · GitHub
Open Graph Title: perf(db): drop 0287's two zero-row scans from the ACCESS EXCLUSIVE hold by waleedlatif1 · Pull Request #6609 · simstudioai/sim
X Title: perf(db): drop 0287's two zero-row scans from the ACCESS EXCLUSIVE hold by waleedlatif1 · Pull Request #6609 · simstudioai/sim
Description: Summary Migration 0287 adds workflow_blocks.error_enabled and then ran three backfill statements. Two of them scanned the whole table to match a jsonb key, data ->> 'errorEnabled', that only ever existed on an unmerged branch. This deletes those two and keeps the one real edge-driven backfill. The two deleted statements could never have matched anything UPDATE "workflow_blocks" SET "error_enabled" = true WHERE "data" ->> 'errorEnabled' = 'true'; UPDATE "workflow_blocks" SET "data" = "data" - 'errorEnabled' WHERE "data" ->> 'errorEnabled' IS NOT NULL; The key was never written by any released version, confirmed two ways: git log origin/main -S"errorEnabled" is empty — the key never shipped. It existed only on an unmerged branch, and the squash that landed the feature (#6458) writes only the column. Every errorEnabled reference in the tree is the Drizzle field mapping to the error_enabled column (packages/db/schema.ts:297), never a key inside the data jsonb. BlockData has no such field, and BlockState.errorEnabled is a sibling of data, never nested inside it. All five persistence writers — workflow-persistence/src/{save,load}.ts and the realtime UPDATE_ERROR_ENABLED / upsert / full-state-replace paths — set the column only. So both statements filtered on data ->> 'errorEnabled', which is unindexable without an expression index and therefore forces a sequential scan that detoasts the jsonb payload of a large, hot table — to match nothing. Why that cost mattered more than it looks Two things compound it: One transaction. drizzle wraps all pending migrations in a single transaction with statement_timeout = 0. The ACCESS EXCLUSIVE lock that ALTER TABLE ... ADD COLUMN takes on workflow_blocks is held from that statement through 0288 and 0289 all the way to COMMIT. Both deleted scans ran inside that lock window, with no statement timeout to cut them off — every reader and writer of workflow_blocks blocks behind them. Migrations run before image promotion. Any stall lands entirely on old-version traffic, so the cost is paid by users on the currently-deployed app. The retained backfill is driven from workflow_edges and resolves blocks through the primary key index, so it never reads or detoasts data. It is by far the cheaper plan and touches only the small subset of blocks that already have an error edge. Verified against a production-shaped database: the deleted statements matched nothing, and the retained one planned as an index-driven join. This lands before the migration has run anywhere it matters, so there is no cleanup debt and nothing to reconcile. Why it is safe to edit an already-merged migration 0287 is already on staging (it landed in #6458). Editing the file is safe because drizzle's postgres-js migrator gates purely on timestamp, not on the file hash (drizzle-orm/pg-core/dialect.cjs: if (!lastDbMigration || Number(lastDbMigration.created_at) < migration.folderMillis)). The hash is stored but never compared, so an environment that already applied 0287 skips the edited file silently rather than failing on a hash mismatch, and an environment that has not yet applied it runs the shortened version. meta/_journal.json is untouched, so ordering against 0288 and 0289 is preserved either way. The retained backfill is load-bearing — it is not part of the cleanup UPDATE "workflow_blocks" AS b SET "error_enabled" = true FROM "workflow_edges" AS e WHERE e."source_block_id" = b."id" AND e."source_handle" = 'error'; Every released version draws the error port with no toggle in front of it, so a block someone already wired an error edge out of has the output on. Defaulting those rows to false would hide a port live workflows route failures through. workflow-block.tsx:754 (Boolean(currentBlock?.errorEnabled || hasErrorConnection)) is the render-time twin of the same rule. This statement keeps its -- migration-safe: acknowledgment and its explanatory comment. Rejected alternatives Adding COMMIT; to break the batch. Shortens the lock hold but risks a wedged deploy. 0289 creates four enum types, a table, and three indexes, none with IF NOT EXISTS (Postgres does not support it on CREATE TYPE at all). With a mid-batch COMMIT, a later failure leaves those objects applied but the journal unwritten, and the replay dies on 42710 duplicate_object — turning a transient stall into a deploy that cannot move without manual intervention. Renumbering 0287, or moving the backfill to a new migration file. Buys zero lock reduction: all pending files share the same transaction, so the scans would still run inside the same ACCESS EXCLUSIVE hold. Batching the deleted UPDATEs, or adding an expression index / CREATE INDEX CONCURRENTLY to support them. Not applicable — the statements are gone. Batching zero matched rows still requires the same full scans to discover there is nothing to do. Rewriting the retained backfill as WHERE EXISTS (...). Avoids duplicate join rows, but a semi-join drives from workflow_blocks instead of primary-key lookups — a worse plan. Left as-is. Scope No COMMIT; introduced, not renumbered, meta/_journal.json untouched, no schema.ts change implied. The retained statement is now correctly the final one, with no dangling --> statement-breakpoint. One residual: a developer machine that ran the unmerged branch may still have data->>'errorEnabled' set locally, which will no longer be migrated onto the column or stripped. This is self-healing — the save path writes data wholesale from the client payload (data: sql`excluded.data` in apps/realtime/src/database/operations.ts) and errorEnabled is a top-level BlockState field, so the next save of that workflow drops the stale key and writes the column. No row outside a developer's own machine can be in that state. Type of Change Bug fix Testing bun run scripts/check-migrations-safety.ts passes — no blocking issues, one acknowledged data-backfill warning on the retained statement. Behavior was verified against a production-shaped database with read-only queries and EXPLAIN. Checklist Code follows project style guidelines Self-reviewed my changes Tests added/updated and passing No new warnings introduced I confirm that I have read and agree to the terms outlined in the Contributor License Agreement (CLA)
Open Graph Description: Summary Migration 0287 adds workflow_blocks.error_enabled and then ran three backfill statements. Two of them scanned the whole table to match a jsonb key, data ->> 'errorEnabled', th...
X Description: Summary Migration 0287 adds workflow_blocks.error_enabled and then ran three backfill statements. Two of them scanned the whole table to match a jsonb key, data ->> 'errorEnab...
Opengraph URL: https://github.com/simstudioai/sim/pull/6609
X: @github
Domain: github.com
| react-profiling | 0 |
| react-import-map | react,react-dom,react-dom/client,react-dom/profiling,react-is,react-reconciler,react/compiler-runtime,react/jsx-dev-runtime,react/jsx-runtime,scheduler@d5750093facf |
| route-pattern | /:user_id/:repository/pull/:id/files(.:format) |
| route-controller | pull_requests |
| route-action | files |
| fetch-nonce | v2:78808d14-8cc6-5e80-0a9f-78551fdff704 |
| current-catalog-service-hash | ae870bc5e265a340912cde392f23dad3671a0a881730ffdadd82f2f57d81641b |
| request-id | A920:3BD2EB:76542:A7FE1:6AFB49A5 |
| html-safe-nonce | ba455d138b910849b9e7e53578882fd8e1e87e73120bc31bc16eb96968b73f2b |
| visitor-payload | eyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiJBOTIwOjNCRDJFQjo3NjU0MjpBN0ZFMTo2QUZCNDlBNSIsInZpc2l0b3JfaWQiOiI4ODI1MDk3ODE0MzI2MDY5NjY5IiwicmVnaW9uX2VkZ2UiOiJpYWQiLCJyZWdpb25fcmVuZGVyIjoiaWFkIn0= |
| visitor-hmac | 5a2e8a276bf2983afefba74b9d3abb759f36bccbf2c79e694d883a07c547a538 |
| hovercard-subject-tag | pull_request:4260548361 |
| github-keyboard-shortcuts | repository,pull-request-list,pull-request-conversation,pull-request-files-changed,copilot |
| google-site-verification | Apib7-x98H0j5cPqHWwSMm6dNU4GmODRoqxLiDzdx9I |
| octolytics-url | https://collector.github.com/github/collect |
| analytics-location | / |
| fb:app_id | 1401488693436528 |
| apple-itunes-app | app-id=1477376905, app-argument=https://github.com/simstudioai/sim/pull/6609/files |
| twitter:image | https://avatars.githubusercontent.com/u/40672544?s=400&v=4 |
| twitter:card | summary_large_image |
| og:image | https://avatars.githubusercontent.com/u/40672544?s=400&v=4 |
| og:image:alt | Summary Migration 0287 adds workflow_blocks.error_enabled and then ran three backfill statements. Two of them scanned the whole table to match a jsonb key, data ->> 'errorEnabled', th... |
| og:site_name | GitHub |
| og:type | object |
| hostname | github.com |
| expected-hostname | github.com |
| None | acf7616d06cc051644705b379878c9bb13dc5e70ecf4ffd9b34140f8ac01d7e1 |
| turbo-cache-control | no-preview |
| diff-view | unified |
| go-import | github.com/simstudioai/sim git https://github.com/simstudioai/sim.git |
| octolytics-dimension-user_id | 199344406 |
| octolytics-dimension-user_login | simstudioai |
| octolytics-dimension-repository_id | 912559512 |
| octolytics-dimension-repository_nwo | simstudioai/sim |
| octolytics-dimension-repository_public | true |
| octolytics-dimension-repository_is_fork | false |
| octolytics-dimension-repository_network_root_id | 912559512 |
| octolytics-dimension-repository_network_root_nwo | simstudioai/sim |
| turbo-body-classes | logged-out env-production page-responsive |
| disable-turbo | true |
| browser-stats-url | https://api.github.com/_private/browser/stats |
| browser-errors-url | https://api.github.com/_private/browser/errors |
| release | 26504db649b58525278f77a52b80ba5f10d5f4de |
| ui-target | full |
| theme-color | #1e2327 |
| color-scheme | light dark |
Links:
Viewport: width=device-width