Transaction ID wraparound | Postgres.FM 027 |

Michael and Nikolay discuss transaction ID wraparound - what it is, and some ideas to minimise the risk of it ever happening to you!
Here are links to a few things we mentioned:
* The Internals of PostgreSQL chapter 5 (book by Hironobu SUZUKI) www.interdb.jp/pg/pgsql05.html
* PostgreSQL 14 internals chapter 7 (book by Egor Rogov) edu.postgrespro.com/postgresq...
* Transaction ID Wraparound (blog post from Sentry) blog.sentry.io/2015/07/23/tra...
* What We Learned from the Recent Mandrill Outage (blog post from Mailchimp) mailchimp.com/en-gb/what-we-l...
* How to simulate transaction ID wraparound (blog post by Prashant Dixit) fatdba.com/2021/07/20/how-to-...
* Add 64-bit XIDs into PostgreSQL 16 (commitfest entry) commitfest.postgresql.org/41/...
* Partitioning (docs) www.postgresql.org/docs/curre...
* Consider using VACUUM’s INDEX_CLEANUP option in an emergency (tweet by Peter Geoghan) / 1350259956117692425
* Add wraparound failsafe to VACUUM (commit) github.com/postgres/postgres/...
* Do you vacuum everyday? (talk by Hannu Krosing) • Hannu Krosing. Do you ...
* Multixacts wraparound monitoring (mailing list thread) www.postgresql.org/message-id...
* Subtransactions Considered Harmful (blog post by Nikolay) postgres.ai/blog/20210831-pos...
* Buffer management in PostgreSQL (talk by Alexander Korotkov) • Buffer management in P...
* OrioleDB github.com/orioledb/orioledb
* pageinspect www.postgresql.org/docs/curre...
* pg_hexedit github.com/petergeoghegan/pg_...
* pg_visibility www.postgresql.org/docs/curre...
* Visibility Map (docs) www.postgresql.org/docs/curre...
-------------
What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / samokhvalov and @michristofides / michristofides
~~~
Postgres FM is brought to you by:
- Nikolay Samokhvalov, founder of Postgres.ai
- Michael Christofides, founder of pgMustard
~~~
This is the uncut version, with video. Check out Postgres.fm to find audio-only podcast episodes, edited for convenient listening.

[@RU-qv3jl]

I mean I think that the benefits of partitioning are obvious. I also think that there are a lot of people who don‘t know internals and won‘t think about it. I also think that with partitioning it is worth cautioning not to go too far. By default the planner will only re-order, I think, 8 tables or something like that? So too many partitions can lead to worse plans as you run into the genetic optimiser more quickly right? I think that would also be worth discussing (Says me just part way through the episode) :)
Another really nice chat but the way, thanks. I always like hearing your thoughts.

[@AmineTengilimoglu]

Thank you for this useful session. I think the continuation of this session should be multixactID subtransaction and idle in transaction helix.

[@michristofides]

Thank you! Added to the list :)

[@santoshmishra-my2gc]

Thank you very much for this.Wishing you all very Happy new year 💐🎉

[@sid4201]

Thank you for this informative content. I am using partitions and still have this wraparound problem now, since partition is based on date, I am thinking if dropping older partitions can help.

[@NikolaySamokhvalov]

you need to check why autovacuum doesn't process old tuples to prevent wraparound

[@santoshmishra-my2gc]

Can I request to please have session on performance degradation after database upgrade . Important checks example like Analyze, Reindex

[@michristofides]

Thanks, added to the list!