Visitar URL original
perf(db): index a workspace's live files in upload order by waleedlatif1 · Pull Request #9434 · simstudioai/sim · GitHub
Skip to content

perf(db): index a workspace's live files in upload order - #9434

Merged
waleedlatif1 merged 2 commits into
stagingfrom
perf/files-list-idx
Oct 29, 2026
Merged

perf(db): index a workspace's live files in upload order#9434
waleedlatif1 merged 2 commits into
stagingfrom
perf/files-list-idx

Conversation

@waleedlatif1

@waleedlatif1 waleedlatif1 commented Oct 29, 2026 •
edited
Loading

Copy link
Copy Markdown
Collaborator

Summary

  • Adds workspace_files_workspace_active_uploaded_idx (workspace_id, uploaded_at, id) with the predicate the other live workspace-file indexes share (deleted_at IS NULL AND context = 'workspace' AND workspace_id IS NOT NULL), built concurrently with the replay-safe recovery pattern
  • No index served the file list's filter in upload order, so the planner combined the context and search-keyset indexes, matching every workspace's live files, filtered to one workspace on the heap and sorted, even for a bounded read; the full list and the SSR seed now read the workspace's rows in index order with no sort (the uploadedAt keyset pages still order on date_trunc('milliseconds', uploaded_at) and move to this order in the pagination PR, which aligns their keyset with the plain column)
  • listWorkspaceFiles orders by uploaded_at, id, so ties come back in a stable order across reads
  • The key stays narrow (no name/key columns) so an entry can never approach the btree size limit and fail a write
  • Marks workspace_files_workspace_active_keyset_idx for retirement once its scans stop after deploy
  • Adds @sim/db/testing/explain (explainCaptured), a test helper that EXPLAINs the SQL a call actually sends, with its bound parameters

Type of Change

  • Other: performance

Testing

  • New file-list.integration.ts: active, archived and all scopes list exactly their rows in (uploaded_at, id) order (archived and all fail without the tiebreak); the full list and the SSR seed's bounded read plan on the new index with no Sort (fails with the index dropped)
  • Plans verified on a production-shaped local copy under stale statistics: the SSR seed drops from a whole-workspace scan and sort to an ordered index scan; name-sorted pages, key/name/id lookups and the archived list keep their plans
  • Push- and migrate-provisioned databases build the identical index; replay rebuilds an invalid index and keeps a valid one
  • bun run check:migrations origin/staging, drizzle-kit generate (no changes), bun run lint, bun run check:audits, bun run type-check

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)

@waleedlatif1
perf(db): index a workspace's live files in upload order
c6468fe 
The live file list filtered on workspace, owner, context and liveness and
sorted by uploaded_at, and no index served that filter in that order. The
planner combined the context and search keyset indexes, which match the
live workspace files of every workspace, filtered to one workspace on the
heap, then sorted, on disk for a large workspace. A bounded read such as
the SSR seed or an uploadedAt keyset page paid for the whole workspace.

workspace_files_workspace_active_uploaded_idx keys on (workspace_id,
uploaded_at, id) under the predicate the other live workspace-file indexes
share, which the list's filter implies. The key stays narrow: carrying the
name and storage key columns would bring an entry near the btree size limit
and fail the write. The contract-pending note on
workspace_files_workspace_active_keyset_idx now names this index as the one
that takes over its workspace-scoped reads.

The list now orders upload-time ties by id, so its order is deterministic.
explainCaptured in @sim/db/testing/explain EXPLAINs the statement a call
sends, with its bound parameters, for plan assertions in integration tests.

On a prod-shaped local copy with stale statistics, the largest workspace's
301-row seed went from ~210 ms to under 1 ms and an uploadedAt keyset page
from ~205 ms to under 0.1 ms, both ordered index scans with no sort. The
unbounded full list keeps its bitmap plan: its estimate is about an eighth
of the real row count, at which the planner prices this index above it.
Lookups by key, name and id and the name-sorted folder page keep their
plans.
@waleedlatif1
waleedlatif1 requested a review from a team as a code owner October 29, 2026 02:11
@vercel

vercel Bot commented Oct 29, 2026 •
edited
Loading

Copy link
Copy Markdown

The latest updates on your projects. Learn more about Vercel for GitHub.

1 Skipped Deployment
Project Deployment Actions Updated
docs Ignored Ignored Preview Oct 29, 2026 2:43am UTC

Request Review

cubic-dev-ai[bot]
cubic-dev-ai Bot reviewed Oct 29, 2026
View reviewed changes

@cubic-dev-ai cubic-dev-ai Bot left a comment

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

No issues found across 9 files

Confidence score: 5/5

  • Automated review surfaced no issues in the provided summaries.
  • No files require special attention.

Turn on auto-fix | Re-trigger cubic

@greptile-apps

greptile-apps Bot commented Oct 29, 2026 •
edited
Loading

Copy link
Copy Markdown
Contributor

RetriggerView in GreptileConfidence Score: 5/5

[Medium impact] The PR appears safe to merge, with no new actionable issues.

Summary

Adds an index for a workspace’s live file list and bounded server-rendered seed, with stable ordering by uploaded_at, id.

  • Workspace file lists read live files in stable upload order.

Reviews (2) · Last reviewed commit: "docs(db): say the upload-order index ser..." · Reviewed by Greptile

greptile-apps[bot]
greptile-apps Bot reviewed Oct 29, 2026
View reviewed changes
Comment thread packages/db/schema.ts Resolved
@waleedlatif1

waleedlatif1 commented Oct 29, 2026

Copy link
Copy Markdown
Collaborator Author

@greptile

@waleedlatif1

waleedlatif1 commented Oct 29, 2026

Copy link
Copy Markdown
Collaborator Author

@cubic-dev-ai review this PR

@cubic-dev-ai

cubic-dev-ai Bot commented Oct 29, 2026

Copy link
Copy Markdown
Contributor

@cubic-dev-ai review this PR

@waleedlatif1 I have started the AI code review. It will take a few minutes to complete.

cubic-dev-ai[bot]
cubic-dev-ai Bot reviewed Oct 29, 2026
View reviewed changes

@cubic-dev-ai cubic-dev-ai Bot left a comment

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

No issues found across 9 files

Confidence score: 5/5

  • Automated review surfaced no issues in the provided summaries.
  • No files require special attention.

Turn on auto-fix | Re-trigger cubic

@waleedlatif1
waleedlatif1 merged commit 70724d0 into staging Oct 29, 2026
69 checks passed
@waleedlatif1 waleedlatif1 mentioned this pull request Oct 29, 2026
Merged
@waleedlatif1
waleedlatif1 deleted the perf/files-list-idx branch October 29, 2026 08:04
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@greptile-apps greptile-apps[bot] greptile-apps[bot] left review comments
@cubic-dev-ai cubic-dev-ai[bot] cubic-dev-ai[bot] left review comments

Assignees

No one assigned

Labels

None yet

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

1 participant

@waleedlatif1