Hot take: this otherwise excellent postmortem is too forgiving of PostgreSQL; transaction ID wraparound autovacuum is a nasty failure mode that too many learn about only when their own production systems are capsized by it!
Conversation
Replying to
I agree, but at the same time it's worth noting that the ultimate cause is usually how autovacuum interacts with some other thing that has a conflicting heavyweight lock (e.g. automated TRUNCATE or DROP TRIGGER). That happened during the similar Joyent Manta incident.
1
6
Also worth noting: "Avoid re-vacuuming pages containing only frozen tuples" feature in 9.6 can significantly ameliorate performance drop from anti-wraparound autovacuum. It would be nice to know the Postgres version in use on affected Mailchimp DB.
. o O ( The true 64-bit xid support we're about to get is intended for zheap, possibly gist (?) and other AMs which don't to have to freeze, but could probably also work for [traditional] heap if we could find space in the page header for a reference epoch, or some such scheme )
1
2
GiST? Not sure I see how that relates to xid wraparound? But wasn't there a patch for page-level epochs?
1
Show replies
Nice! As a user I really appreciate all the community’s work on a bunch of vacuum improvements over the years. (Also very much appreciated your talk at pgOpen in SF last year!)
1
64bit xids would fix. Could be worth the trade-offs in 2019