db: add low-priority compaction to consolidate files at lower levels

[xxmplus]

Summary

Pebble has no mechanism to compact L1-L6 levels based on file count. Compaction at these levels triggers only when level_size / level_target_size >= 1.0. This allows unbounded accumulation of small SST files when the total size stays below the level target.

L0 has a file-count threshold (L0CompactionFileThreshold = 500, compaction_picker.go:1125-1144) but no equivalent exists for lower levels.

Observed Behavior

A customer running a short-TTL workload (600s MVCC GC) after a 15 TiB bulk ingestion accumulated 443K SST files on a single store:

level    size | tables   size
  L4     22GB |   191K   22GB    (avg 115 KiB/file)
  L5     59GB |   143K   59GB    (avg 413 KiB/file)
  L6    1.5TB |    43K  1.5TB    (avg 35 MiB/file)

With dynamic level sizing (10x multiplier, L6 at 1.5 TiB), L5 target is ~150 GiB. L5 fill factor is 59/150 = 0.39, so no compaction is triggered despite 143K files.

A healthy node in the same cluster has 43K total SSTs with comparable data size.

Proposal

Add a low-priority compaction type that consolidates files when a level's file count is disproportionately high relative to its total size. This should run at low priority to avoid competing with regular compactions that address LSM shape.

Possible approaches:

• Add a file-count-based score component for L1-L6 (similar to L0's L0CompactionFileThreshold)
• Schedule a periodic consolidation pass that merges small files within a level
• Trigger consolidation when average file size at a level falls below some fraction of the target file size for that level

References

• support#3625
• L0 file-count heuristic: compaction_picker.go:1125-1144
• Level score calculation: compaction_picker.go:989-1106
• Related: pebble#5005 (space amplification heuristics)

Jira issue: PEBBLE-1448