[v26.1.x] [CORE-16119] `storage`: materialize `segment_index` via `input_stream`

[vbotbuildovich]

Backport #30236 to branch v26.1.x. This issue was created as a placeholder because the original PR's commit(s) could not be automatically cherry-picked.
Here are the commands to execute:

git checkout v26.1.x
git checkout -b manual-backport-30236-v26.1.x-922
git cherry-pick -x 2a5ffceada

git push origin manual-backport-30236-v26.1.x-922
gh pr create \
  --title "[v26.1.x] [CORE-16119] `storage`: materialize `segment_index` via `input_stream`" \
  --base "v26.1.x" \
  --label "kind/backport" \
  --head "manual-backport-30236-v26.1.x-922" \
  --draft \
  --repo "redpanda-data/redpanda" \
  --milestone "v26.1.x-next" \
  --body "Backport of PR https://github.com/redpanda-data/redpanda/pull/30236 "

[CORE-16119]: https://redpandadata.atlassian.net/browse/CORE-16119?atlOrigin=eyJpIjoiNWRkNTljNzYxNjVmNDY3MDlhMDU5Y2ZhYzA5YTRkZjUiLCJwIjoiZ2l0aHViLWNvbS1KU1cifQ