Title: perf(database): index WorkerDeployment on (environmentId, status, id) for the deployments list by ericallam · Pull Request #4591 · triggerdotdev/trigger.dev · GitHub
Open Graph Title: perf(database): index WorkerDeployment on (environmentId, status, id) for the deployments list by ericallam · Pull Request #4591 · triggerdotdev/trigger.dev
X Title: perf(database): index WorkerDeployment on (environmentId, status, id) for the deployments list by ericallam · Pull Request #4591 · triggerdotdev/trigger.dev
Description: What Adds a composite index @@index([environmentId, status, id]) to WorkerDeployment. The public deployments list (GET /api/v1/deployments) filters by status and paginates by id descending. The existing indexes cover (environmentId, createdAt) and the PK, but nothing covers status. So for a status filter Postgres walks back through the environment's deployments discarding non-matching statuses, reading roughly 350 rows for every 1 returned (p99 ~1.1s on the busiest environments). The new index makes the status filter index-satisfied and lets id serve both the cursor range and the ORDER BY id DESC, bounding the read to a single page. Full composite (not partial) because callers filter by arbitrary status values with no single dominant one. Query SELECT ... FROM "WorkerDeployment" WHERE "environmentId" = $1 AND "status" = $2 [AND "id" < $3] ORDER BY "id" DESC LIMIT $4; Source: apps/webapp/app/routes/api.v1.deployments.ts. Evidence Reproduced on an isolated stack: one environment seeded with 7,000 deployments, the filtered status appearing 1 in 333 rows. Before (no index): Seq Scan on "WorkerDeployment" (rows=21) Rows Removed by Filter: 6979 Buffers: shared hit=206 Execution Time: 2.9 ms (+ a sort for id desc) After (with the index): Index Scan Backward using "WorkerDeployment_environmentId_status_id_idx" Index Cond: (environmentId = $1 AND status = $2) Buffers: shared hit=23 Execution Time: 0.43 ms Rows-removed-by-filter drops to 0; buffers 206 -> 23. The cursor (mid-pagination) variant uses the same index with all three predicates as the index condition. A dense/common status keeps the cheap PK backward scan (already fine); the index targets exactly the rare-status paths that were amplified. End-to-end against the running webapp API: ?status=FAILED returns the correct newest-first page and paginates correctly across pages, and the emitted SQL matches the query above. Rollout Index only, CREATE INDEX CONCURRENTLY IF NOT EXISTS in its own migration file. Online-safe under write load. Pre-apply the index in production before the migration deploys, per repo convention (the migration is then a no-op). Rollback: drop the index. No data migration. refs TRI-13171
Open Graph Description: What Adds a composite index @@index([environmentId, status, id]) to WorkerDeployment. The public deployments list (GET /api/v1/deployments) filters by status and paginates by id descending. The exi...
X Description: What Adds a composite index @@index([environmentId, status, id]) to WorkerDeployment. The public deployments list (GET /api/v1/deployments) filters by status and paginates by id descending. The exi...
Opengraph URL: https://github.com/triggerdotdev/trigger.dev/pull/4591
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:f8099eda-df59-230a-180e-0514e42cf02e |
| current-catalog-service-hash | ae870bc5e265a340912cde392f23dad3671a0a881730ffdadd82f2f57d81641b |
| request-id | 937A:1E24B0:63D265:94381C:6AFAF160 |
| html-safe-nonce | 33704bfd0ea71937c3a618991e042a1fe576086d50bf48fb801724d9cd82067b |
| visitor-payload | eyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiI5MzdBOjFFMjRCMDo2M0QyNjU6OTQzODFDOjZBRkFGMTYwIiwidmlzaXRvcl9pZCI6IjI4MTg1NTU1Mzk0MjIzNzYyODgiLCJyZWdpb25fZWRnZSI6ImlhZCIsInJlZ2lvbl9yZW5kZXIiOiJpYWQifQ== |
| visitor-hmac | 2224d947c13127397743a612bc498cf4bb6f6fc946db6bd847d75a89f1217dc2 |
| hovercard-subject-tag | pull_request:4262359221 |
| 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/4591/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 | What Adds a composite index @@index([environmentId, status, id]) to WorkerDeployment. The public deployments list (GET /api/v1/deployments) filters by status and paginates by id descending. The exi... |
| 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