Avoid Vacuum (to prevent wraparound) on read-only databases

Question

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;

Edit

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.

Answer 1

It is really the frozen map (introduced in version 9.6), not the related visibility map, which will help you here. The table counters will still increase and the tables will still get vacuumed for wraparound, but that vacuuming will now be very fast as it will just skip to the end and be done.

For your weekly maintenance task, you could do a VACUUM FREEZE, not ordinary VACUUM, for the to-be-retired database. That way they won't need to be visited again in the future, except through the perfunctory vacuum facilitated by the frozen map.

If you need to buy yourself some time for that change to propagate through the system, you could increase autovacuum_freeze_max_age. That should make the autovac a bit more docile while you slowly do VACUUM FREEZE on those old databases at off-peak times of your choosing. But don't just increase autovacuum_freeze_max_age and then forget about it, or when the new age is met you will be in an even deeper hole than you are now.

If you need to figure out which databases still need manual freezing, you can use pg_visibility_map_summary.

Answer 2

from your description , you actually need this process or else your old data will effectively become future data that you can’t access (wraparound)

You have 2 options here:

1. Run the vacuum yourself in non-peak hours to better control load
2. Reduce the number of transactions (E.g. if you load data hourly you’ll have 24 transactions, each one loading just 41k persons )

Answer 3

Sorry, I'd feel better putting this in the comments section but I don't have the reps yet for this site.

Reading the docs, it seems to me that in your case multixacts can be prompting Postgresql to initiate the vacuum process. Please see this docs link and search the text for multixacts:

https://www.postgresql.org/docs/9.4/static/routine-vacuuming.html

Perhaps one workaround is to put files in separate tables... maybe one table for each day?