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
  • Products
  • 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 / Shaun's PlanetPostgreSQL3 / PG Phriday: 10 Things Postgres Could Improve – Part 1
PG Phriday: 10 Things Postgres Could Improve – Part 1
Shaun Thomas

PG Phriday: 10 Things Postgres Could Improve – Part 1

June 12, 2020/3 Comments/in Shaun's PlanetPostgreSQL /by Shaun Thomas

Postgres is a database software engine with a lot to love, and certainly a non-zero amount to hate. Rather than approaching the topic from a glib and clickbaity angle, let’s see what’s really lurking in those dark recesses, and how we may potentially address them.

Though the original blog post calling out Postgres’ faults gave ten distinct bullet-points that need to be addressed, we can’t really justify ten posts as a response. Luckily, several of the critiques fall into distinct categories that we can address in turn. Thus, this series will come in four parts:

  1. Transaction ID Complications
  2. Pitfalls of Replication
  3. MVCC and Storage Concerns
  4. Engine Functionality

We will approach each of these topics in turn and attempt to give a fair assessment of how Postgres actually operates, what the drawbacks actually are, and if there are any workarounds possible until the underlying issue is fully resolved.

We hope you enjoy!

Transaction ID Complications

Postgres represents the transaction pointer as a 32-bit integer. I went over this in much more depth in my Around the World in Two Billion Transactions, article. Transaction wraparound is indeed a real issue, especially as database sizes and transaction volumes continue to increase.

From the perspective of a DBA or end-user, much of that article is not entirely reassuring, as it ultimately concludes with the message that, "Help is coming. Maybe." Maybe the new Postgres pluggable storage engines will alleviate transaction ID wraparound concerns in some capacity. Maybe adequate monitoring can stave off the worst risks.

Maybe. Eventually.

Multiple Attack Vectors

What compounds this even further, is that older all-visible tuples should be "frozen" so they no longer artificially hold back the valuable cyclical transaction ID pointer. This means every table page must be visited at least one subsequent future time to clear that value, incurring both a read and potentially a write on every page in the database. Is this viable on a 1TB database? How about a 500TB database?

Eventually, sure. Allow autovacuum to do its job. Tune it to be as greedy as storage performance metrics will permit without adversely impacting query performance. That’s possible, but requires expertise that is in limited supply without contacting a 3rd party consulting organization like 2ndQuadrant.

And the situation gets even more confounded by the resolution paths. Any long-running transaction as identified by pg_stat_statements could be holding the XID epoch artificially low, or preventing autovacuum from cleaning up critical pages. Additionally, enabling hot_standby_feedback on a replica can cause even SELECT queries to prevent tuple freezing on the Primary node.

Even more insidious is an issue few users even know to investigate: the presence of prepared transactions. If a transaction is prepared and subsequently abandoned without being cleaned up, it can also prevent the XID from advancing. These types of transactions persist across database restarts, and could lurk unseen for weeks or even months before anyone realizes there’s a problem, if they do at all. And the only way to tell this has happened is to consult the pg_prepared_xacts system catalog, which most users don’t even know exists.

While disabled by default thanks to max_prepared_transactions being set to 0 in new clusters for modern versions of Postgres, that wasn’t always the case. Older clusters that keep propagating old defaults through upgrades, or slightly more advanced users who enabled them explicitly may not know of the above risk. So while the risk here is rare and minimal, it’s one that should not be ignored. Readers of this blog may know, and Postgres subject matter experts of all description, but who else?

Saving Ourselves

So how can we mitigate the risk here until a more permanent fix is forthcoming?

Currently one of the best approaches to partially address this particular difficulty, is to make liberal use of partitions. Assume partitioning is done by date, and older data is unlikely to experience further modifications past a certain point. In that scenario, some partitions could be frozen in perpetuity since their data pages would be free of new MVCC entries. This means all new data would exist in a kind of rolling live segment of recent partitions that are likely a much smaller proportion of available data.

Doing this is an invasive modification to the database design, and possibly the app itself. It also requires a certain level of familiarity to properly deploy, and again, likely would be best done with oversight from a consulting firm or some other available expert.

We also need to fastidiously monitor Postgres transaction ID age, along with all of the previously discussed elements. These are essential:

  1. The age-adjusted XID of each database in the instance.
  2. The amount of idle transactions in connected sessions.
  3. The maximum observed time of the oldest idle transaction.
  4. The amount of prepared transactions.
  5. The maximum observed time of the oldest prepared transaction.
  6. All of the same checks on any physical replica, assuming hot_standby_feedback is enabled.

While we could supply queries for each of these, there are Postgres monitoring tools such as check_postgres.pl that already implement all of them.

Finally, never disable autovacuum. Further, the default settings are incredibly conservative with regard to resource consumption. Modern disks are rarely starved of IO throughput, and should make autovacuum much more aggressive. At the very least, this means increasing vacuum_cost_limit to 2000 or higher to allow background autovacuum workers to accrue longer run times between forced pauses.

Conclusion

Still, our countermeasures are comparatively few, and consist of monitoring and crude workarounds. The perceived problem itself is still present, and will likely remain so for several more years at minimum.

The fact of the matter is that users who encounter this without sufficient research will be caught completely off-guard. Even with adequate monitoring, addressing the situation isn’t always obvious or straightforward, and falls into an area rife with tripwires and buried in esoteric system catalogs. And in a worst case scenario, it’s still necessary to shut down the database so it can be frozen in an offline state.

The risk of corruption here is incredibly low due to all of the built-in safeguards, but that’s of little consolation to a company with an offline primary database. Whether by finally moving to a 64-bit XID tracking value, or through augmenting the Postgres storage engine to include something like zheap, this particular wart on the proverbial Postgres toad will continue to thwart unprepared adopters.

Tags: AutoVacuum, PG Phriday, transactions, XID
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
3 replies
  1. Michael Lewis
    Michael Lewis says:
    July 1, 2020 at 7:54 pm

    The bit about recommending vacuum_cost_limit be set to 2000 should perhaps read autovacuum_vacuum_cost_limit, or else recommend keeping the default of -1 there to continue to inherit the value. If someone has tweaked autovacuum_vacuum_cost_limit to set to something besides -1, and vacuum_cost_delay is still set to 0 as is default, then changing only vacuum_cost_limit will have zero impact on both auto and manual vacuum.

    Reply
    • Shaun Thomas
      Shaun Thomas says:
      July 10, 2020 at 6:44 pm

      True. I tend to operate from the perspective that leaving the default of autovacuum_vacuum_cost_limit at the default is the best policy, and to only tweak `vacuum_cost_limit` instead. That’s obviously not a requirement, or what may be currently deployed.

      Reply

Trackbacks & Pingbacks

  1. Shaun M. Thomas: PG Phriday: 10 Things Postgres Could Improve – Part 2 – Cloud Data Architect says:
    June 20, 2020 at 6:07 am

    […] week, we examined Postgres XID wraparound complications in greater depth to see exactly how easily they can surprise even prepared enterprises. But we also […]

    Reply

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 AdaBoost Machine Learning model with 2UDA – PostgreSQL and Orange... How to use AdaBoost Machine Learning model with 2UDA – PostgreSQL and Orange (Part 6) Webinar: PostgreSQL continuous backup and PITR with Barman [Follow Up] Webinar: PostgreSQL continuous backup and PITR with Barman [Follow Up]
Scroll to top
×