1---
2name: mz-optimize-memory
3description: >
4 Reduce the memory footprint and cost of Materialize compute clusters:
5 find where arrangement memory sits, pick and size the right optimization
6 lever (index strategy, GROUP SIZE hint tuning, view slimming, subquery
7 decorrelation), adjudicate
8 proposed index or view changes, run measured experiments safely, and
9 package verified changes. Use when a Materialize cluster or replica uses
10 too much memory or is OOMing or crash-looping, when the user wants to
11 downsize a replica or cut Materialize spend, when deciding which indexes
12 to add or drop, or when tuning GROUP SIZE hints.
13metadata:
14 source: https://skillmd.com/skills/materializeinc/mz-optimize-memory
15---
16
17# Optimize Cluster Memory
18
19Reduce what a Materialize compute cluster spends on memory, with every
20claim backed by a measurement. The deliverable of an engagement is a set
21of per-item verdicts with measured evidence, validated changes expressed
22in the user's own deployment tooling, and a right-sized replica at the
23end. Freed memory is not saved money until the replica is resized down.
24
25For freshness problems (an object lagging behind wall-clock time) use
26the materialize-debug-freshness skill instead. Memory work and freshness
27work share instruments but have different workflows.
28
29## Ground rules
30
31- Ask before changing anything, and before reading the user's data. Catalog,
32 introspection and EXPLAIN reads need no permission; a SELECT over the user's
33 relations (a cardinality probe, the match-rate query, an exactness proof)
34 reads their data and is asked for first. Anything that creates, alters,
35 drops, or writes rows, in the user's environment or on their machine
36 (including a local Docker container), is proposed first and run only after a
37 yes. Approval covers a class of change, not each statement: an approved
38 experiment cluster covers building and dropping candidate objects on it, and
39 anything beyond it needs a fresh ask.
40- Never change objects on production clusters, even with permission to
41 experiment. Object definitions usually have a source of truth
42 outside Materialize (mz-deploy, dbt, or deploy scripts), and a
43 catalog-only change is silently reverted, or half-reverted, by the
44 next deploy. Accepted changes are delivered as edits to that source
45 of truth, or as DDL for the user to run where the catalog is the
46 source.
47- Experiments run on a dedicated experiment cluster that the user
48 creates (you supply the commands). Never build or rebuild candidate
49 objects on a production cluster: an object built there hydrates on
50 the production replica and spends production memory and CPU on an
51 experiment. Read-only `EXPLAIN ANALYZE` and introspection reads against
52 production are diagnosis and are fine; anything that hydrates goes
53 to the experiment cluster.
54- Do not recommend `REFRESH EVERY` materialized views as an optimization
55 lever in any situation.
56- Every verdict rests on a measurement: both sides sized, the join key read
57 from the plan, the consumer count counted. A graph-shape or plan-text
58 argument alone is not a verdict, and an estimate gates an experiment but
59 never settles one: shared arrangements restructure join plans in ways static
60 bytes-per-row arithmetic (the width math of step 5) cannot see, so an
61 estimate can err either way, and any non-trivial index or boundary verdict (a
62 boundary: an indexed view placed at a (collection, key) point several
63 consumers share) gets a measured build before it ships.
64- Leave-alone is the default outcome of a check, and it stays in your
65 notes. Check many things (a hint that measures correct, an exact
66 min/max or TopK whose arrangement is just the input it must retain
67 to answer retractions, a single-copy arrangement no signature applies
68 to), change only what a measured signature calls for, and report
69 only the opportunities. A change without such a signature is churn:
70 a rebuild for no measured saving. State a leave-alone only for an
71 item the user asked about (a proposal item, a named object) or when
72 the residual footprint needs explaining, such as why the replica
73 cannot go lower.
74- Batch by confidence. Changes you are at least about 97% sure of (a hint
75 retune that `mz_expected_group_size_advice` measured, an index drop that no
76 plan reads, removal of columns the plan proves unread) can ship in one batch;
77 anything less certain is applied alone and re-measured before the next, so
78 each saving stays attributable and an interaction (a new index restructuring
79 a neighbour's plan) shows up as its own number. Record the estimate per
80 change and the measurement per batch either way.
81
82## Connection and tools
83
84Prefer the Materialize MCP developer endpoint when it is available. Its tools
85describe themselves; what matters for this skill: every cluster-bound read
86(`EXPLAIN ANALYZE`, `mz_introspection`, `EXPLAIN` of user objects) goes through
87the `query` tool with its cluster argument, plus its `cluster_replica` argument
88on a multi-replica cluster (any extra replica counts, including an unbilled
89support replica on Cloud). The endpoint's other tool, `query_system_catalog`,
90is a trap here: it does not refuse those statements (the `mz_introspection`
91relations pass its name check) and answers them about the session's default
92cluster: with exactly one replica there the answer looks valid (often an empty
93result, or another cluster's numbers) and carries no error; with several it
94fails with `log source reads must target a replica`, with none with `has no
95replicas available to service request`. Never send a cluster-bound read there.
96The tools cannot SET anything, so the cluster is always the argument, never a
97session setting. On servers before v26.40 the endpoint's own instructions say
98never to query `mz_introspection.mz_dataflow_arrangement_sizes`; that rule
99predates the `query` tool and concerns the catalog tool only (later servers say
100so themselves), so it does not apply to `query` with a cluster argument (what
101that relation's `id` joins to is in "Reading introspection"). Tool responses
102are bare row arrays without column names, numbers as strings, capped by default
103at 1 MB per response and 60 seconds per request (both per-environment settings
104an operator can move), so keep SELECT lists short and in a fixed order, and
105give census queries a `LIMIT` or an aggregation rather than listing every
106arrangement of a big cluster (a `LIMIT` bounds the result, not the work of a
107join). A timed-out request cancels the peek but sometimes not the work: its
108dataflow can run to completion on the cluster, so do not resend it unchanged.
109Timestamps arrive as milliseconds since the epoch unless cast to `text`, and
110object names outside the session's database need the database prefix, or the
111statement fails with `unknown catalog item` when a schema of that name exists
112in the session's database and with `unknown schema` when it does not.
113
114If no MCP server is configured, or its `query` tool is absent (older
115Materialize, or disabled by the operator; `query` needs v26.30 and its
116`cluster_replica` argument v26.33), suggest setting one up (the
117mcp-developer-analysis skill covers client configuration), or ask the user for
118a SQL connection string instead. Whenever you ask for a connection string, warn
119the user that it should be for a role scoped to what the current phase needs
120(read-only for diagnosis; see "Making changes", below, for the experiment
121role), never an admin or superuser. Check that the scoping is real before
122relying on it: `SHOW enable_rbac_checks`. Cloud enforces RBAC by default, while
123self-managed instances default to `enableRbac: false` and the emulator image
124turns the checks off, and with the checks off the GRANTs and ownership gates on
125user objects stop being enforced, so a scoped role protects nothing there (the
126gates on system objects, and `restrict_to_user_objects` below, are separate and
127still apply). Ask also that the role not have `restrict_to_user_objects` set,
128which hides the system catalog and makes `EXPLAIN ANALYZE` fail on objects the
129role does not own, even with the RBAC checks off.
130
131All writes happen outside MCP. The "Making changes" section defines who
132executes them and where.
133
134## Reading introspection
135
136Rules for every introspection read, over MCP or SQL alike:
137
138- **Measure hydrated dataflows only.** Introspection state (everything `EXPLAIN
139 ANALYZE` and `mz_introspection` read) fills as hydration completes: a
140 still-hydrating dataflow returns empty or partial numbers, and what it does
141 return measures catching up, not steady state. Gate every measurement on
142 `mz_internal.mz_hydration_statuses` (maintained by the control plane, so it
143 answers even when the replica is busy). This applies equally after every
144 rebuild you perform mid-engagement: rebuild, wait for hydrated, then
145 re-measure. Right after a replica crash the relation can briefly serve the
146 previous incarnation's rows, so re-read it once the replica is back before
147 trusting a `true`. For a cluster that cannot reach hydration at all, see
148 "Intake: a cluster that cannot hydrate".
149- Introspection is served by the target cluster and is session-scoped:
150 `SET cluster = ...` first (or the cluster argument of the MCP
151 `query` tool). On a cluster with several replicas, target one
152 replica too (`SET cluster_replica = ...`, or the tool's
153 `cluster_replica` argument): introspection reads on a multi-replica
154 cluster fail outright otherwise ("log source reads must target a
155 replica"), readings legitimately differ across replicas, and the
156 one you measure must itself be hydrated.
157- Results can lag a few seconds behind DDL; if a query returns empty
158 rows right after a change, wait and re-run before concluding
159 anything.
160- `mz_introspection.mz_dataflow_arrangement_sizes.id` is a dataflow id
161 (`uint8`), never joinable to `mz_objects.id`: reach the catalog through
162 `mz_introspection.mz_compute_exports` (`dataflow_id` to `export_id`), or
163 match `name` (`Dataflow: <db>.<schema>.<object>`, the name the object was
164 created under, not its current name: a blue/green deploy or a dbt run creates
165 objects under a staging name and swaps them in, so the dataflow keeps the
166 staging name and only the export path finds it).
167- The docs' dataflow troubleshooting page
168 (https://materialize.com/docs/transform-data/dataflow-troubleshooting/)
169 carries the census and per-operator queries this skill builds on and the
170 CPU-side instruments it does not repeat (`mz_scheduling_elapsed`,
171 `mz_compute_operator_durations_histogram`, the `mz_dataflow_addresses` region
172 walk); the freshness page
173 (https://materialize.com/docs/transform-data/freshness-troubleshooting/)
174 covers lag.
175
176## Intake: a cluster that cannot hydrate
177
178Users often arrive with a cluster that is OOMing, not with a stable but
179oversized one. Do not start the measurement workflow against an OOM-looping
180replica: a replica that dies mid-hydration yields no usable measurements
181("Reading introspection"). Confirm the state first from two control-plane
182relations, which answer even while the replica is looping:
183`mz_internal.mz_hydration_statuses` keeps reporting `hydrated = false` across
184the replica's restarts (a single `false` reading only says still hydrating; a
185cluster with no replica rows in `mz_cluster_replicas` also stays `false`, with
186`replica_id` NULL), and `mz_internal.mz_cluster_replica_status_history`
187confirms that MEMORY is the cause (`offline` rows with reason `oom-killed`,
188which covers every memory-class limit, RAM, heap and spill disk alike, without
189saying which; any other failure leaves the reason NULL; the current
190`mz_cluster_replica_statuses` often reads `online` between two kills, so it
191cannot; the emulator's process orchestrator records no reason, so there count
192the `offline` rows). The second check matters because a cluster can also fail
193to hydrate on a compute grind (a cross join or a skewed key grinding one worker
194for hours with flat memory: the quadratic-join shape, below;
195`mz_introspection.mz_scheduling_elapsed_per_worker` summed per worker shows the
196one busy worker whenever the replica still answers) or on starved inputs, and a
197bigger replica fixes neither. The loop is over, and measurement meaningful,
198once every object on the cluster is hydrated and the latest history row per
199process is `online`; kills that recur after hydration completes are the
200steady-state case this skill exists for, which ends only when the cluster holds
201less state (the levers below) or the replica gets bigger.
202
203Offer two ways to reach measurable ground, both executed by the user:
204
205- Develop against a smaller input data set until the pipeline is
206 optimized, then scale the data back up.
207- Temporarily size the cluster (or a clone of it) large enough to
208 hydrate without OOMing, measure and optimize there, then right-size
209 down. The oversized replica is a measurement platform, not the fix.
210
211## Cost model
212
213The high-level model:
214
215- Indexes and materialized views are maintained INCREMENTALLY by
216 persistent dataflows that update their results as inputs change. That
217 is why they need memory continuously: the operators must retain
218 enough state to react to any input change.
219- Most of the retained state lives in ARRANGEMENTS: in-memory
220 collections of rows organized by a key, maintained as inputs change.
221 The kind of arrangement decides which lever can reach it. INDEX
222 arrangements are shareable: `CREATE INDEX` maintains an arrangement
223 of the indexed collection that other dataflows can read, but only
224 WITHIN the index's cluster. Every other arrangement is
225 intra-dataflow: private working state visible only to the dataflow
226 that built it.
227- Intra-dataflow arrangements sit on operators' inputs and outputs. An
228 operator's INPUT arrangement is either an explicit plan node or built inside
229 the operator with no node of its own; both show in `EXPLAIN ANALYZE MEMORY`.
230 The explicit node has three spellings in EXPLAIN: `ArrangeBy` in `EXPLAIN
231 OPTIMIZED PLAN`; `ArrangeBy` with one `arrangements[i]` line per key in
232 `EXPLAIN PHYSICAL PLAN` (verbose text, that statement's default); and
233 `Arrange (key)` in the arrow text that a bare `EXPLAIN <object>` or `EXPLAIN
234 PHYSICAL PLAN AS TEXT` prints. Some operators can REUSE an arrangement that
235 already exists on their input (an index, or an upstream operator's arranged
236 output) instead of building one, and some leave their OUTPUT arranged so the
237 next operator in the same dataflow can reuse it. Reuse is within a dataflow;
238 sharing across dataflows always needs an index. Those two properties, reuse
239 of an input arrangement and an arranged output, decide what an index can
240 replace; the operator table below gives them per operator.
241- Only operators that retain state cost memory. A join's output is a plain,
242 unarranged collection, so "this join emits 500M rows" is not a memory cost by
243 itself. Look at what the output feeds: hierarchical MIN/MAX reduces, TopKs,
244 and explicit arrangements retain. Accumulable aggregates (sum/count/avg)
245 collapse to per-group accumulators and stay cheap.
246- Not all retained memory is arrangement state: the persist sink at the end of
247 every materialized view holds its own buffer, largest at hydration and
248 drained afterwards, so that share of the peak is observable only during a
249 hydration and a replica restart is how you get another one. See "Memory
250 beyond arrangements" below.
251- A plain VIEW is a saved query, not a computed thing: every consuming
252 dataflow INLINES the view's definition and the optimizer replans it
253 in that consumer's context, pruning columns to that consumer's
254 demand. Five dataflows over one view means five independent copies
255 of its operators and arrangements. This is why a big view in the
256 SQL is not a thing you will find in the dataflow census: its cost
257 appears inlined inside each consumer, which is also why mining
258 measured arrangements beats reading view SQL. Two objects stop the
259 inlining and make a view computed once, with different products:
260 - an INDEX on the view computes it once per cluster, and its
261 product IS an arrangement, which consumers on that cluster read
262 directly, joins included;
263 - a MATERIALIZED VIEW computes it once globally and persists the
264 results, readable from any cluster, but persisted results do NOT
265 end in an arrangement: each consuming dataflow arranges what it
266 reads by whatever key it needs.
267 So an MV dedups the computation but not the arrangement state; indexing the
268 MV on each reading cluster recovers the missing half for the consumers that
269 read it on the index's key, one shared arrangement instead of one private
270 copy each.
271- An index's own dataflow can hold more arrangements than the one it
272 exports: indexing a computed view also computes the view, with
273 whatever intra-dataflow arrangements that takes. The exported
274 arrangement is the shareable product, not the dataflow's whole
275 footprint.
276
277### Operator-level detail
278
279Per physical-plan operator, with input arrangements counted whether
280printed as a node or built internally:
281
282| Operator | Input arrangement | Reuses an existing input arrangement | Output arranged, reusable | Memory |
283|---|---|---|---|---|
284| Get, Constant, Map/Filter/Project, FlatMap, Negate | none | reads through one without holding it | no | none of their own |
285| Explicit arrangement node (`ArrangeBy` / `Arrange`, spellings above) | builds one arrangement per key it prints (default text: `Arrange (key)`, one `(key)` each; verbose: one `arrangements[i]` line each); the node printed as `Unarranged Raw Stream` (verbose: `raw=true` with no `arrangements` line) builds nothing and only hands an existing arrangement through as a plain collection | yes, silently: it lists only the arrangements it builds; keys that the input already provides pass through unlisted, and a node whose keys all exist is elided from the plan | yes, one per key built | rows times width per key built; nothing for the raw-stream form |
286| Join, `type=differential` (linear) | both inputs of every stage are arranged: an input relation reuses an existing arrangement or gets a printed Arrange; the running intermediate result is one side of every stage after the first and is arranged internally (`JoinStage`) | yes | no | input relations plus every intermediate |
287| Join, `type=delta` | every input relation, by each key it is probed on: reuses an existing arrangement or gets a printed Arrange; no intermediate is arranged | yes | no | input relations only, no intermediates |
288| Reduce | internal, by the group key (hierarchical variants by (hash, group key)) | no | yes, by the group key | see Reduce below |
289| Distinct (a `SELECT DISTINCT` or key-only `GROUP BY`; `Distinct` in the optimized plan, `Reduce::Distinct` in the verbose physical plan, `Distinct GroupAggregate` in the arrow text and EXPLAIN ANALYZE) | internal, by all its columns (`Arranged DistinctBy`) | no | yes, by the whole row | about two records per group, input plus output, multiplicity only |
290| TopK | internal, per hierarchy level, by (hash, group key) | no | only `Monotonic Top1` | non-monotonic: input per level plus a reduced arrangement per level; `MonotonicTopK`: one stage, about (workers x limit) records per group while updates flow and near the limit once quiesced, plus a reduced arrangement; `Monotonic Top1`: one record per group |
291| Threshold | Arrange by the whole row (all columns, in order), printed by the physical plan only | in principle, never in practice (its input is a Union) | yes, by the whole row | one whole-row arrangement plus the output |
292| Consolidating Union, LetRec boundary, monotonic-input consolidation | no arrangement; a merge batcher instead | no | no | transient, see Consolidation below |
293| Persist sink (MV output, absent from EXPLAIN) | no arrangement; a correction buffer instead, logged as `mv_sink(<id>)::write` | no | no | see "Memory beyond arrangements" |
294
295**Reduce.** A Reduce arranges its own input in its own layout (group key plus
296the aggregate arguments), so it never consumes an existing arrangement and no
297index can replace that input arrangement: an index on the group key is read as
298a full scan, and the Reduce, a bare `count(*)` included, still builds its own
299(measured). What that input arrangement holds depends on the aggregate class:
300
301- Accumulable aggregates (sum, count, avg) carry their accumulators in the diff
302 with empty values, so the input consolidates to one record per group and the
303 arranged output adds a second: about two records per group, however many rows
304 feed it. Cheap per row, not per group: the pair costs roughly 150 bytes per
305 group for one accumulator (`count`, `sum`) and 300 for an `avg` (two), so a
306 very large number of very small groups is where an accumulable stops being
307 cheap. Do not calibrate this on a `generate_series` rig: over groups that are
308 all alike the accumulators store compactly and the figure reads ten times
309 lower.
310- A plain `SELECT DISTINCT` or key-only `GROUP BY` is the Distinct row
311 above. An aggregate-level
312 DISTINCT (`count(DISTINCT v)`) instead adds an arrangement pair sized
313 by groups times distinct values. `DISTINCT ON` is not a Reduce at
314 all but a TopK with `limit=1`.
315- Basic aggregates (`string_agg`, `jsonb_agg`, and the other
316 non-accumulable, non-hierarchical ones) keep every qualifying input
317 row of the group, thinned to the aggregate's argument columns.
318 Identical thinned rows consolidate into one record carrying a
319 multiplicity, so the record count is the distinct (group key,
320 argument) tuples per group, not the rows: a low-cardinality argument
321 column makes a large group small.
322- Non-monotonic min/max (the bucketed hierarchical rendering) keeps the same
323 full input, thinned the same way; no exact incremental extremal aggregate can
324 hold less, because a retraction of the current extremum must be answerable
325 from what is retained. The hierarchy then adds two arrangements per bucket
326 level (`Arranged MinsMaxesHierarchical input` and `Reduced Fallibly
327 MinsMaxesHierarchical`), and every level whose bucket count exceeds the
328 group's retained record count holds a near-full copy of the input, which is
329 what GROUP SIZE hints trim. A monotonic min/max (append-only input) escapes
330 this and keeps the winner in the diff, one record per group; an MV is never
331 monotonic, whatever its definition (its sink may retract), so only a dataflow
332 reading an append-only source directly gets that rendering.
333
334A GROUP BY mixing classes is planned as separate Reduces (one for the
335accumulables, one for the hierarchicals, one per basic aggregate, such as
336`string_agg` or `jsonb_agg`) joined on the group key; that join reads the
337Reduces' output arrangements directly and adds none of its own.
338Map/filter/project work fused into a Reduce leaves the output arranged (the
339`Fused with Child Map/Filter/Project` line is printed under Get and FlatMap
340nodes too, so by itself it is no sign of an arrangement); the fusion does not
341happen when the projection emits a mapped column, as `avg` does, and the `Mfp`
342left above the Reduce costs the next consumer a new arrangement.
343
344**TopK.** The non-monotonic rendering arranges its input per hierarchy level
345(`Arranged TopK input` in `mz_arrangement_sizes`) and keeps a reduced
346arrangement per level (`Reduced TopK input`), keyed by (hash, group key); the
347reduced arrangements hold the retractions of the rows the hierarchy does not
348emit, once in total rather than per level and concentrated in the last levels,
349near-full there whenever groups exceed the limit and empty when they fit inside
350it. Like Reduce, no TopK rendering consumes an existing arrangement, so no
351index can replace its input arrangement, and only `Monotonic Top1` leaves its
352result arranged, so a join over the other two gets a printed Arrange. An MV's
353output is never monotonic (see Reduce).
354
355**Join implementations.** A DIFFERENTIAL join over more than two inputs
356runs as a series of binary joins whose intermediate results are standing
357arrangements, proportional to the intermediate sizes, which can dwarf
358both the inputs and the output. A DELTA join maintains no intermediates:
359it streams each input's updates against the other inputs' existing
360arrangements, so its memory cost is exactly its input arrangements,
361which shared indexes can supply. Reading this in `EXPLAIN ANALYZE
362MEMORY`: the memory printed on a `Differential Join` operator line is
363exactly the eliminable intermediate state; the input arrangements are
364the separate Arrange lines below it, and those are kept under either
365implementation. Which implementation the plan picked
366(`type=differential` vs `type=delta`) often matters more than how big
367the join is, and it can be changed: supplying the input arrangements as
368indexes lets the planner pick a delta join ("Flipping differential joins
369to delta", references/indexes.md).
370
371**Consolidation.** A consolidating Union (printed `Consolidating Union`; the
372planner marks a Union when any of its inputs is a Negate, so EXCEPT and the
373outer-join lowerings carry one), the consolidation at a LetRec loop boundary,
374and the input consolidation of monotonic reduces and TopKs hold updates in a
375merge batcher until each update's timestamp completes, then emit them and keep
376nothing: no maintained trace. Their memory still shows in
377`mz_arrangement_sizes` (batcher records and bytes are summed into the same
378`records` and `size` columns under the consolidating operator's id:
379`UnionConsolidation` for a Union, `LetRecConsolidation` at a LetRec boundary,
380`Consolidated ReduceMonotonic input`, `Consolidated MonotonicTopK input` and
381`Consolidated MonotonicTop1 input` for the monotonic operators) and the row
382disappears once the batcher drains. Normally that is a hydration-time
383transient. It is standing state when a temporal filter feeds the consolidation:
384every future-dated retraction is parked until its time arrives, one record per
385live row for as long as the row lives. The near-term ones (within the
386`compute_temporal_bucketing_summary` horizon, two seconds by default) sit in
387the consuming operator's batcher and the rest in a separate `Temporal delay`
388operator just ahead of it (temporal bucketing), which logs them the same way
389under its own operator id.
390
391### Memory beyond arrangements
392
393This matters most at hydration. The persist sink at the end of every
394materialized view stashes updates in a correction buffer until they are
395written. At hydration that buffer holds the MV's entire snapshot (nothing is
396written until the snapshot is complete), and the written copy is kept until
397persist reads it back, so the sink's peak is two copies of the MV's output: it
398scales with what the MV emits, not with what it reads or with the dataflow's
399own arrangements, which are separate lines in the same census. A rehydration of
400an existing MV peaks at the same two copies by another route: the sink writes
401the difference between the recomputed output and what the shard already holds,
402so it buffers both, with opposite signs, until they cancel.
403
404The sink does not appear in EXPLAIN, but its buffer IS in the arrangement
405introspection: the operator `mv_sink(<id>)::write` in `mz_arrangement_sizes`,
406rolled into the dataflow and object totals. When an MV's own plan applies the
407`mz_now()` predicate with nothing arranging or consolidating between it and the
408sink (a filter-and-project MV), the buffer also parks every future-dated
409retraction until its time arrives, one record per live row. A future-dated
410retraction is parked exactly once, by the first time-batching operator after
411the filter, so any intervening arrangement absorbs it instead, and a consumer
412that reads the filtered view through an index or another MV never parks it at
413all (its plan shows `ReadIndex` or the MV read, not the `mz_now()` Filter). A
414sink line that stays big after hydration is therefore either pending
415future-dated retractions (the line drains the moment they fire) or a write
416backlog, the sink not keeping up with persist. A consolidation batcher line
417reads the same way (see the Consolidation paragraph above).
418
419What the arrangement sum misses around the sink: its in-flight batches being
420appended to persist (only the `::write` buffer is logged, never the append) and
421allocator overhead beyond the logged `size`, largest right after a rebuild.
422
423To watch the sink buffer directly, read the `mz_persist_sink_correction_*`
424metrics via `mz_introspection.mz_cluster_prometheus_metrics`
425(`metric_name` column; cluster-wide counters and per-worker high-water
426gauges, no per-sink label).
427
428A replica's total heap (`heap_bytes` in
429`mz_internal.mz_cluster_replica_metrics`, RAM plus swap) therefore exceeds its
430arrangement sum, and post-hydration arrangement totals understate the peak
431twice over: they miss that gap, and the arrangements themselves peak at
432hydration, when each merge batcher holds the entire snapshot until the first
433seal, and trace merges (the batch merging inside an arrangement) transiently
434hold both source batches and their merged output (one index export measured
4354.4x its settled size twelve seconds after its build). That is why replicas are
436sized from the measured whole-cluster hydration peak (see "Making changes",
437below), never from arrangement totals. The lever for a peak dominated by one
438MV's sink is in the lever table ("MV hydration spikes").
439
440Read `heap_bytes`, not `memory_bytes`, for that comparison. On Cloud and with
441the self-managed defaults, replicas run with swap enabled and no scratch disk,
442so arrangements are ordinary swappable heap: `memory_bytes` counts resident RAM
443only and can fall below the arrangement sum once cold pages are paged out,
444`heap_limit` is the enforced RAM-plus-swap ceiling, and `disk_bytes` there
445reports swap, not disk. Treat swap as transparent unless there is so much of it
446that hydration time or freshness suffers. Under the process orchestrator (the
447emulator, and a local environmentd) the orchestrator reports NULL for
448`heap_bytes`, `heap_limit` and `disk_bytes`, so `memory_bytes` is all you get;
449it counts resident RAM, and a container on a host with swap enabled can still
450page out, so a falling `memory_bytes` there is not by itself freed memory.
451`mz_internal.mz_cluster_replica_utilization` reports the same readings as
452percentages of the replica's allocation (`cpu_percent`, `memory_percent`, and
453`heap_percent`, which is `heap_bytes` over `heap_limit`), the quickest headroom
454read.
455
456## Workflow
457
458Work top-down. The numbered order is the default shape of an
459engagement, not a script. Skip or reorder steps when the evidence points
460elsewhere, except two load-bearing orderings: hints before attribution
461(step 2) and estimates before builds (step 5).
462
4631. Census. Confirm the cluster is hydrated ("Reading introspection"), then rank
464 dataflows by size: `mz_introspection.mz_dataflow_arrangement_sizes` on the
465 target cluster. This tells you where the memory is; nothing else in the
466 workflow makes sense before it. Rank by size to order the work, but keep the
467 census complete: the duplication probe of step 3 clusters every arrangement,
468 since the same small arrangement built in twenty dataflows is a large total,
469 and every object a proposal names gets measured whatever its size.
4702. Hints early. Query `mz_introspection.mz_expected_group_size_advice` on the
471 target cluster first: one read lists every operator on the cluster with a
472 hint recommendation and its reclaimable savings. Then run `EXPLAIN ANALYZE
473 HINTS FOR MATERIALIZED VIEW <mv>` (also `FOR INDEX`) on the dataflows where
474 the savings are substantial, to place each hint. It is cheap, needs no logic
475 change, and comes first because oversized hierarchies inflate the masses
476 every later step attributes. The single biggest hint win is usually an
477 un-hinted min/max, which silently defaults to an 8-level hierarchy (an
478 un-hinted `DISTINCT ON` or per-group `LIMIT` TopK defaults to the same
479 eight). Details: [references/hint-sizing.md].
4803. Attribute before choosing a lever. The same headline number can be
481 hierarchical aggregates holding their full input, raw re-keyed arrangements,
482 expand-then-collapse join products, or payload bytes riding through
483 arrangements, and each needs a different lever. The operator table in the
484 cost model says what each operator retains; the reference files refine it
485 (what a hierarchy level of a min/max or TopK costs, width and
486 key-amortization effects, the packed-row state of window functions,
487 temporal-filter retraction copies). Per-operator attribution: `EXPLAIN
488 ANALYZE MEMORY FOR MATERIALIZED VIEW <mv>` (or `FOR INDEX <idx>`; those are
489 the only two explainees), or `mz_introspection.mz_arrangement_sizes` joined
490 to `mz_dataflow_operator_dataflows`. Standard probes on the big masses:
491 - Bytes/row sweep: size ÷ records per operator. Rows of keys and scalar
492 columns cost about 15 to 50 B/row arranged (three integers 17, a uuid and
493 an integer 25, an integer with a timestamp and a 32-character string 50);
494 an outlier far above that fingerprints a payload column riding the
495 arrangement (a 200-byte `jsonb` document reads at about 230). Skip small
496 arrangements: each carries a fixed floor of a couple of hundred bytes per
497 worker (about 4.5 kB on a 16-worker replica), which dominates the ratio
498 below a few hundred records.
499 - Cross-dataflow duplication: cluster all sized arrangements by record
500 count, within a few percent. Near-identical counts are the tell for one
501 collection arranged in several dataflows even when keys, names, and
502 projections differ; resolve each cluster in the plans before treating it
503 as one. The tolerance exists because readings of one collection are logged
504 asynchronously per operator, and `records` includes un-merged batches
505 whose insert/retract pairs cancel only on merge; the exact percentage is
506 empirical.
507 - Intra-dataflow twin census: within each big dataflow, list arrangements
508 sharing a key and record count but differing in width (the key is read
509 from the operator's name, `ArrangeBy[[Column(0, "k")]]`, in
510 `mz_dataflow_operator_dataflows` joined on `operator_id`;
511 operator-internal arrangements carry no key in their name and need the
512 plan). These are usually per-consumer projection divergences that defeated
513 common-subexpression sharing. Do this as its own pass; it does not fall
514 out of the cross-dataflow view.
515 - Cardinality probes: rows vs distinct join-key pairs vs distinct
516 group keys, which size an index candidate and estimate join pair
517 mass before any rebuild (the quadratic-join shape, below).
518 - Stuck projection pushdown: `EXPLAIN OPTIMIZED PLAN WITH (arity)` on
519 dataflows whose big arrangements are wider than their consumers
520 read; the blockers and the fix are one lever (below).
521 - Worker skew: `EXPLAIN ANALYZE CPU WITH SKEW FOR MATERIALIZED VIEW <mv>`
522 (or `FOR INDEX`) when one worker seems to carry a dataflow. It prints one
523 row per operator per worker (`worker_id`, `cpu_ratio`, `worker_elapsed`,
524 `avg_elapsed`, `total_elapsed`); the operator's max `cpu_ratio` is its
525 skew. The counters are cumulative from operator creation, so a reading
526 during hydration measures hydration and a reading afterwards still
527 contains it; steady-state skew is the difference of two readings. Mapping
528 a measured row or plan operator back to its SQL clause is its own section
529 below ("Mapping measurements back to SQL").
5304. Classify each big mass with the lever table below. Report the masses
531 a lever can help; the rest stays in your notes (ground rules).
5325. Estimate before implementing. From the attribution, estimate each
533 candidate's saving (what you remove minus what you add) with a stated
534 confidence (the formulas per lever, and what to do with a wide interval, are
535 under "Adjudicating proposals and estimating"). Width math (bytes/row on
536 both sides; the model is in references/indexes.md) and plan reasoning gate
537 the experiment, for every index candidate, your own proposals included, not
538 only the ones you were asked to judge; the measured build settles it (ground
539 rules). Adoption and plan-shape questions can be settled for free on a
540 data-free rig first ([references/indexes.md]); size questions need the
541 experiment cluster. Gate implementation on estimated value vs effort and
542 risk, and leave complex rewrites as designed-and-estimated proposals when
543 they do not clear the gate.
5446. Measure on the experiment cluster (see "Making changes"): build the
545 candidate, compare against the baseline, verify exactness, record the
546 number and each dataflow's hydration time (Landmines: an enabling
547 index can flip a join onto a hot key).
5487. Hand over: the estimated-vs-measured table (see "Adjudicating proposals
549 and estimating"), accepted changes expressed in the user's source of truth,
550 and the realization step: the replica resize, sized to clear the measured
551 full-hydration peak rather than the steady state (see "Final evaluation and
552 sizing" under "Making changes"), and the cleanup checklist.
553
554## Mapping measurements back to SQL
555
556Mapping measurements back to SQL takes two hops. The first hop, measured row to
557plan operator, is exact. Each row of `EXPLAIN ANALYZE` is one operator of the
558default `EXPLAIN` (the physical/LIR plan), but not in the same order: the rows
559are sorted by LIR id descending, so a multi-input operator's children come out
560reversed relative to the plan text; the plan text also has lines with no row
561(`Fused with Child Map/Filter/Project`), the With/Return scaffolding is folded
562into labels (`With l0 = <op>`, `Returning <op>`), relations print as ids (`Read
563u20`) rather than names, and some labels differ (`Differential Cross Join` in
564the plan is `Differential Join` in ANALYZE). So never map rows to operators by
565position: `EXPLAIN PHYSICAL PLAN WITH (node identifiers)` annotates every
566operator with its `LirId`, the same ids `mz_introspection.mz_lir_mapping` keys
567by (with raw operator-id ranges per node; its `global_id` is a transient id,
568bridged from the object name through `mz_introspection.mz_mappable_objects`),
569and a measured row joins its plan operator by that id. The second hop, plan
570operator to source clause, has no id to follow, and operator labels are generic
571and repeat, so map it by landmarks: the column names the plan prints (`#2{col}`
572annotations in group keys, order-by lists, join equivalences, filters; derived
573columns print as bare `#n`, and names can be lost across projections), the
574feeding join's complete key set (a three-relation chain is a different CTE than
575a two-relation one on the same column), sibling aggregates (a `min` beside a
576`count(*)` is a different clause than a lone `min`), the relations the operator
577reads, distinctive constants, and the current hint value (`exp_group_size=N` on
578the operator in `EXPLAIN OPTIMIZED PLAN`; a value unique in the view pins its
579clause). A single landmark is a hypothesis; confirm it with the full key set
580and the siblings. When that leaves a mapping below about 97% certain, make it
581certain with a marker: put the view's text into `EXPLAIN OPTIMIZED PLAN FOR
582SELECT ...`, give the suspected clause a distinctive hint value (`OPTIONS (...
583INPUT GROUP SIZE = 1234)`), and the one operator whose `exp_group_size` changed
584is that clause. One EXPLAIN, no hydration, any hint-taking clause (min/max,
585`DISTINCT ON`, per-group `LIMIT`); for an operator without a hint clause, mark
586a hint-taking neighbour in the same CTE and read the subtree around it. Two
587things to know about markers: two clauses that are byte-identical, hint
588included, are ONE operator in the plan (the optimizer shares the subtree), so
589HINTS reports one row for both and both clauses get the same edit, and a marker
590on one of them splits them, which is diagnostic, not a problem; and a hint's
591trace in the physical plan is coarser (a min/max prints only its bucket list,
592the 16^n bracket, a TopK prints nothing), so read markers and current values in
593the optimized plan. Anchor every edit by content, never by line number: SQL
594read back from the catalog is the engine's re-rendering, so line numbers will
595not match the user's source. Structurally identical clauses that want the same
596value get one collective instruction. references/hint-sizing.md uses this
597technique for hint clauses; it works for any operator.
598
599## Lever table
600
601Dispatch from measured signatures to levers. Each reference file
602carries the full method, verification steps, and worked examples.
603
604| Signature | Lever | Details |
605|---|---|---|
606| HINTS reports savings; un-hinted min/max (levels=8, no exp_group_size) | Re-tune or add GROUP SIZE hints | [references/hint-sizing.md] |
607| Same (collection, key) arranged in 2+ dataflows | Shared index; slim projection view + index when the base is wide | [references/indexes.md] |
608| Recurring composite re-key that no existing index serves | New boundary view at exactly that (collection, key, column-union) + index, repoint consumers | [references/indexes.md] |
609| One dataflow, two big arrangements, same key and count, different widths | Union-width view + index (intra-dataflow twin) | [references/indexes.md] |
610| Expensive computed view consumed by 2+ dataflows | Index the computed view; dedups the whole computation and pays off even when consumers read it unkeyed | [references/indexes.md] |
611| Three-or-more-input `type=differential` join whose Differential Join line carries big memory (the intermediates) | Index the extra probe keys the delta paths lack so the planner flips it to a delta join | [references/indexes.md] |
612| Temporal-filtered view (mz_now window) read by several consumers | Index the shared temporal-filtered view (N private copies become 1, and consumers that carried the `mz_now()` predicate themselves stop parking retractions in their sinks) | [references/indexes.md] |
613| Bytes/row outlier (payload riding arrangements) | Extract-in-place payload slimming (below) | inline |
614| Reduce or arrangement carrying columns no consumer reads | Dead-column narrowing (below) | inline |
615| DISTINCT over more columns than its consumers read (often `DISTINCT *` in a shared view) | Stuck projection pushdown (below): drop a redundant DISTINCT, or narrow its column list | inline |
616| DISTINCT ON pick plus a sibling max/min over the same relation and key | Argmax redundancy (below): delete the redundant max/min aggregate, read the value from the pick | inline |
617| LEFT JOIN whose right side provably always matches and carries no key the optimizer knows | Convert to INNER via one of the two provable guarantee classes (with the key known the diamond is already gone and the conversion buys nothing); no census signature, so hunt for it on every rebuild | [references/outer-joins.md] |
618| Wide Distinct over ALL columns of a join side plus a full-width self-join (general outer-join lowering) | Stuck projection pushdown (below): pre-project the preserving side, or rewrite the ON to a plain equi-join | [references/outer-joins.md], "The pushdown gaps" |
619| A stack of 2+ LEFT JOINs lowered per join (`n` differential joins with a matched-key Distinct each, no `Threshold`) instead of one delta join | Restore the VOJ (variadic outer join) lowering (move inner joins out of the stack, `ON` instead of `USING`, local predicates and expressions into derived tables, decorrelate) when the match rate and the right sides' keys clear the crossover in the reference | [references/outer-joins.md] |
620| Window function (OVER/PARTITION BY) with wide rows or hot partitions | Stuck projection pushdown (below): narrow the window's input relation (memory); the incremental rewrites (DISTINCT ON, min/max, the LAG/LEAD self equi-join) buy freshness and can cost memory | [references/window-functions.md], "The projection-pushdown blocker" |
621| `CrossJoin`, or a `Distinct` and `ArrangeBy` keyed on the outer relation's own columns, feeding a join back to it (a decorrelated subquery), or any form in the SQL triage list of "Query shapes to recognize" | Manual decorrelation: nine rewrite patterns, each with its exactness rule | [references/subqueries.md] |
622| Index or MV that nothing reads | Drop it, with the pinning procedure (below) | inline |
623| Replica's hydration peak dominated by `mv_sink(<id>)::write` buffers of one or two huge MVs | Split the MV into smaller MVs ("MV hydration spikes" below; last resort) | inline |
624
625Inline levers:
626
627**Extract-in-place payload slimming.** Materialize pushes column projections
628down aggressively, through TopK and Reduce, into a join's inputs, and into the
629source read, but it never moves a scalar EXPRESSION: a `Map` stays where the
630query put it (fused into the source read only when it already sits directly
631above it), above TopK, Reduce and Join alike, so a jsonb/text blob joined first
632and unpacked later is demanded whole by every operator between the source and
633the extraction point, the join's input arrangements and TopKs included. Fix at
634the point where the payload first enters the pipeline: extract exactly the
635consumed fields in the first view that reads the source relation (the scan or
636wrapper view, whatever the pipeline's first layer is called), so picks,
637hierarchical aggregates, and joins carry only slim columns. Prefer this over a
638fetch-back join (slim the pipeline, then re-join the fat relation at the end):
639the fetch-back re-arranges the fat relation by the fetch key, a full-width
640arrangement that costs back most of the saving when the pipeline you slimmed
641held only a copy or two. Extraction expressions are deterministic per row, so
642evaluating them earlier is outputs-identical as long as they cannot fault: a
643cast, division, or subscript that errors on a row a later filter would have
644discarded poisons the whole collection and every consumer of it. Guard such an
645expression with the downstream predicate (`CASE WHEN <pred> THEN <expr> END`),
646and seed the two-way `EXCEPT ALL` proof (see "Verification discipline", below)
647with a row that would fault.
648
649**Dead-column narrowing and compact types.** A reduce keyed wider than
650its consumers read, or a GROUP BY carrying columns no consumer uses,
651retains dead bytes in every group. Narrow the GROUP BY or the carried
652columns to what is actually consumed. A column used only as `IS NOT
653NULL` can be narrowed to a boolean at the scan. Relatedly, narrow the
654representation: text that is really a UUID casts to `uuid`, text
655booleans to `boolean`, small fixed string vocabularies to small integer
656codes. (Casting between integer widths rarely saves memory: integer
657storage is already value-sized.) These are outputs-changing at the
658column-type level, so they apply inside pipelines, with the consumer
659contract preserved at the pipeline's outputs; prove exactness at the narrowed
660sites with the two-way `EXCEPT ALL` proof.
661
662**Stuck projection pushdown (manual projection).** The optimizer pushes
663projections down until it meets a construct that demands its full input width,
664and everything below that point then carries every column, however few the
665consumer reads. Four constructs do this: window functions (their three-operator
666gadget, see references/window-functions.md, packs the window's whole input
667scope), the general outer-join lowering (it equates every preserving column),
668`DISTINCT` (every listed column, so `DISTINCT *` in a shared view arranges the
669full width inside each consumer), and a surviving `Threshold` (the
670implementation of `EXCEPT`, `EXCEPT ALL`, and `INTERSECT`, keyed by the whole
671row). An indexed view is a fifth, deliberate one: the index holds the full
672width of its view, whatever its readers project (its remedy is the slim
673projection view of indexes.md). One diagnostic serves all of them: `EXPLAIN
674OPTIMIZED PLAN WITH (arity)` annotates every operator with its column count,
675and a region whose arity stays far above the columns the output reads, and
676above what the region itself consumes (partition and order columns, join keys,
677the distinct list), is a stuck projection. One fix shape serves the first four,
678in two flavors: project manually just below the blocker (wrap the window's
679input in an explicit projection; pre-project the preserving side of the outer
680join; apply the DISTINCT to exactly the columns needed, at the consumer; narrow
681both operands of the set operation), or remove the blocker (rewrite the window
682function to an incremental form, references/window-functions.md; rewrite the ON
683to a plain equi-join, references/outer-joins.md; drop a redundant DISTINCT;
684move the set operation). Exactness differs: the manual projection is
685outputs-identical for windows and outer joins when the dropped columns reach no
686output, while narrowing a DISTINCT changes the result unless the dropped
687columns are functionally determined by the kept ones, so state that delta and
688let the user decide. A DISTINCT is redundant, and already elided, when the
689optimizer knows a key contained in its column list (`EXPLAIN OPTIMIZED PLAN
690WITH (keys)`); when the uniqueness is only semantic, make the key known (a
691keyed boundary, references/outer-joins.md) or prove it with a count versus
692count-distinct probe before dropping it.
693
694**Argmax redundancy.** A `SELECT DISTINCT ON (k) ... ORDER BY k, v DESC` pick
695makes a sibling `max(v) ... GROUP BY k` over the same relation provably
696redundant when `v` is the first ordering term after the key and is not null, or
697when the pick spells `v DESC NULLS LAST`: the pick's first row already carries
698the maximum (with another column ordered before `v`, the pick is not an argmax
699at all). The condition is about NULL placement, since the default for DESC is
700NULLS FIRST, which returns the NULL that `max` skips. Deleting the whole max
701aggregate and reading the value from the pick is exact; prove it with the
702standard two-way `EXCEPT ALL`.
703
704**Dead objects and the pinning procedure.** An MV nobody reads is a
705whole-dataflow drop; confirm it is unconsumed several independent ways (no
706downstream objects, no serving reads, ask the owner) before touching it. For a
707dead index, establish adoption before any DDL, never by dropping it to see what
708happens: `mz_internal.mz_compute_dependencies` lists every dataflow that
709imports the index (rows whose `dependency_id` is the index; other indexes count
710as adopters too, and the catalog-level `mz_object_dependencies` never shows
711index adoption), the stored plan's `Used Indexes` footer confirms it per
712consumer, and `mz_introspection.mz_arrangement_sharing` is the live refcount of
713the index's trace, which counts the consumers that read it as an arrangement
714and not those that full-scan it, so a count of 1 is no evidence that nothing
715reads it. Dropping the index then prints a NOTICE naming dependent objects.
716That NOTICE means the index dataflow stays alive and maintained until every
717named dependent is rebuilt (or altered, or environmentd restarts, which replans
718an object whose dependencies changed and so drops the pin). It does not mean
719the index is needed, and it does not mean the drop failed. No NOTICE means
720nothing had adopted the index and the drop freed the dataflow at once. Plans
721adopt indexes at CREATE time, so the full procedure is: drop the index, then
722rebuild every consumer the NOTICE names, then verify no zombie dataflow remains
723(the query below lists every maintained dataflow whose export is no catalog
724object; a pinned consumer's `EXPLAIN` footer prints the dropped index as
725`[DELETED INDEX]`, the cheapest per-consumer check). Dropping alone frees
726nothing once the NOTICE names a dependent.
727
728```sql
729-- zombie dataflows: still maintained, exporting no catalog object
730SELECT d.name FROM mz_introspection.mz_dataflows d
731WHERE d.name LIKE 'Dataflow: %.%.%' AND NOT EXISTS (
732 SELECT 1 FROM mz_introspection.mz_compute_exports e
733 JOIN mz_catalog.mz_objects o ON o.id = e.export_id
734 WHERE e.dataflow_id = d.id);
735```
736
737**MV hydration spikes (the persist sink).** When one or two huge MVs dominate a
738cluster's hydration peak (each sink buffers roughly two copies of its MV's
739output at the peak; cost model), the lever is to break the MV into several
740smaller MVs (by key range, or by splitting rarely-joined column groups apart),
741so that no single snapshot is buffered whole and the pieces' peaks do not all
742coincide. This is tedious (consumers must be repointed or reunited with a UNION
743ALL, and the object graph changes) and it only lowers the peak when the pieces
744hydrate at different times. Materialize hydrates up to four dataflows
745concurrently by default, so splitting into fewer than about four pieces changes
746little and simultaneous sink buffering stays bounded by roughly four pieces'
747worth. That four is the `compute_hydration_concurrency` setting, and lowering
748it is the direct alternative to splitting. Only the Cloud flag service can
749target one replica with it, and Cloud users cannot set system parameters
750themselves, so they ask Materialize; a self-managed operator sets it with
751`ALTER SYSTEM SET` as `mz_system` or through the system-parameters ConfigMap,
752and either way the new value applies to every replica in the environment.
753Recommend either only when the sink spike is the biggest thing hurting the
754cluster's memory, established two ways: during a hydration (a replica restart
755produces one on demand), the `mv_sink(<id>)::write` lines in
756`mz_arrangement_sizes` (and the `mz_persist_sink_correction_*` high-water
757gauges) show the buffers; and independently of hydration, measure each MV's
758output size directly:
759
760```sql
761SELECT sum(mz_row_size(mv.*)) FROM mv; -- bytes of the MV's rows,
762 -- per-row overhead included
763```
764
765`mz_row_size` takes the row record (`mv.*` or just `mv`; do not wrap that
766whole-row reference in `row(...)`, which nests a record and adds a header per
767row). A row whose packed bytes fit the 23-byte inline buffer reports a flat 24
768bytes, and a longer row reports its packed length plus that 24-byte overhead.
769Two copies of that total is the sink's expected hydration contribution, as an
770order of magnitude (arrangements store rows in a different representation, so
771it is not byte-exact); compare it against the arrangement totals and the
772measured `heap_bytes` peak before choosing this lever.
773
774## Query shapes to recognize
775
776Three SQL shapes plan into far more state or work than they read as.
777Look for them during attribution, in the plan and in the SQL, before
778choosing levers.
779
780**IS NOT DISTINCT FROM in a join condition.** On nullable columns, when it is
781the join's only usable condition, it plans as a cross join with a post-filter:
782both inputs arranged with an empty key, everything on one worker (on NOT NULL
783columns it simplifies to `=` and stays an equi-join, and beside a real equality
784it becomes a post-filter over an ordinary equi-join). The default `EXPLAIN`
785shows it as `Differential Cross Join` over `Arrange (empty key)` inputs and the
786optimized plan as `CrossJoin`, so grep for either label in its plan text; note
787that `mz_lir_mapping` and EXPLAIN ANALYZE label the same operator a plain
788`Differential Join`, where worker-elapsed skew is the tell. Rewrite to plain
789equality, or, when NULL matching is intended, to `a.k = b.k OR (a.k IS NULL AND
790b.k IS NULL)`, which is exact and which the planner turns back into an
791equi-join (then keyed on a nullable column: run the closure audit in
792"Landmines").
793
794**Quadratic joins (cross joins and skewed keys).** A join with no usable
795equality condition, or an equality on a low-cardinality or heavily skewed
796non-unique column, produces pair volumes far above either input, and every pair
797for one key is produced on that key's one worker. The typical presentation is
798time, not memory: hydration that never finishes, or a rebuilt MV grinding for
799hours on one worker while memory stays flat (a downstream filter or collapse
800discards the pairs after they are produced). The defense is the pre-build
801probe, because a grinding replica is hard to inspect after the fact: before
802building or rebuilding any join, estimate the pair mass from distinct-key
803counts and the top per-key frequencies on both sides (the sum over hot keys of
804left-count times right-count); a result orders of magnitude above both inputs
805is a stop sign, not a tuning problem. On a live but responsive cluster,
806`EXPLAIN ANALYZE CPU WITH SKEW` exposes the per-worker elapsed per operator,
807and a true cross join shows as `Arrange (empty key)` inputs in the default
808`EXPLAIN`; an empty key hashes to one worker, so every cross join on a replica
809runs on that same worker and several of them stack up there. During a heavy
810grind, replica-served introspection may not answer at all; that
811unresponsiveness combined with one busy worker is itself the signature.
812
813**Subqueries decorrelate into joins keyed by the outer columns.** A subquery
814that reads a relation, sits inside a `CASE`, or is more than Map, Filter,
815Project, and table-function calls is lowered into a `Distinct` over the
816correlation key, the subquery computed once as a dataflow, and a join back on
817that key, or a `CrossJoin` when nothing correlates; the cost is that join's
818arrangements plus the subquery's own operators, which hold one group per
819distinct outer value when the outer keys are joined in below an aggregate or a
820`LIMIT`, never a re-execution per outer row. The memory is every arrangement
821keyed by the correlation key (the join-back pair, the `Distinct` over the key,
822the subquery's own), at that key's width, so the expensive shapes are a
823subquery correlated on a payload column (`jsonb`, `list`, `array`, long
824`text`), a subquery inside an outer join's `ON` (the key is every preserving
825column), and the nullable cases (`NOT IN`, `IN` outside a filter conjunct)
826whose outer keys seed a `CrossJoin`. In the plan, look for a `CrossJoin`, or a
827`Distinct` plus `ArrangeBy` keyed on the outer relation's own columns (all of
828them, or a payload column), feeding a join back to it. In SQL, look for `NOT
829IN` on a nullable side, `IN`/`ANY` on a nullable comparison outside a filter
830conjunct (`WHERE`, an inner join's `ON`, `HAVING`), an `IN` over a subquery
831that aggregates or uses `LIMIT` (even in `WHERE`), a subquery in an outer
832join's `ON`, `= ANY(<collection column>)`, and a scalar or `LATERAL ... LIMIT
8331` subquery correlated only on the current row. The full signature list, the
834nine rewrites with their exactness obligations, what the optimizer already
835handles, and what no rewrite fixes are in [references/subqueries.md].
836
837## Landmines
838
839Hazards of the changes this skill makes. Run these checks before
840shipping any index package or rebuild, not only at sites that already
841misbehaved.
842
843**The nullable-key closure audit.** An index on a nullable key column arranges
844the whole NULL population under one key, on one worker. Private arrangements
845are safe: the planner puts an `IS NOT NULL` filter below them, so NULL rows
846never enter the join. A shared index cannot have that filter below it (pushing
847it down would forfeit adoption), so the planner hoists the guard above the
848join. Any join on that key then pairs the whole NULL population of one side
849against the other's (a self-join squares it), on one worker, and discards the
850product afterwards. Symptoms: one worker grinding for minutes to hours while
851the rest idle, flat memory (produce-discard retains nothing), replica
852introspection unresponsive. The audit: for every index and MV in the package,
853read `EXPLAIN PHYSICAL PLAN AS VERBOSE TEXT`; in each join stanza, flag `IS NOT
854NULL` filters sitting in the closure region (between the join line and its
855input operators) whose columns are that join's arrangement key. A filter below
856an arrange is the safe placement, not a hit. Confirmation without plan reading:
857the NULL population shows as a record-count excess on exactly one worker in
858`mz_arrangement_sizes_per_worker`. Fix, in the order the pinning procedure
859requires ("Dead objects" above, "Making changes" below): drop the raw
860nullable-key index (so no future consumer can re-arm the landmine and so the
861new index does not adopt it), create a passthrough view `WHERE key IS NOT NULL`
862and index that, then rebuild every consumer the NOTICE names.
863
864**An enabling index can flip a join onto a hot key.** New shared indexes
865change join implementations, not just share state: with all inputs
866pre-arranged the planner flips linear chains to delta plans, and a delta
867path that streams through a high-fan-out lookup funnels that key's
868traffic through one worker, at hydration and in steady state, which can
869turn a minutes-long hydration into a many-hours grind. After adding
870indexes, check `EXPLAIN OPTIMIZED PLAN WITH (join implementations)` on the
871big consumers and inspect delta paths whose stage keys can fan out, and
872compare every rebuilt dataflow's hydration time with its pre-change one
873(`mz_internal.mz_compute_hydration_times`); the verification rules in
874references/indexes.md carry the mitigation.
875
876**A dropped or replaced index can leave a zombie dataflow.** Plans adopt
877indexes at CREATE time, so a new index adopts the old one and DROP INDEX leaves
878the old dataflow maintained until every consumer the NOTICE names is rebuilt (a
879same-version restart keeps every cached plan except those of objects whose
880dependencies changed, such as a dropped index's dependents, which are replanned
881against the current catalog and adopt whatever indexes exist then; an upgrade
882or an optimizer-flag change replans everything). The drop-create-rebuild-verify
883order in "Making changes" is the guard.
884
885## The index advisor is one input, never ground truth
886
887`mz_internal.mz_index_advice` reasons only over the object dependency
888graph. It has no notion of sizes, column widths, key correctness, or
889external consumers:
890
891- Its 'add index' rows are width-blind. On a wide view they can
892 recommend an index whose full-width arrangement washes out or exceeds
893 the narrow private copies it would replace. Size both sides first.
894- Indexes on tables are invisible to it. It will never tell you to drop
895 one, however large and dead.
896- It only propagates advice along chains under sinks or indexed
897 objects. Everything under a plain, un-indexed materialized view is
898 silently absent from its output.
899- Its MV-demotion rows ('convert to a view', with or without an index)
900 see catalog dependencies only, other clusters' maintained objects
901 included; they cannot see readers that are not catalog objects (ad
902 hoc SELECTs, SUBSCRIBEs, dashboards, applications). Converting a
903 serving MV on such advice breaks contracts the advisor knows nothing
904 about.
905
906Consequently: adjudicate every advisor row with a measurement, and
907never treat its silence as evidence.
908
909## Adjudicating proposals and estimating
910
911When a fix list needs verdicts, whether from a colleague, the advisor,
912or your own earlier pass:
913
914- One verdict per item: adopt, modify, or reject, each with its
915 measurement (ground rules). "Modify" is a first-class verdict; the
916 most common cases are the right key on the wrong, too-wide object,
917 and a literal filter column folded into the join key, which makes an
918 index that serves neither (references/indexes.md,
919 index key correctness).
920- Before endorsing an index on a shared relation, run the closure audit and the
921 width math. Before endorsing a drop, check adopters
922 (`mz_compute_dependencies`, the stored plan's footer) and external consumers.
923 Treat every unmeasured item as unproven; a typical unreviewed list contains
924 correct keys, wrong keys, redundant items, and at least one actively
925 dangerous one.
926- Estimates come from per-operator attribution: a dedup saves the sum
927 of the private copies you remove minus what you add; a hint retune
928 saves the advice view's estimated savings for the operator, confirmed
929 by measuring after the retune; a slimming saves delta-bytes/row times
930 records times copies. State a confidence per
931 number. An interval that stays wide is a method problem: narrow it by
932 attributing further, not by re-asserting it. If a number cannot be
933 tightened, name the unknown quantity and the experiment that would
934 pin it.
935- A rewrite that relies on a data property (a window bound, a
936 uniqueness the optimizer cannot see, a match rate, a column that is
937 never NULL) gets that property derived, never assumed: a probe
938 establishes it, then state it, verify it, and record the guard rule
939 that keeps it true.
940- Report estimated vs measured for everything implemented, in one
941 table, and retract cleanly any number that measurement refuted.
942
943## Making changes
944
945Who executes what, and where:
946
947- Diagnosis: read-only, anywhere; catalog, introspection and EXPLAIN reads need
948 no permission, reads of the user's data do (ground rules).
949- Experiments: on a dedicated experiment cluster that the user creates.
950 Propose it with an intended replica size and expected lifetime, and
951 hand the user the commands, for example:
952
953 ```sql
954 CREATE CLUSTER optimize_experiments (SIZE = '<size>');
955 ```
956
957 Run these yourself only if the user explicitly asks. A standing cluster costs
958 credits on Cloud and node capacity anywhere else, and one the user created
959 does not get forgotten. `CREATE CLUSTER` fails with `creating cluster would
960 violate max_clusters limit` on an environment already at its limit; the limit
961 is raised by Materialize on Cloud and by the operator on self-managed, so
962 report it to the user rather than working around it.
963- Object DDL inside the experiment cluster, two options (MCP cannot
964 write; offer both and let the user pick):
965 1. A direct SQL connection for a restricted role whose DDL surface is exactly
966 the experiment cluster and an experiment schema (`GRANT CREATE, USAGE ON
967 CLUSTER optimize_experiments TO <agent_role>`, the same on the experiment
968 schema, plus USAGE on the production schemas and cluster (PUBLIC has the
969 cluster's by default, hardened environments revoke it) and SELECT on the
970 objects being compared). `CREATE INDEX` requires OWNERSHIP of the indexed
971 object, so this role cannot index a production MV directly: create a view
972 over it in the experiment schema, index that view, and have the candidate
973 consumers read the view. The role cannot build anywhere else (CREATE is
974 granted nowhere else) and cannot alter or drop any cluster (ownership
975 gates those, and the user owns the experiment cluster). All of that holds
976 only while `enable_rbac_checks` is on, which is the Cloud default but not
977 the self-managed or emulator default, and only while the role is neither a
978 superuser nor a member of the role that owns the production objects; where
979 the checks are off, option 2 below is the only real containment.
980 2. You write self-contained SQL scripts that the user executes, and
981 you measure read-only over MCP. No new credentials, at the cost of
982 a user round-trip per build iteration.
983
984 Either way the experiment role needs nothing beyond SELECT on production
985 objects and USAGE on their schemas and cluster. Applying accepted changes is
986 a separate phase with separate privileges, and it is the user's, through the
987 source of truth.
988- Deployment of accepted changes: ask where the source of truth for object
989 definitions lives (mz-deploy, dbt, custom scripts, or the catalog itself) and
990 express the changes there. The materialize-dbt and mz-deploy skills cover
991 those tools. Full-rebuild deployment tools recreate all objects together,
992 which dissolves most of the pinning hazards below; what survives everywhere
993 is index-before-MV ordering within the deploy. After an incremental deploy
994 (only the changed objects rebuilt), have the user run the restart check of
995 the final evaluation below on the production cluster, at a time a rehydration
996 is acceptable, to confirm the headroom the resize leaves; a full-rebuild
997 deploy that hydrates the new generation on its own cluster is that check
998 already (read the peak from that replica's gauges and history).
999- The replica resize at the end is likewise a recommendation for the
1000 user to execute.
1001
1002**Final evaluation and sizing.** A cluster's memory peak is normally at
1003hydration, when everything rebuilds at once, which a restart, a redeploy or an
1004upgrade does in production; upgrades roll out on Cloud automatically and the
1005platform reschedules replicas, so the peak is a routine event, not one the user
1006can avoid by never restarting. Two things make the peak. Arrangement state:
1007plan for at least 2x its steady-state footprint even where the measured
1008hydration peak is smaller, since Materialize's engineering treats a 2x
1009hydration spike as acceptable and any release can move a cluster's spike up to
1010that without notice. Materialized-view sinks are outside that rule: each
1011buffers about two copies of its MV's output at hydration (cost model), on top
1012of the arrangement peak and unrelated to the steady state, and drains
1013afterwards, so this share exists only while a hydration runs and a replica
1014restart is the only way to produce another one.
1015
1016Therefore the final evaluation of an accepted change set rehydrates the whole
1017final state at once: restart the replica of the experiment cluster that holds
1018it (set a managed cluster's `REPLICATION FACTOR` to 0 and back, or drop and
1019recreate the replica of an unmanaged one), or build the final set from scratch
1020on a fresh experiment cluster, and record that hydration's peak. Rebuilding
1021only the changed dataflows understates the spike a production rehydration will
1022produce, so a change aimed at the peak, or able to raise it (a new index, a new
1023boundary), is measured by rehydrating the whole experiment cluster as well.
1024
1025Read the peak from `mz_internal.mz_cluster_replica_metrics_history` as
1026`heap_bytes`, RAM plus swap, since `memory_bytes` reads flat once swap absorbs
1027a spike (under the process orchestrator, the emulator and a local environmentd,
1028no `heap_bytes` exists and `memory_bytes` is the only peak available);
1029`mz_cluster_replica_metrics` is a point-in-time reading and misses a peak that
1030has passed, and the history is sampled once a minute per replica, so treat a
1031single high sample as a lower bound. For the sink share read also the
1032`mz_persist_sink_correction_max_per_sink_worker_*` high-water gauges in
1033`mz_cluster_prometheus_metrics`, which keep the peak after the buffer drains,
1034while the `mv_sink` line is often gone before a busy replica's introspection
1035reports it.
1036
1037Two numbers set the size: the steady-state `heap_bytes`, of which the replica
1038needs at least 2x whatever the experiments showed, and the measured
1039whole-cluster hydration peak of `heap_bytes` (RAM plus swap), which must stay
1040at or below roughly 90% of `heap_limit`, the enforced RAM-plus-swap ceiling
1041(`mz_cluster_replica_metrics.heap_limit`; `heap_percent` in
1042`mz_cluster_replica_utilization` is that ratio); recommend the larger of the
1043two resulting sizes. Read the column, never a remembered ratio: how far
1044`heap_limit` sits above a size's RAM differs by replica family and deployment
1045and changes without notice; on self-managed with swap enabled the ceiling comes
1046from the pod's cgroup memory and swap limits; under the process orchestrator
1047the column is NULL and this check is unavailable. Swap is mostly transparent;
1048it matters only when heavy swapping slows hydration or freshness, which shows
1049up as hydration time and lag, not as a memory failure.
1050
1051Replicating objects onto the experiment cluster: capture definitions
1052with `SHOW CREATE ...`, whose output is plain SQL with names resolved;
1053change its `IN CLUSTER` to the experiment cluster and replay. A
1054materialized view's `create_sql` catalog column ends in an `AS OF`
1055timestamp that `CREATE` rejects; strip it and the rest replays, but its
1056id annotations (`[u123 AS "db"."sch"."name"]`, `IN CLUSTER [u1]`) bind
1057by id and ignore the name, so it replays only inside the same
1058environment.
1059
1060Mechanics that hold whenever objects are built or replaced by hand
1061(experiment clusters, and environments deployed by raw SQL scripts):
1062
1063- Plans are fixed at CREATE time and cached across same-version restarts: an MV
1064 or index does not adopt an index or hint added after it was created until
1065 something replans it (an upgrade or an optimizer-flag change replans
1066 everything, a same-version restart only the objects whose dependencies
1067 changed), so changing a plan on demand means DROP and CREATE of the
1068 consumers, indexes first, then MVs.
1069- Every rebuild changes adoption. After any DROP or CREATE, re-read adoption
1070 (`mz_compute_dependencies`, the stored plan's footer) and the replica numbers
1071 before restating them in the report; a figure quoted from before the rebuild
1072 is stale, including an index's consumer count and the rehydration peak.
1073- A new index created while an old index on the same chain exists adopts the
1074 old one as its input. DROP INDEX then leaves the old dataflow alive and
1075 maintained (the NOTICE lists every pinned dependent) until all adopters are
1076 rebuilt or environmentd restarts. Therefore: drop obsolete indexes before
1077 creating their replacements, then rebuild every consumer the NOTICE names,
1078 then verify no zombie dataflows remain.
1079- `DROP ... CASCADE` on a view or materialized view takes every dependent with
1080 it, the indexes on dependent materialized views and views included; the
1081 NOTICE's DETAIL lists them, and a recreate script must bring every listed
1082 object back in dependency order (the input indexes an object should adopt
1083 before the object, its own indexes after it).
1084- After rewriting an MV, restore NOT NULL parity with `WITH (ASSERT NOT NULL
1085 ...)` if the rewrite loses the inference. It is an enforced assertion, not
1086 metadata: a NULL reaching an asserted column makes every read of the MV fail
1087 until the row is gone, so assert only what the rewrite guarantees. Confirm
1088 column names, order and types are identical (the `mz_columns` diff under
1089 "Verification discipline").
1090- `EXPLAIN ... FOR MATERIALIZED VIEW` shows the stored plan and will not
1091 reflect a newly added index; explain the MV's SELECT, with the session on the
1092 MV's cluster (`SET cluster`, or the `query` tool's cluster argument), to
1093 preview what a fresh build would do (the `Target cluster:` footer shows which
1094 cluster the preview used). On any other cluster the preview silently shows
1095 the un-indexed plan, since index arrangements are cluster-local.
1096
1097Close every engagement with a cleanup checklist for the user: candidate objects
1098dropped, the experiment cluster listed with its DROP CLUSTER command (it is the
1099user's to drop), and the resize command for the production replica once changes
1100are deployed.
1101
1102## Verification discipline
1103
1104- Prove exactness with `EXCEPT ALL` in both directions per relation, ideally in
1105 one query per relation so both sides read at one timestamp.
1106- Declared types are part of exactness: diff the `mz_catalog.mz_columns`
1107 signature (name, position, type) of every changed materialized view against
1108 its pre-change snapshot. A rewrite can keep every row and still change a
1109 column's type (the partial-sum widening below is the common case), which
1110 `EXCEPT ALL` cannot see and typed readers can.
1111- Snapshots before DDL: `INSERT INTO snap SELECT * FROM mv` is rejected
1112 whenever the MV's definition chain contains `mz_now()` ("calls to
1113 mz_now in write statements"). With temporal filters, file snapshots
1114 also go stale as rows age out. The valid proof there is the live
1115 comparison: build the replacement under a temporary name and run the
1116 two-way EXCEPT ALL against the still-running original at a single
1117 timestamp.
1118- Rewrites whose only semantic delta is intermediate-type widening (for
1119 example partial sums of an int4 column carried as int8 and a total that
1120 arrives as numeric) may still be offered where the user asked for
1121 byte-identical output, but always disclosed as an option with the delta
1122 stated, never shipped silently. The user may know overflow cannot occur in
1123 their data.
1124- Mutation probes on shared data: insert new keys only, or keep an
1125 exact undo; delete exactly what you inserted; re-verify the baseline
1126 afterwards.
1127- Leave no scratch objects behind, and report honest partials rather
1128 than working around a blocker.
1129
1130---
1131
1132Developing this skill (test methodology, eval harness): see
1133[DEVELOPMENT.md](DEVELOPMENT.md).