Skip to content

perf(coderd/database): aggregate user activity and agent usage stats in one pass - #31628

Open
kylecarbs wants to merge 2 commits into
perf/db-workspaces-listfrom
perf/db-agent-stats-user-activity
Open

kylecarbs wants to merge 2 commits into
perf/db-workspaces-listfrom
perf/db-agent-stats-user-activity

Conversation

@kylecarbs

@kylecarbs kylecarbs commented Nov 2, 2026 •

Copy link
Copy Markdown
Member

Two insights and telemetry queries now do less work:

  • GetUserActivityInsights uses one grouped pass with GROUPING SETS instead of two aggregations and a sort over rows carrying template lists.
  • GetWorkspaceAgentUsageStats sorts only the needed columns, aggregates per agent group first, and then joins the one-row-per-agent latest_sessions. It no longer groups on the session counts JSON.
Query (warm median) Before After
GetUserActivityInsights, all templates / one template (PG17) 640 / 52 ms 326 / 42 ms
GetWorkspaceAgentUsageStats, 1 hour, 8k agents / 1.2M rows, PG17 2.4 to 4.6 s (noisy) 1.66 s
same, PG13 2.83 s 1.86 s
same, 143k agents / 486k rows, PG17 / PG13 2.11 / 2.90 s 1.78 / 2.28 s

GetWorkspaceAgentStats is unchanged. An earlier revision rewrote it as a single pass, but on realistic data (few agents, many rows each) it was slower than the existing query: 1264 vs 878 ms on PG17 and 1644 vs 1136 ms on PG13. It only looked faster on sparse seed data with about 3 rows per agent per hour, so it was reverted. Thanks @EhabY for catching this.

Equivalence:

  • User activity: 84 of 84 comparisons matched, covering 21 parameter sets with and without extra edge-case data, plus an EXCEPT both ways.
  • GetWorkspaceAgentUsageStats: row count and md5 of the sorted output matched the original query on PG13 and PG17, for both data shapes above.

Depends on #31627.

Benchmark setup

The table was loaded into scratch databases on PG13.23 and PG17.11, shifted to the current time. Each server was capped at 4 GB, with shared_buffers=1GB and work_mem 4 MB (the default) and 16 MB. Timings are EXPLAIN (ANALYZE, TIMING OFF) medians of 3 to 6 warm runs.

Two data shapes were used:

  • Sparse: 143k agents, about 486k rows in the hour.
  • Dense: 8k agents, about 1.2M rows in the hour, 5% legacy rows without latency.

Generated by Coder Agents on behalf of @kylecarbs.

@EhabY

EhabY commented Nov 9, 2026

Copy link
Copy Markdown
Collaborator

All three queries return the same rows as before on PG13 and PG17, including the edge cases. The related tests pass.

Can't reproduce: the GetWorkspaceAgentStats gain (2044 → 1303 ms). For a 1-hour window I get:

Setup Before This PR
PG13, 1.3M rows 950 ms 1220 ms
PG17, 11M rows 1300 ms 1260 ms

The old query ran both aggregations in parallel workers. The single pass adds a single-threaded re-sort of the whole window. What data and Postgres version did you measure on?

The other two gains reproduce: GetUserActivityInsights is about 2.5× faster and GetWorkspaceAgentUsageStats about 30% faster.

Suggestions (both return the same rows):

  1. In GetWorkspaceAgentStats, put agent_id first in the GROUP BY. PG13–16 can then reuse the window's sort on agent_id instead of re-sorting, which is about 13% faster on PG13.
  2. In GetWorkspaceAgentUsageStats, aggregate first and then LEFT JOIN latest_sessions. That removes the (array_agg(...))[1] workaround and joins about 8k groups instead of about 1M rows.

@linear-code

linear-code Bot commented Nov 9, 2026

Copy link
Copy Markdown

CORE-835

… pass

- GetUserActivityInsights uses one grouped pass with GROUPING SETS instead
  of two aggregations and a sort over rows carrying template lists.
- GetWorkspaceAgentStats computes totals and each agent's latest session
  counts from one scan of the window.
- GetWorkspaceAgentUsageStats sorts only the needed columns and no longer
  groups on the session counts JSON.

Generated by Coder Agents.
…ns after grouping

Review on #31628 found the single pass GetWorkspaceAgentStats slower on
realistic data. With 8k agents and about 150 stat rows each per hour it
took 1264 ms vs 878 ms on PG17 and 1644 ms vs 1136 ms on PG13, so restore
the previous query. The earlier gain only showed on sparse seed data with
about 3 rows per agent.

GetWorkspaceAgentUsageStats now aggregates first and joins latest_sessions
per group, which drops the array_agg workaround. Output is identical; it
is about 10% faster than the previous revision on PG13 and PG17.

Generated by Coder Agents.
@kylecarbs
kylecarbs force-pushed the perf/db-agent-stats-user-activity branch from 0b091f2 to 19bcdd5 Compare November 9, 2026 21:28
@kylecarbs kylecarbs changed the title perf(coderd/database): aggregate user activity and agent stats in one pass perf(coderd/database): aggregate user activity and agent usage stats in one pass Nov 9, 2026

@cstyan cstyan left a comment

Copy link
Copy Markdown
Collaborator

Choose a reason for hiding this comment

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

LGTM, the GROUPING bit in the first query was a bit hard to understand, potentially a different name for is_bucket_row and/or a comment on what the grouping/grouping sets portions produce would be useful. However I don't have any decent concrete suggestion for those, so not blocking.

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.

3 participants