Move all indices to store row address instead of row id

[wjones127]

Originally, when stable row ids were designed, indexes were planned to store row ids. When compaction happened, we could skip remapping. This turns out to be more difficult than expected in practice, and many indexes, such as Zone Map, map more naturally to addresses than row ids. Having a special row id path for stable row ids means lots of complex index invalidation branches.

For row addresses, we have a good mechanism for making remapping fast: the Fragment Reuse Index. So perhaps we should move all indexes to always store row addresses, even when stable row id is enabled.

Need: secondary (scalar/vector) indexes must stay correct and efficient as data churns underneath them — specifically, to resolve conflicts well against the three operations that move or rewrite rows: compaction, update / merge_insert, and reindexing. What an index entry points to (row address vs stable row id) determines how cheaply each conflict is handled — whether it forces a retry, a full index invalidation, or a read-time remap.

How it works today with SRID: with SRID enabled, scalar/vector indexes key on the stable row id rather than the row address. That forces SRID-specific handling in every churn path (merge_insert, optimize, compaction) and a read-time mask that maps the index's fragment_bitmap through the inverted row-id index to drop stale entries. This is the heaviest part of the feature to maintain.

Recommended: collapse to one path — always key secondary indexes on row address + the Fragment Reuse Index (FRI), even when SRID is enabled, and retire the stable-row-id indexing path. FRI is the deferred compaction remap mechanism already used by non-SRID tables: old→new row addresses recorded per compaction, applied to each index's fragment_bitmap at load, and trimmed once all indexes catch up.

Comparison across the three churn conflicts:

Transaction to commit	Concurrently Committed	Row address + FRI (recommended)	Stable row id
Compaction	Update	Compaction must retry because the compacted fragment has changed.	Compaction must retry because the compacted fragment has changed.
Update	Compaction	Update can rewrite impacted fragment’s deletion vector to the new fragment produced based on FRI and recommit	Update can rewrite impacted fragment’s deletion vector based on the reverse row index to the new fragment produced based on FRI and recommit.
Compaction	Reindexing	Compaction can commit and defers remapping, just produces a FRI that remaps reindexed index at read time	Compaction can commit. Index remains valid, the reverse index remaps row id back to new row address at read time.
Reindexing	Compaction	Index can commit, will be auto remapped using FRI at read time	Compaction can commit. Index remains valid, the reverse index remaps row id back to new row address at read time.
Update (Horizontal)	Reindexing	Can commit. Index still valid if not updating the indexed column.	Can commit. Index still valid if not **updating the indexed column.
Update (Vertical)	Reindexing	Can commit. Newly updated rows lose coverage	Can commit. Index still valid if not updating the indexed column. But user must tell the query plan explicitly somehow (e.g. partial schema, explicit input) to gain this optimization.
Reindexing	Update (Horizontal)	Can commit. Index still valid if not updating the indexed column.	Can commit. Index still valid if not updating the indexed column.
Reindexing	Update (Vertical)	Can commit. Newly updated rows lose coverage	Can commit. Index still valid if not updating the indexed column. But user must tell the query plan explicitly somehow (e.g. partial schema, explicit input) to gain this optimization.

Overall, both solutions do runtime remapping, FRI is always better than row ID index (green boxes) because row id index is always ≥ FRI (it equals the row-id metadata in memory once everything is compacted). As long as we continue to trim FRI, it can remain efficient. Trimming FRI also increases enterprise value as a part of auto table optimization experience.

The main benefit of SRID-based index is around its ability to keep index valid even after an update (red boxes). However:

• it is useful for a narrow use case that is typically done via horizontal update
• it will be superseded by the overlay file feature we are developing
• even when it is useful, it requires direct query plan hint that is not always available

So overall we believe it it better to just always use row address for indexing purpose, and we can remove a lot of complexity in the codebase related to SRID-based index handling.