console: Add freshness percentile statboxes and p99 by jdonelson · Pull Request #40127 · MaterializeInc/materialize

jdonelson · 2026-10-23T18:12:19Z

Stacked on #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 console: Rank freshness percentiles over every 1-minute reading #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).
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.

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
Closes: CNS-207

🤖 Generated with Claude Code

linear-code · 2026-10-23T18:12:27Z

CNS-213

CNS-207

def- · 2026-10-23T19:06:18Z

QA LLM Review

1. MEDIUM -- Summary says "No readings in this range" while the objects subscribe is still loading

console/src/platform/freshness/FreshnessPage.tsx:241

Before the all-objects subscribe delivers its snapshot, FreshnessContent renders the Summary with objectIds=[]. useScopeLagPercentiles then resolves to null without sending a request, so all four cards show "—" and "No readings in this range" for a scope that may have plenty of readings. This happens on every cold load of a shared ?cluster=/?schema= link, until that snapshot arrives. It is the false empty state the objects.length === 0 && snapshotComplete guard at :223 exists to prevent.

Details

FreshnessPage waits only for the clusters or schemas snapshot (scopeReady), not for useAllObjects. While allObjects is still { data: [], snapshotComplete: false }, the guard at :223 falls through. statboxObjectIds is [], the scope query's queryFn returns null at queries.ts:196, isLoading goes false, and StatCard takes the value === undefined branch at FreshnessStatboxes.tsx:69. The graph area below still shows its spinner, so the page claims "no readings" right above an indicator that says it is still loading. Before this PR, that window showed only the loader.

I reproduced it by rendering FreshnessPage at /?cluster=u1 with allObjects set to { data: [], snapshotComplete: false } and every /api/sql request held open. The Summary text was p90—No readings in this range p99—No readings in this range p99.9—No readings in this range pMAX—No readings in this range, and no SQL request was sent. As a control, the same render with one object and snapshotComplete: true shows four skeletons and no captions. The new page test only covers the control case, because renderAt seeds allObjects with snapshotComplete: true.

Fix: treat !snapshotComplete as loading for the Summary. For example, render the cards' skeleton state until the objects snapshot lands, or disable the scope query (enabled: snapshotComplete) and show the skeleton while it is disabled.

jdonelson · 2026-10-26T13:44:32Z

Re the QA review's MEDIUM finding (Summary shows empty values while the objects subscribe is still loading): fixed in ea5ba4b. The Summary's query waits for the objects snapshot and the cards show their loading state until then. A page test covers a link opened before the snapshot lands.

(The "No readings in this range" caption it quoted was removed in 8b0d5f9. The cards showed "—" in that window until this fix.)

leedqin

Great work keeping the Summary and the controls up across a range change, and the statboxes tests read the way the page does. Suites 79/79, eslint and tsc clean on this head, and the QA review's Summary-before-snapshot finding is fixed here. Small nits plus two product questions, none blocking.

Is there a Figma for the Summary and the new control rows? The screenshot helps, but a design link in the description is what we usually go by.
Cost note for later: the Summary's TopK keeps objects × 1,441 rows at 24 hours plus one row_number over them, so on the biggest clusters it will take a few seconds per refresh, on top of the IN-list cliff. Fine for now, worth folding into CNS-186.

For whoever runs it in a browser: "Not queryable" at heading size inside a two-column card at narrow widths, and the Summary grid in dark mode.

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

+  { key: "peak", label: "pMAX" },
+];
+
+const StatCard = ({


Could we reuse MetricCard? Same label-over-value tile on Chakra Card. If it moves to components/, both pages share one.

Done in 4aeaf44. MetricCard moved to components/ unchanged, and the statboxes pass the theme's tertiary background to keep their grey fill.

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

+      borderWidth="1px"
+      borderColor={colors.border.primary}
+      borderRadius="lg"
+      background="chakra-subtle-bg"


Nit: chakra-subtle-bg isn't used anywhere else in the Console; surfaces come from the theme (colors.background.secondary). How does this look in dark mode?

Covered by 4aeaf44. The fill now comes from colors.background.tertiary, which matches the old grey in light and dark.

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

  }),
