[Ideas] Devices list — perf fix + scale plan (LATERAL latest-metrics, cap lift, server-side pagination)

[bdunncompany]

Devices list: perf fix + scale plan (short + long-term)

I've been digging into GET /devices and want to surface three connected issues that I think are worth resolving together, even though the fixes split cleanly into three independent PRs.

What I was looking at

The visible symptom was the Devices page being slow on a 70-device fleet — about 6–8 seconds to render. Profiling pointed at one query, not the UI.

The latest-metrics lookup in routes/devices/core.ts uses a GROUP BY MAX(timestamp) + self-join against device_metrics. To produce 70 rows of "latest sample" it reads every metric row for every device in the page to compute the max timestamp. Live EXPLAIN ANALYZE on production data with ~8,993 rows per device:

-- Existing shape (GROUP BY MAX + self-join)
->  Index Only Scan using device_metrics_device_id_timestamp_pk on device_metrics
      Index Cond: (device_id = devices.id)
      rows=8981 loops=70           ← reads ALL metric rows per device
      Heap Fetches: 112551
->  Index Scan ... (re-fetch the row at max(timestamp), 70 loops)
Execution Time: 1038.574 ms

-- LATERAL + LIMIT 1 against the (device_id, timestamp) PK
->  Limit (rows=1 loops=70)
      ->  Index Scan Backward using device_metrics_device_id_timestamp_pk
            Index Cond: (device_id = devices.id)
            (actual time=0.026..0.026 rows=1)
Execution Time: 2.594 ms

Both run against the same (device_id, timestamp) primary key. The old plan reads the full per-device history (8,981 × 70 ≈ 628k row fetches) and then re-joins to pick the row at max(timestamp). The LATERAL form does one backward index seek per device — LIMIT 1 stops the scan after a single tuple. The new shape's cost is constant per device regardless of metric retention depth; the old shape gets linearly worse as history grows.

I also tested DISTINCT ON (device_id) … ORDER BY device_id, timestamp DESC. It was actually slower than the existing query (~2,267 ms), because the planner sorted the full set on disk before picking one per device — the optimizer didn't find the index-scan-backward path that LATERAL forces. So LATERAL isn't just "an option," it's the one that gets the right plan.

Shape	Time
GROUP BY MAX + self-join (current)	~1,039 ms
DISTINCT ON … ORDER BY (tested)	~2,267 ms
LATERAL + LIMIT 1 (proposed)	2.594 ms

That's roughly 400× on this dataset. While reading the code path for that fix two related structural issues jumped out.

Issue 2: 100-device cap

