Is your feature request related to a problem? Please describe.
Invisible indexes logically hide indexes from application traffic so that query plans do not use these indexes but writes keep them up-to-date. This is useful to validate that an index should be dropped without risking the cost of rebuilding the index if dropping it was a mistake. This can still be risky for tier-zero services which are not eager to shoulder the risk of a needed index being dropped even if for a short amount of time.

Describe the solution you'd like

We could make the index visibility fine-grained such that sessions of a certain user or application have different index visibility.

Note that we also already support using a float value for visibility to allow an index to be "partially visible". For example, if the visibility value for an index is 0.1, the optimizer has a 10% chance of considering the index when planning.

Additional context

Perhaps the best way for risk-sensitive workloads to know that they can drop an index is to ensure that the index isn't being used, see #68542.

Jira issue: CRDB-16312