The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/18/routine-vacuuming.html Description:
Hello The current doc says (Routine Vacuuming for all available versions) : "The sole disadvantage of increasing autovacuum_freeze_max_age (and vacuum_freeze_table_age along with it) is that the pg_xact and pg_commit_ts subdirectories of the database cluster will take more space, because it must store the commit status and (if track_commit_timestamp is enabled) timestamp of all transactions back to the autovacuum_freeze_max_age horizon. The commit status uses two bits per transaction, so if autovacuum_freeze_max_age is set to its maximum allowed value of two billion, pg_xact can be expected to grow to about half a gigabyte and pg_commit_ts to about 20GB. If this is trivial compared to your total database size, setting autovacuum_freeze_max_age to its maximum allowed value is recommended. Otherwise, set it depending on what you are willing to allow for pg_xact and pg_commit_ts storage. (The default, 200 million transactions, translates to about 50MB of pg_xact storage and about 2GB of pg_commit_ts storage.)" Based on my experience, the growing size of pg_xact and pg_commit_ts subdirectories is not the "sole" disadvantage of increasing autovacuum_freeze_max_age. The higher the value is, the later autovacuum will be triggered to prevent wraparound. Autovacuum launches workers to do the work primarily for 2 reasons : 1. If the no. for dead tuples is greater than the calculated threshold, and 2. If the no. of txids consumed by the database cluster have reached/surpassed autovacuum_freeze_max_age. If we increase the autovacuum_freeze_max_age to 2 billion (as recommended above), to trigger on the second condition autovacuum might be too late already. This is a more important point in my opinion than the one in the docs. My suggestion would be : 1. To not recommend setting autovacuum_freeze_max_age to 2 billion, as it contradicts with the paragraph already mentioned in the docs : "The maximum time that a table can go unvacuumed is two billion transactions minus the vacuum_freeze_min_age value at the time of the last aggressive vacuum. If it were to go unvacuumed for longer than that, data loss could result. To ensure that this does not happen, autovacuum is invoked on any table that might contain unfrozen rows with XIDs older than the age specified by the configuration parameter autovacuum_freeze_max_age. (This will happen even if autovacuum is disabled.)" 2. Remove the word "sole", as its not true (already explained above why). If my understanding is flawed, I would love to hear your feedback. Thanks and Regards Divya Sharma
