Title: perf(audit-logs): read a listing narrowed only by time through per-workspace keyset scans by waleedlatif1 · Pull Request #9450 · simstudioai/sim · GitHub
Open Graph Title: perf(audit-logs): read a listing narrowed only by time through per-workspace keyset scans by waleedlatif1 · Pull Request #9450 · simstudioai/sim
X Title: perf(audit-logs): read a listing narrowed only by time through per-workspace keyset scans by waleedlatif1 · Pull Request #9450 · simstudioai/sim
Description: Summary An organization's audit listing scopes rows with one OR predicate (the org's workspaces, or workspace-less rows naming the org). With no filter, or only a time range (the audit page's default "Past 30 days" view), no index returns that OR in listing order, so each page collects every in-scope row inside the range and sorts it. For a busy organization that becomes a scan over a large part of audit_log, long enough to hit replica recovery conflicts. This PR changes only those listings, the ones narrowed by nothing but a time range: The page ids come from a disjoint union. Each organization workspace gets one keyset scan of audit_log_workspace_created_at_id_idx, through unnest(...) cross join lateral (... limit page). The metadata-attributed org-level rows are read through audit_log_org_level_created_at_id_idx (0444). The organization-resource rows that don't name the org in metadata get a small resource_idx read. Every part is ordered and limited to one page, so the first page of the union is exactly the first page of the scope, and union all can't produce duplicates because the parts are disjoint. Workspace ids are deduplicated. The time range is kept as written (created_at >= / <=). It is also stated as implied bounds on the sort key (date_trunc('milliseconds', created_at) >= / <= date_trunc(bound)). Because date_trunc never decreases, these bounds drop no row the exact predicate keeps, and each scan starts and stops at the range. The cursor is the same keyset position, written as a row comparison so the scan can seek to it. Any other filter (action, resource type/id, actor, actor email, workspace, search, alone or combined with time) keeps the single predicate unchanged, SQL and parameters byte for byte. Under such a filter a per-workspace scan cannot stop after a page; that is what made #9419's fan-out slow, and #9438 reverted it. The single predicate lets the filter's own index drive. queryAuditLogs now takes the scope and the filters instead of prebuilt SQL so it can make this choice. Callers updated: list and export use cases, /api/v1/audit-logs. buildOrgScopeCondition output is unchanged, so the detail read and the data drain are untouched. No schema change. audit_log_workspace_created_at_id_idx stays: this path depends on it, and workspace-filtered listings already use it today. Type of Change Other: performance Testing list-scope.integration.ts pages through 13 filter shapes (none, start, end inside a millisecond, range, action, action+time, resource type(s), resource, actor, workspace, search). Each runs with current members only and with departed members included, at page sizes 1/3/7/100, with and without the byte budget. Every run must match the single predicate's order exactly. The fixture covers workspace rows, org-level rows of both kinds, another org's rows, departed and null actors, four rows per PostgreSQL millisecond (ties broken by id), a duplicated workspace id, and a workspace the organization no longer holds. list-plans.integration.ts checks the plans. Unfiltered, departed-included, after-cursor, start-date and date-range listings must read each workspace with an Index Scan Backward on the keyset index directly under the limit that stops it, with the range and cursor in its Index Cond. These checks fail on the previous single-predicate listing. Every narrowed shape must keep the single predicate (no fan-out). Mutation checks: dropping the workspace-id dedupe, dropping the exact time predicate, or routing everything back to the single predicate each turns these tests red. A one-off compile of every narrowed filter combination, with and without a cursor, across three scopes produced SQL text and parameters identical to staging. On the production replica, the busiest organization's unfiltered and 30-day listings go from a scan of the whole scope to a few thousand buffer hits. A one-hour window costs about the same as today. Organizations with many workspaces stay bounded at one short scan per workspace. Audit-log and data-drain integration suites, related unit suites, lint, check:audits, type-check, test:scripts. Checklist Code follows project style guidelines Self-reviewed my changes Tests added/updated and passing 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 An organization's audit listing scopes rows with one OR predicate (the org's workspaces, or workspace-less rows naming the org). With no filter, or only a time range (the audit page...
X Description: Summary An organization's audit listing scopes rows with one OR predicate (the org's workspaces, or workspace-less rows naming the org). With no filter, or only a time range (the au...
Opengraph URL: https://github.com/simstudioai/sim/pull/9450
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:c216f04b-b48e-c0ea-2761-94bdb6c5a3f8 |
| current-catalog-service-hash | ae870bc5e265a340912cde392f23dad3671a0a881730ffdadd82f2f57d81641b |
| request-id | C01C:2AD8BA:1774E1:228002:6AFB0FAD |
| html-safe-nonce | 89114c355709a2c6b1b6020fbd13b0657756fed35798f9d70f6cca01019707b0 |
| visitor-payload | eyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiJDMDFDOjJBRDhCQToxNzc0RTE6MjI4MDAyOjZBRkIwRkFEIiwidmlzaXRvcl9pZCI6Ijg3OTA2MDAzOTIxOTE0NDY5NTgiLCJyZWdpb25fZWRnZSI6ImlhZCIsInJlZ2lvbl9yZW5kZXIiOiJpYWQifQ== |
| visitor-hmac | 303e76ccb4aba75f951408c72235f311c45d67613268bb38767173ecc85b0181 |
| hovercard-subject-tag | pull_request:5073531632 |
| 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/simstudioai/sim/pull/9450/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 An organization's audit listing scopes rows with one OR predicate (the org's workspaces, or workspace-less rows naming the org). With no filter, or only a time range (the audit page... |
| og:site_name | GitHub |
| og:type | object |
| hostname | github.com |
| expected-hostname | github.com |
| None | 4275ec98d19a530a209480d02854be81de24ad5a05b648333242fc516bfb573c |
| 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 | ba13208acc32a4e7a4a3b54cb2f0dea6001ba5f8 |
| ui-target | full |
| theme-color | #1e2327 |
| color-scheme | light dark |
Links:
Viewport: width=device-width