[v23.3.x] cloud_storage: Implement "carryover" cache trimming mechanism

[vbotbuildovich]

Backport https://github.com/redpanda-data/redpanda/pull/18056 to branch v23.3.x. Note that this issue was created as a placeholder, since the original PR's commit(s) could not be automatically cherry-picked. Here are the commands to execute:

git checkout v23.3.x
git checkout -b vbotbuildovich/backport-18056-v23.3.x-113
git cherry-pick -x 57985b74ff30f1cb8a27f06a4cbb688b2201b569 940fcd4d802e1f3665243f681d58b323c9e27ece 5ece9d401917d929a8d5e986b6aabacfd54884c1 fdf34981fac71b011a3c47e3adf86006e6d9da09 125659467d743a3821516b975efe4724bdd24da0 e1c30bc6e18a08de9fb74d4331450488642b5b53 0f84fdbbbecd80221ee199de0850a00f16de0789 6b57e0c167eb4da482b5122f482f3135f070ab02 61a09b47099e2422b86c401e11f5e5c6cfba9157 9f1b51b6a7e3455f3bc1c25965f3d19bb657dada

git push origin vbotbuildovich/backport-18056-v23.3.x-113
gh pr create \
  --title "[v23.3.x] cloud_storage: Implement "carryover" cache trimming mechanism " \
  --base "v23.3.x" \
  --label "kind/backport" \
  --head "vbotbuildovich/backport-18056-v23.3.x-113" \
  --draft \
  --repo "redpanda-data/redpanda" \
  --milestone "v23.3.x-next" \
  --body "Backport of PR https://github.com/redpanda-data/redpanda/pull/18056 "

JIRA Link: [CORE-2695](https://redpandadata.atlassian.net/browse/CORE-2695)