catalog: add mz_object_hydration_history

[aljoscha]

Stacked on #38344.

Motivation

Adds the durable surface that hydration episodes are recorded into. The collector arrives in the next PR, so this rung can be reviewed as a catalog change.

Design doc: 20260817_durable_object_hydration_history.md

Description

Adds mz_internal.mz_object_hydration_history, with no index. The table starts in mz_internal to mark it unstable while its shape and semantics settle.

An episode is identified operationally by (object_id, replica_id, installed_at). object_id holds the dataflow id reported by the replica, with SemanticType::GlobalId, so consumers resolve it through mz_internal.mz_object_global_ids rather than joining mz_objects directly. installed_at is replica-stamped and stable across environmentd restarts.

The identity is not declared as a relation key. A key is an optimizer contract, while this best-effort sampler enforces uniqueness through its anti-join. A duplicate must remain visible rather than letting the optimizer silently elide it.

There is no index initially. The collector anti-joins by history identity, but its subscribe runs on the selected user replica and cannot use an index arranged on the catalog server. Such an index would pin the whole table without removing the collector's recurring import and arrangement cost. Adding one later also changes the mz_indexes fingerprint, so it requires a MigrationStep::replacement for mz_indexes pinned to the then-current development version.

Durability is best effort. The table is exempt from the bootstrap reset and from forced replacement, because either would discard sampled history that cannot be rebuilt. Schema evolution is allowed and keeps the shard and rows. A replacement step naming this table trips an assert in validate_migration_steps; giving the exemption up is meant to be deliberate and release-noted. A focused policy test pins evolution participation and replacement exclusion.

The bootstrap-reset filter is converted from name/schema comparisons to a set of resolved ids, which is clearer now that several history tables are exempt. The ontology link for replica_id uses SemanticType::ReplicaId, matching both the column and its target. User documentation states that 30 days is the retention default, disabling collection pauses retention, and dropped dataflow IDs may no longer resolve through mz_object_global_ids.

Verification

Catalog goldens cover builtin counts, OIDs, index accounting, information_schema, and the autogenerated mz_internal relation spec. The catalog migration tests exercise the migration path.

This release will add the mz_internal.mz_object_hydration_history table.

Ref: SQL-644

[linear-code]

SQL-632

SQL-644

[aljoscha]

this needs to be cut down quite a bit and focus on the facts, no handwaving about support and whatnot, when we ship the feature this will be on. Also cut the stuff about surviving restarts, this is expected of tables.

[aljoscha]

Cut to two paragraphs, matching the length of siblings like mz_cluster_replica_metrics_history. Dropped the restart sentence and the whole "contact support to enable" paragraph. What's left is what it records, 30 day retention, that ids may name dropped objects, and that recording is best effort with only successful hydration captured.

[aljoscha]

cut this down to teh facts please

[aljoscha]

Done, down to three lines: exempt from the bootstrap reset and forced migrations because the contents can't be rebuilt, and clearing them for a schema change is still allowed with a pointer to the tripwire. The reasoning about why that trade is acceptable lives in the design doc instead.

[aljoscha]

are we sure we want the index? what do comparable internals have?

[aljoscha]

Checked the closest comparable. mz_internal.mz_object_arrangement_size_history is the same kind of thing, a durable retention-bounded internal history table, and it carries two builtin indexes: mz_object_arrangement_size_history_object_ind on (object_id) and mz_object_arrangement_size_history_ts_ind on (collection_timestamp), both with is_retained_metrics_object: true.

So one index on (object_id) with is_retained_metrics_object: true is exactly the sibling's first index, and I'd keep it. I deliberately did not copy the sibling's second one: our retention deletes a bounded batch per sweep rather than being something users scan, so indexing finished_at would cost every environment an arrangement to speed up a background job that is already cheap.

This also settles a question I had flagged separately, whether is_retained_metrics_object should be false here. The sibling precedent is unambiguous, so true stays.

[aljoscha]

Following up on my own answer: you are right, and my precedent argument was the wrong test. is_retained_metrics_object: true resolves to a 30 day logical compaction window (metrics_retention), which holds the since back so the shard, and for the index the arrangement in memory, retain 30 days of update history rather than just current state.

Our history lives in the rows, and the retention sweep retracts them on its own schedule, so nothing here needs to be readable at an old timestamp. The window would buy nothing and cost every environment memory on the catalog server. Set to false on both the table and the index, with the reasoning recorded at the definition.

That the sibling sets true while also pruning explicitly looks like the same redundancy, but I left it alone rather than widen this PR.

[aljoscha]

ahh, I really think we shouldn't add the index by default, the history collection can grow quite a bit, so this index might grow out of hand, no?

[aljoscha]

why did we add this comment? is it not clear from how things work so far? Might just want to drop this and spare the noise?

[aljoscha]

Fair, dropped. It was explaining a property of the list that holds regardless of this change.

[aljoscha]

what's the "installation" here?

[aljoscha]

are we sure about the ontology links? stll helpful to say what this points at, no?

[aljoscha]

ahh, I really think we shouldn't add the index by default, the history collection can grow quite a bit, so this index might grow out of hand, no?

[def-]

QA LLM Review

1. MEDIUM -- promised object_id index and mz_indexes replacement step are not in the diff

