kv,sql: improve rebalancing for incrementing indexes

[irfansharif]

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

Heavy read/write workloads on incrementing indexes (timestamps are a common one) create moving hotspots in KV that are difficult to split+rebalance for better aggregate utilization. With such write patters, we're reading/writing to the right hand side of the last range in the index, and with our current structure of splitting off + rebalancing the RHS as a reaction to increased # of batch requests load, we'd be continuously rebalancing (with network costs/utilization) but still have single-range hostspots.

Describe the solution you'd like

A few ideas to improve here:

• splitting + rebalancing ahead of the incrementing index
  • detecting such patterns automatically, and/or through heuristics (such as indexes on commonly incrementing types like timestamps) or user-provided hints
• auto-hashsharding indexes on timestamps, or on sequence-based indexes
• (bad idea) encoding incrementing keys to sort in decrementing order (would not help with load distribution, but prevents incoming writes from contending with load-based splits and lease transfers

Additional context

Relevant internal issues: https://github.com/cockroachlabs/support/issues/1590, https://github.com/cockroachlabs/support/issues/1533.

Jira issue: CRDB-16157

[irfansharif]

For detection of incrementing indexes, short of sophisticated visualizers I wonder if simple heuristics get us most of the way. Strawman: % of requests on an index targeting the last X% of an index’s keyspan sustained over some rolling period of time, surfaced as a warning similar to how we surface the presence of full table scans. +cc @mwang1026.