GET /devices is capped at limit=100 (via getPagination's default maxLimit). The web DevicesPage currently fetches once with no explicit limit, so a partner-scope user sees at most 100 devices regardless of how many are actually enrolled. Once past ~100 endpoints, the list silently truncates — there's no "next page" UI because pagination happens client-side over whatever the single fetch returned.

For very small fleets this isn't visible. The day you hit 101 it is.

The quick fix is small: raise the /devices cap (other routes keep the default 100) and have the web fetch ask for it. The natural ceiling is around 500 devices (~1 MB of JSON, well within what the page renders fast). Two ways to do it:

• Hardcoded 500 (simplest): one constant in core.ts, one query param in DevicesPage.tsx. Three changed lines total.
• Configurable via env (e.g. BREEZE_DEVICES_LIST_MAX=500, default 500): same diff plus the env-var plumbing. Lets a self-hoster with 800 endpoints raise it without a code change.

I lean toward the env-var path because the alternative just moves the cliff from 100 to 500, but I'd take your read.

Mobile note: apps/mobile/src/services/api.ts:436 also hits /devices, but it uses the default page size (no explicit limit param). The cap change doesn't affect mobile unless someone separately bumps that call site, which is out of scope for this fix.

Adjacent to but separate from this: Discussion #684 (per-page-size selector, merged via #705) already gave users 10–200 per-page UI control. Path A makes that selector actually work above 100. Path B is what makes it work above 500.

This unblocks 100–500 device fleets (or higher with the env-var path) immediately with negligible risk and no UX changes.

Issue 3: the structural problem the cap lift doesn't solve

For fleets above 500 (MSPs running 1000–3000+ endpoints across many orgs), lifting the cap doesn't help — the architecture itself is wrong for that size. Current shape:

• API accepts page, limit, basic filters (siteId, status, osType, orgId, search on hostname) and returns { data, pagination: { page, limit, total } }. So the bones are there.
• What's missing on the API: sort + sortDir parameters (currently hardcoded lastSeenAt DESC), and multi-value or composite filters that match the UI's filter set.
• What's missing on the web: DeviceList needs to stop doing sortedDevices.slice() and become a controlled component that requests a new page on every sort/filter/page/search change. DevicesPage (or a wrapper) owns the (page, pageSize, sort, filters) state and refetches on change. Search needs debouncing.

The design target I'd argue for: no user-visible cap on total fleet size. A user with 10,000 endpoints should be able to see, sort, filter, and act on every one of them — just not in a single HTTP round trip. The way you make that work without unbounded memory or query cost is cursor-based pagination with an internal per-page safety cap that the user doesn't have to think about. Walking the cursor chain is "give me everything"; the per-page max only exists to keep any one response bounded.

What I'd propose

A coherent design across four sub-decisions. None of these are dogmatic — laying them out together because they hang together.

1. Cursor pagination, keyset on (last_seen_at DESC, id). Stable under churn (every agent check-in bumps last_seen_at, which would shift rows between pages under offset pagination), constant per-page cost, scales to any fleet size. Trade-off: no random-access "jump to page 47," only forward/back. For an RMM device list I think that's correct — users search/filter to narrow, they don't navigate by page number.
2. Optional total count via includeTotal=true, off by default. A separate count(*) isn't free; let the Devices page opt in for "showing 1–25 of 312," let scripts/bulk consumers walk the cursor without paying. Skips the "approximate count" path entirely, which always feels wrong in an admin UI.
3. Extend listDevicesSchema with first-class params (status, osType, role, orgIds[], siteIds[], groupIds[], search); keep FilterConditionGroup as the escape hatch. Matches the pattern already in the codebase. Common path stays fast and predictable; advanced filter stays for the long tail.
4. Internal per-page max: 1000. Defensive only — caps any single response at ~2–3 MB JSON, ~5× the UI's largest natural page size (200), ~20× mobile's default (50). User never bumps into it.

Why these four together: cursor + optional-total + first-class-filters + 1000-cap form a design where the user sees "no cap on total fleet size" while every individual request is bounded for safety. A user with 10,000 endpoints walks the cursor chain to access all of them; the UI shows "showing 1–25 of 312" when it wants; scripts get fast responses without paying for counts they won't read; and no caller can blow up the server with a limit=1000000 request.

Where I'm least confident

• Cursor on a frequently-updated sort key. A device's last_seen_at can update between your "next page" request and the server's read of it; the cursor might briefly skip or duplicate a row at the boundary. The id tiebreaker helps but doesn't fully eliminate the case. Mitigation: stabilize on a less-volatile sort key (e.g., enrolled_at) for the cursor and sort client-side within the page, or accept the rare boundary anomaly as the cost. Want your read.
• 1000 vs 500 vs 2000. Defensible but arbitrary. Could shift after measuring real-world payload sizes on a 500-device + full-hardware response.

Happy to make any of these into a real spec doc before writing code — they're at the level of "broad intent" not "API contract" yet.

TimescaleDB compatibility

apps/api/migrations/optional/ includes TimescaleDB hypertable migrations for device_metrics. LATERAL + LIMIT 1 against the chunk-aware index is the recommended shape for "latest per series" queries on Timescale and gets even better there than on stock PG (chunk pruning skips old chunks entirely). I tested on stock PG; I haven't bench-tested with the Timescale hypertable. No reason to expect regression — flagging in case you have a Timescale env handy to spot-check.

Proposed PRs

If this framing makes sense to you, I'd split into three independent PRs you can approve separately:

• PR 1 — perf: the LATERAL+LIMIT 1 query swap. One file (routes/devices/core.ts), no schema change, no API contract change. Identical output payload. Has a unit-test update in the same PR that pins the new query shape and asserts response mapping. Tested live in production (the perf numbers above are from this exact change running against real data).
• PR 2 — cap lift: raise /devices maxLimit to 500 (or env-var driven if you prefer) + web fetch asks for it. Two files. Tactical band-aid that's still useful on its own because most early adopters are well under 500 devices.
• PR 3 — server-side pagination: the architectural change. Multi-PR design conversation; I'd want alignment on the four questions above before writing.

PR 1 and PR 2 are independent — either can land alone without affecting the other. Happy to open both as soon as you greenlight. PR 3 I'd prefer to do as a design doc / spec first so we're not negotiating cursor vs offset in review comments.

[ToddHebebrand]

Thanks @bdunncompany — went through all three against the code and the analysis holds end to end. Splitting into three independent PRs is the right call; taking them one at a time.

PR 1 — LATERAL + LIMIT 1 (perf). Green light. Confirmed core.ts:292-318 is still the max(timestamp) subquery + self-join you profiled; the LATERAL … LIMIT 1 swap against the existing (device_id, timestamp) PK is correct and the ~400× is credible. One nudge: you benched stock PG — spot-check against the optional TimescaleDB hypertable (apps/api/migrations/optional/) in the same PR, or note it explicitly as untested. Pin the new query shape in the unit test as you proposed.

PR 2 — cap lift. Already shipped — ac3f5d8f ("raise /devices limit cap to 500", #748). core.ts:200 is getPagination(query, 500) and DevicesPage.tsx:85 already fetches ?limit=500. Nothing to do here; PR 3 supersedes it for the Devices UI anyway.

PR 3 — server-side pagination. Green light on the architecture, and you were right to settle the four design questions before code rather than in review. Where I've landed on all four:

1. Default walk order is a stable key — hostname ASC, keyset (hostname, id). Deliberate change from the current last_seen_at DESC default. Keysetting the volatile default would skip actively-checking-in devices from a full-fleet walk (they jump up into already-seen pages) — that breaks the "see every device" goal this is designed for. last_seen_at/status stay user-selectable sorts; on those the client carries a seen-id Set to dedupe the bounded boundary fuzz. Net: the default fleet walk is provably skip/dup-free; volatile sorts are a transient view where minor fuzz is acceptable. First-paint sort changing to hostname is intentional (also better standalone UX than a list that reshuffles on every check-in).
2. includeTotal=true, opt-in, default off, computed once on the cursor-less first request (client carries it; cursor pages never recompute). Default-off because the failure modes are asymmetric — forgetting it costs the UI a cosmetic number; forgetting to opt out costs a script a count(*) per page across a 10k walk.
3. First-class params (sort, sortDir, role, orgIds[], siteIds[], groupIds[]) extending listDevicesSchema, with FilterConditionGroup via the existing filterEngine as the escape hatch — predicates only; the keyset ORDER BY/LIMIT is never delegated to the filter subsystem (a filter-supplied ORDER BY silently breaks keyset correctness).
4. One named constant DEVICES_LIST_HARD_MAX = 1000 as the defensive per-response ceiling — the property that matters is that it's a single tunable, not the specific number.

Two repo-shape constraints to fold in:

• The keyset needs covering indexes ((hostname, id), plus (last_seen_at, id) / (enrolled_at, id) / per whitelisted sort col). That's a migration — idempotent, date-prefixed, no inner BEGIN/COMMIT per CLAUDE.md.
• devices is RLS shape 1 (direct org_id); no new table so no policy change, but add a test that a partner-scope cursor walk and the includeTotal count both stay inside breeze_has_org_access (forge a cross-org cursor, assert no foreign rows).

Keep PR 1 and PR 3 independent — PR 1 can land first and makes every PR 3 page fast. PR welcome on both.

[ToddHebebrand]

Quick status checkpoint for anyone landing here:

• PR 1 (LATERAL + LIMIT 1 perf): ✅ merged as perf(api): /devices latest-metrics — LATERAL+LIMIT 1 over GROUP BY MAX #747
• PR 2 (cap lift to 500): ✅ merged as feat(devices): raise /devices limit cap to 500 for small-MSP fleets #748
• PR 3 (server-side keyset cursor): in review as feat(api): keyset cursor pagination for GET /devices (#742 PR 3 — server) #777 (API) + feat(web): DevicesPage walks /devices cursor (#742 PR 3b — web) #778 (web)

Will close this discussion once #777 + #778 land.

[ToddHebebrand]

Closing — all three PRs from the plan have landed:

• PR 1 (LATERAL + LIMIT 1 perf): perf(api): /devices latest-metrics — LATERAL+LIMIT 1 over GROUP BY MAX #747 ✅
• PR 2 (cap lift to 500): feat(devices): raise /devices limit cap to 500 for small-MSP fleets #748 ✅
• PR 3 (server-side keyset cursor): feat(api): keyset cursor pagination for GET /devices (#742 PR 3 — server) #777 (API) ✅ + feat(web): DevicesPage walks /devices cursor (#742 PR 3b — web) #778 (web) ✅ — both merged.

The Devices list now runs on keyset cursor pagination end-to-end. Thanks for the detailed perf writeup and scale plan.