sql: support manual splits on table boundaries for secondary tenants

[irfansharif]

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

On the host tenant, we unconditionally split on all table/index/partition boundaries. We opted not to do the same for secondary tenants as that would open us up to an order-of-magnitude more ranges in the system. Still, as secondary tenants, it may be beneficial to split on particular table boundaries for performance reasons. It will certainly be necessary when defining divergent zone configs on adjacent tables, but even that, it'd be nice for us to support. This issue is similar but not the same thing as #54254 (which talks about supporting splits within a table boundary).

Describe the solution you'd like

Supporting a form of ALTER TABLE ... SPLIT AT to allow defining manual splits on table boundaries (by splitting at the end/start of the table).

Describe alternatives you've considered

Once we support multi-tenant zone configs (#49854), users could define no-op zone configs on the tables they wish to split apart. Another option would asking users to define dummy table partitions on each table, where the partition would straddle the entire table's keyspace. This would work as long as we always split on partitions for secondary tenants (do we?).

Jira issue: CRDB-7790
Epic: CRDB-25188

[irfansharif]

If we went with #66063, this issue would generalize to also now supporting manual splits on table boundaries for the host tenant.

[ajstorm]

@irfansharif is this a duplicate of #54254??

[irfansharif]

No it’s not.

[knz]

@ecwall has completed this work.

[irfansharif]

This issue was not talking about a secondary tenant being able to use ALTER TABLE … SPLIT AT to split within a table, which the tenant capabilities work you linked internally (#94644, #95013, #95040, #96390) now lets us do. It’s talking about introducing syntax to let uses split at the end of tables explicitly, which is different. I think you meant to close #54254.

[knz]

Ok thanks for explaining. But IMHO this is not a task for the multi-tenant vteam. It's a task for the KV team, as a long tail of work resulting from the enablement of range coalescing.