Repository navigation
Conversation
kylecarbs
force-pushed
the
perf/db-template-usage-rollup
branch
from
November 9, 2026 21:28
419e7eb to
7640160
Compare
UpsertTemplateUsageStats recomputes every bucket since the latest stored one on each run, and ON CONFLICT DO UPDATE locked and WAL-logged every unchanged row. Compare computed rows with stored ones first and write only the differences. The latency join matches the bucket key directly, and session minutes are gated with a window function, which removes a large sort spill. Generated by Coder Agents.
kylecarbs
force-pushed
the
perf/db-template-usage-rollup
branch
from
November 10, 2026 16:11
7640160 to
0f04c86
Compare
Collaborator
|
Warning Risk: on PG13, the first rollup into an empty table takes ~100 s instead of ~17 s.
The stack's seed doesn't show this (about one report per 6 minutes per agent): there, PG13 takes 25–27 s for an empty table with every variant. Suggestion: latencies AS (
SELECT
date_trunc('hour', was.created_at) + trunc(date_part('minute', was.created_at) / 30) * 30 * '1 minute'::interval AS start_time,
was.template_id,
was.user_id,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY was.connection_median_latency_ms)::real AS median_latency_ms
FROM
workspace_agent_stats AS was
WHERE
was.created_at >= (SELECT t FROM latest_start)
AND was.created_at < NOW()
AND was.connection_median_latency_ms > 0
AND EXISTS (
SELECT 1 FROM stats
WHERE stats.start_time = date_trunc('hour', was.created_at) + trunc(date_part('minute', was.created_at) / 30) * 30 * '1 minute'::interval
AND stats.template_id = was.template_id
AND stats.user_id = was.user_id
AND date_trunc('minute', was.created_at) = ANY(stats.minute_buckets)
)
GROUP BY
1, was.template_id, was.user_id
),Stored rows match the PR's in every scenario on PG13 and PG17. |
This branch has not been deployed
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
UpsertTemplateUsageStats, the periodic rollup behind template insights, recomputes every bucket since the latest stored one on each run.ON CONFLICT DO UPDATEthen locked and WAL-logged every row, including unchanged ones.Computed rows are now compared with stored ones first, and only the differences are written. Session app rows use one full join against the stored rows to find inserts, updates, and deletes. The latency join matches the bucket key directly (same 30-minute bucket expression as the rest of the query) instead of going through a minute-bucket CTE. Session minutes are gated with a window function instead of a join, which removes a 244 MB sort spill.
Review focus: this query writes persistent insights data. A subtle error would corrupt stored history silently and would not self-correct.
template_usage_statsandtemplate_usage_stats_session_apps.Depends on #31629.
Generated by Coder Agents on behalf of @kylecarbs.