Support using index in backfilling

[hzxa21]

Is your feature request related to a problem? Please describe.

basically if we are creating a sink from A to B, and we know B or B's downstream prefer the backfilling rows to be ordered by X, we better add ORDER BY X to A if A is a MV, or create a new MV A' as SELECT .. from A ORDER BY X and create the sink from A'

Sometimes we need backfilling to emit rows in a specific ordering to reduce unnecessary downstream changes. Currently we can work around it by creating a MV with ORDER BY and use this MV for backfilling but this MV cannot be dropped after backfilling because it exists in the streaming graph. Instead, using index for backfilling seems more flexible.

Describe the solution you'd like

No response

Describe alternatives you've considered

No response

Additional context

No response

[xxchan]

This was also once mentioned here #12824 (comment)

[kwannoel]

Currently we can work around it by creating a MV with ORDER BY and use this MV for backfilling but this MV cannot be dropped after backfilling because it exists in the streaming graph. Instead, using index for backfilling seems more flexible.

Do you mean that you'd like to use the index for backfilling side, but the MV for the upstream side? And then be able to drop the index after backfilling done?

[xxchan]

Yes, the index is only useful for backfilling. Persisting data twice both in MV and index seems to wasteful.