การป้องกันการวนรอบของรหัสธุรกรรม

PostgreSQL labels every row version with the transaction ID (XID) that created it. XIDs are 32-bit, so there are only about 4 billion of them. They are compared in a circular fashion, and if old rows are not frozen, the comparison can break.

If XIDs wrap before old rows are frozen, recent rows could appear to be in the future and become invisible. To protect your data, PostgreSQL will refuse new writes before that happens.

VACUUM marks very old, still-visible rows as frozen, meaning they are visible to all transactions forever. Frozen rows no longer depend on their original XID, so they are safe from wraparound.

This controls how old a row's XID must be before VACUUM freezes it. Lower values freeze sooner; higher values defer work but increase wraparound risk.

When a table's oldest XID exceeds autovacuum_freeze_max_age, autovacuum triggers an anti-wraparound vacuum automatically, even if the table is otherwise idle.

Check how close each database is to wraparound by reading the age of its oldest unfrozen XID.

Drill down to find the specific tables driving the age up. The one with the highest age is the next anti-wraparound target.

The server log warns as you approach the limit:

• database must be vacuumed within N transactions
• Eventually the database goes read-only to protect itself

Never ignore these messages.

If a table is far behind, run a vacuum that freezes everything immediately rather than waiting for autovacuum.

To stay safe:

• Keep autovacuum enabled and well-tuned
• Avoid extremely long-running transactions that pin the oldest XID
• Monitor age(datfrozenxid) with alerts
• Investigate any table that resists freezing

You can roughly gauge headroom by comparing the oldest XID age against the ~2 billion safe limit. If a database is consistently climbing toward it, investigate what blocks freezing before alerts fire.

You learned wraparound prevention:

• 32-bit XIDs can wrap after ~4 billion transactions
• VACUUM freezes old rows to make them permanently visible
• Autovacuum runs anti-wraparound vacuums automatically
• Monitor age(datfrozenxid) at database and table level
• Avoid long transactions and heed the log warnings