console: stop full-scanning catalog indexes for clusters over 330 objects by leedqin · Pull Request #40306 · MaterializeInc/materialize

leedqin · 2026-10-28T15:35:14Z

Motivation

A production cluster with about 2,000 objects pinned mz_catalog_server at roughly 99% CPU while people viewed it in the Console. Turning off console-freshness-2855 for that organization brought it down to about 32%.

The Console sent every object id on the cluster as a literal object_id IN (...) list. Past 330 entries, Materialize stops planning that list as an index lookup. It scans the whole index instead and evaluates one OR term per id on every row. #38717 is the optimizer-side fix. This PR removes the Console's exposure without waiting for a release.

What changes

New buildIdListQuery (console/src/api/materialize/idList.ts): the ids as an unnested array, joined against the indexed relation. It drops repeated ids, since a join would repeat their rows and double GROUP BY counts.
These builders now join that list instead of a literal IN:
- buildObjectLagHistoryQuery, buildLatestLagQuery, buildHydrationCountsQuery (Freshness card, Freshness page, critical path, object freshness panel)
- buildMaterializationLagQuery (Indexes, Materialized Views, Largest maintained, workflow graph, Shell plan insights)
- buildObjectHydrationTimesQuery, buildObjectCreationTimesQuery (cluster page hydration timeline)
buildMaterializationLagQuery no longer joins mz_objects. It only supplied type, which none of its cluster-page callers read.
The Shell's plan insights take each import's type, source type and cluster from useAllObjects. buildBlockedDependenciesQuery and its five catalog joins are gone.

Plans on `mz_catalog_server`

EXPLAIN of the compiled queries, on v26.48 with the Console's search_path and serializable isolation, reading the real relations. The cells show the Used Indexes footer at 2,000 ids.

Query	`main`	This PR
Lag series (1h and 24h)	lag index full scan	lag index differential join
Latest lag	lag index full scan	lag index differential join
Materialization lag	lag index full scan, `mz_objects` full scan	lag index differential join
Hydration times (30s poll)	hydration times full scan	hydration times lookup by replica
Creation times	object lifetimes full scan	object lifetimes differential join
Hydration counts	full scan	full scan

Up to 330 ids, main reads each of these by lookup. Hydration counts scan in both versions, because mz_hydration_statuses_ind is keyed on (object_id, replica_id). The scan no longer evaluates a per-row OR over every id.

Timings

Compiled queries run on a local mz_catalog_server (v26.48-dev) with the Console's session, reading the real catalog relations. Median of 5 runs:

Query	ids	`main`	This PR
Latest lag	400	2,632 ms	11 ms
Materialization lag	400	2,954 ms	55 ms
Materialization lag	2,000	14,766 ms	255 ms
Hydration counts	400	49 ms	9 ms
Creation times	400	27 ms	11 ms
Hydration times	400	25 ms	10 ms

At 20 ids both versions were within a millisecond of each other. That includes hydration times and creation times, which took the fast path on main and now build a dataflow.

At production scale, on a one-worker bench cluster. Copies of the catalog relations carry production's indexes, with 3,000 objects, 24h of per-minute lag readings (4.32M rows) and 63k object lifetime events. Sequential runs, median of 9 (3 for main at 2,000 ids). Both versions returned the same rows at 300 ids.

Query	50 ids	300 ids	2,000 ids
Lag series 1h	30 → 28 ms	163 → 144 ms	169.7 s → 0.9 s
Lag series 24h	56 → 48 ms	285 → 267 ms	170.5 s → 1.8 s
Latest lag	21 → 19 ms	110 → 92 ms	169.9 s → 0.6 s
Hydration counts	17 → 5 ms	86 → 11 ms	552 → 60 ms
Materialization lag	83 → 32 ms	450 → 128 ms	170.8 s → 0.8 s
Hydration times	16 → 7 ms	80 → 14 ms	450 → 67 ms
Creation times	4.6 → 5.1 ms	19 → 11 ms	4.3 s → 61 ms

