If you are interested to hear how this turns out, now would be a good time to like and subscribe…
> select age(datfrozenxid) from pg_database where datname <> 'postgres' and not datistemplate;
age
------------
1906258915
1908160175
(2 rows)
Conversation
Well, at the moment things actually look good (knocks wood), so the ending is going to either feel anti-climatic or it’s going to go full on wtfbbq. I really hope the former, it’s been a long week.
1
1
Ugh… this took some weird turns and may have stepped into NDA territory. That said… this was a pain because it involves Postgres 10, a 10TB+ table (~5TB heap, 20 indexes for another 5TB+), and xid exhaustion every 10 days; vacuum has been tenuous on this system for a long time.
7
2
Replying to
Of the 20 indexes, how many used UUIDs? Tend to see big slowdowns with these indexes in larger tables. There is no natural locality, and so VACUUM often needs to dirty almost every page in the index.
Replying to
One unique index on a uuid column, but we have many multi-column indexes and a few on weird (long) text identifiers where the first 8 columns fall into 1 of 4 patterns... just the worst.
1
Replying to
It's probably not much use to you now, but fyi all of those specific patterns are helped by enhancements in Postgres 12, 13, and 14. Before 12, low cardinality indexes stored large groups of duplicates in more-or-less random order, increasing write amplification during VACUUM.