Webinar: Tuple Freezing & Transaction Wrap around Through Pictures [Follow Up]

The Postgres Implementation of Multi-Version Concurrency Control has many technical advantages and strengths. However, if you are deploying a large database, with a heavy write update, insert and delete workload, the concepts around Transaction wrap around are very important to understand — so proper design, monitoring, and administration decisions can be made.

To explore this topic further, 2ndQuadrant arranged a live webinar, “Tuple Freezing & Transaction Wrap around Through Pictures”, hosted jointly by Tom Kincaid and Andrew Dunstan.

Through a series of diagrams, pictures, and animations, this webinar gave an overview of the Postgres MVCC architecture. Using the same approach, the hosts went into technical details around the meaning and dangers associated with Transaction wrap around and the role Tuple Freezing plays in avoiding serious outage events.

This webinar concluded with recommendations on how to properly tune monitor and design for the issue associated with Transaction wraparound and Tuple Freezing.

Those who weren’t able to attend the live webinar can now view the recording here.

Due to limited time, some of the questions were not answered during the live webinar, so answers by the host are provided below:

Question: If the vacuum_freeze_min_age is newer than the oldest running transaction in the system, can “vacuum freeze” cause that newer row to become visible to that oldest running transaction and thereby violate MVCC semantics?

Answer: Postgres doesn’t ever freeze a tuple younger than the oldest live txnid, so the problem simply shouldn’t arise.

Question: Can you share a proper query for checking “which tables should be getting autovacuumed right now”?

Answer: This is a fairly in-depth topic a bit beyond the scope of this presentation. However, I would suggest the following resources:

Webinar: MVCC and Vacuum Basics in PostgreSQL 

Blog: Autovacuum Tuning Basics

Webinar: Postgres Vacuuming Through Pictures

Question: Any opinion on zheap?

Answer: It will be a great addition to the world of Postgres. I am not sure what the current ETA is though.

Question: I have noticed that the Postgres catalog tables are having the age higher than the user tables. How to tackle this?

Answer: If you are concerned about the age on these tables becoming too old, you could perform nightly vacuum freeze operations on these tables.

Question: Are the freezing mean tuples rewritten (to flip bits and incurs new dead tuples) or is coupling with vacuuming meant to prevent that?

Answer: Freezing only sets the frozen bit on existing tuples. It does not generate any new dead tuples, nor create new live tuples. Unlike Insert, and update operations, it just alters existing tuples in place.

Question: As you set your autovacuum settings progressively more aggressive, do you reach a point where the cost of vacuuming drops because it is vacuuming pages that are still in memory?  What does the graph of vacuum resource consumption (Y axis) vs. autovacuum aggressiveness (X axis) look like?

Answer: This is a good question but the answer is really workload, configuration and hardware-dependent, unfortunately. Things that come into play is how much memory is on your system and how large has shared_buffers been tuned to be. Please also have a look at the documentation for postgresql.conf settings, vacuum_cost_page_hit and vacuum_cost_page_miss. Very few applications can fit all their non-frozen pages in memory, which means freezing will almost always involve some page eviction. The larger the database and especially the larger the tables, the bigger the impact that freezing can have in almost all cases.

Question: Can Analyze can be used in any way to access deleted rows?

Answer: Perfectly understandable question. The task of analyzing and regenerating table planner statistics is accomplished by the vacuum process. However, it’s entirely independent of the vacuum process that accesses rows that have been deleted. So the answer to your question is no, Analyse only looks at live rows.

Question: Can Autovacuum prevent wrap around, or only manual vacuum?

Answer: Autovacuum can prevent wrap around for nearly all workloads. However, xid and mutlixid consumption need to be monitored to ensure it is keeping up.

Question: When you run a vacuum for a partition table, is it better to run the vacuum against each single partition?

Answer: Generally speaking, you are better off vacuuming each partition. Especially for date-based partitions that not going to be updated frequently. Vacuum them, freeze them and you may not worry about them again. I imagine there are cases where it is better to vacuum the entire table but I can’t think of any at this point. Autovacuum processes each partition separately.

Question: With ALTER table changing ALTER column type from numeric(8) to numeric(10,2), will all records with re-written?

Answer: This particular transformation does not cause the table to be rewritten. If the table is rewritten because of this type of operation, the resulting table is similar to what you get with VACUUM FULL, i.e all the dead tuples are gone, and all the tuples have the xmin of the ALTER command. None will be frozen.

To be the first to know about upcoming PostgreSQL webinars by 2ndQuadrant, visit our Webinars page.

For any questions, comments, or feedback, please visit our website or send an email to [email protected].