src/catalog/src/builtin/mz_internal.rs:4973

The commit message and PR body both state that this change adds a builtin index on object_id and "declares the required replacement migration step" for mz_indexes. Neither exists in the diff: there is no BuiltinIndex for the new table and no new entry in MIGRATIONS. The two are consistent with each other (no index means no mz_indexes fingerprint change), so CI is green, but the record now says a mz_indexes step was declared when it was not, which is exactly the belief that makes the follow-up catalog-open panic.

Details

Evidence that nothing was added:

• git grep MZ_OBJECT_HYDRATION_HISTORY returns only the table, its SystemObjectDescription, the BUILTINS_STATIC entry, and the two migration/reset call sites.
• test/sqllogictest/mz_catalog_server_index_accounting.slt gains no index row (only the s835 → s836 shift of mz_notices), test/testdrive/catalog.td's SELECT COUNT(id) FROM mz_indexes WHERE id LIKE 's%' is unchanged, and catalog_server_explain.slt — whose query list is generated by enumerating catalog-server indexes — gains no EXPLAIN INDEX block.
• The newest MIGRATIONS entry is still the 26.39.0-dev.0 mz_audit_events replacement.

Two consequences worth deciding between:

1. If the index was meant to ship, it is missing, and the user query the PR describes ("how long did this object take to hydrate") full-scans up to 30 days of history on mz_catalog_server.
2. If it was deliberately dropped, the commit message needs the same edit. It matters beyond tidiness: make_mz_indexes (src/catalog/src/builtin/mz_catalog.rs:754) inlines every builtin index as a VALUES row, so whoever adds the index later must add a MigrationStep::replacement for mz_catalog.mz_indexes pinned to the then-current dev version. The NOTE above the 26.34.0-dev.0 step spells out the failure mode: a step at a stale version is skipped and "the fingerprint check then panics at catalog open". A follow-up that trusts this commit message and skips the step reproduces that panic on every upgrading environment.

2. LOW -- unique key declared on the new table is not enforced by anything, and cannot be corrected without clearing the table

src/catalog/src/builtin/mz_internal.rs:4994

.with_key(vec![0, 2, 3]) tells the optimizer that (object_id, replica_id, installed_at) is unique, so DISTINCT/Reduce over those columns can be elided and join cardinalities assumed. Unlike the other keyed builtin tables, whose rows are derived one-per-catalog-object and are therefore unique by construction, this table's rows will come from a best-effort sampling collector, and this diff removes the two mechanisms that would otherwise paper over a duplicate: the bootstrap reset (src/adapter/src/coord.rs:3068) and forced schema migration (src/adapter/src/catalog/open/builtin_schema_migration.rs:812) both now skip it.

Details

That combination also makes the key hard to walk back. Changing it is a RelationDesc change, and the tripwire added at builtin_schema_migration.rs:735 forbids a migration step for this table, so the documented escape hatch is to remove the tripwire and the exemption, which discards the history the exemptions exist to protect.

Worth confirming before the collector lands that a re-recorded episode (same object, same replica, same replica-stamped installed_at, written twice across an environmentd restart) is impossible rather than merely unlikely. If it is not, the row multiplicity is silently wrong and so is any query the optimizer simplifies using this key.

[aljoscha]

Both valid, thanks. Fixed.

1. Stale record. Correct, and the reason it matters is the reason I fixed it rather than shrugged. The index was dropped deliberately after review (an arrangement holding the whole table, growing with objects times replicas times re-hydrations, for a query nobody runs yet), and the mz_indexes step went with it because there is no longer a fingerprint change. The commit message and PR body still claimed both. A future contributor trusting that message would skip declaring the step when adding an index and reproduce exactly the catalog-open panic the 26.34.0-dev.0 NOTE warns about.

Commit message and PR body now say there is no index and no step, and the commit message carries the forward-looking warning so it is attached to the thing someone will read when they add one:

NOTE: Adding one later is not only an index. make_mz_indexes inlines the builtin index set as VALUES, so a new index changes the mz_indexes fingerprint and needs a MigrationStep::replacement for it pinned to the then-current dev version. A step at a stale version is skipped and the fingerprint check panics at catalog open.

2. The key. Dropped it. Your asymmetry argument is the decisive one: the key buys a DISTINCT elision nobody needs, while being wrong once turns into a silently wrong answer rather than a visible duplicate row, and correcting it costs a descriptor change, which for this table means giving up the exemptions that protect its contents.

I also checked what comparable tables do, and none of them declare a key: mz_object_arrangement_size_history (same shape, background collector plus its own pruner), mz_cluster_replica_metrics_history, mz_cluster_replica_status_history, mz_storage_usage_by_shard. Mine was the outlier.

On whether a duplicate is actually possible: I believe it is prevented by construction, since the collector's subscribe reads the history table, so a second write for the same episode would have to be computed from a frontier that does not yet include the first, and the OCC write then fails with TimestampPassed. The outer GROUP BY collapses to the identity within one batch, and collection applies the retention cutoff so a retracted row is not resurrected. But "I believe it is prevented" is exactly the wrong footing for a promise to the optimizer, so the identity is now enforced operationally by the anti-join and documented in the design doc instead of declared on the relation.