Title: perf(webapp,database): index RuntimeEnvironment.pauseSource for the billing-limit reconcile tick by ericallam · Pull Request #4590 · triggerdotdev/trigger.dev · GitHub
Open Graph Title: perf(webapp,database): index RuntimeEnvironment.pauseSource for the billing-limit reconcile tick by ericallam · Pull Request #4590 · triggerdotdev/trigger.dev
X Title: perf(webapp,database): index RuntimeEnvironment.pauseSource for the billing-limit reconcile tick by ericallam · Pull Request #4590 · triggerdotdev/trigger.dev
Description: What The billingLimit.reconcileTick worker calls getOrgIdsWithBillingPauseSource() on BILLING_LIMIT_RECONCILE_INTERVAL_MS (~every 90s) to find which orgs currently have billing-limit-paused environments. Two problems: RuntimeEnvironment.pauseSource had no index, so WHERE pauseSource = 'BILLING_LIMIT' was a sequential scan of the whole table on the control-plane primary, every tick. Prisma distinct dedups after fetching, so it read every paused row (thousands) to produce a handful of distinct org ids. This PR: Adds a partial index on RuntimeEnvironment (pauseSource, organizationId) WHERE pauseSource IS NOT NULL. Nearly all rows have pauseSource = null, so the index stays tiny. Second column lets the DB satisfy the distinct-org lookup from the index. Defined in SQL (Prisma can't express partial indexes), matching the existing partial-unique indexes on this model. Switches the query from findMany({ distinct }) to groupBy(["organizationId"]), pushing DISTINCT into the DB so it returns only the distinct orgs. Evidence Correctness — colocated postgresTest (testcontainers, no mocks): multiple BILLING_LIMIT envs in one org collapse to one org id, pauseSource = null envs are excluded, each org id returned once. 5/5 tests in billingLimitReconciliation.test.ts pass. Plan change — EXPLAIN ANALYZE on a synthetic table (200k rows, 5,250 BILLING_LIMIT across ~40 orgs, mirroring the test-side numbers from the investigation): Before (no index) After (partial index) Plan Seq Scan (194,750 rows removed by filter) Bitmap Index Scan on partial index Buffers 1355 51 (index 6 + heap 45) Exec time 6.06 ms 0.59 ms Index size 56 kB vs table 11 MB. The key win: cost now scales with the paused-env count, not total table size, which matters most on prod where the table is far larger. Rollout & rollback Index: CREATE INDEX CONCURRENTLY IF NOT EXISTS, in its own migration file. Pre-apply the index manually on the control-plane primary before deploying the migration (the migration is a no-op if the index already exists). Query change is behavior-equivalent (same distinct org set), so no flag needed. Rollback: revert the deploy and drop the index. No data migration either direction. Notes / limitations The planner uses a Bitmap Heap Scan, so organizationId is still read from the heap (45 blocks for the matched rows only, not the whole table). A pure index-only scan isn't chosen for the bitmap path; the second index column keeps that open for the index-scan path at negligible cost. refs TRI-13169
Open Graph Description: What The billingLimit.reconcileTick worker calls getOrgIdsWithBillingPauseSource() on BILLING_LIMIT_RECONCILE_INTERVAL_MS (~every 90s) to find which orgs currently have billing-limit-paused environ...
X Description: What The billingLimit.reconcileTick worker calls getOrgIdsWithBillingPauseSource() on BILLING_LIMIT_RECONCILE_INTERVAL_MS (~every 90s) to find which orgs currently have billing-limit-paused environ...
Opengraph URL: https://github.com/triggerdotdev/trigger.dev/pull/4590
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:f7e0edd7-1804-de14-ffe4-fc349671f8ca |
| current-catalog-service-hash | ae870bc5e265a340912cde392f23dad3671a0a881730ffdadd82f2f57d81641b |
| request-id | 9F10:31D3B1:9927:C8C5:6AFAF16A |
| html-safe-nonce | b9376ae09a8788435278f1e3dbae84769b9f3cd2f71e62a12d757ba5a756725f |
| visitor-payload | eyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiI5RjEwOjMxRDNCMTo5OTI3OkM4QzU6NkFGQUYxNkEiLCJ2aXNpdG9yX2lkIjoiODA1MTc1ODgyMzQ4NTY2NTY0MiIsInJlZ2lvbl9lZGdlIjoiaWFkIiwicmVnaW9uX3JlbmRlciI6ImlhZCJ9 |
| visitor-hmac | 0508645f80163f44702a495e718ab9aa98d7213beb9f0cf8a30c9020b4fdfd38 |
| hovercard-subject-tag | pull_request:4262355865 |
| 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/4590/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 The billingLimit.reconcileTick worker calls getOrgIdsWithBillingPauseSource() on BILLING_LIMIT_RECONCILE_INTERVAL_MS (~every 90s) to find which orgs currently have billing-limit-paused environ... |
| 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