I have a table with about 4M rows, but each row has some binary data that may consume up to 1Mb.
The autovacuum process on this table is taking over 1.5 hours to complete, which seems way to long to me.
I understand that when Postgres updates or deletes a row it keeps a copy of the old row and then autovacuum cleans this up later.
If my rows have a lot of binary data and they are getting updated regularly, is this the cause of my very slow autovacuum? And is it trying to load all of that data into memory?
Best Answer
4M rows with each one up to 1MB means that the table is up to 4TB, not counting the obsolete rows themselves. 1.5 hours would be astonishingly fast to vacuum a table of that size. Especially if you still have the default settings of "autovacuum_vacuum_cost_delay" and kin, which would throttle the autovacuum to about 8MB/S of read throughput, and less of write throughput.
All parts of the table not already having the visibility map set for it will have to be read into memory, but not all at the same time. All the tables indexes will also have to be read, perhaps multiple times.