The one slower cell is creation times at 50 ids, where main took the fast path. Latest lag at 2,000 ids returned no rows on either version, because by then the bench readings had aged past its two-minute window, so main's time there is the scan alone.

Behavior changes

buildMaterializationLagQuery returns a row for an id with no catalog object, with null hydration and lag. Before, the mz_objects join dropped it. Its callers look rows up by their own ids, so nothing renders differently.
Shell plan insights wait for the allObjects snapshot before publishing. If that subscribe errors, they publish without the blocked-dependency insights.

Tests

SQL plan tests that EXPLAIN each changed query at 400 ids on mz_catalog_server. They fail on main's builders and pass here.
SQL result tests for the newest reading at 400 ids, and for a repeated id in hydration counts. The latter fails if buildIdListQuery keeps repeated ids.
Unit tests for toBlockedDependencies, and for useBlockedDependencies staying in loading until allObjects has its snapshot.
The buildBlockedDependenciesQuery SQL test is removed along with the builder.

Release notes

This release will reduce mz_catalog_server load from the Console on clusters with more than 330 objects.

🤖 Generated with Claude Code

…ects The Console passed every object id on a cluster to `mz_catalog_server` as a literal `IN (...)` list. Past 330 entries Materialize plans that list as a full scan of the index, with one OR term per id evaluated on every row, so viewing a cluster with about 2,000 objects pinned the catalog server. Route those id lists through `buildIdListQuery`, an `unnest`ed array joined against the indexed relation, which reads the index by key at any size. This covers the freshness series, latest lag and hydration counts, the materialization lag query behind the Indexes, Materialized Views, Largest maintained and workflow graph views, and the cluster page's hydration timeline (hydration times and creation times). The materialization lag query also drops its `mz_objects` join, which only supplied a type its cluster-page callers never read. The Shell's plan insights now label blocked dependencies from `useAllObjects` instead of joining `mz_objects`, `mz_sources`, `mz_indexes`, `mz_materialized_views` and `mz_clusters`. Closes: CNS-186

Plan tests now assert the label EXPLAIN prints for the expected read, such as `(differential join)` or `(lookup)`, through one shared `explainOnCatalogServer` helper. `buildIdListQuery` moves into `expressionBuilders.ts` with the other shared subquery builders. Plan insights get a test for an `allObjects` subscribe that errors before its snapshot, which must end loading rather than hold the notice back.

ggevay

Thank you, LGTM from the database side!

Some minor comments, but ok to fix those in followup PRs to have at least the current fixes in as soon as possible.

ggevay · 2026-10-28T17:05:24Z

+ * Joined to a relation indexed on the id alone, the list reads it by key at any
+ * size (EXPLAIN on v26.48 at 2000 ids).
+ */
+export function buildIdListQuery(ids: string[]) {


Could useArrangmentsMemory (arrangementMemory.ts:47) use buildIdListQuery too, here or in a follow-up?

It sends every index or MV id on the cluster as a literal IN list, every 5 s, from the Indexes and Materialized Views tabs. The query runs on the cluster's own replica, so it doesn't scan anything on mz_catalog_server. The cost is in optimization instead: the IN list is copied into every branch of the mz_objects union. On v26.47.1 with 2,000 ids, optimizing takes 5.2 s per execution, which is longer than the 5 s polling interval. So one open tab keeps about one environmentd core busy.

Separately, two other places still send literal id lists: workflowGraphNodes.ts (every node of an object's upstream and downstream graph, which can exceed 330 for a source that feeds many objects) and nonRunningSources.ts (a Shell query's imports).

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]>

