You own a busy transactional database that also serves hour-long analytical reports and feeds a downstream change-data-capture consumer. How would you set a policy for how long old row versions are retained, and keep cleanup from either falling behind or starving the workload?

## Frame it as one shared dial Version retention is not a per-query setting. The engine keeps everything newer than the oldest snapshot in use, so the depth of history is determined by whichever consumer is furthest behind. In the described system three consumers can hold it: an hour-long report, a change-data-capture slot whose consumer may stall, and (if replicas exist and report their oldest reader upstream) replica queries. Any of them, unbounded, makes the writer's storage unbounded. The first architectural move is to say out loud that this dial is a platform-level resource and that no individual client gets to set it. ## Choose the failure mode deliberately There are exactly two, and you are always picking one: - **Unbounded retention.** No reader is ever aborted for missing history. Bloat grows with the oldest reader; the eventual failure is disk exhaustion or a table too large to scan, and it hurts everything at once. - **Bounded retention.** History deeper than a chosen window may be reclaimed. Readers that outlive the window die with a targeted error, or a stalled slot is dropped. The failure is loud, attributable and confined. For a platform with an SLO on the transactional workload, bounded is almost always right: it converts a shared, unbounded, silent risk into a per-consumer, bounded, visible one. Write the chosen number down, for example "the writer retains at most 15 minutes of history", and make every long-reader design justify itself against it. ## Enforce mechanically, not by convention Policies that depend on developers keeping transactions short fail. Enforce with: - a maximum statement duration and a maximum transaction duration on the transactional role; - termination of sessions idle inside an open transaction (connection pools with autocommit off are the usual source); - a cap on bytes retained per replication or change-capture slot, so an abandoned consumer is broken rather than allowed to fill the primary; recreating a slot and re-snapshotting is a recoverable incident, a full disk on the writer is not; - an explicit decision on standby feedback: on means replica reports never get cancelled but the primary bloats; off means the primary is protected and long replica queries may be cancelled. Pick per replica according to what that replica is for; - an audit for orphaned two-phase prepared transactions, which pin history indefinitely and survive restarts. ## Size cleanup as a capacity problem Cleanup must reclaim at least as fast as the workload creates dead versions, averaged over the window you can tolerate. So measure both sides: dead versions generated per second (roughly, update plus delete rate, multiplied by the number of indexes for the index work) and the rate cleanup achieves on the hot relations. If the ratio is under one, you will bloat no matter how good the horizon policy is. Give cleanup enough I/O budget and parallelism to win, but not so much that it competes with the transactional workload at peak. The usual shape is a generous budget with per-table aggressiveness raised on the few hot relations, rather than a globally aggressive setting that taxes everything. Be alert to the **degradation spiral**: bloat means more pages to scan, which slows the cleanup pass, which lets bloat grow. Once you are in it, tuning is insufficient and you must reclaim space to get back to a stable regime. ## Isolate the competing workloads - **Analytics on its own replica**, with its own retention budget and feedback disabled toward the primary, so an hour-long report costs storage where it is cheap and cancellations where they are tolerable. - **Change capture treated as a monitored dependency**, not a fire-and-forget subscriber: alert on consumer lag and retained bytes long before the cap, with a documented runbook for dropping and re-seeding. - **Partition by time on the high-churn tables.** Expiry becomes a partition DROP, which produces zero dead versions and returns space instantly; cleanup work per partition stays bounded; and a bloated partition can be reorganised without touching the rest. - **Schema shape.** Keep hot-updated columns in narrow tables, avoid updating indexed columns unnecessarily so in-page update optimisations apply, and debounce counter-style writes. Every version not created is cleanup work not needed. ## Observability and the numbers you defend Four signals, alerted on trend rather than absolute value: age of the oldest open transaction (should be flat and small), retained bytes per slot, bloat ratio per hot relation, and cleanup lag or last-successful-pass age per relation. These map one-to-one onto the three consumers and the capacity side, so any page tells you immediately which lever to pull. ## The principal-level point The deliverable is not a set of parameter values; it is a written contract: how much history the platform funds, who is allowed to hold it, what breaks first when someone exceeds it, and who gets paged. Parameter tuning follows from that contract and will be re-derived every time hardware or workload changes.