[DocDB] Merge empty tablets

[FranckPachot]

Jira Link: DB-10716

Description

When data is deleted and leave empty tablets, they stay empty, using CPU and RAM. If they are on a key that is always increasing, like a primary key from a sequence or an index on a timestamp, they will never be used again by new rows.
During the lifecycle of an SQL application, such tables will grow, and then be automatically split, and old data will be deleted. This can result to an always increasing number of tablets.

It would be great that such tablets are merged to leave at most one empty tablet. This can be manual as it usually result from purge or archiving of data.

See https://yugabyte.slack.com/archives/C4K1838GL/p1711987353340979?thread_ts=1711798545.131779&cid=C4K1838GL

Workaround: use declarative partitioning on top to be able to drop an empty partition when it has all empty tablets, but this brings back the PostgreSQL partitioning limitations (no global indexes) that we don't have with sharding

If REINDEX CONCURRENTLY was supported, for primary key and secondary indexes, it would also be a workaround as this operation doesn't need to be frequent #10694

Issue Type

kind/enhancement

Warning: Please confirm that this issue does not contain any sensitive information

• I confirm this issue does not contain any sensitive information.