Repository navigation
perf(coderd/database): index workspace_app_stats by session_ended_at - #31766
dylanhuff-at-coder merged 1 commit into
Conversation
GetTemplateAppInsightsByTemplate (insights metrics collector, every 5 minutes on every replica) filters workspace_app_stats on session_ended_at. The table had no index on it and is never purged, so the query sequentially scanned all app history on every run. Add a btree index on session_ended_at so that filter becomes a range scan. On a 3M row synthetic table the collector query went from about 124 ms to about 19 ms. dbrollup's UpsertTemplateUsageStats filters on the same column, but its lower bound comes from a CTE and newer Postgres versions (18 in testing) don't pick the index for it, so that scan needs its own query change. The index build runs inside the migration transaction and blocks app stat writes while it builds. Active session upserts also stop being HOT updates since they now change an indexed column.
|
/coder-agents-review Please limit the review to P0, P1, and P2 issues only. |
|
Chat: Review posted | View chat Review history
deep-review v0.13.0 | Round 1 | Last posted: Round 1, no findings, COMMENT. Review Finding inventoryFinding inventory: PR #31766Findings
Contested and acknowledgedNone. Round logRound 1Round type: round 1, full panel. Netero: no findings. Law not run (effective +4). Panel: Knuckle (migration specialist), Pariston, Mafuuu (core), Killua, Knov, Gon, Leorio, Mafu-san (pool), Chopper (wildcard). A PR comment asked to limit the review to P0, P1, and P2 issues. After cross-check no finding is P2 or higher, so no inline comments were posted. Severity decisions:
Orchestrator verification: txnmigrator.go:41 opens one transaction in Lock and :57 commits in Unlock; stats.go:309 sets the 15 s flush timeout. Reviewed against 42640dc..d98d5e9. About deep-reviewCRF = Coder Review Finding (P0-P4, Nit, Note)
|
There was a problem hiding this comment.
This PR adds a btree index on workspace_app_stats.session_ended_at for the insights metrics collector query. As requested, this review covers only P0 to P2 issues, and it found none. Reviewers reproduced the switch from a sequential scan to an index scan on PG13, PG16, and PG17, and the migration up/down tests and make lint/migrations pass.
Out of scope (needs a ticket or explicit acceptance by a human):
coderd/database/dbpurge: nothing purgesworkspace_app_stats, so the table and all of its indexes, including the one added here, grow without bound.coderd/database/queries/insights.sql:688: per the PR description,UpsertTemplateUsageStatsstill scans all app history on PG17 and later because its lower bound comes from a CTE, and no ticket is linked for that query change.agent/agentscripts,agent/agentssh,site/: the PR reportsmake pre-pushfailing on cleanmainin the author's workspace with different failure counts per run, and no ticket is linked.
🤖 This review was automatically generated with Coder Agents.
Adds an index on
workspace_app_stats(session_ended_at). The table had no time index and is never purged, so the insights collector's app usage query scanned all of it every 5 minutes on every replica. On 3M synthetic rows that query went from 124 ms to 19 ms.NOTE: the index build holds a lock that blocks app stat writes until the migration batch commits (about 1.4 to 3.4 s per 3M rows in testing), and active session upserts stop being HOT updates.
This is separate from #31765 so that one can be backported without a migration. dbrollup's
UpsertTemplateUsageStatsstill needs its own query change to use this index on Postgres 17+ (noted on CORE-776). The migration number may need a bump at merge time.I skipped the pre-push hook for this push since
make pre-pushalso fails on cleanmainin my workspace. This branch only failed in tests it doesn't touch.Failing pre-push tests
Clean
main(42640dc), two runs: 9 Go and 122 JS failures, then 6 Go and 104 JS.This branch, Go:
agent/agentscriptsTestCloseWaitsForCanceledStartReports, TestExecuteCanceledScriptReportsCanceled and TestStartCronDuringCloseDoesNotBlockClose, plusagent/agentsshTestNewServer_CloseActiveConnections, TestSSHShutdownReason and Test_sessionStart_orphan. JS: timeouts across 19 files, mostlyAgentsPageLayout,AgentCreatePage,ChatTopBarandWorkspacePage.Part of CORE-439.