Hacker News new | comments | ask | show | jobs | submit login

If you're looking for a good series of blog posts about xid wraparound in Postgres check out these posts by Josh Berkus:

http://www.databasesoup.com/2012/09/freezing-your-tuples-off...

http://www.databasesoup.com/2012/10/freezing-your-tuples-off...

http://www.databasesoup.com/2012/12/freezing-your-tuples-off...

And this more recent one by Robert Haas:

https://rhaas.blogspot.com/2018/01/the-state-of-vacuum.html

As Josh states at the end of the third post the current best practices for dealing with this are really workarounds and as Robert states it requires monitoring and management. Postgres is an amazing piece of software and managing this is doable but IMHO this is one of Postgres' worst warts. It would be awesome if someone could donate some funding to improve this.




My admittedly very superficial understanding of this issue is that the most common way to run into the xid wraparound problem is tuning the autovacuum in the wrong direction. So you notice that vacuum is taking up a lot of your servers resources, and decrease the frequency. Or you notice that it can't really keep up, but don't tune it to be more aggressive or provide enough resources for it to do its job. Or you don't monitor this at all, which is a pretty bad idea if you do billions of transactions (with less you can't really hit this issue).

This is also a problem that gets far harder to fix once you've run into it. If you have sufficient transaction volume to potentially hit this, you need to monitor autovacuum and make adjustments early before you get close to the wraparound. If you don't, you suddenly have to perform all the vacuum work at once, blocking that table until it's done.




Applications are open for YC Summer 2019

Guidelines | FAQ | Support | API | Security | Lists | Bookmarklet | Legal | Apply to YC | Contact

Search: