doc: design for durable object hydration history

[aljoscha]

Motivation

Materialize exposes current hydration state, but it disappears when a dataflow or a replica restarts. A user can tell whether an object is hydrated now, not how long the last hydration took or whether today's is unusually slow.

This is the design for recording completed compute-object hydration episodes in a durable table. It lands first so the implementation PRs stacked on it can be read against it.

Description

The document is deliberately short and spends its space on the parts that are not obvious from the diff:

• Why the collector's subscribe reads the very table it is about to write. That is what makes the write idempotent across concurrent environmentd processes, and it looks redundant otherwise.
• The three conditions in the aggregation query that each prevent a specific wrong row: grouping by dataflow rather than catalog item, requiring max(installed_at) <= min(hydrated_at), and only reporting a start that every worker observed.
• Why durability is best effort in both directions, and how giving up the migration exemption is meant to be a deliberate act rather than an accident.
• The frontier-skew limitation that the choice of write timestamp implies, which is accepted for now, and what removing it would require.
• What a sampler structurally cannot record, so the table's gaps are documented rather than surprising.

Scope is limited to successful hydration of indexes and materialized views. Failed episodes, replica-wide episodes, resource peaks, and storage objects are excluded because the signals to do them correctly do not exist yet, and each exclusion says which signal is missing.

Verification

Documentation only.

Closes: SQL-632

[linear-code]

SQL-632

[SangJunBak]

What about unmanaged user replicas?

[aljoscha]

Good catch, that sentence was stale and I have fixed it. We collect unmanaged replicas too. They were excluded in an earlier revision because aggregating across workers needed the replica's worker count, which is unknown for unmanaged replicas. Reading worker 0 removed that need, and I updated the code but missed this line.

The only replicas actually skipped are those with introspection disabled, whose log arrangements exist but are never populated, so a subscribe would read a sealed empty collection forever. The doc now says that instead.

[SangJunBak]

Not sure what a compatibility view is

[aljoscha]

Fair, that paragraph was written while the compute change was still open and it reads like jargon now. A 'compatibility view' would mean: rename the log to something new, then create a view under the old name so existing queries keep working. I have rewritten the paragraph to say that in words, and to state the actual consequence, which is that the new columns are appended rather than reorganized.

[SangJunBak]

I was a bit confused what "episode" meant earlier in this design doc and assumed it meant "crisis". Like "Becky's having an episode right now". Might be worth defining this earlier.

[aljoscha]

Ha, fair, and I would rather not have Becky in the reader's head. I have defined it at first use in the overview: an episode is one dataflow's hydration on one replica, from installation to hydrated, recorded once. It is the unit the table stores one row of.

[SangJunBak]

If we want the Console to use this table, we may want to consider an index. Otherwise it negatively impacts the performance for everything else in the Console once queried.

[aljoscha]

Worth reopening, because the index was dropped deliberately and the reasoning assumed nothing queries this table yet.

It had one, on (global_id, replica_id). antiguru pushed back in #38346 on the grounds that a builtin index arranges the whole table in memory on mz_catalog_server, in every environment, forever, to serve queries nobody was making yet. I agreed and dropped it, along with is_retained_metrics_object.

If the Console is going to query it, that changes the input, and your point about unindexed scans hurting everything else on mz_catalog_server is the right one to weigh against the standing memory cost. Two things worth knowing for that decision:

The table is small by construction, one row per dataflow per replica per hydration, pruned at 30 days. It grows with deployments and object churn, as you note on the other thread, not with query volume.

Adding an index later is not free but is cheap: it needs a MigrationStep::replacement for mz_indexes pinned to the then-current dev version. Non-destructive, and I have noted it in the commit message so it is not lost.

I would rather add it when the Console query exists and we can size the arrangement against a real access pattern than guess now. Happy to be overruled if you know the query is coming.

[SangJunBak]

I'm assuming you're talking about this from the perspective of the coordinator task that's running the subscribe?

[aljoscha]

Yes. That is the coordinator-side task that owns the subscribe, so 'the loop' there is the read-then-write loop running on behalf of the sweep, not anything on the replica. I can name it explicitly if it is still ambiguous on a reread.

[SangJunBak]

Not clear what the aggregate actually does. Is this what happens in the anti-join step? What do we actually record for a catalog item with two global IDs? Can you give a concrete example?

[aljoscha]

The aggregate is gone entirely, and your question is a good part of why. Aljoscha pushed on the same thing, and chasing it showed the aggregation only existed to paper over a bad choice of key.

We were storing the item id, resolved from the log's export_id. The log's grain is one row per export, and an item can own several exports at once, so the translation created collisions that the GROUP BY then hid by merging rows and taking min() of their timestamps. That produced a row for an episode that never happened.

It now stores export_id directly as global_id, and there is no aggregate at all. Concretely, for your example of a catalog item with two global ids: a materialized view being replaced has both live, a replica runs both dataflows, and we record two rows, one per global_id, each with its own real timestamps. Added that example to the doc.

The anti-join is separate and unchanged: it is what stops us re-recording an episode we already wrote, keyed on (global_id, replica_id, installed_at).

