console: Rank freshness percentiles over every 1-minute reading by jdonelson · Pull Request #40123 · MaterializeInc/materialize

jdonelson · 2026-10-23T15:05:32Z

The Freshness page's p90 ranked the graph's 60 bucket peaks, so its meaning changed with the range. At 24 hours it was the 7th worst 24-minute peak. It now ranks every 1-minute reading in the range, in SQL.

buildObjectLagPercentilesQuery returns each object's peak, p90, p99 and p99.9 by nearest rank. Each object keeps only the worst readings p90 needs (a LATERAL TopK), so no query sorts every reading. At 24 hours it took 49 ms for 20 objects, 411 ms for 200 and 828 ms for 400 in a local benchmark (v26.48), reading the lag index through the id list from console: stop full-scanning catalog indexes for clusters over 330 objects #40306.
buildScopeLagPercentilesQuery does the same over all objects' readings pooled. Its hook has no caller here; the statboxes in the next PR use it.
computeStats takes the percentiles from the query. pMAX comes from the same query, so no percentile can exceed it. pMAX and the graph are separate fetches on one schedule, so they can briefly disagree.

The UI half is the next PR in this stack, #40127: p99 and p99.9 columns, statboxes, and the page's fine print, which still describes the old p90 until then. Merge the two together.

Tests: SQL tests check every percentile against a reference computed from the raw rows, covering young objects, unreadable readings and readings outside the range. Unit tests cover the NULL-to-unreadable mapping and computeStats.

This release will compute the Freshness page's percentiles over every 1-minute reading in the selected range.

Closes: CNS-212

🤖 Generated with Claude Code

linear-code · 2026-10-23T15:05:39Z

CNS-212

leedqin

Loving the SQL tests: checking every percentile against a reference over young, unreadable and out-of-range objects is exactly the harness this needs. I ran the freshness suites on the stack head (79/79), eslint and tsc are clean, and I EXPLAINed both builders on a local v26.47.1 mz_catalog_server: index lookup and a consolidating monotonic TopK per object, as the docstring says. Nothing blocking. Two suggestions on the query, a few nits, and two things for the description:

The page note still describes the 60-bucket p90 until #40127 lands. We squash-merge one PR at a time, so main carries that copy for the gap. Could we move the rewritten note into this PR, or merge the two back to back?
Could you link #40127 by number here? It links back, so the stack reads both ways.