-  columnHelper.accessor("p90", {
-    header: "p90",
+  columnHelper.accessor("p99", {


At the default 1 hour every row's p99 equals its pMAX (61 readings), so the table opens with two identical columns. Worth a product check: grey p99 under 100 readings, or default the range to 24 hours? CNS-213 also had product suggesting Now, p90 and pMAX, so flagging that this settles it.

Deliberate. Product wants p99, and the page's fine print explains the overlap at 1 hour.

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

+ * Pooling dilutes one slow object with the healthy ones' minutes, so these
+ * describe the scope, not any one object, and never judge by the threshold.
+ */
+const FreshnessStatboxesInner = ({


Nit: we use the Inner suffix for a suspense split, and here it's the memo target. Maybe FreshnessStatboxesBase, or memo inline.

Done in c8dcc90, as ...Base. I renamed FreshnessTableInner the same way so the two match.

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

+      padding="3"
+    >
+      <Text textStyle="text-ui-reg" color={colors.foreground.secondary}>
+        Highlight objects that had a


Nit: "had a current freshness exceeding" reads oddly for the current option. Maybe "whose [current] freshness exceeded".

Done in c8dcc90: "whose [statistic] freshness exceeds" for current and "exceeded" for the others.

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

+            represents the worst minute during a 6-minute span.
+          </Text>
+          <Text textStyle="text-small">
+            A percentile needs enough readings to differ from pMAX: about 100


Nit: five paragraphs is a lot of fine print. I'd keep the two that change what a reader does (what a percentile ranks, why p99 equals pMAX at 1 hour) and move the graph-vs-Now mechanics to a tooltip. Not blocking.

jdonelson · 2026-10-27T21:11:31Z

Re the Summary cost note: I checked scope sizes against catalog snapshots from the last 7 days, covering 90 production deployments (staging excluded).

Objects per cluster, as cluster mode loads them (617 clusters): 457 have 20 or fewer, 151 have 21–200, 5 have 201–330, 3 have 331–1,000, and 1 has more than 1,000 (the largest, 1,315).

Objects per schema, as schema mode loads them (1,213 schemas): 1,081 have 20 or fewer, 126 have 21–200, 1 has 201–330, 3 have 331–1,000, and 2 have more than 1,000 (the largest, 1,882). The snapshot records no schema for indexes, so these run slightly low.

The scope query measured locally at 24 hours on v26.47.1: 30 ms for 20 objects, 171 ms for 200 and 253 ms for 300, about 0.85 ms per object. That puts the largest production cluster near 1.1 s per refresh and the largest schema near 1.6 s. The multi-second cases are in staging, which has clusters of up to 10,000 objects and schemas of up to 28,908. A busy production mz_catalog_server may run slower than a laptop, so treat these as a floor.

jdonelson · 2026-10-27T21:22:55Z

No Figma; the screenshot is the reference. Checked in a browser: "Not queryable" wraps inside the card at narrow widths and the card grows to fit. The Summary reads fine in dark mode.

- A collapsible Summary section shows the scope's pooled p90, p99, p99.9 and pMAX, from the pooled query in the previous commit. Its pMAX also takes in the latest minute, so like the table's pMAX it updates once a minute. - The threshold menu offers current, p90, p99 and pMAX. The table gains a p99 column, ordered Now, p90, p99, pMAX. - The controls split into a scope row ("[types] within [cluster] over the last [range]") and a highlight row below the Summary. Both render before the data loads, so a range change cannot remove them. The header is no longer pinned, and the redundant headline is removed. - The page note explains percentiles of 1-minute readings and why p99 equals pMAX at short ranges. Closes: CNS-213 Co-Authored-By: Claude Opus 5.5 <[email protected]>

Drop the captions from the Summary cards and give them the subtle background. "—" and "Not queryable" remain as values. Co-Authored-By: Claude Opus 5.5 <[email protected]>

Before the objects subscribe delivers its snapshot, the Summary saw an empty scope and showed "—" in every card. It now waits, showing its loading state, and sends no query until the list is complete. Co-Authored-By: Claude Opus 5.5 <[email protected]>

The scope percentile hook returns null for an unreadable rank, so the statboxes map it with lagOrUnreadable, as computeStats does. Co-Authored-By: Claude Opus 5.5 <[email protected]>

Moves MetricCard to components/ and renders the freshness statboxes with it. An optional background lets the statboxes keep their grey fill, now from the theme's tertiary surface. Co-Authored-By: Claude Opus 5.5 <[email protected]>

The prompt shown before a cluster or schema is chosen uses body text, and the fine print waits for a selection, since it explains numbers that are not on screen yet. Co-Authored-By: Claude Opus 5.5 <[email protected]>

Name the memoized statboxes and table components ...Base, keeping Inner for suspense splits, and word the threshold sentence "whose [statistic] freshness exceeds" for current and "exceeded" otherwise. Co-Authored-By: Claude Opus 5.5 <[email protected]>

Ticks show 24-hour local time and zone over the date, as the connector graphs do, on round clock times spaced so labels neither overlap nor clip. MultilineXTick moves to graphComponents for both. Co-Authored-By: Claude Opus 5.5 <[email protected]>

The y-axis top is rounded up from 110% of the highest reading, so a peak on a round value no longer draws along the graph's top border. Co-Authored-By: Claude Opus 5.5 <[email protected]>

The graph runs from the start of the range to now, advanced each minute, with a point per bin so a line breaks where its object has no reading. Lines are clipped to the plot. Part of CNS-207. Co-Authored-By: Claude Opus 5.5 <[email protected]>

It left a wide gap before "over the last" when the selected name, or the placeholder, was shorter than the minimum. Co-Authored-By: Claude Opus 5.5 <[email protected]>

The scope row becomes [Cluster ▾ | picker] [Time range] [Object types] using a new ScopedSelect, styled like the sidebar's links so the scope menu reads as a control beside the static labels. Its whole purple area opens the menu. One frame now holds the highlight row, graph and tables. SearchableSelect gains frameless, and LabeledSelect labels no longer wrap. Co-Authored-By: Claude Opus 5.5 <[email protected]>

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 #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](https://linear.app/materializeinc/issue/CNS-212) 🤖 Generated with [Claude Code](https://claude.com/claude-code) --------- Co-authored-by: Claude Opus 5.5 <[email protected]>

def- · 2026-10-29T18:07:28Z

QA LLM Review (Post Merge)

@jdonelson — an automated review of commit 3ebcc478626 found the following potential MEDIUM+ issue(s) after this PR was merged.

1. MEDIUM -- The graph drops the newest fetched minute whenever `now` is older than it

console/src/platform/freshness/graphWindow.ts:35

fitToRange only keeps bins with timestamp <= nowMs, and nowMs comes from useNow(60_000), which can be up to a minute stale. When the series refetch lands after a minute boundary that the last useNow tick came before, the server's newest bin is thrown away. At the default 1 hour range (60 s bins), the graph then leaves out the current minute for up to a minute, while the "Now" column, pMAX and the line's red highlight already show it.

Details

The newest reading's occurred_at is the minute just passed (maybe_record_wallclock_lag truncates to the recording interval), so a fetch at 14:31:05 returns bin 14:31:00. With useNow's last tick at 14:30:40, the loop stops at 14:30:00. I confirmed this by running fitToRange verbatim with those inputs: the 14:31 bin and its spike are missing from the output.

This happens again and again, not just once. TanStack restarts refetchInterval after every query update, and window focus refetches too, so the fetch time drifts against the fixed useNow interval. With exceeded=current, a row can turn red from a spike that its drawn line does not reach. Client clock skew has the same effect: a clock behind the server always hides the newest minutes, and a clock ahead shows a blank stretch at the right that reads as "no readings". Before this change the graph used server timestamps only.

Fix: end the window at the newer of the two times, e.g. const endMs = Math.max(nowMs, data.endTime), and use endMs for startTime, the loop bound and endTime.

jdonelson requested a review from a team as a code owner October 23, 2026 18:12

jdonelson requested a review from leedqin October 23, 2026 18:12

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

jdonelson force-pushed the jeremydonelson/cns-213-freshness-percentile-statboxes-and-stable-percentile-display branch from ea5ba4b to 83d4d05 Compare October 26, 2026 13:49

leedqin mentioned this pull request Oct 26, 2026

console: Rank freshness percentiles over every 1-minute reading #40123

Merged

leedqin reviewed Oct 26, 2026

View reviewed changes

leedqin requested changes Oct 26, 2026

View reviewed changes

jdonelson force-pushed the jeremydonelson/cns-213-freshness-percentile-statboxes-and-stable-percentile-display branch from 83d4d05 to c8dcc90 Compare October 27, 2026 21:22

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

jdonelson force-pushed the jeremydonelson/cns-213-freshness-percentile-statboxes-and-stable-percentile-display branch from 1777339 to 6241681 Compare October 28, 2026 19:59

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

console: Show only label and value in freshness statboxes

ca1077e

Drop the captions from the Summary cards and give them the subtle background. "—" and "Not queryable" remain as values. Co-Authored-By: Claude Opus 5.5 <[email protected]>

console: mark unreadable scope percentiles in the statboxes

6af0c66

The scope percentile hook returns null for an unreadable rank, so the statboxes map it with lagOrUnreadable, as computeStats does. Co-Authored-By: Claude Opus 5.5 <[email protected]>

console: body-size freshness prompt, note only with a selection

d55e0aa

The prompt shown before a cluster or schema is chosen uses body text, and the fine print waits for a selection, since it explains numbers that are not on screen yet. Co-Authored-By: Claude Opus 5.5 <[email protected]>

console: freshness review nits

b129979

Name the memoized statboxes and table components ...Base, keeping Inner for suspense splits, and word the threshold sentence "whose [statistic] freshness exceeds" for current and "exceeded" otherwise. Co-Authored-By: Claude Opus 5.5 <[email protected]>

console: leave headroom above the freshness graph's tallest line

82b3b15

The y-axis top is rounded up from 110% of the highest reading, so a peak on a round value no longer draws along the graph's top border. Co-Authored-By: Claude Opus 5.5 <[email protected]>

console: drop the min-width around the freshness scope picker

37f44ce

It left a wide gap before "over the last" when the selected name, or the placeholder, was shorter than the minimum. Co-Authored-By: Claude Opus 5.5 <[email protected]>

jdonelson force-pushed the jeremydonelson/cns-213-freshness-percentile-statboxes-and-stable-percentile-display branch from 6241681 to 37f44ce Compare October 28, 2026 20:17

leedqin approved these changes Oct 28, 2026

View reviewed changes

jdonelson merged commit 3ebcc47 into main Oct 29, 2026
82 checks passed

jdonelson deleted the jeremydonelson/cns-213-freshness-percentile-statboxes-and-stable-percentile-display branch October 29, 2026 17:33

Conversation

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

Uh oh!

Uh oh!

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

Uh oh!

Uh oh!

def- commented Oct 23, 2026

QA LLM Review

1. MEDIUM -- Summary says "No readings in this range" while the objects subscribe is still loading

Uh oh!

jdonelson commented Oct 26, 2026

Uh oh!

leedqin left a comment • edited Loading Uh oh! There was an error while loading. Please reload this page.

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!

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!

jdonelson commented Oct 27, 2026

Uh oh!

jdonelson commented Oct 27, 2026

Uh oh!

Uh oh!

def- commented Oct 29, 2026

QA LLM Review (Post Merge)

1. MEDIUM -- The graph drops the newest fetched minute whenever now is older than it

Uh oh!

Reviewers

Uh oh!

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

jdonelson commented Oct 23, 2026 •

edited by github-actions Bot

Loading

linear-code Bot commented Oct 23, 2026 •

edited

Loading

leedqin left a comment •

edited

Loading

1. MEDIUM -- The graph drops the newest fetched minute whenever `now` is older than it