Title: perf(database): index EnvironmentVariableValue.valueReferenceId so secret deletes stop seq-scanning by ericallam · Pull Request #4555 · triggerdotdev/trigger.dev · GitHub
Open Graph Title: perf(database): index EnvironmentVariableValue.valueReferenceId so secret deletes stop seq-scanning by ericallam · Pull Request #4555 · triggerdotdev/trigger.dev
X Title: perf(database): index EnvironmentVariableValue.valueReferenceId so secret deletes stop seq-scanning by ericallam · Pull Request #4555 · triggerdotdev/trigger.dev
Description: Why this change EnvironmentVariableValue.valueReference is an onDelete: SetNull foreign key. Deleting a SecretReference (the env var edit/delete path for secret values) fires the cascade UPDATE ONLY "EnvironmentVariableValue" SET "valueReferenceId" = NULL WHERE $1 = "valueReferenceId". That cascade is scan-shaped: with no index on valueReferenceId, it reads the entire table to find the rows referencing the deleted secret. The parent SecretReference delete does almost no work itself; its latency is dominated by this cascade. Diagnosis EnvironmentVariableValue was indexed on environmentId and (variableId, environmentId), but not on valueReferenceId. The SET NULL cascade therefore did a full sequential scan of the whole table. Two sibling SET NULL cascades on the same delete (OrganizationIntegration.tokenReferenceId, User.mfaSecretReferenceId) are index-backed and stay fast, which isolates the missing index as the cause. Change Add @@index([valueReferenceId]) on EnvironmentVariableValue, created with CREATE INDEX CONCURRENTLY IF NOT EXISTS so prisma migrate deploy stays safe on a live table. Benchmark (local, seeded) Local Postgres seeded with 1,000,000 EnvironmentVariableValue rows, EXPLAIN (ANALYZE, BUFFERS) on the SET NULL cascade with zero matching rows (the worst case: reads the whole table, affects nothing): before after plan Seq Scan (1M rows) Bitmap Index Scan execution 183 ms 2.8 ms In a variant where the secret matched several thousand rows, the parent SecretReference delete's EnvironmentVariableValue_valueReferenceId_fkey trigger dropped from 216 ms to 88 ms (the residual is the heap work of nulling those rows). Expected impact The cascade drops from a full-table sequential scan to a targeted index lookup. The win grows with the table, so the benefit is larger than the seeded numbers above. Risks One extra btree to maintain on EnvironmentVariableValue writes; small, single-column, and it should be pre-created before the migration deploys (per the repo index rules). No behavior change: same rows nulled, no ordering or result-set change, read paths untouched. Companion to the same fix on ProjectAlert.channelId.
Open Graph Description: Why this change EnvironmentVariableValue.valueReference is an onDelete: SetNull foreign key. Deleting a SecretReference (the env var edit/delete path for secret values) fires the cascade UPDATE ONL...
X Description: Why this change EnvironmentVariableValue.valueReference is an onDelete: SetNull foreign key. Deleting a SecretReference (the env var edit/delete path for secret values) fires the cascade UPDATE ONL...
Opengraph URL: https://github.com/triggerdotdev/trigger.dev/pull/4555
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:a59a5996-5ba0-ec72-316e-2ee84d222650 |
| current-catalog-service-hash | ae870bc5e265a340912cde392f23dad3671a0a881730ffdadd82f2f57d81641b |
| request-id | 8C7E:3776CB:3403A8F:4706C8F:6AFAF194 |
| html-safe-nonce | f91ef368aadf194ec4970acda8aaa67272919347091322bd5ee03d59e0f1f25c |
| visitor-payload | eyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiI4QzdFOjM3NzZDQjozNDAzQThGOjQ3MDZDOEY6NkFGQUYxOTQiLCJ2aXNpdG9yX2lkIjoiNTkxMzQxNzIzMjQxODUzMzc4MCIsInJlZ2lvbl9lZGdlIjoiaWFkIiwicmVnaW9uX3JlbmRlciI6ImlhZCJ9 |
| visitor-hmac | 4ee53306e899ce19fe96a6bf2eef7c07b026be572ce3bfa17af67adf0e87ecc9 |
| hovercard-subject-tag | pull_request:4244225984 |
| 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/triggerdotdev/trigger.dev/pull/4555/files |
| twitter:image | https://avatars.githubusercontent.com/u/534?s=400&v=4 |
| twitter:card | summary_large_image |
| og:image | https://avatars.githubusercontent.com/u/534?s=400&v=4 |
| og:image:alt | Why this change EnvironmentVariableValue.valueReference is an onDelete: SetNull foreign key. Deleting a SecretReference (the env var edit/delete path for secret values) fires the cascade UPDATE ONL... |
| og:site_name | GitHub |
| og:type | object |
| hostname | github.com |
| expected-hostname | github.com |
| None | 0178036d8230524a18422872393ab7e708443ae4c2047f758560a2db870c1913 |
| turbo-cache-control | no-preview |
| diff-view | unified |
| go-import | github.com/triggerdotdev/trigger.dev git https://github.com/triggerdotdev/trigger.dev.git |
| octolytics-dimension-user_id | 95297378 |
| octolytics-dimension-user_login | triggerdotdev |
| octolytics-dimension-repository_id | 572570113 |
| octolytics-dimension-repository_nwo | triggerdotdev/trigger.dev |
| octolytics-dimension-repository_public | true |
| octolytics-dimension-repository_is_fork | false |
| octolytics-dimension-repository_network_root_id | 572570113 |
| octolytics-dimension-repository_network_root_nwo | triggerdotdev/trigger.dev |
| 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 | a85e96a83bed7871993e6b1ffda24105928eee17 |
| ui-target | full |
| theme-color | #1e2327 |
| color-scheme | light dark |
Links:
Viewport: width=device-width