Stats endpoint returns 500 once the database passes about 4.5 GiB #27

New Issue

2026-09-22T00:50:15+02:00

clawbot commented

2026-09-22 00:50:15 +02:00

Found in the 24-hour verification run for #3 (next at 3898daa): from about 3.5 hours in, with the database at 4.5-5.3 GiB and 1-2 M live routes, every /api/v1/stats request takes the full 4 s (statsContextTimeout, internal/server/handlers.go:25) and returns HTTP 500, so the status page shows nothing in production. It answered in 0.3 s at 0.5 GiB.

Cause to confirm: GetStatsContext (internal/database/database.go, from about line 930) runs on every request a COUNT(*) over each of asns, prefixes_v4, prefixes_v6, peerings, bgp_peers, live_routes_v4, live_routes_v6, plus MIN/MAX(last_updated) over the union of both route tables, which is a full scan of the largest tables. The status page polls every 2 s. The same function logs Failed to get route timestamps on every call (MIN(last_updated) scanned into *time.Time).

Requirements

Make the stats request cheap regardless of table size. Preferred, plainest approach: compute the database counts at most once per interval (for example every 30 s) in one place, keep the last result in memory behind a mutex, and have the handlers serve that copy; a request never runs the scans itself. Do not add tables, triggers or counters maintained on the write path.
Make the route timestamp query use an index or drop the union scan (check internal/database/schema.sql for what is indexed), and fix the scan-type error so the warning disappears.
Keep the JSON shape of /api/v1/stats and the status page unchanged.
Files: internal/database/database.go, internal/server/handlers.go and their tests; say so on the PR if more is needed.
A worker earlier ran a single test directly with the go tool; do not. Use make test only.
Experiments: own container names and 127.0.0.1 ports, no --memory, removed by name afterwards. Never touch containers whose names start with routewatch-verify.

Definition of done

A test shows that many stats requests in a row cause at most one database computation per interval.
script/cibuild green. Commit title ends (closes #N) with this issue's number.

Model: fable-5-1

Found in the 24-hour verification run for https://git.eeqj.de/sneak/routewatch/issues/3 (`next` at `3898daa`): from about 3.5 hours in, with the database at 4.5-5.3 GiB and 1-2 M live routes, every `/api/v1/stats` request takes the full 4 s (`statsContextTimeout`, `internal/server/handlers.go:25`) and returns HTTP 500, so the status page shows nothing in production. It answered in 0.3 s at 0.5 GiB. Cause to confirm: `GetStatsContext` (`internal/database/database.go`, from about line 930) runs on every request a `COUNT(*)` over each of `asns`, `prefixes_v4`, `prefixes_v6`, `peerings`, `bgp_peers`, `live_routes_v4`, `live_routes_v6`, plus `MIN/MAX(last_updated)` over the union of both route tables, which is a full scan of the largest tables. The status page polls every 2 s. The same function logs `Failed to get route timestamps` on every call (`MIN(last_updated)` scanned into `*time.Time`). ## Requirements - Make the stats request cheap regardless of table size. Preferred, plainest approach: compute the database counts at most once per interval (for example every 30 s) in one place, keep the last result in memory behind a mutex, and have the handlers serve that copy; a request never runs the scans itself. Do not add tables, triggers or counters maintained on the write path. - Make the route timestamp query use an index or drop the union scan (check `internal/database/schema.sql` for what is indexed), and fix the scan-type error so the warning disappears. - Keep the JSON shape of `/api/v1/stats` and the status page unchanged. - Files: `internal/database/database.go`, `internal/server/handlers.go` and their tests; say so on the PR if more is needed. - A worker earlier ran a single test directly with the go tool; do not. Use `make test` only. - Experiments: own container names and 127.0.0.1 ports, no `--memory`, removed by name afterwards. Never touch containers whose names start with `routewatch-verify`. ## Definition of done - A test shows that many stats requests in a row cause at most one database computation per interval. - `script/cibuild` green. Commit title ends ` (closes #N)` with this issue's number. Model: fable-5-1

clawbot referenced this issue from a commit

2026-09-22 01:35:39 +02:00

Serve /api/v1/stats from a cache and index-scan the route timestamps (closes #27)

clawbot referenced a pull request that will close this issue

2026-09-22 01:36:07 +02:00

Serve /api/v1/stats from a cache and index-scan the route timestamps (closes #27) #28

clawbot commented

2026-09-22 01:36:23 +02:00

Implemented in #28.

/api/v1/stats and the status page now serve the database statistics from an in-memory copy recomputed at most once every 30 s; a request no longer runs the count and MIN/MAX scans itself.
The oldest/newest route timestamps now read one row from each end of the last_updated index and parse into time.Time, so the per-call Failed to get route timestamps warning is gone.
The JSON shape of the endpoint and the status page is unchanged.

script/cibuild is green.

Model: opus-4-8

Implemented in https://git.eeqj.de/sneak/routewatch/pulls/28. - `/api/v1/stats` and the status page now serve the database statistics from an in-memory copy recomputed at most once every 30 s; a request no longer runs the count and MIN/MAX scans itself. - The oldest/newest route timestamps now read one row from each end of the `last_updated` index and parse into `time.Time`, so the per-call `Failed to get route timestamps` warning is gone. - The JSON shape of the endpoint and the status page is unchanged. `script/cibuild` is green. Model: opus-4-8

sneak commented

2026-09-22 08:38:18 +02:00

who decided 30s? why can’t we keep the in memory stats updated in realtime?

clawbot commented

2026-09-22 09:02:10 +02:00

The 30 s interval was my own judgement call as the implementing worker, not a ruling from you. Switching to realtime: the database row counts are seeded once at startup and kept current in memory on every insert and delete, so a stats read needs no recompute and no timer. The new PR spells out the full design, including the one figure I keep as a cheap indexed query rather than an in-memory counter (the distinct-prefix histogram, whose in-memory form would need a ~1M-entry per-prefix table this memory-tuned service avoids).

Model: opus-4-8

The 30 s interval was my own judgement call as the implementing worker, not a ruling from you. Switching to realtime: the database row counts are seeded once at startup and kept current in memory on every insert and delete, so a stats read needs no recompute and no timer. The new PR spells out the full design, including the one figure I keep as a cheap indexed query rather than an in-memory counter (the distinct-prefix histogram, whose in-memory form would need a ~1M-entry per-prefix table this memory-tuned service avoids). Model: opus-4-8

clawbot referenced this issue from a commit

2026-09-22 09:23:51 +02:00

Serve /api/v1/stats counts from realtime in-memory counters (closes #27)

clawbot referenced a pull request that will close this issue

2026-09-22 09:24:33 +02:00

Serve /api/v1/stats counts from realtime in-memory counters (closes #27) #29

clawbot commented

2026-09-22 09:24:48 +02:00