We use a Postgresql 10 database server to import nearly 1 million user files every day. To keep the databases easier to handle (backup, maintenance) we create a new database every week. The database from the previous weeks are vacuumed, backuped and no further write operations are performed on them. In total we have 17 databases with > 7 million imported files (500GB) in each. That are 120 million files which leads to 240 million transactions in total. (To import one file we need two transactions.)
Some days ago, we detected that Postgresql starts the autovacuum process with the ‘prevent wraparound’ option on the oldest databases. Postgresql starts only those vacuums and not the occasional vacuums (clean-up and maintenance tasks) to analyze the current week's database. Due to the additional IO load, we have performance problems with select statements on the current database.
Is there a way to prevent this behavior on the old databases? We tried a vacuum freeze to reset the relevant counters but they start growing immediately when our import is running.
We fetch these counters with the following query:
SELECT oid::regclass::text AS table, --relfrozenxid, age(relfrozenxid) AS xid_age, mxid_age(relminmxid) AS mxid_age, least( (SELECT setting::int FROM pg_settings WHERE name = 'autovacuum_freeze_max_age') - age(relfrozenxid), (SELECT setting::int FROM pg_settings WHERE name = 'autovacuum_multixact_freeze_max_age') - mxid_age(relminmxid) ) AS tx_before_wraparound_vacuum, pg_size_pretty(pg_total_relation_size(oid)) AS size, pg_stat_get_last_autovacuum_time(oid) AS last_autovacuum FROM pg_class WHERE relfrozenxid != 0 AND oid > 16384 ORDER BY tx_before_wraparound_vacuum;
After further investigation, we have read about visibility maps in the 10.x documentation. A freeze run does not touch the XID but set a flag in this map for indication. So we hope that any old database, where the wraparound or freeze vacuum was running, is never touched again by any autovacuum in the future.