2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
    • Support
      • 24/7 PostgreSQL Support
      • Developer Support
    • DBA Services
      • Remote DBA
      • Database Monitoring
    • Consulting Services
      • Health Check
      • Performance Tuning
      • Database Security Audit
      • PostgreSQL Upgrade
    • Migration Services
      • Migrate to PostgreSQL
      • Migration Assessment
  • Products
    • Postgres-BDR ®
    • PostgreSQL High Availability
    • Kubernetes Operators for BDR & PostgreSQL
    • Managed PostgreSQL in the Cloud
    • Installers
      • Postgres Installer
      • 2UDA
    • 2ndQPostgres
    • pglogical
    • Barman
    • repmgr
    • OmniDB
    • SQL Firewall
    • Postgres-XL
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / Webinars3 / Webinar: Tuple Freezing & Transaction Wrap around Through Pictures...
Tuple Freezing & Transaction Wraparound Through Pictures
Bilal Ibrar

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

May 27, 2020/0 Comments/in Webinars /by Bilal Ibrar

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].

Tags: mvcc, PostgreSQL, tuple, webinars
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
How to use Logistic Regression Machine Learning model with 2UDA – PostgreSQL... How to use Logistic Regression Machine Learning model with 2UDA – PostgreSQL and Orange (Part 5) Webinar: Cloud Native BDR and PostgreSQL [Follow Up] Webinar: Cloud Native BDR and PostgreSQL [Follow Up]
Scroll to top
×