Automatic tablet splitting versus manual pre-splitting for range-sharded tables with skewed write distribution

Answer

For a range‑sharded YSQL table with a monotonically increasing key and a skewed write pattern (few tenants generate most writes), automatic tablet splitting under the default gflags will eventually engage, but it may not react fast enough to prevent prolonged write concentration on a single tablet. Manual pre‑splitting gives immediate leader distribution, yet it incurs per‑tablet memory and compaction overhead that scales with the number of splits. When the hot‑key ranges are known (e.g., specific tenant IDs), SPLIT AT VALUES on those hot keys is more effective than a uniform SPLIT INTO N because it targets only the congested ranges. Re‑splitting an already‑populated table with SPLIT AT VALUES is an online operation; it does not require a full table rebuild, though it triggers leader reassignment and background compaction rebalancing.

Confirmed facts

• Automatic splitting triggers when a tablet exceeds the size threshold defined by ysql_tablet_split_size_mb (default 64 MiB in recent YugabyteDB releases).
• Manual SPLIT INTO N creates N tablets immediately, distributing initial write leaders across them.
• SPLIT AT VALUES splits only at the supplied key values, leaving other ranges untouched.
• Applying SPLIT AT VALUES to a populated table is processed online; the table remains readable and writable while the split proceeds.

Likely explanation / trade‑off analysis

Under extreme key skew, a single hot tenant can keep a tablet growing well beyond the split threshold for minutes or hours before the background split process finishes, during which write latency on that tablet may spike. If the workload can tolerate this temporary hotspot, the simpler automatic approach avoids the upfront design effort and the ongoing per‑tablet overhead of many small tablets. Conversely, if write latency must be evenly distributed from the moment the table is created, pre‑splitting (especially SPLIT AT VALUES on the hot tenant IDs) eliminates the initial concentration, at the cost of extra memory for each tablet’s MemStore and increased compaction activity.

Steps to decide for your workload

1. Verify the current split thresholds: yb-admin --master_addresses <master> get_flag ysql_tablet_split_size_mb and ysql_tablet_split_size_threshold_mb.
2. Monitor tablet size growth for the hot key range using the system view:

   SELECT tablet_id, start_key, end_key, approximate_size_mb
   FROM ysql_tablet_history
   WHERE table_name = 'your_table'
   ORDER BY approximate_size_mb DESC
   LIMIT 5;

3. If the hot tablet’s size approaches or exceeds the threshold within a time window shorter than your latency SLA, consider manual pre‑splitting.
4. If you can identify the hot tenant IDs (e.g., from tenant‑metadata or access logs), run:

   ALTER TABLE your_table SPLIT AT VALUES ('hot_tenant_id_1'), ('hot_tenant_id_2'), ...;

5. If hot keys are not known, start with automatic splitting and tune the thresholds upward (e.g., double ysql_tablet_split_size_mb) only after confirming that split frequency does not cause excessive overhead.
6. After applying splits, verify leader distribution:

   yb-admin --master_addresses <master> list_tablets <table_id> | grep leader_count

Missing diagnostic detail

To give a definitive recommendation, we need to know the observed growth rate (size per minute) of the tablet that holds the hot tenant’s writes. If that rate is known, we can compare it to the split threshold and decide whether automatic splitting will engage within your latency tolerance.

Verification commands (safe, scoped)

# Check split flags
yb-admin --master_addresses 127.0.0.1:7100 get_flag ysql_tablet_split_size_mb
yb-admin --master_addresses 127.0.0.1:7100 get_flag ysql_tablet_split_size_threshold_mb

# Observe tablet sizes for a specific table
SELECT tablet_id, start_key, end_key, approximate_size_mb
FROM ysql_tablet_history
WHERE table_name = 'orders'
ORDER BY approximate_size_mb DESC
LIMIT 10;

# List leader distribution after a split
yb-admin --master_addresses 127.0.0.1:7100 list_tablets <table_id> | awk '/leader/ {print $0}'

Diagram

• Hot tenant writes → single tablet (pre‑split)
• SPLIT AT VALUES on hot tenant → multiple tablets, leaders spread
• Online re‑split → leader reassignment + background compaction