0

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?

1

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.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.