Clearing wraparound risk without taking the database offline

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.

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

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.

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