RSS keeps growing after sync reaches tip while CPU is near-idle

db-sync RSS grows unbounded after catch-up; PostgreSQL 18 tuning fixes preprod but not preview (13.7.1.0 / node 11.0.1)

## Summary
After db-sync catches up, its process RSS keeps climbing while following the tip
instead of plateauing. Upgrading and tuning PostgreSQL fixed this on preprod but
preview still reproduces it. Metric plotted is the cardano-db-sync process RSS,
x-axis is wall-clock time.

## Environment
- cardano-db-sync 13.7.1.0, cardano-node 11.0.1, networks preprod + preview
- RSS/CPU sampled ~every 5s

## Before PostgreSQL change
PostgreSQL with stock defaults except two knobs:
- maintenance_work_mem = 2 GB
- max_parallel_maintenance_workers = 8
(everything else default: shared_buffers 128 MB, work_mem 4 MB, effective_cache_size 4 GB, etc.)

Behavior - both networks: RSS grew ~+3.5 GB after CPU dropped to near-idle
(3566 -> 7109 MB), both converging on the same ~7.1 GB GHC RTS heap ceiling,
then partial reclaim.

### Plot Files

[before-postgres-update-preview-13.7.1.0-node-11.0.1_time.html](https://github.com/user-attachments/files/29374797/before-postgres-update-preview-13.7.1.0-node-11.0.1_time.html)

[before-postgres-update-preprod-13.7.1.0-node-11.0.1_time.html](https://github.com/user-attachments/files/29374814/before-postgres-update-preprod-13.7.1.0-node-11.0.1_time.html)

## After PostgreSQL change
Upgraded to PostgreSQL 18.4 and tuned:
- shared_buffers 128 MB -> 10 GB
- effective_cache_size 4 GB -> 24 GB
- work_mem 4 MB -> 15 MB
- maintenance_work_mem 2 GB (unchanged)
- max_parallel_maintenance_workers 8 -> 4
- max_parallel_workers / max_worker_processes -> 12
- max_wal_size -> 4 GB, checkpoint_completion_target 0.9, effective_io_concurrency 200

Impact:
| Network | After CPU drops to idle | Result |
|---|---|---|
| preprod | RSS flat at ~4772 MB, CPU avg 0.2%, zero work bursts | FIXED |
| preview | RSS climbs 3678 -> 7489 MB (+3.8 GB) over ~2.25h, then settles ~5 GB | STILL REPRODUCES |

Note: the tuning actually *lowered* max_parallel_maintenance_workers (8 -> 4), so the
preprod fix is driven by the large shared_buffers / effective_cache_size / work_mem
increases improving ingest throughput, not by maintenance parallelism.

On preview, CPU is mostly ~0% but shows regular 20-44% bursts every ~5.4 min
(still doing periodic work at the tip); RSS rises monotonically (~28 MB/min) with
two large step jumps (+1648 MB, +1260 MB).

### Plot Files

[after-postgres-update-preview_13.7.1.0-node-11.0.1_cpu_ram_by_time.html](https://github.com/user-attachments/files/29374821/after-postgres-update-preview_13.7.1.0-node-11.0.1_cpu_ram_by_time.html)

[after-postgres-update-preprod_13.7.1.0-node-11.0.1_cpu_ram_by_time.html](https://github.com/user-attachments/files/29374826/after-postgres-update-preprod_13.7.1.0-node-11.0.1_cpu_ram_by_time.html)

## Interpretation
RSS growth is in db-sync's GHC heap. PostgreSQL throughput acts as the trigger via
ingest backpressure: faster ingest (preprod, tuned) keeps the heap bounded; preview
still accumulates. The ~7 GB ceiling and delayed reclaim are GHC RTS behavior, not a
PG or GHC bug. Root cause looks like per-cycle retention while following the tip
(lazy thunk buildup and/or ledger-state snapshot serialization).

## Impact
On smaller-RAM hosts, RSS can keep growing for hours after the expensive part of
sync is over, risking OOM before a major GC reclaims it.

## Suggested next steps
- Rerun preview with RTS eager-return/idle-GC flags to separate RTS retention from a true leak.
- Correlate the RSS step jumps with --state-dir snapshot write times.
- Heap-profile (+RTS -hT) during the growth window.

Start by rerunning preview with RTS eager-return and idle-GC flags, then correlate RSS step jumps with --state-dir snapshot write times. Capture a +RTS -hT heap profile during the growth window; done means distinguishing RTS retention from a true leak and identifying whether per-cycle retention persists while following the tip.

Written by the indexing model from the issue text.