[SangJunBak]

This is moreso my unfamiliarity with OCC, but DML for all tables or just for that table? Are OCC write permits shared across all tables?

[aljoscha]

Shared across all tables. It is a single Semaphore on the coordinator, sized from max_concurrent_occ_writes at startup, and every read-then-write takes a permit from it regardless of which table it targets. It exists because concurrent OCC loops make each other redo work, so total work scales quadratically without a cap.

That is exactly why the sweep does not take one: a session's wait is bounded by its statement timeout, while a sweep has none and its subscribe must first hydrate a dataflow on a user replica. Holding a permit would let background sampling stall user DML on any table for as long as that takes. Noted the shared scope in the doc, since it is the part that makes the decision matter.

[SangJunBak]

Maybe worth to note that we sweep one replica at a time

[aljoscha]

Added. The scheduling section now says one sweep visits one replica, and that sweeps never overlap.

[SangJunBak]

This part wasn't too clear to me. My interpretation:
"Even if the interval is high, the task is going to wake up and check to see if the interval length's changed. A disabled collector wakes up less frequently.". Didn't get the part about "that is the cadence of every environment in the default configuration.". Do we mean we're reusing the default cadence?

[aljoscha]

Your interpretation is right and the sentence was badly written. Rewritten to: the cap exists so that lowering a long interval at runtime takes effect within the cap rather than after the old interval elapses, and the cap is much coarser while collection is disabled, because a disabled collector has nothing to do but notice it has been enabled. The clause you quoted was trying to say that disabled is the default in every environment, so the coarse path is the common one. It now says that directly.

[SangJunBak]

A couple of high level questions:

[SangJunBak]

How might this compete with Michael Abebe's prometheus sink? What's the real motivation of having this table? Agents? Console?

[SangJunBak]

Are we going to converge this with MZ_COMPUTE_HYDRATION_STATUSES and the other relations? One difference is currently, we set REFRESH every MVs' hydrated to true even when there's no active dataflow on them.

[SangJunBak]

More on the product question: How important are the hydration statistics of objects during 0dt upgrades? It feels like the main motivation of durably recording these statistics is to show the user when to upsize based on hydration statistics. However since we don't durably record statistics for the read only envd, by removing a period of statistics when most objects hydrate, feels like it could lead to some product problems in Cloud. Some cases I'm thinking of:

1. In Cloud, customer first hydrates their replica. Weeks go by without changing it, but memory/cpu requirements change because of compute/optimizer changes. But agents / Console can't surface this information (although Greenberg's pinning would help with this)
2. In Cloud, customers are constantly doing blue green deployments, to which this won't matter too much
3. In Self managed, 0dt upgrades are visible/controlled by the users. So if a cluster is undersized, they'll just have to increase the size via the leader envd, which would be visible in mz_object_hydration_history

[aljoscha]

This is the best question in the review, and I went and checked rather than reasoning from the doc, because the answer is not obvious from the design as written.

The premise is right: a read-only generation writes nothing, the collector returns early in read-only mode, and a 0dt upgrade hydrates the incoming replicas while still read-only.

But the data is not lost, because collection samples current state rather than events. The stamps live in the replica's own log, the replicas keep running across promotion, and the first sweep after promotion picks up those episodes with the replica's original installed_at and hydrated_at. So an upgrade is recorded late, not never, and the durations are the real ones rather than post-promotion artifacts.

What genuinely is lost is an episode whose dataflow goes away before that first post-promotion sweep. That is the general sampling limit, not upgrade-specific.

I have added a section saying this, since 'read-only generations do nothing' was the only mention and it reads much worse than the truth.

That leaves your case 1 as the real product gap, and it is not about upgrades: an object that hydrated once and never again has one row from whenever it happened, so it tells you nothing about what it would cost to hydrate today after compute or optimizer changes. This design cannot fix that, only re-hydration produces a new measurement. Flagging it for the product call rather than deciding it here.

[aljoscha]

Deferring this one to Aljoscha rather than answering for the team, since it is a scope question rather than a design one. What I can say from the implementation side: this table is deliberately cheap and coarse, one sampled row per dataflow per replica per hydration, retained 30 days, with no index. It is not a metrics pipeline and would be a poor one. If the answer is that a Prometheus sink covers the use cases, that is an argument for dropping this rather than shrinking it.

[aljoscha]

Also for Aljoscha to call. Technically they are different shapes: mz_compute_hydration_statuses is current state, retracted when the dataflow goes away, while this is an append-only history that deliberately outlives the object and the replica. The REFRESH materialized view case you mention is a good example of why converging them is not free, since hydrated = true with no active dataflow has no episode to record, and this table would simply have no row.

Worth noting the hydrated flag divergence is a real inconsistency between the two regardless of whether we converge them.

[aljoscha]

Answered each below. Two of them, the Prometheus sink comparison and convergence with mz_compute_hydration_statuses, are scope calls I have passed to Aljoscha rather than deciding in review.

[aljoscha]

Thanks, this was a genuinely useful pass. Pushed doc fixes for everything actionable. Two of your catches were real staleness rather than unclear writing:

