keep in mind, if you are running postgresql on ads, this video may not apply.
Also note at around the 28 / 30 minute mark of the YouTube video above the author discusses How to Manage Transaction ID wrap around – with AUTOVACUUM_FREEZE_MAX_AGE
The video above only applies to self-managed postgresql (for example, on-prem, or EC2 based self-managed).
If auto-vacuum is not removing dead-tuples / bloat from one or more tables maybe some other process is reading from that table and not committing or holding the connection open.
In that case you could look for that by modifying this query to include the table that is not being cleaned up.
SELECT pid, user, pg_stat_activity.query_start, now() – pg_stat_activity.query_start AS query_time, query, state, wait_event_type, wait_eventFROM pg_stat_activityWHERE (now() – pg_stat_activity.query_start) > interval ‘5 minutes’;
So why vacuum at all?
Well if you know Oracle as a DBA you know that Oracle holds records that are read by current transactions and updated by other processes in a space called UNDO. PostgreSQL does not have undo, so it just leaves on information in place, and creates new blocks for updates.
So in PostgreSQL vernacular Once the old block is no longer needed, that block becomes “bloat” in the table, or also referred to a “dead tuples”. Auto-vacuum or manual vacuum cleans this up.
If you do not vacuum, your entire data base can be “bloated” unnecessary “dead” records and that can only slow things down.
Here is another PDF on the subject of monitoring vacuum operations:
And this one on the subject of Managing Freezing in PostgreSQL
https://www.2ndquadrant.com/en/blog/managing-freezing/
And this is an excellent article on monitoring and avoiding the condition
https://www.crunchydata.com/blog/managing-transaction-id-wraparound-in-postgresql