Dates in RC snapshots are provisional until the release ships. One commit is added per snapshot; the final snapshot sets the real dates. ## ⚠️ Needs the release owner before this can merge **1. This PR conflicts with `main`, and the conflict is substantive.** While this PR sat open, [#40201](#40201) ("docs: describe which SQL Server constraint Materialize records as a key") merged to `main` on 2026-11-05 and independently added its own `## v26.49.0` section — an `### Advisory {#v26.49-advisory}` plus an `### Improvements {#v26.49-improvements}` bullet. Both sides edit the same region of `_index.md`, so `mergeable` is `CONFLICTING`. Two problems ride along with the conflict: - **The same PR is described twice.** [#40201](#40201) documents the behavior of [#40131](#40131), which `rc.1` already published here as a Bug Fix ("Reduced the risk of new SQL Server source tables returning incorrect results after a unique constraint or primary key is dropped upstream…"). *Recommendation: keep `#40201`'s Advisory and Improvement and drop the `rc.1` bug-fix bullet* — the hand-written pair is more complete, carries the upgrade-impact guidance, and links `/ingest-data/sql-server/#unique-keys`. - **Anchor collision.** `main`'s `## v26.49.0` carries `{#v26.49-advisory}` and `{#v26.49-improvements}`; this branch's combined section carries `{#v26.49-improvements}`, `{#v26.49-agent-skills}`, and `{#v26.49-bug-fixes}`. Merging both defines `#v26.49-improvements` twice on one page. *Recommendation: rename this branch's three anchors to `{#v26.49.1-improvements}`, `{#v26.49.1-agent-skills}`, `{#v26.49.1-bug-fixes}`* — which is what the `v26.49.1` snapshot's review notes intended — *and leave `{#v26.49-advisory}` and `{#v26.49-improvements}` to `#40201`'s section.* `#40201`'s review links already point at `/releases/#v26.49-advisory`, and the compatibility YAML's `/releases/#v26491` target is unaffected. **2. Wording drift against the merged `v26.49.1` snapshot.** Commit `39e6b854a` landed here on 2026-11-05, before [MaterializeInc/mz-skills#355](MaterializeInc/mz-skills#355) merged on 2026-11-06. Two entries read differently on the branch than in the merged snapshot — the hydration improvement is "Investigate hydration on the cluster details page" here versus "See how a cluster hydrated, and why it did not" there, and the Freshness and Disk-column bullets differ likewise. Resolving the conflict above is the natural moment to decide which wording ships. ## Note on the section heading The `v26.49.1` patch train reused this branch (commit `39e6b854a`) rather than opening its own, so this PR now publishes a single combined `## v26.49.1` section — this train's `rc.1`–`rc.3` entries plus the three `v26.49.1` entries, with the Cloud version in parentheses. That is the house convention for a patch that ships in its parent's rollout week; compare the published `## v26.48.2` (`*Released to Materialize Cloud: 2026-10-29 (v26.48.0)*`). The compatibility YAML accordingly carries one `v26.49.1` row dated 2026-11-06, which is what actually shipped to Self-Managed that day. The `final` commit for this train is therefore **empty**: its increment carries no entries ([MaterializeInc/release#635](MaterializeInc/release#635) states "No PRs in `v26.49.0` require review", `v26.49.0-rc.3...v26.49.0` is the lone version-bump commit, and the agent-skills window is empty), and the real dates — Cloud **2026-11-05**, Self-Managed **2026-11-06** — were already set by `39e6b854a`. It exists so the branch's commit-subject ledger records that the snapshot was applied. ## Decisions required (carried over from `rc.1`) - **`DROP INDEX`** ([#39098](#39098)): it now rejects drops that used to succeed. Call it out as a breaking change rather than an Improvement? - **Console on large clusters** ([#40306](#40306)): the Cloud Console deploys separately, so this may already be live. Keep it in v26.49? - **Maintained Objects page** ([#40105](#40105)): omitted because it is behind a LaunchDarkly flag. Is it generally available? - **`mz profile init` OAuth** ([#40319](#40319)): omitted because the PR lists open items. Which `mz` release carries it? - **Upsert v2** ([#40189](#40189)): still omitted because `enable_upsert_v2` is `false`. Publish it? ## Snapshot drafts (with PR links) in mz-skills - rc.1: [release_notes.md](https://github.com/MaterializeInc/mz-skills/blob/main/data/mz-release-notes/v26.49.0/rc.1/release_notes.md), [omitted.md](https://github.com/MaterializeInc/mz-skills/blob/main/data/mz-release-notes/v26.49.0/rc.1/omitted.md), [MaterializeInc/mz-skills#351](MaterializeInc/mz-skills#351) - rc.2: [release_notes.md](https://github.com/MaterializeInc/mz-skills/blob/main/data/mz-release-notes/v26.49.0/rc.2/release_notes.md), [omitted.md](https://github.com/MaterializeInc/mz-skills/blob/main/data/mz-release-notes/v26.49.0/rc.2/omitted.md), [MaterializeInc/mz-skills#352](MaterializeInc/mz-skills#352) - rc.3: [release_notes.md](https://github.com/MaterializeInc/mz-skills/blob/main/data/mz-release-notes/v26.49.0/rc.3/release_notes.md), [omitted.md](https://github.com/MaterializeInc/mz-skills/blob/main/data/mz-release-notes/v26.49.0/rc.3/omitted.md), [MaterializeInc/mz-skills#353](MaterializeInc/mz-skills#353) - final: [release_notes.md](https://github.com/MaterializeInc/mz-skills/blob/main/data/mz-release-notes/v26.49.0/final/release_notes.md), [omitted.md](https://github.com/MaterializeInc/mz-skills/blob/main/data/mz-release-notes/v26.49.0/final/omitted.md) **Borderline PRs omitted:** rc.1: 10 — [rc.1/omitted.md#borderline](https://github.com/MaterializeInc/mz-skills/blob/main/data/mz-release-notes/v26.49.0/rc.1/omitted.md#borderline), rc.2: 0, rc.3: 0, final: 0 🤖 Generated with [Claude Code](https://claude.com/claude-code) --------- Co-authored-by: Claude <[email protected]> Co-authored-by: Valerie Chiang <[email protected]> Co-authored-by: Pranshu Maheshwari <[email protected]>

