search

cockroachdb / cockroach

CockroachDB — the cloud native, distributed SQL database designed for high availability, effortless scale, and control over data placement.
https://www.cockroachlabs.com
Other
30.1k stars 3.8k forks source link

sql: allow fine-grained control of index visibility #82363

Open ajwerner opened 2 years ago

ajwerner commented 2 years ago

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 https://github.com/cockroachdb/cockroach/issues/68542.

Jira issue: CRDB-16312

wenyihu6 commented 2 years ago

@mgartner @michae2 These are the draft PRs that include all of my work done for fine grained invisibility so far. (https://github.com/cockroachdb/cockroach/pull/87259 and https://github.com/cockroachdb/cockroach/pull/87301) Feel free to ping me on anything. I'm more than happy to be an open source contributor.

rytaft commented 1 year ago

I've updated the description of this issue since partially visible indexes are now fully supported. The only remaining unimplemented idea in this issue is "We could make the index visibility fine-grained such that sessions of a certain user or application have different index visibility."