db: holistically consider space amplification compaction heuristics

We have a number of ways in which we may accumulate space amplification.

- Uncompacted tombstones and the KV pairs they delete may coexist within the LSM.
- Virtualized sstables pin backing sstables, with unreferenced sstable data being wastage.
- Blob files may contain unreferenced value data that is wastage.

Depending on the source of the space amplification, we have a few ways of reducing it:

- Delete-only compactions can reclaim disk space quickly and at the highest compaction priority level.
- Regular compaction picking inflates file sizes by both tombstone deleted bytes estimates and a linearly scaled [inflation for virtual sstables](https://github.com/cockroachdb/pebble/blob/3b5564003c0127984029ebc26cb9a1b6ed015938/compaction_picker.go#L1234-L1264).
- Blob file rewrite compactions can elide unreferenced values in a blob file by rewriting the blob file.

But there are some deficiencies,

- Blob file rewrites cannot elide data that is referenced by a backing sstable but not actually reachable through any of the extant virtual sstables. (#4915)
- Compactions could be better about deciding when to naturally rewrite blob files if doing so would reclaim disk space. (#4763)
- With the exception of delete-only compactions, reclaiming disk space is performed at the lowest compaction priority to avoid risking LSM inversion. We've observed situations in which extremely large volumes of space amplification are possible as these low-priority compactions are never run. (cockroachlabs/support#3264)

Space amplification is a barrier to increasing node density. We should consider space amplification holistically and adjust our compaction picking and mechanics to ensure we reclaim disk space promptly and without impacting user workloads.

A motivating slack conversation: https://cockroachlabs.slack.com/archives/CAC6K3SLU/p1750862146318639

Jira issue: PEBBLE-526

Epic PEBBLE-1211

Start with compaction_picker.go, especially the linked inflation logic, and read issues #4915 and #4763 for the known blob-file limitations. Review the listed compaction mechanisms and supporting discussion before defining a coherent scope; done would mean space amplification is reclaimed promptly without impacting user workloads.

Written by the indexing model from the issue text.