[Bug]: Stats query writes ~55 MB to disk per execution — 1.25 TB of SSD writes in 6 days

[tema-mazy]

Summary

Stats query writes ~55 MB to disk per execution — 1.25 TB of SSD writes in 6 days

Summary

The location_stats query behind the location detail view sorts the entire stats table into
a file-backed temp B-tree on every execution, twice. On my machine that is ~55 MB of disk
writes for a single read-only SELECT, and the client had written 1.25 TB after 6 days
11 hours of uptime.

The same query is also the source of the slow statement warnings in the client log
(1.1–1.3 s per call).

This is a single-query fix. An index alone does not help — see "Why the index alone fails".

Environment

Defguard client	1.6.9
OS	macOS 26.5.2 (Apple silicon)
DB	~/Library/Containers/net.defguard/Data/Library/Application Support/net.defguard/defguard.db, 19 MB, WAL
location_stats	182,275 rows / 1 active location / 30 days
PRAGMA temp_store	0 (default → temp files on disk)
Process uptime	6 d 11 h
Bytes written (Activity Monitor)	1.25 TB (vs 2.98 GB read)

The query

WITH cte AS (
    SELECT id, location_id,
           COALESCE(upload   - LAG(upload)   OVER (PARTITION BY location_id ORDER BY collected_at), 0) upload,
           COALESCE(download - LAG(download) OVER (PARTITION BY location_id ORDER BY collected_at), 0) download,
           last_handshake,
           strftime($1, collected_at) collected_at,
           listen_port, persistent_keepalive_interval
    FROM location_stats
    ORDER BY collected_at
    LIMIT -1 OFFSET 1
)
SELECT ...
FROM cte
WHERE location_id = $2 AND collected_at >= $3
GROUP BY collected_at
ORDER BY collected_at
LIMIT $4;

Both predicates (location_id, collected_at) are applied outside the CTE. The window
function is therefore computed over the whole table, and the sort cannot be satisfied by any
index. Cost is O(all rows) per call and grows without bound as history accumulates.

EXPLAIN QUERY PLAN:

|--CO-ROUTINE cte
|  |--CO-ROUTINE (subquery-3)
|  |  |--SCAN location_stats
|  |  `--USE TEMP B-TREE FOR ORDER BY   <- spill #1
|  |--SCAN (subquery-3)
|  `--USE TEMP B-TREE FOR ORDER BY      <- spill #2
`--SCAN cte

With temp_store=0 those temp B-trees are files on the SSD.

Measurements

Disk write rate via iostat -d -w 1 disk0, machine otherwise idle.

Baseline: 0.12 – 0.64 MB/s

15 consecutive executions of the current query:

  KB/t  tps   MB/s
 80.27 1080  84.67
222.50  478 103.92
224.28  406  88.98
364.10  201  71.52
223.59  397  86.75
107.72  724  76.15
333.75  314 102.24
223.85  291  63.55
160.41  612  95.93
261.86  143  36.69

≈ 800 MB for 15 runs → ≈ 55 MB written per execution.

Log shows these firing in bursts of ~3 every ~11 s while the location view is open:

[2026-07-28][17:52:34.529][WARN][sqlx::query] slow statement: ... elapsed=1.129598625s
[2026-07-28][17:52:34.537][WARN][sqlx::query] slow statement: ... elapsed=1.123934167s
[2026-07-28][17:52:45.659][WARN][sqlx::query] slow statement: ... elapsed=1.134748250s
[2026-07-28][17:52:45.661][WARN][sqlx::query] slow statement: ... elapsed=1.129177750s
[2026-07-28][17:52:45.677][WARN][sqlx::query] slow statement: ... elapsed=1.136281541s

1.25 TB ÷ 55 MB ≈ 23,000 executions over the 6 d 11 h uptime — consistent with the observed
cadence averaged over idle and active periods. Extrapolated, that is on the order of
70 TB/year of avoidable SSD writes for a client that is mostly sitting in the tray.

Why the index alone fails

The shipped schema has idx_collected_location (collected_at, location_id) — wrong leading
column for this access pattern. But adding the "correct" one does not help either:

CREATE INDEX idx_location_stats_location_collected ON location_stats (location_id, collected_at);

Re-running the unmodified query with that index present:

  KB/t  tps   MB/s
348.49  212  72.24
361.60  204  72.07
356.60  208  72.29
177.90  421  73.16
153.12  497  74.27
354.85  208  72.10
354.75  230  79.66

Still ~72–80 MB/s. Plan:

|--CO-ROUTINE cte
|  |--SCAN location_stats USING INDEX idx_location_stats_location_collected   <- all rows
|  `--USE TEMP B-TREE FOR ORDER BY                                            <- still spills

SQLite uses the index to scan, but with no WHERE inside the CTE it still materialises and
sorts every row. The query must be rewritten; the index is necessary but not sufficient.

Proposed fix

Push both predicates into the CTE, lower-bounded at the floor of the bucket containing $3,
not at $3 itself. The COALESCE/MAX(collected_at) subselect retains the one sample
immediately before that floor, which is the LAG() baseline for the first in-window bucket —
without it the first bucket reads as 0 traffic.

