Title: perf(webapp,database): index the newest-task-version lookup by ericallam · Pull Request #4518 · triggerdotdev/trigger.dev · GitHub
Open Graph Title: perf(webapp,database): index the newest-task-version lookup by ericallam · Pull Request #4518 · triggerdotdev/trigger.dev
X Title: perf(webapp,database): index the newest-task-version lookup by ericallam · Pull Request #4518 · triggerdotdev/trigger.dev
Description: Implementing PlanetScale Insights improvement. Summary Validating a schedule (creating or updating one through the API or the dashboard, and deploying a project that declares schedules) looks up the newest version of a task by slug. That lookup reads every version of the task and sorts them to return one. A project gains a row per task on every deploy, so the work grows with the project's age: the oldest projects pay the most, and dev-mode redeploys make it worse. This was picked because it was the largest single consumer of database time on the schedules path, and the fix is a sort key with no index behind it. Fix BackgroundWorkerTask is indexed on (projectId, slug), which serves the equality but not the ORDER BY createdAt DESC. Postgres seeks the index, then bitmap-scans and top-N sorts the whole group to produce a single row. Adding createdAt to the index lets it scan backward and stop at the first row. The same call site also selected all 21 columns, including five JSON blobs, to read one field (triggerSource), so it now selects that field alone. Benchmark Local Postgres 17, 997,000 seeded rows / 748 MB, group sizes chosen to match the distribution seen in production. Group size Before After 15,000 versions of one task 11.118 ms, 1,510 buffers, 15,000 rows scanned 0.027 ms, 4 buffers, 1 row 2,000 versions of one task 2.081 ms, 1,455 buffers, 2,000 rows scanned 0.022 ms, 4 buffers, 1 row before: Limit -> Sort (top-N heapsort) -> Bitmap Heap Scan after: Limit -> Index Scan Backward using BackgroundWorkerTask_projectId_slug_createdAt_idx An ascending index scanned backward is enough here, so no descending index is needed. Impact and risk Real-world gain lands between the two rows above and scales with how many deploys a project has accumulated. Projects with few deploys will see little change, since there is barely anything to sort. The new index costs noticeably more than the existing two-column one: 43 MB against 7.3 MB on the benchmark rig. Adding createdAt makes every key unique, which defeats btree deduplication, so this is a real disk and write cost rather than a rounding error. Writes to this table happen at deploy time, not on the run path, so the write amplification is acceptable. The existing (projectId, slug) index is now a redundant prefix and could be dropped, but this PR keeps it so index usage can be observed before removing it. Behavior is unchanged: same predicate, same ordering, same row returned. The narrowed select is the only code change, and the field it keeps is the only one the caller read. Deploy note: the migration is 20260806100000_add_background_worker_task_project_id_slug_created_at_index and uses CREATE INDEX CONCURRENTLY IF NOT EXISTS, so it can be pre-applied by hand before the deploy.
Open Graph Description: Implementing PlanetScale Insights improvement. Summary Validating a schedule (creating or updating one through the API or the dashboard, and deploying a project that declares schedules) looks up th...
X Description: Implementing PlanetScale Insights improvement. Summary Validating a schedule (creating or updating one through the API or the dashboard, and deploying a project that declares schedules) looks up th...
Opengraph URL: https://github.com/triggerdotdev/trigger.dev/pull/4518
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:25b96834-b48e-3329-a201-396dbbf3f168 |
| current-catalog-service-hash | ae870bc5e265a340912cde392f23dad3671a0a881730ffdadd82f2f57d81641b |
| request-id | 9B82:1CA15F:43122D8:5ACE736:6AFAF696 |
| html-safe-nonce | 91780afec2dd200502e42f093a25a3cf4c6068a37c60f286dd8797fac56b9b65 |
| visitor-payload | eyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiI5QjgyOjFDQTE1Rjo0MzEyMkQ4OjVBQ0U3MzY6NkFGQUY2OTYiLCJ2aXNpdG9yX2lkIjoiMjEwMjMyNzYwOTE4NTIwMzg2MiIsInJlZ2lvbl9lZGdlIjoiaWFkIiwicmVnaW9uX3JlbmRlciI6ImlhZCJ9 |
| visitor-hmac | 4603899a06891b1b1bdbeb7f8ef4320cefc55eb4e7dc668cd13cfb607f0d6b4c |
| hovercard-subject-tag | pull_request:4219939668 |
| 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/4518/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 | Implementing PlanetScale Insights improvement. Summary Validating a schedule (creating or updating one through the API or the dashboard, and deploying a project that declares schedules) looks up th... |
| 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