Skip to content

Commit fa17cd3

Browse files
geokatgeokat
committed
perf: cap count queries, use native UUID ops for audit/conn logs (backport #23835)
Audit and connection log pages were timing out due to expensive COUNT(*) queries over large tables. This commit adds opt-in count capping: requests can return a `count_cap` field signaling that the count was truncated at a threshold, avoiding full table scans that caused page timeouts. Text-cast UUID comparisons in regosql-generated authorization queries also contributed to the slowdown by preventing index usage for connection and audit log queries. These now emit native UUID operators. Frontend changes handle the capped state in usePaginatedQuery and PaginationWidget, optionally displaying a capped count in the pagination UI (e.g. "Showing 2,076 to 2,100 of 2,000+ logs") --- Cherry picked from 86ca61d Related to: https://linear.app/codercom/issue/PLAT-31/connectionaudit-log-performance-issue
1 parent 3470ce8 commit fa17cd3

28 files changed

+848
-408
lines changed

‎coderd/apidoc/docs.go‎

Lines changed: 6 additions & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/apidoc/swagger.json‎

Lines changed: 6 additions & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/audit.go‎

Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -26,6 +26,11 @@ import (
2626
"github.com/coder/coder/v2/codersdk"
2727
)
2828

29+
// Limit the count query to avoid a slow sequential scan due to joins
30+
// on a large table. Set to 0 to disable capping (but also see the note
31+
// in the SQL query).
32+
const auditLogCountCap = 2000
33+
2934
// @Summary Get audit logs
3035
// @ID get-audit-logs
3136
// @Security CoderSessionToken
@@ -66,7 +71,7 @@ func (api *API) auditLogs(rw http.ResponseWriter, r *http.Request) {
6671
countFilter.Username = ""
6772
}
6873

69-
// Use the same filters to count the number of audit logs
74+
countFilter.CountCap = auditLogCountCap
7075
count, err := api.Database.CountAuditLogs(ctx, countFilter)
7176
if dbauthz.IsNotAuthorizedError(err) {
7277
httpapi.Forbidden(rw)
@@ -81,6 +86,7 @@ func (api *API) auditLogs(rw http.ResponseWriter, r *http.Request) {
8186
httpapi.Write(ctx, rw, http.StatusOK, codersdk.AuditLogResponse{
8287
AuditLogs: []codersdk.AuditLog{},
8388
Count: 0,
89+
CountCap: auditLogCountCap,
8490
})
8591
return
8692
}
@@ -98,6 +104,7 @@ func (api *API) auditLogs(rw http.ResponseWriter, r *http.Request) {
98104
httpapi.Write(ctx, rw, http.StatusOK, codersdk.AuditLogResponse{
99105
AuditLogs: api.convertAuditLogs(ctx, dblogs),
100106
Count: count,
107+
CountCap: auditLogCountCap,
101108
})
102109
}
103110

‎coderd/database/modelqueries.go‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -608,6 +608,7 @@ func (q *sqlQuerier) CountAuthorizedAuditLogs(ctx context.Context, arg CountAudi
608608
arg.DateTo,
609609
arg.BuildReason,
610610
arg.RequestID,
611+
arg.CountCap,
611612
)
612613
if err != nil {
613614
return 0, err
@@ -744,6 +745,7 @@ func (q *sqlQuerier) CountAuthorizedConnectionLogs(ctx context.Context, arg Coun
744745
arg.WorkspaceID,
745746
arg.ConnectionID,
746747
arg.Status,
748+
arg.CountCap,
747749
)
748750
if err != nil {
749751
return 0, err

‎coderd/database/modelqueries_internal_test.go‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -145,5 +145,13 @@ func extractWhereClause(query string) string {
145145
// Remove SQL comments
146146
whereClause = regexp.MustCompile(`(?m)--.*$`).ReplaceAllString(whereClause, "")
147147

148+
// Normalize indentation so subquery wrapping doesn't cause
149+
// mismatches.
150+
lines := strings.Split(whereClause, "\n")
151+
for i, line := range lines {
152+
lines[i] = strings.TrimLeft(line, " \t")
153+
}
154+
whereClause = strings.Join(lines, "\n")
155+
148156
return strings.TrimSpace(whereClause)
149157
}

0 commit comments

Comments
 (0)