The bucket floor matters. The outer filter compares the bucketed string
(collected_at >= strftime($1, $3)), so when $3 falls mid-bucket the current query still
aggregates the whole bucket. Lower-bounding the CTE at $3 would silently truncate that first
bucket to a partial sum. datetime(strftime($1, $3)) reproduces today's behaviour exactly.

WITH cte AS (
    SELECT id, location_id,
           COALESCE(upload   - LAG(upload)   OVER (PARTITION BY location_id ORDER BY collected_at), 0) upload,
           COALESCE(download - LAG(download) OVER (PARTITION BY location_id ORDER BY collected_at), 0) download,
           last_handshake,
           strftime($1, collected_at) collected_at,
           listen_port, persistent_keepalive_interval
    FROM location_stats
    WHERE location_id = $2
      AND collected_at >= COALESCE(
            (SELECT MAX(collected_at) FROM location_stats
              WHERE location_id = $2
                AND collected_at < datetime(strftime($1, $3))),
            datetime(strftime($1, $3)))
    ORDER BY collected_at
    LIMIT -1 OFFSET 1
)
SELECT id, location_id,
       SUM(MAX(upload, 0))   "upload!: i64",
       SUM(MAX(download, 0)) "download!: i64",
       last_handshake,
       collected_at "collected_at!: NaiveDateTime",
       listen_port  "listen_port!: u32",
       persistent_keepalive_interval "persistent_keepalive_interval?: u16"
FROM cte
WHERE collected_at >= strftime($1, $3)
GROUP BY collected_at
ORDER BY collected_at
LIMIT $4;

plus a migration:

CREATE INDEX IF NOT EXISTS idx_location_stats_location_collected
    ON location_stats (location_id, collected_at);
CREATE INDEX IF NOT EXISTS idx_tunnel_stats_tunnel_collected
    ON tunnel_stats (tunnel_id, collected_at);
-- idx_collected_location (collected_at, location_id) is then redundant for this query

Resulting plan:

|--CO-ROUTINE cte
|  |--SEARCH location_stats USING INDEX idx_location_stats_location_collected (location_id=? AND collected_at>?)
|  |--SCALAR SUBQUERY 1
|  |  `--SEARCH location_stats USING COVERING INDEX idx_location_stats_location_collected (location_id=? AND collected_at<?)
|  `--USE TEMP B-TREE FOR ORDER BY   <- now over one window's worth of rows, not the table

15 executions, disk write rate:

  KB/t  tps   MB/s
306.62  104  31.02
370.84   31  11.18
  4.00    6   0.02
  7.46  214   1.56
 13.82  124   1.68

Two short bursts then baseline — that is 15 runs finishing in well under a second each, versus
10+ seconds of sustained 70–100 MB/s for the same 15 runs of the current query.

	writes / query	wall time
current query	~55 MB	0.47 s
current query + index	~55 MB	0.47 s
rewritten + index	~0	0.08 s

Correctness

Diffed full result sets (all 8 columns, up to 500 rows) old vs new against the 182,275-row
production DB, across window starts on and off bucket boundaries:

2026-06-01 00:00:00            rows=500   IDENTICAL
2026-06-29 11:03:02.412828     rows=500   IDENTICAL   <- exactly the oldest row
2026-07-04 07:22:13            rows=465   IDENTICAL   <- mid-bucket
2026-07-06 00:00:01            rows=424   IDENTICAL
2026-07-11 13:59:59            rows=291   IDENTICAL   <- end of bucket
2026-07-15 05:30:00            rows=203   IDENTICAL
2026-07-19 23:00:00            rows=119   IDENTICAL
2026-07-22 08:47:31            rows=80    IDENTICAL
2026-07-27 12:00:00            rows=22    IDENTICAL
2026-07-28 18:00:00            rows=1     IDENTICAL   <- newest row only
2030-01-01 00:00:00            rows=0     IDENTICAL   <- empty window

Byte-for-byte identical in every case. Note the mid-bucket cases specifically: they are what
fails if the CTE is bounded at $3 rather than at the bucket floor, and the failure is a quietly
wrong first data point rather than an error.

tunnel_stats has the same query shape and should get the same treatment; it just happens to be
empty on my install so it never showed up in the logs.

Secondary suggestions

• Set PRAGMA temp_store = 2 (memory) on the connection. Defensive — these sorts are small once
  the query is fixed, and it removes a whole class of "read query writes to disk" surprises.
• The client keeps 30 days of stats at ~1 sample / 14 s per location. Worth confirming that
  retention is intentional and documented, since query cost scales linearly with it.

Steps to reproduce

1. Run the client with a location connected until location_stats holds ~100k+ rows.
2. Open the location detail / stats view.
3. Watch iostat -d -w 1 disk0 (or Activity Monitor → Disk → Bytes Written for the Defguard
   process). Writes jump to ~70–100 MB/s in bursts while the view is open.

Expected behavior

0 reads/writes

Actual behavior

1.25 TB of SSD writes in 6 days

Defguard version

1.6.9

Environment details

Macos 26.5.2 silicon

Deployment / install method

Custom

Relevant logs / output

Relevant configuration (redacted)

[tema-mazy]

[j-chmielewski]

Thank you @tema-mazy for this detailed analysis. We will be looking into this shortly.

Related issues: #874, #1062