Unmanaged replicas. The doc said we visit one managed replica per interval. That has been false since I switched collection to read worker 0 only, which removed the need for a replica's worker count and with it the reason unmanaged replicas were excluded. Code was right, doc was stale.

The dangling frontier-skew term. I renamed that section when #38322 moved the write timestamp to the oracle, and left a forward reference pointing at the old name.

The rest: defined episode at first use, rewrote the compatibility-view paragraph in plain words, rewrote the interval-cap sentence you quoted, noted that a sweep visits one replica, and noted that OCC permits are one process-wide semaphore rather than per table.

On the aggregate, your question landed at the same time as Aljoscha's, and between them the answer turned out to be that it should not exist. The table was keyed by item id while the log's grain is one row per export, and the GROUP BY was hiding the resulting collisions by merging rows and taking min() of their timestamps. It now stores global_id and there is no aggregate at all. Your two-global-id example is in the doc now, and the answer is that we record both rows.

On 0dt, I checked rather than reasoned: read-only generations really do write nothing, but the episodes are not lost. Collection samples current state, the replicas survive promotion, and the first post-promotion sweep records them with their original replica-side timestamps. Late, not never. New section says so. Your case 1 is a real gap that this design cannot close, and I have flagged it rather than papered over it.

Left open for Aljoscha, since they are scope rather than design: the Prometheus sink comparison, convergence with mz_compute_hydration_statuses, and whether the Console's needs justify bringing back the index that was deliberately dropped.

[antiguru]

Approving. Nothing here is blocking, and the doc does the thing design docs usually don't, which is spend its length on the parts that look wrong until explained. The subscribe-reads-its-own-table paragraph and the RowSetFinishing note are both things a reader would otherwise "simplify" later.

The worker-0 argument is right, and it is a better answer than the one I had. The reported output frontier only advances as timely's dataflow-wide progress tracking allows, so worker 0's stamp already accounts for the slowest worker. Keeping both stamps on one process clock also dissolves the cross-worker anchor-skew problem the compute doc left as an open follow-up question, rather than working around it. Worth keeping that paragraph as prominent as it is.

Some notes, all non-blocking.

The started_at example is stale against what landed. The mechanism you describe is right, but the case you name as common is now handled the other way. handle_create_dataflow computes starts_immediately = dataflow.import_ids().next().is_none() and stamps a real HydrationStart there, so an import-free dataflow does get an observed start.

The actual common case for the installation-time reading is better than the one in the doc, and it is in the implementation comment on the backfill in handle_hydration: an index over an already-hydrated arrangement reports hydration while still suspended, which happens for a handful of mz_catalog_server indexes on every bootstrap. That is a stronger example precisely because it is not exotic, and it makes the point that the backfill is a normal path rather than a guard against something unexpected.

Related: "the two are not distinguishable here" is slightly more pessimistic than the code. starts_immediately stamps a separate event, so an immediately-started dataflow lands started_at microseconds after installed_at, where the backfill sets them exactly equal. I would not encourage a consumer to lean on that, since it is exact equality of two independently stamped events and nothing documents it as a contract, but it might be worth saying that explicitly rather than leaving the impression that both readings collapse.

Disabling collection also suspends retention, which the enable-then-disable case makes awkward. The reasoning is sound for the default configuration, where the table is empty and an always-on subscribe buys nothing. The case not covered is an operator who enables collection, accumulates rows, and turns it off: those rows then sit past their retention period indefinitely. Worth either saying that outright as a consequence, or gating retention on the table being non-empty rather than on collection being enabled.

Goals and Rollout disagree with each other. Goals says "default off in production, on in CI"; Rollout says it stays off in the sqllogictest runner defaults. The Rollout reasoning is convincing, since catalog-content and plan assertions would churn, but the Goals bullet currently promises something the rollout retracts. Qualifying it in Goals would also make the deviation from the usual flag guidance a visible decision rather than something a reader finds two pages later.

The no-permit decision is load-bearing on single-flight, and those two facts live apart. Skipping the process-wide OCC semaphore is safe because a sweep never overlaps itself, so the worst case is one extra concurrent read-then-write. Anyone who later parallelizes sweeps removes that bound without touching the code that documents it. An assert or a comment tying them together would be cheap insurance.

The PR description is stale. It promises to explain "the three conditions in the aggregation query," including the max(installed_at) <= min(hydrated_at) guard, but the doc now says no aggregation remains. Worth updating so a reader going description-then-doc does not go looking for a section that was correctly deleted.

Cross-link, and a disclosure. Neither this doc nor 20260817_compute_hydration_timestamps.md references the other, though this one describes that one's change. A pointer in each would help.

You should also know the compute doc is currently stale in a way that touches this one, and I am putting up a correction for it. It specifies renaming the log to mz_compute_hydration_timestamps_per_worker with the old name as a compat view, where the implementation appended the columns in place and kept the name, OID and object kind. Your approach is the one that landed and the one I would have chosen knowing what the per-worker relation's consumers actually do, so the correction goes in the compute doc, not here. The other correction is the backfill point above, which my doc wrongly describes as a repair for an unanticipated path.

---

Generated by Claude Code