Skip to content

perf(coderd/database): index audit log filters and group members by group - #31623

Open
kylecarbs wants to merge 1 commit into
mainfrom
perf/db-audit-group-indexes
Open

kylecarbs wants to merge 1 commit into
mainfrom
perf/db-audit-group-indexes

Conversation

@kylecarbs

@kylecarbs kylecarbs commented Nov 2, 2026 •

Copy link
Copy Markdown
Member

Adds three indexes: audit_logs(action), audit_logs(resource_type), and group_members(group_id). No query text changes.

Filtering audit logs by a rare action or resource type walked idx_audit_logs_time_desc through the whole table looking for a page of matches. The dbpurge connection event cleanup (DeleteOldAuditLogConnectionEvents) scanned the full table each run. group_members was only indexed by (user_id, group_id), so lookups by group scanned every membership.

Query (warm, single run) Before After
Audit page / count, rare action (500 matches) 1690 / 380 ms 2.0 / 1.5 ms
Audit page / count, rare resource type 1669 / 363 ms 2.0 / 1.1 ms
Audit page offset 500 / count, mid-frequency action (5k matches) 563 / 865 ms 5.5 / 6.4 ms
DeleteOldAuditLogConnectionEvents (batch of 1000) 690 ms warm, about 10 s cold under 0.2 ms
GetGroupMembersCountByGroupIDs (25 groups) 393 to 410 ms 342 ms

One case got slightly slower: the first page of a mid-frequency action went from 17.7 to 26.5 ms.

NOTE: This migration builds indexes on audit_logs and group_members inside the migration transaction, which blocks writes to those tables until it finishes. On 5M audit rows each index took 1.3 to 17 s. Deployments with very large audit logs should expect a longer upgrade step.

This is the bottom of an 8 PR stack of database performance fixes ordered from simplest to most review-heavy: #31623, #31624, #31625, #31626, #31627, #31628, #31629, #31630.

Test setup and whole-stack results

All numbers in this stack come from a Postgres 17 database seeded with synthetic data:

  • 100k users, 10 organizations, 1k groups, 800k group memberships
  • 300 templates, 6k template versions
  • 210k workspaces, 2.1M builds and provisioner jobs, 1.1M agents, 3.4M apps
  • 5M audit logs, 2M connection logs, 1M notification messages, 1.5M inbox notifications
  • 3M agent stats rows, 600k template usage rows

Per-query numbers are EXPLAIN (ANALYZE, BUFFERS) warm medians of 3 on a clone. Old and new query output was compared across many parameter sets per change.

End to end: main and the full stack ran against identical copies, with --provisioner-daemons=0 and Postgres capped at 4 GB. API p50 for one request at a time:

Endpoint main Full stack
/insights/templates (7 days) 26.2 s 1.7 s
/insights/user-activity 3.8 s 0.43 s
/insights/user-latency 2.0 s 0.39 s
/insights/daus 2.6 s 0.94 s
/workspaces (admin, no filter) 5.4 s 3.2 s
/workspaces?q=status:running 4.7 s 3.3 s
/workspaces?q=template:X 1.3 s 0.45 s
/users / ?q=status:active 264 / 275 ms 139 / 137 ms

Under a 16-client mix for 120 s, main completed about 160 requests and the full stack about 634.

Caveats: synthetic data on one Postgres version. Part of the improvement under load comes from less overall contention.

Decision log
  • Single-column indexes instead of (action, time DESC). B-tree deduplication keeps them at 33 MB each versus about 150 MB. The composite would bring rare filters from about 2 ms to 0.2 ms, which isn't worth 4x the size.
  • A partial index on the legacy connection actions was rejected. Postgres refuses enum values added in the same transaction, which is how fresh databases and multi-version upgrades apply migrations.

Generated by Coder Agents on behalf of @kylecarbs.

@kylecarbs
kylecarbs added this pull request to stack #31631 November 2, 2026 13:49
@kylecarbs kylecarbs changed the title perf/db audit group indexes perf(coderd/database): index audit log filters and group members by group Nov 2, 2026
@kylecarbs
kylecarbs marked this pull request as ready for review November 3, 2026 15:20
@coder
coder Bot requested review from geokat and mafredri November 3, 2026 15:22
@linear-code

linear-code Bot commented Nov 9, 2026

Copy link
Copy Markdown

CORE-830

…roup

Audit log filters on a rare action or resource type walked the time index
through the whole table, and the dbpurge connection event cleanup scanned
it. group_members was only indexed by (user_id, group_id), so lookups by
group scanned every membership row.

Add idx_audit_logs_action, idx_audit_logs_resource_type, and
idx_group_members_group_id. No query text changes.

Generated by Coder Agents.
@kylecarbs
kylecarbs force-pushed the perf/db-audit-group-indexes branch from c6682d5 to a64c4e4 Compare November 9, 2026 21:28
@kylecarbs
kylecarbs temporarily deployed to main November 9, 2026 21:28 — with GitHub Actions Inactive

@mafredri mafredri left a comment

Copy link
Copy Markdown
Member

Choose a reason for hiding this comment

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

Did you measure how much storage space these new indexes take?

That is the only possible blocker I could see. Would also suggest the comment change but otherwise 👍🏻

-- found, which reads the whole table for rare values. Single column indexes
-- stay small through btree deduplication; frequent values still use the time
-- index. They also serve the capped count and the dbpurge connection event
-- cleanup, which filters on action.

Copy link
Copy Markdown
Member

Choose a reason for hiding this comment

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

Rather than commenting these in the migration, let's add COMMENT ON to the indexes.

This branch was previously deployed

1 inactive deployment
main — a64c4e45 Deployed Nov 9, 2026 by kylecarbs via cla #252859
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