|
Hi, I have a fairly large database (several TBs) where auto vacuum is enabled. My focus is to keep the datfrozenxid reasonably low and avoid manual vacuum. When I run following query, I see the percentage creeping
up every day. Right after running vacuum manually on tables with large relfrozenxid the following query would return 7% on pgprd1 database but after 5 months it increased to 40%. So, eventually I am afraid I have to vacuum the tables manually which has its
own problems like creating massive WALs in a short time etc. I would like to avoid manual vacuuming for this reason. SELECT freez, txns, ROUND(100*(txns/freez::float)) AS perc, datname FROM (SELECT foo.freez::int, age(datfrozenxid) AS txns, datname FROM pg_database d JOIN (SELECT setting AS freez FROM pg_settings WHERE name = 'autovacuum_freeze_max_age') AS foo ON (true) WHERE d.datallowconn) AS foo2 ORDER BY 3 DESC, 4 ASC; 1500000000 | 599260139 | 40 | pgprd1 1500000000 | 50138249 | 3 | postgres 1500000000 | 50138249 | 3 | template1 Auto vacuum is working as I can see from logs it is repeatedly vacuuming same tables, but I don’t see that as a problem because those are the tables that get updated/inserted continuously. Following are the settings I have. I am wondering if there is a way autovacuum can keep the above “datfrozenxid” low and not keep increasing. Thank you ahead time for reading my post. postgres=# select name, setting, unit from pg_settings where name like '%autovacuum%'; name | setting | unit ---------------------------------------+------------+------ autovacuum | on | autovacuum_analyze_scale_factor | 0.1 | autovacuum_analyze_threshold | 5000 | autovacuum_freeze_max_age | 1500000000 | autovacuum_max_workers | 5 | autovacuum_multixact_freeze_max_age | 400000000 | autovacuum_naptime | 60 | s autovacuum_vacuum_cost_delay | 2 | ms autovacuum_vacuum_cost_limit | -1 | autovacuum_vacuum_insert_scale_factor | 0 | autovacuum_vacuum_insert_threshold | 5000 | autovacuum_vacuum_scale_factor | 0 | autovacuum_vacuum_threshold | 5000 | autovacuum_work_mem | -1 | kB log_autovacuum_min_duration | 0 | ms (15 rows) |
