Clearing wraparound risk without taking the database offline

Overview

An insurance platform hit warnings that the database was within a few tens of millions of transactions of wraparound protection kicking in, which would have forced the cluster into read-only mode. AceMQ engaged under the emergency SLA and worked the problem alongside the customer's platform team.

Challenge

Autovacuum on the largest tables was being cancelled repeatedly and restarted from scratch, so the freeze horizon never advanced. Long-running analytics sessions and an idle-in-transaction connection pool were pinning the xmin horizon, meaning even successful vacuum runs could not reclaim tuples. Cost-based delay settings inherited from a much smaller deployment throttled autovacuum to a fraction of the available I/O.

Environment

PostgreSQL primary with streaming replicas on on-premises hardware, supporting policy administration and claims workloads.

Approach

The immediate priority was advancing the freeze horizon on the highest-age tables before the cluster protected itself. AceMQ ran targeted manual vacuum freeze operations against those tables with tuned worker settings, while simultaneously removing the blockers preventing autovacuum from completing so the fix would hold after the incident.

Solution

• Ranked tables by age of the frozen transaction ID and ran targeted vacuum freeze against the worst offenders first
• Terminated the idle-in-transaction sessions and long-running snapshots pinning the xmin horizon
• Retuned autovacuum cost delay, cost limit, and worker count against the actual I/O capacity of the hardware
• Set per-table autovacuum thresholds on the large tables instead of relying on cluster-wide percentage scaling
• Added statement timeout and idle-in-transaction session timeout to prevent recurrence from the application tier
• Instrumented monitoring on table age, dead tuple counts, and autovacuum cancellation events

Outcome

Technologies