René's URL Explorer Experiment
Title: perf(db): serve workspace usage drill-downs index-only on usage_log by waleedlatif1 · Pull Request #9447 · simstudioai/sim · GitHub
Open Graph Title: perf(db): serve workspace usage drill-downs index-only on usage_log by waleedlatif1 · Pull Request #9447 · simstudioai/sim
X Title: perf(db): serve workspace usage drill-downs index-only on usage_log by waleedlatif1 · Pull Request #9447 · simstudioai/sim
Description: Summary
Replaces usage_log_workspace_created_at_idx (workspace_id, created_at) with usage_log_workspace_created_at_cost_idx (workspace_id, created_at, billing_entity_type, billing_entity_id, user_id, source, cost, workflow_id), built concurrently before the old one is dropped (0446, replay-safe in the 0441/0442 pattern)
Every Workspaces drill-down read (totals, time series, source and member series, workflow ranking) filters on the entity, one workspace and a created_at window. With only (workspace_id, created_at) to work with, the planner either fetched a heap row per workspace row in the window, or scanned usage_log_billing_entity_created_at_cost_idx and fetched a heap row per organization row to check workspace_id. 0441 dropped the compact entity/period index that used to narrow the second through a BitmapAnd, so a large organization's workflow ranking went from ~4.6s to ~13.7s, reading thousands of rows per row returned. Each of these reads is now an index-only scan bounded by the workspace's rows in the window
workspace_id, created_at still lead, so the readers that name only the workspace keep their plans and order: the workspace_id FK's ON DELETE SET NULL, and the ledger list filtered to a workspace (which now also checks user_id in the index instead of on the heap)
Comments in usage-analytics.ts / usage-analytics-queries.ts that said a narrowed read heap-fetches per row are updated
Why this shape
Entity after created_at, not before. (workspace_id, billing_entity_type, billing_entity_id, created_at) would bound the same drill-downs, but the readers without an entity predicate (FK cascade, workspace ledger list) would lose the created_at order and bound. A workspace's rows billed to another entity (personal usage before it joined the org) are rare, and the index drops them without a heap fetch either way
Replace, don't add. Inserts maintain the same number of indexes; only this entry gets wider. The ledger insert is a low-rate single-row statement, so the wider entry is noise next to the heap tuple and the other index writes
Not widening usage_log_billing_entity_created_at_cost_idx instead. Adding workspace_id/workflow_id there would make the drill-down scan the whole organization's window and slow the org-level totals and series, which are by far the heaviest readers of that index
No INCLUDE. drizzle cannot declare one, and drizzle generate must stay in sync with the schema; trailing key columns serve the same scans (same as 0441)
Size. About 3x the index it replaces, measured on a seeded table and consistent with a hypothetical-index estimate; the cost of making every drill-down column available without the heap
Left alone
The per-user sum in an org (user_id + entity + window): rare, and already index-only on usage_log_billing_entity_created_at_cost_idx. The analogous change would widen usage_log_user_created_at_idx, the most-scanned index on the table, for little gain
A drill-down over a Stripe period (stamp-matched, opt-in preset): the period stamps are not in this index, so it keeps the plans it has today; not seen in production traffic
Model/BYOK drill-down dimensions still read description/metadata from the heap, now only for the workspace's rows
Type of Change
Other: performance (index)
Testing
New apps/sim/lib/billing/core/usage-analytics-plans.integration.ts: EXPLAINs the exact SQL of the totals, time series, source series, member series and workflow ranking narrowed to a workspace holding most and a small share of its organization's usage (with an unrelated organization and rows billed to another entity in the same workspace) and asserts an Index Only Scan of the new index bounded on workspace_id + created_at. All 10 cases fail on the old schema; dropping user_id and workflow_id from the key fails exactly the member series and workflow ranking cases. Passes with both push and INTEGRATION_DB_PROVISION=migrate
Production EXPLAIN (read-only) of the drill-down fingerprints: today they pick the narrow workspace index plus heap, a BitmapAnd with usage_log_workflow_id_idx, or the entity index with a heap filter on workspace_id (row estimates are skewed for large organizations). The competing index-only scan bounded on workspace_id + created_at costs a fraction of each of those
Local Postgres 17 seeded with a skewed org/workspace distribution: every drill-down read becomes an index-only scan with zero heap fetches, touching 7x fewer buffers for a workspace holding half its org and 38x fewer for a small one; the FK cascade and the workspace ledger list use the new index, and org-level reads keep their plans
Migration replayed twice and after a simulated INVALID build: rebuilds the index and ends valid
bun run check:migrations, drizzle-kit generate (no changes), bun run lint, bun run check:audits, type-check (apps/sim, packages/db), usage-analytics.test.ts
Checklist
Code follows project style guidelines
Self-reviewed my changes
Tests added/updated and passing (new tests pass the test-audit authoring gate)
No new warnings introduced
I confirm that I have read and agree to the terms outlined in the Contributor License Agreement (CLA)
Open Graph Description: Summary
Replaces usage_log_workspace_created_at_idx (workspace_id, created_at) with usage_log_workspace_created_at_cost_idx (workspace_id, created_at, billing_entity_type, billing_entity_id, user_...
X Description: Summary
Replaces usage_log_workspace_created_at_idx (workspace_id, created_at) with usage_log_workspace_created_at_cost_idx (workspace_id, created_at, billing_entity_type, billing_entity_id, user_...
Opengraph URL: https://github.com/simstudioai/sim/pull/9447
X: @github
direct link
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:47b64b7b-884d-8f67-97d5-1165cf8f647d |
| current-catalog-service-hash | ae870bc5e265a340912cde392f23dad3671a0a881730ffdadd82f2f57d81641b |
| request-id | B5A4:1828CD:E807:13EE3:6AFB19BE |
| html-safe-nonce | 78a9c927e909489a4b999469ab4b2e05a11e50ea048d4a4c7589216625a3c5e5 |
| visitor-payload | eyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiJCNUE0OjE4MjhDRDpFODA3OjEzRUUzOjZBRkIxOUJFIiwidmlzaXRvcl9pZCI6IjE0NDg2ODkyMTI0NTEwNjU0IiwicmVnaW9uX2VkZ2UiOiJpYWQiLCJyZWdpb25fcmVuZGVyIjoiaWFkIn0= |
| visitor-hmac | eb774e3e25fc5821f2fc17369999757fec8a3c06274947b4312ec39de75934d1 |
| hovercard-subject-tag | pull_request:5073469531 |
| 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 | ///pull_requests/show/files |
| fb:app_id | 1401488693436528 |
| apple-itunes-app | app-id=1477376905, app-argument=https://github.com/simstudioai/sim/pull/9447/files |
| twitter:image | https://avatars.githubusercontent.com/u/40672544?s=400&v=4 |
| twitter:card | summary_large_image |
| og:image | https://avatars.githubusercontent.com/u/40672544?s=400&v=4 |
| og:image:alt | Summary
Replaces usage_log_workspace_created_at_idx (workspace_id, created_at) with usage_log_workspace_created_at_cost_idx (workspace_id, created_at, billing_entity_type, billing_entity_id, user_... |
| og:site_name | GitHub |
| og:type | object |
| hostname | github.com |
| expected-hostname | github.com |
| None | 24933791aba0f10b2f189f8b953a1452dd79933933ac13a0a67b8ad3acee3d9f |
| turbo-cache-control | no-preview |
| diff-view | unified |
| go-import | github.com/simstudioai/sim git https://github.com/simstudioai/sim.git |
| octolytics-dimension-user_id | 199344406 |
| octolytics-dimension-user_login | simstudioai |
| octolytics-dimension-repository_id | 912559512 |
| octolytics-dimension-repository_nwo | simstudioai/sim |
| octolytics-dimension-repository_public | true |
| octolytics-dimension-repository_is_fork | false |
| octolytics-dimension-repository_network_root_id | 912559512 |
| octolytics-dimension-repository_network_root_nwo | simstudioai/sim |
| 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 | 030711cc180b563614a434f4376acf7da5d445fa |
| ui-target | full |
| theme-color | #1e2327 |
| color-scheme | light dark |
Links:
| Skip to content | https://github.com/simstudioai/sim/pull/9447/files#start-of-content |
| https://github.com/ |
| Sign in | https://github.com/login?return_to=https%3A%2F%2Fgithub.com%2Fsimstudioai%2Fsim%2Fpull%2F9447%2Ffiles |
| GitHubRepository hosting, code review, and collaboration | https://github.com/platform |
| CopilotAI coding agent for tasks, projects, and automated work | https://github.com/features/copilot |
| ActionsCI/CD and automation | https://github.com/features/actions |
| Code QualityQuality analysis, fixes, and enforcement | https://github.com/features/code-quality |
| CodespacesCloud development environments | https://github.com/features/codespaces |
| Code SecurityVulnerability detection and remediation | https://github.com/security/advanced-security/code-security |
| Secret ProtectionSecret scanning and leak prevention | https://github.com/security/advanced-security/secret-protection |
| All Features | https://github.com/features |
| Documentation | https://docs.github.com/en |
| Training | https://learn.github.com/skills |
| Certifications | https://learn.github.com/credentials |
| Build with Copilot | https://github.com/features/ai |
| Customize Copilot | https://docs.github.com/en/copilot/how-tos/copilot-on-github/customize-copilot/customize-copilot-overview |
| Integrations | https://github.com/marketplace |
| Trending repos | https://github.com/trending |
| GitHub Sponsors | https://github.com/open-source/sponsors |
| Developer articles | https://github.com/resources/articles |
| ChangelogYour source for the latest features, improvements, and fixes across GitHub products, all in one place. | https://github.blog/changelog/ |
| Enterprises | https://github.com/enterprise |
| Team | https://github.com/team |
| Startups | https://github.com/enterprise/startups |
| Nonprofits | https://github.com/solutions/industry/nonprofits |
| Healthcare | https://github.com/solutions/industry/healthcare |
| Financial services | https://github.com/solutions/industry/financial-services |
| Manufacturing | https://github.com/solutions/industry/manufacturing |
| Government | https://github.com/solutions/industry/government |
| See all industries | https://github.com/solutions/industry |
| Why GitHub | https://github.com/why-github |
| Customer Stories | https://github.com/customer-stories |
| Business insights | https://github.com/solutions/executive-insights |
| Trust Center | https://github.com/trust-center |
| GitHub Advanced SecurityEnterprise-grade code security | https://github.com/security/advanced-security |
| Copilot for businessEnterprise-grade AI features | https://github.com/features/copilot/copilot-business |
| Premium SupportEnterprise-grade 24/7 support | https://github.com/enterprise/premium-support |
| Documentation | https://docs.github.com/en |
| Resource center | https://github.com/resources |
| Blog | https://github.blog/ |
| Events & webinars | https://github.com/resources/events |
| Community Forum | https://github.com/orgs/community/discussions |
| Customer Stories | https://github.com/customer-stories |
| Integrations | https://github.com/marketplace |
| Customer support | https://help.github.com/ |
| Training | https://learn.github.com/skills |
| GitHub Status | https://www.githubstatus.com/ |
| About | https://github.com/about |
| Partners | https://github.com/partners |
| Changelog | https://github.blog/changelog/ |
| GitHub UniverseThe event for developers, by developers — talks, workshops, and what’s next for GitHub. | https://githubuniverse.com/ |
| Pricing | https://github.com/pricing |
| Sign in | https://github.com/login?return_to=https%3A%2F%2Fgithub.com%2Fsimstudioai%2Fsim%2Fpull%2F9447%2Ffiles |
| Sign up | https://github.com/signup?ref_cta=Sign+up&ref_loc=header+logged+out&ref_page=%2F%3Cuser-name%3E%2F%3Crepo-name%3E%2Fpull_requests%2Fshow%2Ffiles&source=header-repo&source_repo=simstudioai%2Fsim |
| Reload | https://github.com/simstudioai/sim/pull/9447/files |
| Reload | https://github.com/simstudioai/sim/pull/9447/files |
| Reload | https://github.com/simstudioai/sim/pull/9447/files |
| simstudioai | https://github.com/simstudioai |
| sim | https://github.com/simstudioai/sim |
| Code | https://github.com/simstudioai/sim |
| Issues129 (129) | https://github.com/simstudioai/sim/issues |
| Pull requests198 (198) | https://github.com/simstudioai/sim/pulls |
| Actions | https://github.com/simstudioai/sim/actions |
| Projects | https://github.com/simstudioai/sim/projects |
| Security and quality | https://github.com/simstudioai/sim/security |
| Insights | https://github.com/simstudioai/sim/pulse |
| Sign up for GitHub
| https://github.com/signup?return_to=%2Fsimstudioai%2Fsim%2Fissues%2Fnew%2Fchoose |
| terms of service | https://docs.github.com/terms |
| privacy statement | https://docs.github.com/privacy |
| Sign in | https://github.com/login?return_to=%2Fsimstudioai%2Fsim%2Fissues%2Fnew%2Fchoose |
| waleedlatif1 | https://github.com/waleedlatif1 |
| staging | https://github.com/simstudioai/sim/tree/staging |
| perf/usage-log-workspace-covering-idx | https://github.com/simstudioai/sim/tree/perf/usage-log-workspace-covering-idx |
|
Conversation
8
| https://github.com/simstudioai/sim/pull/9447 |
|
Commits
2
| https://github.com/simstudioai/sim/pull/9447/commits |
|
Checks
67
| https://github.com/simstudioai/sim/pull/9447/checks |
|
Files changed
| https://github.com/simstudioai/sim/pull/9447/files |
| Please reload this page | https://github.com/simstudioai/sim/pull/9447/files |
|
perf(db): serve workspace usage drill-downs index-only on usage_log
| https://github.com/simstudioai/sim/pull/9447/files#top |
|
Show all changes
2 commits
| https://github.com/simstudioai/sim/pull/9447/files |
|
f4ba91d
perf(db): serve workspace usage drill-downs index-only on usage_log
waleedlatif1 Oct 29, 2026
| https://github.com/simstudioai/sim/pull/9447/commits/f4ba91d4d60b8889122b008368eb8a0992e0a42c |
|
15de583
perf(db): scope the workspace covering index's claim to created_at wi…
waleedlatif1 Oct 29, 2026
| https://github.com/simstudioai/sim/pull/9447/commits/15de58399063f78ce6b486da83a77c90c61081ca |
|
Clear filters
| https://github.com/simstudioai/sim/pull/9447/files |
| Please reload this page | https://github.com/simstudioai/sim/pull/9447/files |
| Please reload this page | https://github.com/simstudioai/sim/pull/9447/files |
|
usage-analytics-plans.integration.ts
| https://github.com/simstudioai/sim/pull/9447/files#diff-6c30b536943e51669b4d5bf5d57bc29258fa4b085e49911a4a603d816d4c3c2a |
|
usage-analytics-queries.ts
| https://github.com/simstudioai/sim/pull/9447/files#diff-f90252e5f8de4f9b5e54cd6275f81709b2cc720d95bf0f49421fef8fa36968c5 |
|
usage-analytics.ts
| https://github.com/simstudioai/sim/pull/9447/files#diff-e45fb96628aca131706ef3499776fd65ccfef3227afc613ab41e019499d2cebb |
|
0446_usage_log_workspace_created_at_cost_idx.sql
| https://github.com/simstudioai/sim/pull/9447/files#diff-5a79f4dae16ab9a8ac008b2cefc7e0421a3a33e5efdcff32b766c60bf51d8e81 |
|
0446_snapshot.json
| https://github.com/simstudioai/sim/pull/9447/files#diff-f1921e8f366b320107dae5abc13669ee9fe842f75be973360d013b7a76ed9ec2 |
|
_journal.json
| https://github.com/simstudioai/sim/pull/9447/files#diff-37150cc65ad16ee6fbc5fa318fc096be7730e16d63eaf863e3df5521b7030649 |
|
schema.ts
| https://github.com/simstudioai/sim/pull/9447/files#diff-9a959e2ce3cb4c86957bbae021d06eae364f7b0cb18964f5873bab53220af928 |
| apps/sim/lib/billing/core/usage-analytics-plans.integration.ts | https://github.com/simstudioai/sim/pull/9447/files#diff-6c30b536943e51669b4d5bf5d57bc29258fa4b085e49911a4a603d816d4c3c2a |
|
View file
| https://github.com/simstudioai/sim/blob/15de58399063f78ce6b486da83a77c90c61081ca/apps/sim/lib/billing/core/usage-analytics-plans.integration.ts |
|
Open in desktop
| https://desktop.github.com |
| https://github.co/hiddenchars |
| https://github.com/simstudioai/sim/pull/9447/{{ revealButtonHref }} |
| apps/sim/lib/billing/core/usage-analytics-queries.ts | https://github.com/simstudioai/sim/pull/9447/files#diff-f90252e5f8de4f9b5e54cd6275f81709b2cc720d95bf0f49421fef8fa36968c5 |
|
View file
| https://github.com/simstudioai/sim/blob/15de58399063f78ce6b486da83a77c90c61081ca/apps/sim/lib/billing/core/usage-analytics-queries.ts |
|
Open in desktop
| https://desktop.github.com |
| https://github.co/hiddenchars |
| https://github.com/simstudioai/sim/pull/9447/{{ revealButtonHref }} |
|
| https://github.com/simstudioai/sim/pull/9447/files#diff-f90252e5f8de4f9b5e54cd6275f81709b2cc720d95bf0f49421fef8fa36968c5 |
|
| https://github.com/simstudioai/sim/pull/9447/files#diff-f90252e5f8de4f9b5e54cd6275f81709b2cc720d95bf0f49421fef8fa36968c5 |
| apps/sim/lib/billing/core/usage-analytics.ts | https://github.com/simstudioai/sim/pull/9447/files#diff-e45fb96628aca131706ef3499776fd65ccfef3227afc613ab41e019499d2cebb |
|
View file
| https://github.com/simstudioai/sim/blob/15de58399063f78ce6b486da83a77c90c61081ca/apps/sim/lib/billing/core/usage-analytics.ts |
|
Open in desktop
| https://desktop.github.com |
| https://github.co/hiddenchars |
| https://github.com/simstudioai/sim/pull/9447/{{ revealButtonHref }} |
|
| https://github.com/simstudioai/sim/pull/9447/files#diff-e45fb96628aca131706ef3499776fd65ccfef3227afc613ab41e019499d2cebb |
|
| https://github.com/simstudioai/sim/pull/9447/files#diff-e45fb96628aca131706ef3499776fd65ccfef3227afc613ab41e019499d2cebb |
| packages/db/migrations/0446_usage_log_workspace_created_at_cost_idx.sql | https://github.com/simstudioai/sim/pull/9447/files#diff-5a79f4dae16ab9a8ac008b2cefc7e0421a3a33e5efdcff32b766c60bf51d8e81 |
|
View file
| https://github.com/simstudioai/sim/blob/15de58399063f78ce6b486da83a77c90c61081ca/packages/db/migrations/0446_usage_log_workspace_created_at_cost_idx.sql |
|
Open in desktop
| https://desktop.github.com |
| https://github.co/hiddenchars |
| https://github.com/simstudioai/sim/pull/9447/{{ revealButtonHref }} |
| Please reload this page | https://github.com/simstudioai/sim/pull/9447/files |
| Please reload this page | https://github.com/simstudioai/sim/pull/9447/files |
| Please reload this page | https://github.com/simstudioai/sim/pull/9447/files |
|
| https://github.com |
| Terms | https://docs.github.com/site-policy/github-terms/github-terms-of-service |
| Privacy | https://docs.github.com/site-policy/privacy-policies/github-privacy-statement |
| Security | https://github.com/security |
| Status | https://www.githubstatus.com/ |
| Community | https://github.community/ |
| Docs | https://docs.github.com/ |
| Contact | https://support.github.com?tags=dotcom-footer |
Viewport: width=device-width
URLs of crawlers that visited me.