Skip to content

perf(coderd): make background loop queries index backed - #31625

Draft
kylecarbs wants to merge 1 commit into
perf/db-template-avg-build-timefrom
perf/db-background-loops
Draft

kylecarbs wants to merge 1 commit into
perf/db-template-avg-build-timefrom
perf/db-background-loops

Conversation

@kylecarbs

@kylecarbs kylecarbs commented Nov 2, 2026 •

Copy link
Copy Markdown
Member

Makes the queries run by background loops (autobuild, notifications, the job reaper, dbpurge) index-backed, so their cost stops growing with total history.

Query Before After Change
GetWorkspacesEligibleForLifecycleAction (runs every minute) 4.1 s 2.3 s LATERAL lookup of the latest build instead of a correlated MAX(build_number)
AcquireNotificationMessages (batch of 20) 22 ms 3 to 6 ms Partial index on queued messages, so the scan stops at the batch size
GetProvisionerJobsToBeReaped 77 ms warm, 3.2 s cold 2.4 ms warm, 5 ms cold Partial index on incomplete jobs
Organization provisioner jobs list, 1.5M jobs / 67k jobs 9.9 s / 1.45 s 4 ms / 3.3 ms Filter and limit before the joins, plus (organization_id, created_at DESC) index
DeleteOldWorkspaceAgentLogs (1.7M log rows) 7.3 s 1.15 s Start from agents with logs, EXISTS (newer build) instead of grouping every build

Every rewrite returns the same rows:

  • The old lifecycle query's LEFT JOIN plus the MAX() filter already behaved as an inner join. Output matched at 5 timestamps, including dormancy, deletion, failed builds, and reminders.
  • The notification filter is the same condition rearranged to match the index predicate.
  • "Not the latest build" and "a newer build exists" are equivalent.
  • The provisioner jobs list matched byte for byte across 15 parameter sets. Every join returns at most one row per job, so the GROUP BY was redundant.

lifecycle_executor.go changes one line because sqlc now types build_template_version_id as non-nullable.

NOTE: The provisioner_jobs (organization_id, created_at DESC) index is about 79 MB at 2.1M jobs and blocks writes to provisioner_jobs while it builds. The two partial indexes are tiny. The partial index keys are immutable, so job heartbeats, which only touch updated_at, stay eligible for HOT updates.

Depends on #31624.

Decision log
  • About 0.8 s is the floor for the lifecycle query while it must check every workspace's latest build. Going lower needs a stored latest-build pointer (a schema change), which is out of scope here.
  • In the seed, about 5k workspaces stayed "eligible" every minute: running workspaces owned by suspended users while no provisioner was available, so the executor retried them. That is intended behavior, so it isn't filtered out in SQL.
  • No indexes were added for DeleteExpiredAPIKeys (43 ms) or DeleteOldNotificationMessages (200 ms). Both would add write cost to hot tables.
  • UpdateReplica stalls of up to 22 s seen under load were I/O and checkpoint contention, not the query.

Generated by Coder Agents on behalf of @kylecarbs.

@spikecurtis

Copy link
Copy Markdown
Collaborator

ProvisionerJobsToBeReaped is being updated by #31469 ; do the index changes still help the new query?

@kylecarbs

Copy link
Copy Markdown
Member Author

@spikecurtis it looks like this would still help. I'll put this in draft and wait til #31469 is merged and bench again to be sure.

@kylecarbs
kylecarbs marked this pull request as draft November 5, 2026 15:34
@linear-code

linear-code Bot commented Nov 9, 2026

Copy link
Copy Markdown

CORE-832

- GetWorkspacesEligibleForLifecycleAction fetches the latest build per
  workspace with a LATERAL lookup instead of a correlated MAX().
- AcquireNotificationMessages walks a partial index of queued messages
  in created_at order and stops at the batch size.
- GetProvisionerJobsToBeReaped uses a partial index of incomplete jobs.
- The organization provisioner jobs list filters and limits before the
  joins, and drops a GROUP BY that every one row join made redundant.
- DeleteOldWorkspaceAgentLogs starts from agents that have logs and uses
  EXISTS (newer build) instead of grouping every build.

The lifecycle executor change follows the sqlc type of
build_template_version_id, which is now non-nullable.

Generated by Coder Agents.
@kylecarbs
kylecarbs force-pushed the perf/db-background-loops branch from 4a0a553 to 84f88e1 Compare November 9, 2026 21:28
@spikecurtis

Copy link
Copy Markdown
Collaborator

#32099 got merged instead of #31469

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.

2 participants