Follow-up, not for this PR: the page now sends four /api/sql requests per minute per tab at 1 hour (five with #40127), and each request is a fresh SQL session on mz_catalog_server. The series and the two percentile queries share inputs and schedule, so one executeSqlV2 call with several statements would collapse them. Pairs with CNS-186 for the IN-list size cliff.

leedqin · 2026-10-26T17:43:47Z

+ * percentile picks fall past the rows the queries keep and come back NULL,
+ * which reads as unreadable.
+ */
+function maxReadingsPerObject(lookbackMs: number) {


I think we can drop the recording-interval assumption by sizing the TopK from the data, and it keeps this plan. LIMIT (SELECT max(n) - ceil(0.9 * max(n))::bigint + 1 FROM counts) plans as TopK group_by=[object_id, …] limit=#2 with the time filter still pushed into the index lookup (tried on v26.47.1). A correlated LIMIT counts.n - ceil(0.9 * counts.n)::bigint + 1 also plans, but the optimizer then applies the time filter above the join, so I'd take the scalar subquery. Either way maxReadingsPerObject, this NOTE, and the silent NULL when the cadence differs all go away.

Thanks, this sent me digging. The limit can't fall short: mz_wallclock_global_lag_history truncates readings to the minute and keeps one per object per minute, so a shorter wallclock_lag_recording_interval adds no rows. The old NOTE was wrong about that, and 1638148 corrects it. I also tried the data-sized limit. It returned identical results but ran slower at 24 hours (462 → 635 ms at 200 objects), because the computed limit adds a cross join of every reading ahead of the TopK. So I kept the fixed limit.

leedqin · 2026-10-26T17:43:48Z

+
+type PercentileRow = Record<keyof LagPercentiles, IPostgresInterval | null>;
+
+const lagMs = (lag: IPostgresInterval | null) =>


Maybe keep the UNREADABLE mapping in computeStats, the way useLatestLag hands back number | null and currentFrom maps it? That keeps the sentinel in one file and drops the value import of freshnessRows from here, which is one step from a cycle with the type imports going the other way.

Done in 86dfc27. The percentile hooks return null for an unreadable rank, like useLatestLag, and computeStats maps it through one lagOrUnreadable helper. queries.ts no longer imports from freshnessRows. The statboxes in #40127 use the same helper.

leedqin · 2026-10-26T17:43:48Z

+ * The nearest-rank position of percentile `p` among `n` readings, counted from
+ * the worst, which is 1. Matches the pick the percentile queries make in SQL.
+ */
+export function rankFromWorst(p: number, n: number) {


Nit: no caller outside this module, so the export can go.

Done in 6dc6946.

leedqin · 2026-10-26T17:43:48Z

  return lines.map((line) => {
    const stats =
-      statsByKey.get(line.key) ?? computeStats(line.key, data, undefined);
+      statsByKey.get(line.key) ?? computeStats(undefined, undefined);


Nit: statsByKey is built from these same lines, so this fallback (and the one in buildFreshnessRows) never runs. Could drop both, or share one EMPTY_STATS.

Done in 6dc6946. Both use one EMPTY_STATS.

leedqin

Mostly looks good! Just left some comments

jdonelson · 2026-10-27T21:21:18Z

We'll merge #40123 and #40127 back to back. The description now links #40127, and I added the request-batching note to CNS-186.

The Freshness page's p90 ranked the graph's 60 bucket peaks, so its meaning changed with the range. At 24 hours it was the 7th worst 24-minute peak. It now ranks every 1-minute reading in the range, in SQL. - `buildObjectLagPercentilesQuery` returns each object's peak, p90, p99 and p99.9 by nearest rank. Each object keeps only the worst readings p90 needs (a `LATERAL` TopK), so no query sorts every reading. At 24 hours it took 41 ms for 20 objects and 390 ms for 200 in a local benchmark, using the index lookup. - `buildScopeLagPercentilesQuery` does the same over all objects' readings pooled. Its hook has no caller here; the statboxes in the next PR use it. - `computeStats` takes the percentiles from the query. pMAX comes from the same query, so no percentile can exceed it. pMAX and the graph are separate fetches on one schedule, so they can briefly disagree. The UI half is the next PR in this stack: p99 and p99.9 columns, statboxes, and the page's fine print, which still describes the old p90 until then. Merge the two together. Tests: SQL tests check every percentile against a reference computed from the raw rows, covering young objects, unreadable readings and readings outside the range. Unit tests cover the NULL-to-unreadable mapping and `computeStats`. This release will compute the Freshness page's percentiles over every 1-minute reading in the selected range. Closes: CNS-212 Co-Authored-By: Claude Opus 5.5 <[email protected]>

The kept-row count does not depend on the recording interval: mz_wallclock_global_lag_history keeps one reading per object per minute. Co-Authored-By: Claude Opus 5.5 <[email protected]>

The percentile hooks return null for a reading that could not be taken, as useLatestLag does, and computeStats maps both to UNREADABLE through one helper. queries.ts no longer imports from freshnessRows. Co-Authored-By: Claude Opus 5.5 <[email protected]>

Stop exporting rankFromWorst and replace the two unreachable stats fallbacks with one EMPTY_STATS. Co-Authored-By: Claude Opus 5.5 <[email protected]>

The percentile queries used a literal IN list, which past 330 ids scans the whole lag index. They now join buildIdListQuery like the other lag reads: 4.8 s to 0.8 s per object, 4.4 s to 0.3 s pooled, at 400 ids over 24 hours. Co-Authored-By: Claude Opus 5.5 <[email protected]>

leedqin

A couple of follow-ups from today's #40306 work, not blocking.

leedqin · 2026-10-28T20:47:00Z

+ * objects could have. Overestimating only keeps extra rows, because the rank
+ * a percentile needs never falls as the reading count grows.
+ */
+export function buildScopeLagPercentilesQuery({


Could you add both percentile queries at 2,000 ids to the timings, on a bench like #40306's (3,000 objects, 24 hours of readings)? That's the cluster size from today's incident, and the Summary query hasn't been timed yet.

Timed on a bench like #40306's (v26.48.0, 3,000 objects, 24 hours of per-minute readings, 25cc cluster, median of 5), with the lag series alongside as a reference (0.6 s at 1 hour, 2.4 s at 24 hours at 2,000 ids, against #40306's 0.9 s and 1.8 s):

2,000 ids 1 hour 24 hours

Per-object percentiles 548 ms 3,728 ms

Summary percentiles 448 ms 1,030 ms

At 300 ids they are 93/551 ms and 74/160 ms. Per minute, 1 hour is the heavier case: both refresh every minute there (about 1 s per minute per tab at 2,000 ids), while at 24 hours they refresh once per 24-minute bin.

leedqin · 2026-10-28T20:47:00Z

+ * Each object's peak and percentiles over every reading in the range, keyed by
+ * object ID. An object with no readings in the range is absent.
+ */
+export function useObjectLagPercentiles({


If those numbers come back high, I wonder if at 1 hour we could rank the series client-side, since its bins are single readings there. That would save two requests per minute at the default range.

Agreed it would work: at 1 hour the series already holds every reading the percentile queries rank. I filed it as CNS-227 with the numbers above, rather than add a second ranking path to these approved PRs. The main risk is keeping the TypeScript ranking identical to the SQL one, so the issue anchors both to the SQL test's reference implementation.

Stacked on [#40123](#40123), which adds the percentile queries. Merge the two together. - **Summary section:** pooled p90, p99, p99.9 and pMAX for the objects shown, from the pooled query in #40123. pMAX also takes in the latest minute, so it matches the table's pMAX and updates once a minute. The percentiles refresh once per graph point, because one new minute can't update a rank without the query. The boxes use the shared `MetricCard`, moved to `components/`. - **Threshold menu and table:** p99 added. The menu reads "whose [current | p90 | p99 | pMAX] freshness exceeds/exceeded", and the table columns are Now, p90, p99, pMAX. That settles the issue's open question: every menu option has a column to highlight, and p99.9 stays in the Summary, where pooling gives it enough readings. - **Controls:** a scope row ("[types] within [cluster] over the last [range]") and a highlight row below the Summary. Both render before the data loads, so changing the range doesn't remove them. The header is no longer pinned, and the headline above the graph is removed as redundant. - **Graph:** the x-axis shows clock times and dates on round times, spaced so labels don't overlap or clip. The y-axis leaves at least 10% headroom above the tallest line. The graph always spans the selected range, ending at now, and breaks a line where its object has no reading ([CNS-207](https://linear.app/materializeinc/issue/CNS-207)). - **Before a selection:** the prompt uses body text, and the page note waits until a cluster or schema is chosen. - **Page note:** rewritten around percentiles of 1-minute readings, including why p99 equals pMAX at short ranges. <img width="1285" height="699" alt="Screenshot 2026-10-26 at 9 31 56 AM" src="https://github.com/user-attachments/assets/116d9d3e-ce9c-4b4e-abe8-9d4e9875c3dc" /> Tests: `FreshnessStatboxes.test.tsx` covers the values, "Not queryable" and missing values, the latest minute raising pMAX, and a failed request. A page test checks that the Summary and controls render before the data arrives. Unit tests cover `latestPeak` and the p99 option. Graph tests cover the clock-time labels, tick spacing and edges, the headroom, and clipping. `fitToRange` and `useNow` have their own tests. This release will add p99 to the Freshness page's threshold menu and table, and a Summary of the scope's p90, p99, p99.9 and pMAX, and show the graph's full selected range with clock-time labels. Closes: [CNS-213](https://linear.app/materializeinc/issue/CNS-213) Closes: [CNS-207](https://linear.app/materializeinc/issue/CNS-207) 🤖 Generated with [Claude Code](https://claude.com/claude-code) --------- Co-authored-by: Claude Opus 5.5 <[email protected]>

jdonelson requested a review from a team as a code owner October 23, 2026 15:05

jdonelson requested a review from Alphadelta14 October 23, 2026 15:05

jdonelson mentioned this pull request Oct 23, 2026

console: Add freshness percentile statboxes and p99 #40127

Merged

jdonelson added this pull request to stack #40128 October 23, 2026 18:12

jdonelson force-pushed the jeremydonelson/cns-212-freshness-compute-percentiles-over-every-1-minute-reading branch from 9b731db to ea6fde4 Compare October 26, 2026 13:49

leedqin reviewed Oct 26, 2026

View reviewed changes

leedqin requested changes Oct 26, 2026

View reviewed changes

Alphadelta14 removed their request for review October 27, 2026 14:13

jdonelson requested a review from leedqin October 27, 2026 21:23

jdonelson force-pushed the jeremydonelson/cns-212-freshness-compute-percentiles-over-every-1-minute-reading branch from 6dc6946 to e231484 Compare October 28, 2026 19:59

jdonelson and others added 5 commits October 28, 2026 16:17

console: correct the freshness kept-row note

e459a5c

The kept-row count does not depend on the recording interval: mz_wallclock_global_lag_history keeps one reading per object per minute. Co-Authored-By: Claude Opus 5.5 <[email protected]>

console: freshness review nits

dc9ee1d

Stop exporting rankFromWorst and replace the two unreachable stats fallbacks with one EMPTY_STATS. Co-Authored-By: Claude Opus 5.5 <[email protected]>

jdonelson force-pushed the jeremydonelson/cns-212-freshness-compute-percentiles-over-every-1-minute-reading branch from e231484 to a6ea59d Compare October 28, 2026 20:17

leedqin reviewed Oct 28, 2026

View reviewed changes

leedqin approved these changes Oct 28, 2026

View reviewed changes

jdonelson merged commit 1c95906 into main Oct 29, 2026
90 checks passed

jdonelson deleted the jeremydonelson/cns-212-freshness-compute-percentiles-over-every-1-minute-reading branch October 29, 2026 17:33


		type PercentileRow = Record<keyof LagPercentiles, IPostgresInterval \| null>;

		const lagMs = (lag: IPostgresInterval \| null) =>

2,000 ids	1 hour	24 hours
Per-object percentiles	548 ms	3,728 ms
Summary percentiles	448 ms	1,030 ms

Conversation

jdonelson commented Oct 23, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

linear-code Bot commented Oct 23, 2026

Uh oh!

leedqin left a comment

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

leedqin left a comment

Choose a reason for hiding this comment

Uh oh!

jdonelson commented Oct 27, 2026

Uh oh!

leedqin left a comment

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Reviewers

Uh oh!

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

jdonelson commented Oct 23, 2026 •

edited

Loading