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-profiling0
react-import-mapreact,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-controllerpull_requests
route-actionfiles
fetch-noncev2:47b64b7b-884d-8f67-97d5-1165cf8f647d
current-catalog-service-hashae870bc5e265a340912cde392f23dad3671a0a881730ffdadd82f2f57d81641b
request-idB5A4:1828CD:E807:13EE3:6AFB19BE
html-safe-nonce78a9c927e909489a4b999469ab4b2e05a11e50ea048d4a4c7589216625a3c5e5
visitor-payloadeyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiJCNUE0OjE4MjhDRDpFODA3OjEzRUUzOjZBRkIxOUJFIiwidmlzaXRvcl9pZCI6IjE0NDg2ODkyMTI0NTEwNjU0IiwicmVnaW9uX2VkZ2UiOiJpYWQiLCJyZWdpb25fcmVuZGVyIjoiaWFkIn0=
visitor-hmaceb774e3e25fc5821f2fc17369999757fec8a3c06274947b4312ec39de75934d1
hovercard-subject-tagpull_request:5073469531
github-keyboard-shortcutsrepository,pull-request-list,pull-request-conversation,pull-request-files-changed,copilot
google-site-verificationApib7-x98H0j5cPqHWwSMm6dNU4GmODRoqxLiDzdx9I
octolytics-urlhttps://collector.github.com/github/collect
analytics-location///pull_requests/show/files
fb:app_id1401488693436528
apple-itunes-appapp-id=1477376905, app-argument=https://github.com/simstudioai/sim/pull/9447/files
twitter:imagehttps://avatars.githubusercontent.com/u/40672544?s=400&v=4
twitter:cardsummary_large_image
og:imagehttps://avatars.githubusercontent.com/u/40672544?s=400&v=4
og:image:altSummary 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_nameGitHub
og:typeobject
hostnamegithub.com
expected-hostnamegithub.com
None24933791aba0f10b2f189f8b953a1452dd79933933ac13a0a67b8ad3acee3d9f
turbo-cache-controlno-preview
diff-viewunified
go-importgithub.com/simstudioai/sim git https://github.com/simstudioai/sim.git
octolytics-dimension-user_id199344406
octolytics-dimension-user_loginsimstudioai
octolytics-dimension-repository_id912559512
octolytics-dimension-repository_nwosimstudioai/sim
octolytics-dimension-repository_publictrue
octolytics-dimension-repository_is_forkfalse
octolytics-dimension-repository_network_root_id912559512
octolytics-dimension-repository_network_root_nwosimstudioai/sim
turbo-body-classeslogged-out env-production page-responsive full-width
disable-turbotrue
browser-stats-urlhttps://api.github.com/_private/browser/stats
browser-errors-urlhttps://api.github.com/_private/browser/errors
release030711cc180b563614a434f4376acf7da5d445fa
ui-targetfull
theme-color#1e2327
color-schemelight dark

Links:

