Title: fix(db): index large-value workflow_id FKs so workflow deletes don't seq-scan by TheodoreSpeaks · Pull Request #6136 · simstudioai/sim · GitHub
Open Graph Title: fix(db): index large-value workflow_id FKs so workflow deletes don't seq-scan by TheodoreSpeaks · Pull Request #6136 · simstudioai/sim
X Title: fix(db): index large-value workflow_id FKs so workflow deletes don't seq-scan by TheodoreSpeaks · Pull Request #6136 · simstudioai/sim
Description: Summary execution_large_values.workflow_id and execution_large_value_references.workflow_id are ON DELETE SET NULL FKs (since 0212) with no index leading on that column Postgres runs the SET NULL action as an AFTER ROW trigger — UPDATE ... SET workflow_id = NULL WHERE workflow_id = $1 once per deleted parent row — so every DELETE FROM workflow seq-scans both tables per workflow The cost is proportional to table size x workflows deleted per statement, so it was invisible while the retention job kept the archived-workflow backlog small. That job last dispatched in prod on 2026-05-22 (per trigger.dev run history); whenever it resumes it faces ~10 weeks of accumulated backlog, and those deletes would blow the statement timeout 0278 adds an index on both columns, built CONCURRENTLY below an embedded COMMIT per the runner convention in packages/db/scripts/migrate.ts Neither index serves an app query — both exist to make the referential trigger index-driven Type of Change Bug fix Testing Applied the migration against a local DB: both indexes build and report indisvalid = t, and re-running the file is a no-op (already exists, skipping) — required since everything after the embedded COMMIT runs in autocommit and replays on failure. EXPLAIN on the trigger's predicate now shows Index Scan using execution_large_values_workflow_id_idx (forced with enable_seqscan = off; the local table only has ~2.4k rows). bun run check:migrations origin/staging, bun run lint, and the full audit suite (check:boundaries, check:api-validation:strict, check:utils, check:zustand-v5, check:react-query, check:client-boundary, check:bare-icons, check:icon-paths, check:realtime-prune, skills:check, agent-stream-docs:check) all pass. bun run type-check exits 0. Note for deploy: CONCURRENTLY does two table passes and waits out in-flight transactions, so the build will run for a while on prod-sized tables — worth checking indisvalid after, since a failed concurrent build leaves an INVALID index that the next IF NOT EXISTS silently skips. 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 execution_large_values.workflow_id and execution_large_value_references.workflow_id are ON DELETE SET NULL FKs (since 0212) with no index leading on that column Postgres runs the SET NULL ...
X Description: Summary execution_large_values.workflow_id and execution_large_value_references.workflow_id are ON DELETE SET NULL FKs (since 0212) with no index leading on that column Postgres runs the SET NULL ...
Opengraph URL: https://github.com/simstudioai/sim/pull/6136
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:1a36fede-1aff-ccc6-1809-fe1decac30f6 |
| current-catalog-service-hash | ae870bc5e265a340912cde392f23dad3671a0a881730ffdadd82f2f57d81641b |
| request-id | 8C32:19FA2E:901CC0:CC526C:6AFB5077 |
| html-safe-nonce | 49ea6f5bf7acba6e2267ed6b5259862d473ee1e6fdb2be24a8f1b0e729eaca48 |
| visitor-payload | eyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiI4QzMyOjE5RkEyRTo5MDFDQzA6Q0M1MjZDOjZBRkI1MDc3IiwidmlzaXRvcl9pZCI6IjQzMDc5NTU4MzcyNDM3MDc1MTEiLCJyZWdpb25fZWRnZSI6ImlhZCIsInJlZ2lvbl9yZW5kZXIiOiJpYWQifQ== |
| visitor-hmac | a67947912a58d6789e6ef89b48486b4e9e7090ec1d0aa6714dea750e651ec0a7 |
| hovercard-subject-tag | pull_request:4181278496 |
| 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/6136/files |
| twitter:image | https://avatars.githubusercontent.com/u/19564703?s=400&v=4 |
| twitter:card | summary_large_image |
| og:image | https://avatars.githubusercontent.com/u/19564703?s=400&v=4 |
| og:image:alt | Summary execution_large_values.workflow_id and execution_large_value_references.workflow_id are ON DELETE SET NULL FKs (since 0212) with no index leading on that column Postgres runs the SET NULL ... |
| og:site_name | GitHub |
| og:type | object |
| hostname | github.com |
| expected-hostname | github.com |
| None | 7122bb414b542dba14d71a3f0b9f6fd2abce715b9f5912d082800563e6c806d1 |
| 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 full-width |
| disable-turbo | true |
| browser-stats-url | https://api.github.com/_private/browser/stats |
| browser-errors-url | https://api.github.com/_private/browser/errors |
| release | cbf66c024bb6d10e6955baa7413c54cf13a201bb |
| ui-target | full |
| theme-color | #1e2327 |
| color-scheme | light dark |
Links:
Viewport: width=device-width