64-bit XIDs again - Mailing list pgsql-hackers

Hackers,

I know there were already couple of threads about 64bit XIDs.

I read them carefully, but I didn't find all the arguments for 64bit XIDs mentioned. That's why I'd like to raise this subject again.

Now hardware capabilities are much higher than when Postgres was designed. In the modern PostgreSQL scalability tests it's typical to achieve 400 000 - 500 000 tps with pgbench. With such tps it takes only few minutes to achieve default autovacuum_freeze_max_age = 200 millions. 

Notion of wraparound is evolutioning during the time. Initially it was something that almost never happens. Then it becomes something that could happen rarely, and we should be ready to it (freeze tuples in advance). Now, it becomes quite frequent periodic event for high load database. DB admins should take into account its performance impact.

Typical scenario that I've faced in real life was so. Database is divided into operative and archive parts. Operative part is small (dozens of gigabytes) and it serves most of transactions. Archive part is relatively large (some terabytes) and it serves rare selects and bulk inserts. Autovacuum work very active for operative part and very lazy for archive part (as it's expected). System works well until one day age of archive tables exceeds autovacuum_freeze_max_age. Then all autovacuum workers starts to do "autovacuum to prevent wraparound" on archive tables. If even system IO survive this, operative tables get bloated because all autovacuum workers are busy with archive tables. In such situation I typically advise to increase autovacuum_freeze_max_age and run vacuum freeze manually when system have enough of free resources.