Skip to contenthttps://github.com/simstudioai/sim/pull/9447/files#start-of-content
https://github.com/
Sign inhttps://github.com/login?return_to=https%3A%2F%2Fgithub.com%2Fsimstudioai%2Fsim%2Fpull%2F9447%2Ffiles
GitHubRepository hosting, code review, and collaborationhttps://github.com/platform
CopilotAI coding agent for tasks, projects, and automated workhttps://github.com/features/copilot
ActionsCI/CD and automationhttps://github.com/features/actions
Code QualityQuality analysis, fixes, and enforcementhttps://github.com/features/code-quality
CodespacesCloud development environmentshttps://github.com/features/codespaces
Code SecurityVulnerability detection and remediationhttps://github.com/security/advanced-security/code-security
Secret ProtectionSecret scanning and leak preventionhttps://github.com/security/advanced-security/secret-protection
All Featureshttps://github.com/features
Documentationhttps://docs.github.com/en
Traininghttps://learn.github.com/skills
Certificationshttps://learn.github.com/credentials
Build with Copilothttps://github.com/features/ai
Customize Copilothttps://docs.github.com/en/copilot/how-tos/copilot-on-github/customize-copilot/customize-copilot-overview
Integrationshttps://github.com/marketplace
Trending reposhttps://github.com/trending
GitHub Sponsorshttps://github.com/open-source/sponsors
Developer articleshttps://github.com/resources/articles
ChangelogYour source for the latest features, improvements, and fixes across GitHub products, all in one place.https://github.blog/changelog/
Enterpriseshttps://github.com/enterprise
Teamhttps://github.com/team
Startupshttps://github.com/enterprise/startups
Nonprofitshttps://github.com/solutions/industry/nonprofits
Healthcarehttps://github.com/solutions/industry/healthcare
Financial serviceshttps://github.com/solutions/industry/financial-services
Manufacturinghttps://github.com/solutions/industry/manufacturing
Governmenthttps://github.com/solutions/industry/government
See all industrieshttps://github.com/solutions/industry
Why GitHubhttps://github.com/why-github
Customer Storieshttps://github.com/customer-stories
Business insightshttps://github.com/solutions/executive-insights
Trust Centerhttps://github.com/trust-center
GitHub Advanced SecurityEnterprise-grade code securityhttps://github.com/security/advanced-security
Copilot for businessEnterprise-grade AI featureshttps://github.com/features/copilot/copilot-business
Premium SupportEnterprise-grade 24/7 supporthttps://github.com/enterprise/premium-support
Documentationhttps://docs.github.com/en
Resource centerhttps://github.com/resources
Bloghttps://github.blog/
Events & webinarshttps://github.com/resources/events
Community Forumhttps://github.com/orgs/community/discussions
Customer Storieshttps://github.com/customer-stories
Integrationshttps://github.com/marketplace
Customer supporthttps://help.github.com/
Traininghttps://learn.github.com/skills
GitHub Statushttps://www.githubstatus.com/
Abouthttps://github.com/about
Partnershttps://github.com/partners
Changeloghttps://github.blog/changelog/
GitHub UniverseThe event for developers, by developers — talks, workshops, and what’s next for GitHub.https://githubuniverse.com/
Pricinghttps://github.com/pricing
Sign inhttps://github.com/login?return_to=https%3A%2F%2Fgithub.com%2Fsimstudioai%2Fsim%2Fpull%2F9447%2Ffiles
Sign uphttps://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
Reloadhttps://github.com/simstudioai/sim/pull/9447/files
Reloadhttps://github.com/simstudioai/sim/pull/9447/files
Reloadhttps://github.com/simstudioai/sim/pull/9447/files
simstudioaihttps://github.com/simstudioai
simhttps://github.com/simstudioai/sim
Codehttps://github.com/simstudioai/sim
Issues129 (129)https://github.com/simstudioai/sim/issues
Pull requests198 (198)https://github.com/simstudioai/sim/pulls
Actionshttps://github.com/simstudioai/sim/actions
Projectshttps://github.com/simstudioai/sim/projects
Security and qualityhttps://github.com/simstudioai/sim/security
Insightshttps://github.com/simstudioai/sim/pulse
Sign up for GitHub https://github.com/signup?return_to=%2Fsimstudioai%2Fsim%2Fissues%2Fnew%2Fchoose
terms of servicehttps://docs.github.com/terms
privacy statementhttps://docs.github.com/privacy
Sign inhttps://github.com/login?return_to=%2Fsimstudioai%2Fsim%2Fissues%2Fnew%2Fchoose
waleedlatif1https://github.com/waleedlatif1
staginghttps://github.com/simstudioai/sim/tree/staging
perf/usage-log-workspace-covering-idxhttps://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 pagehttps://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 pagehttps://github.com/simstudioai/sim/pull/9447/files
Please reload this pagehttps://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.tshttps://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.tshttps://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.tshttps://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.sqlhttps://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 pagehttps://github.com/simstudioai/sim/pull/9447/files
Please reload this pagehttps://github.com/simstudioai/sim/pull/9447/files
Please reload this pagehttps://github.com/simstudioai/sim/pull/9447/files
https://github.com
Termshttps://docs.github.com/site-policy/github-terms/github-terms-of-service
Privacyhttps://docs.github.com/site-policy/privacy-policies/github-privacy-statement
Securityhttps://github.com/security
Statushttps://www.githubstatus.com/
Communityhttps://github.community/
Docshttps://docs.github.com/
Contacthttps://support.github.com?tags=dotcom-footer

Viewport: width=device-width


URLs of crawlers that visited me.