Title: perf(webapp): avoid unindexed fileId scan in get-background-worker-by-version by d-cs · Pull Request #4245 · triggerdotdev/trigger.dev · GitHub
Open Graph Title: perf(webapp): avoid unindexed fileId scan in get-background-worker-by-version by d-cs · Pull Request #4245 · triggerdotdev/trigger.dev
X Title: perf(webapp): avoid unindexed fileId scan in get-background-worker-by-version by d-cs · Pull Request #4245 · triggerdotdev/trigger.dev
Description: What The GET /api/v1/projects/:projectRef/background-workers/:envSlug/:version endpoint loaded each file's tasks through the nested files.tasks relation. Prisma resolves that as a separate query: SELECT id, slug, "fileId" FROM "BackgroundWorkerTask" WHERE "fileId" IN (...) BackgroundWorkerTask.fileId is not indexed — the FK constraint exists, but Postgres does not auto-create an index for foreign keys — so on a large table this can only run as a sequential scan, which gets progressively slower as the table grows and was observed taking minutes per call in production. The loader already loads every task for the worker via tasks: true, which uses the indexed workerId relation, and those rows already include fileId. This PR groups task slugs by fileId in memory from that already-loaded data and drops the files.tasks include entirely. Behavior change (latent bug fix) The response shape is unchanged, but there is a semantic correction for source files reused across worker versions (files are de-duplicated by @@unique([projectId, contentHash]), so one file row can be linked to many workers). Before: file.tasks came from the BackgroundWorkerFile.tasks relation, i.e. every BackgroundWorkerTask with that fileId — across all workers sharing the file. So a worker's manifest could list tasks it doesn't actually have. After: file.tasks is grouped from the queried worker's own tasks, so it reflects only that worker version's tasks. Verified on a local DB: 460 files are referenced by tasks from more than one worker; of 6819 (worker, file) pairs, 6 differ — all one file where the old union leaked a task slug (cancellation-test) into worker versions that never had it. The new per-worker behavior is the correct one for a worker-version manifest. (Thanks to the automated review for flagging this.) Analysis Captured the exact SQL before/after by instrumenting Prisma against real data (a worker with 62 files): Before: 5 statements, including the WHERE "fileId" IN (...) scan. After: 4 statements; the fileId query is gone and the other four are identical. EXPLAIN of the two access paths: Before WHERE "fileId" IN (...) Seq Scan on "BackgroundWorkerTask" Filter: ("fileId" = ANY (...)) -- reads the whole table, scales with table size After WHERE "workerId" IN (...) Index Scan using "BackgroundWorkerTask_workerId_slug_key" Index Cond: ("workerId" = ...) -- bounded by matching rows, scale-independent No new index is required: the workerId access path is already covered by the existing BackgroundWorkerTask_workerId_slug_key unique index. Testing pnpm run typecheck --filter webapp passes. Query capture + EXPLAIN performed against a local database seeded with real worker/file/task data.
Open Graph Description: What The GET /api/v1/projects/:projectRef/background-workers/:envSlug/:version endpoint loaded each file's tasks through the nested files.tasks relation. Prisma resolves that as a separate quer...
X Description: What The GET /api/v1/projects/:projectRef/background-workers/:envSlug/:version endpoint loaded each file's tasks through the nested files.tasks relation. Prisma resolves that as a separate ...
Opengraph URL: https://github.com/triggerdotdev/trigger.dev/pull/4245
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:f9a7e71f-d6ba-c827-4684-d2dd28eae285 |
| current-catalog-service-hash | ae870bc5e265a340912cde392f23dad3671a0a881730ffdadd82f2f57d81641b |
| request-id | 8236:2B9148:62F6DF:939DF1:6AFAF2D3 |
| html-safe-nonce | f3acc9819b7d3ac5dbbb5ee9f02e90bf18f84fb2bf7286594dec1676dd76cfa8 |
| visitor-payload | eyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiI4MjM2OjJCOTE0ODo2MkY2REY6OTM5REYxOjZBRkFGMkQzIiwidmlzaXRvcl9pZCI6IjYxOTY4MjUyMzY0NzAxNjYyMjciLCJyZWdpb25fZWRnZSI6ImlhZCIsInJlZ2lvbl9yZW5kZXIiOiJpYWQifQ== |
| visitor-hmac | 0d1ef8f075c066d76c95659965d688a06cdd54394e16943789e8f911d24cc1cf |
| hovercard-subject-tag | pull_request:4045435826 |
| 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/4245/files |
| twitter:image | https://avatars.githubusercontent.com/u/45313566?s=400&v=4 |
| twitter:card | summary_large_image |
| og:image | https://avatars.githubusercontent.com/u/45313566?s=400&v=4 |
| og:image:alt | What The GET /api/v1/projects/:projectRef/background-workers/:envSlug/:version endpoint loaded each file's tasks through the nested files.tasks relation. Prisma resolves that as a separate quer... |
| 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