I have a PostgreSQL database with a lot of tables (currently 93k) and I'm seeing issues with transaction id age threatening wraparound, because autovacuum is not getting triggered for clean tables that haven't been written to in a long time.

For example, I have a table that I can tell hasn't been written to since '2018-09-19 10:30:43.625069+00' thanks to the only inserts to this table always setting an updated_at column (current datetime is '2018-10-04 23:58:25.545881+00'), has n_dead_tup = 0, and has age(relfrozenxid) > 10000000.

I have autovacuum_freeze_max_age = 10000000 so this should mean this table will get autovacuumed, but instead I see it get past this point and then autovacuum doesn't get triggered for the table (it's currently at age(relfrozenxid) > 70000000 and autovacuum still hasn't triggered.

I currently have autovacuum set to be maximally aggressive and yet this is still happening. Here are the autovacuum settings:

track_counts = on
autovacuum = on
autovacuum_vacuum_cost_delay = 0
autovacuum_vacuum_cost_limit = 10000
autovacuum_vacuum_scale_factor = 0
autovacuum_vacuum_threshold = 1
autovacuum_freeze_max_age = 10000000
vacuum_freeze_min_age = 1000000

(yes, I realize autovacuum_vacuum_cost_limit doesn't matter because autovacuum_vacuum_cost_delay is 0)

Currently I'm forced to periodically manually trigger vacuums for these tables to keep things from getting out of hand. This feels like a hack, though.

Is there a way to get autovacuum to vacuum these tables, or equivalently a reason why autovacuum is ignoring these tables?


In case anyone comes here wanting to see my script for dealing with this, here it is:

#!/bin/bash -e
while true; do
  sleep 60 #just in case there's nothing sleep so we don't run this too hard; at the start to prevent rapid process bouncing when postgres is offline
  psql -d warehouse -t -c "select nspname || '.' || relname from pg_class join pg_namespace on pg_namespace.oid = pg_class.relnamespace where age(relfrozenxid) > 100000000 and relfrozenxid != 0 order by age(relfrozenxid) desc;" | sed '/^$/d' | parallel -j10 -q bash -c 'echo -n "autovacuum {} to prevent wraparound: " ; psql -d warehouse -c "vacuum freeze {}"'
  echo -n "vacuum postgres database: "
  psql -d postgres -c "vacuum freeze" #just to be safe

You'll probably want to up superuser_reserved_connections by 10 or whatever number you specify to parallel using the -j flag to make sure you can still access the database if regular connections are exhausted without shutting this down.

  • That shouldn't be happening. What minor version are you using (select version()? Are there any ERRORs (or other messages) in the server log file that seems relevant to autovac? Has this been upgraded from previous versions using pg_upgrade, and if so what versions? Has it had a history of crashes or corruption? What is the age(datfrozenxid) from pg_databases for the relevant database(s)?
    – jjanes
    Oct 5 '18 at 13:55
  • Also, what is autovacuum_max_workers, and can you look into pg_stat_activity to see if it is attempting to do autovacuuming? (Also likely to show up in top or ps if you run them showing the full command line)
    – jjanes
    Oct 5 '18 at 13:59
  • One can also log all autovacuum activities by setting log_autovacuum_min_duration to 0.
    – dezso
    Oct 5 '18 at 14:06
  • so plenty of autovacuums are running, just not the ones i need to run. i have autovacuum_max_workers set to 24 (this is on an i3.metal so I have 72 cores, 512 GB RAM, and over 100k IOPS) and with the above settings I typically see 24 autovacuum processes running at all times. Oct 5 '18 at 17:17
  • version is PostgreSQL 10.5 (Ubuntu 10.5-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 64-bit and I've never run pg_upgrade on this cluster. no crashes or corruption. age(datfrozenxid) stays between 100 million and 200 million thanks to my script to aggressively run vacuums on these tables, but without it will grow towards the dreaded 2.1 billion mark (I've not let it go past 1 billion, though, before stopping everything to run vacuum freeze). Oct 5 '18 at 17:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.