Audit log slows to a crawl when nearing 1 million entries

[mafredri]

Problem: Today, rendering the audit log on dev.coder.com takes over 1 minute for the first page.

When the audit log grows to a significant number of rows (currently 970714 entries at the time of writing for dev.coder.com), the pagination causes it to become slow.

The main culprit is the "total" count (COUNT(audit_logs.*) OVER () AS count) which converts the query from rendering N first results to reading the entire table as the count is based on applied filters.

Simply changing this out for (SELECT COUNT(*) FROM audit_logs) AS count makes the query instant, but of course, incorrect.

Solution: We must figure out a way to optimize the GetAuditLogsOffset query to show results faster.

[ibetitsmike]

Here's the explain link for the query: https://explain.dalibo.com/plan/e4ece3cg8cc1gc72

[mafredri]

@ibetitsmike that explain seems like it's for a fairly small database, a larger one matching the mentioned entries would better illustrate the problem, although for the keen observer it may still be obvious.

[ibetitsmike]

@mafredri I've run it on dogfood so it shouldn't be "small", but I've put in a filter to be last 4 or 5 days so the return result is small

coder=> SELECT COUNT(*) FROM audit_logs;
  count  
---------
 2284100
(1 row)

[mafredri]

@ibetitsmike that tracks, the heavy part of this query is essentially the count over the whole table with filters applied, if the dataset is reduced the issue won't be as visible.

[ethanndickson]

Though the query alone is slow, it's also worth noting that for every audit log row we do an additional query to check if the associated resource is deleted.