Repository navigation
Conversation
kylecarbs
marked this pull request as ready for review
November 3, 2026 15:20
…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
force-pushed
the
perf/db-audit-group-indexes
branch
from
November 9, 2026 21:28
c6682d5 to
a64c4e4
Compare
mafredri
approved these changes
Nov 12, 2026
mafredri
left a comment
Member
There was a problem hiding this comment.
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. |
Member
There was a problem hiding this comment.
Rather than commenting these in the migration, let's add COMMENT ON to the indexes.
This branch was previously 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.
Adds three indexes:
audit_logs(action),audit_logs(resource_type), andgroup_members(group_id). No query text changes.Filtering audit logs by a rare action or resource type walked
idx_audit_logs_time_descthrough the whole table looking for a page of matches. The dbpurge connection event cleanup (DeleteOldAuditLogConnectionEvents) scanned the full table each run.group_memberswas only indexed by(user_id, group_id), so lookups by group scanned every membership.DeleteOldAuditLogConnectionEvents(batch of 1000)GetGroupMembersCountByGroupIDs(25 groups)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_logsandgroup_membersinside 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:
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:
mainand the full stack ran against identical copies, with--provisioner-daemons=0and Postgres capped at 4 GB. API p50 for one request at a time:main/insights/templates(7 days)/insights/user-activity/insights/user-latency/insights/daus/workspaces(admin, no filter)/workspaces?q=status:running/workspaces?q=template:X/users/?q=status:activeUnder a 16-client mix for 120 s,
maincompleted 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
(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.Generated by Coder Agents on behalf of @kylecarbs.