Title: perf(database): index ProjectAlert.channelId so alert-channel deletes stop seq-scanning by ericallam · Pull Request #4554 · triggerdotdev/trigger.dev · GitHub
Open Graph Title: perf(database): index ProjectAlert.channelId so alert-channel deletes stop seq-scanning by ericallam · Pull Request #4554 · triggerdotdev/trigger.dev
X Title: perf(database): index ProjectAlert.channelId so alert-channel deletes stop seq-scanning by ericallam · Pull Request #4554 · triggerdotdev/trigger.dev
Description: Why this change Deleting a ProjectAlertChannel fires the FK cascade DELETE FROM ONLY "ProjectAlert" WHERE $1 = "channelId". That cascade is scan-shaped: with no index on channelId, it reads the entire ProjectAlert table to find the few child rows belonging to the deleted channel. The parent DELETE ProjectAlertChannel does almost no work itself; its latency is dominated by this cascade. ProjectAlert is append-heavy and grows over time, so the scan cost only increases. Diagnosis ProjectAlert had no index on channelId (only pkey + a friendlyId unique). The cascade therefore did a full sequential scan of the whole table. The sibling ProjectAlertStorage cascade on the same delete is index-backed and stays fast, which isolates the missing index as the cause. Change Add @@index([channelId]) on ProjectAlert, 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 ProjectAlert rows across 50 channels (~20k rows per channel), EXPLAIN (ANALYZE, BUFFERS) on the cascade delete: before after plan Seq Scan (1M rows) Bitmap Index Scan direct child delete 740 ms 22 ms parent delete ProjectAlert_channelId_fkey trigger 77.7 ms 23.8 ms Expected impact The cascade drops from a full-table sequential scan to a targeted index lookup. The win grows with the table: the more rows in ProjectAlert, the more a scan costs and the more the index saves, so the benefit is larger than the seeded numbers above. Risks One extra btree to maintain on every ProjectAlert insert; acceptable for a single-column index on a high-insert table, and it should be pre-created before the migration deploys (per the repo index rules). No behavior change: no rows orphaned, no ordering or result-set change, read paths untouched. Follow-up ProjectAlert's other cascade FK columns (projectId, environmentId, workerDeploymentId) are also unindexed, but their parents are soft-deleted rather than physically removed, so those cascades do not currently fire. Lower priority unless a hard-delete path is introduced.
Open Graph Description: Why this change Deleting a ProjectAlertChannel fires the FK cascade DELETE FROM ONLY "ProjectAlert" WHERE $1 = "channelId". That cascade is scan-shaped: with no index on channel...
X Description: Why this change Deleting a ProjectAlertChannel fires the FK cascade DELETE FROM ONLY "ProjectAlert" WHERE $1 = "channelId". That cascade is scan-shaped: with no ...
Opengraph URL: https://github.com/triggerdotdev/trigger.dev/pull/4554
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:e9a8ee04-7462-2b6d-1925-b13cead4d148 |
| current-catalog-service-hash | ae870bc5e265a340912cde392f23dad3671a0a881730ffdadd82f2f57d81641b |
| request-id | D4DA:C0C1E:1B9150:2739D7:6AFAF1E7 |
| html-safe-nonce | 5c40d65591aaf1709506c7826425c086c525ec81edd559efb7607d38b43eaa9d |
| visitor-payload | eyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiJENERBOkMwQzFFOjFCOTE1MDoyNzM5RDc6NkFGQUYxRTciLCJ2aXNpdG9yX2lkIjoiNjk5MTc5MTQ5NjQ3NDg0OTc2NyIsInJlZ2lvbl9lZGdlIjoiaWFkIiwicmVnaW9uX3JlbmRlciI6ImlhZCJ9 |
| visitor-hmac | 1b458fed5c6c6c6a722e92f32652279e8c033b218bc25dfe2e2edc76e240f80e |
| hovercard-subject-tag | pull_request:4244163467 |
| 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/4554/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 Deleting a ProjectAlertChannel fires the FK cascade DELETE FROM ONLY "ProjectAlert" WHERE $1 = "channelId". That cascade is scan-shaped: with no index on channel... |
| 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