Skip to content

perf(coderd/database): page GetUsers without a window over every user - #31626

Open
kylecarbs wants to merge 1 commit into
perf/db-background-loopsfrom
perf/db-users-list
Open

kylecarbs wants to merge 1 commit into
perf/db-background-loopsfrom
perf/db-users-list

Conversation

@kylecarbs

@kylecarbs kylecarbs commented Nov 2, 2026 •

Copy link
Copy Markdown
Member

GetUsers used COUNT(*) OVER (), which pushed every matching user (about 99k wide rows) through a window aggregate that spilled to disk. It then sorted all of them by lower(username) before the LIMIT applied.

The filters now go into a CTE. The page walks users_username_lower_idx and stops after LIMIT rows, and the count is a separate COUNT(*) over the same CTE, so it stays exact. Search and role filters keep the single-pass window count: they are unindexed and a second scan costs more there. Parameters are known at plan time, so Postgres drops the unused branch. If a generic plan were reused, results stay correct and speed matches main.

GetUsers (warm) Before After
Default page / offset 50 124 / 124 ms 18 / 19 ms
status:active 105 ms 20 ms
Offset 5000 51 ms 30 ms
Limit 0 (all users) 91 ms 66 ms
Search user123 78 ms 77 ms

End to end, /users went from 264 to 139 ms and ?q=status:active from 275 to 137 ms.

Output, including the count column, was hashed against main for 30 parameter sets, both without and with a selective RBAC filter injected; all matched. sqlc numbers the parameters differently through the CTE, so GetAuthorizedUsers in modelqueries.go passes them in the new order (gentest checks this), and the rbac_roles type override is repeated for the CTE column in sqlc.yaml.

Depends on #31625.

Decision log
  • PaginatedOrganizationMembers was also rewritten (paged 258 to 45 ms) but reverted. It got slower with no limit (307 to 571 ms), which the API allows when clients omit limit, and no equivalent shape fixed both cases.
  • No inbox (user_id, created_at DESC) index: 5k notifications for one user already list in about 1 ms.
  • Not done: pg_trgm for ILIKE search (80 ms floor) and approximate counts.
  • Found but not fixed: in PaginatedOrganizationMembers, an after_id cursor for a user in more than one organization fails with "more than one row returned by a subquery".

Generated by Coder Agents on behalf of @kylecarbs.

@linear-code

linear-code Bot commented Nov 9, 2026

Copy link
Copy Markdown

CORE-833

COUNT(*) OVER () pushed every matching user through a window and sorted
all of them by lower(username) before the LIMIT applied. Filter in a CTE,
page through users_username_lower_idx, and count separately. Search and
role filters keep the single pass window count, since a second scan costs
more there.

sqlc numbers the parameters differently through the CTE, so
GetAuthorizedUsers passes them in the new order, and the rbac_roles type
override is repeated for the CTE column.

Generated by Coder Agents.

This branch has not been deployed

No deployments
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

1 participant