leedqin requested a review from ggevay October 28, 2026 15:36

leedqin marked this pull request as ready for review October 28, 2026 15:47

leedqin requested a review from a team as a code owner October 28, 2026 15:47

leedqin requested a review from jdonelson October 28, 2026 15:47

ggevay approved these changes Oct 28, 2026

View reviewed changes

leedqin merged commit 6d239fe into MaterializeInc:main Oct 28, 2026
84 checks passed

jdonelson mentioned this pull request Oct 28, 2026

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

Merged

bosconi mentioned this pull request Nov 4, 2026

v26.49.0 release notes #40469

Merged

Navigation Menu

Repository navigation

console: stop full-scanning catalog indexes for clusters over 330 objects - #40306

console: stop full-scanning catalog indexes for clusters over 330 objects#40306
leedqin merged 2 commits into
MaterializeInc:mainfrom
leedqin:console-catalog-id-joins

leedqin commented Oct 28, 2026 •

edited by github-actions Bot

Loading

Uh oh!

ggevay left a comment

Uh oh!

ggevay Oct 28, 2026

Uh oh!

Uh oh!

Reviewers

Uh oh!

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

Conversation

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

Uh oh!

Motivation

What changes

Plans on mz_catalog_server

Timings

Behavior changes

Tests

Release notes

Uh oh!

ggevay left a comment

Choose a reason for hiding this comment

Uh oh!

ggevay Oct 28, 2026

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Reviewers

Uh oh!

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

leedqin commented Oct 28, 2026 •

edited by github-actions Bot

Loading

Plans on `mz_catalog_server`