ttl: improve row deletion performance with secondary indexes

[otan]

Currently, a row-level TTL table with secondary indexes may be slower to delete, as it requires access to multiple ranges to perform the deletion.

We can improve this based on this section of the RFC:

We can improve deletion speed on secondary indexes if we split the deletes for secondary indexes and the primary index. This way, we have a lot more flexibility in terms of which writes we batch.

What this allows us to do is throw thousands of rows in a big buffer, decompose their individual writes, bucket them by range, and send a batch per range. This has two benefits, both of which are very significant:

• we can easily build up large batches of 100s of writes for each range, which will stay batched all the way through Raft and down to Pebble.
• these batches don’t run a distributed transaction protocol, so they hit the 1PC fast path instead of writing intents, running a 2-phase commit protocol, then cleaning up those intents.
  This is predicated on filtering out expired rows working as otherwise users could miss entries when querying the secondary index as opposed to the primary.

Jira issue: CRDB-16224

[otan]

cc @rmloveland it may be worth documenting on the TTL docs that tables with multiple indexes may benefit from a lower delete_batch_size, as secondary indexes are on multiple ranges and a larger batch size means potentially more ranges being touched which potentially impacts cluster-wide latency. let me know if you need clarification!

[rmloveland]

Thanks Oliver, added this info as a comment on cockroachdb/docs#13703 to make sure it's captured with some other issues @vy-ton wrote up!

[nvanbenschoten]

#79134 is starting to ask the question of what needs to change during SQL planning and execution if referential integrity is not enforced. It turns out, not much. There are a few SQL optimizations that need to be disabled, mostly related to limit pushdown across table and index joins. We also needed to disable SKIP LOCKED on tables with column families, but that might not be relevant here if we can enforce per-range consistency (a row's column families will never span ranges).

Those findings may be relevant here because it means that we don't need to push filtering of expired rows all the way down to individual index scans (which in turn requires all secondary indexes to store the expiration). Instead, we can lift this filtering higher up in the query plan. Or forgoe it entirely if we're ok with not promising that two separate queries that touch different indexes see consistent results.