PostgreSQL - VACUUM speed (Heroku)

Question

My DB have a main table that is has lots of commits on a daily basis, with about 15 indexes on this table. The table has 25M rows and the dead row count seem to exceed the number of live rows at the moment.

The auto-vacuum seems to run for over a day on this table, and doesn't seem to catch up with the new generated dead rows.

I diverted some actions that I used the DB for filtering to be done in memory, as in, instead of inserting rows and later clean them up using DB queries, I now do it in-memory in-advance.

I'm thinking of removing some indexes since they seem to get scanned to and add a significant overhead.

Any tips on how to improve the speed?

Additional details:

• The DB has 8GB RAM, the table is around 30GB, but the indexes are big, each is around 5GB.
• The dead row count is now around 40M while the rowcount seems to be stuck exactly the same number for a while 25M (and some change).
• I use automatic vacuum tuned up to get triggered frequently; this is the "nightly vacuum" I mention above.

Update: It seems what I tried is working, previously I was committing lots of rows to the DB, and delete shortly after by launching a cleanup run. I changed it to filtering the data in advance and not commit data I will cleanup later, causing much less work on the DB and less dead rows.

It seems the auto-vacuum which is starting to catch up slowly, but it takes significant time since each vacuum on this table runs for almost two days.

(unfortunately since I'm on Heroku, I can't control many parameters)

Answer 1

This means you need to tune your autovacuum settings.

Auto vacuum is not the enemy

The default settings for autovacuum will trigger when the 20% rows are dead. This is not a good choice for a large OLTP system. So tune it on Number basis not the percentage.

Like, set auto vacuum when the dead tuple count reaches 10000.

autovacuum_vacuum_scale_factor = 0
autovacuum_vacuum_threshold = 10000
autovacuum_max_workers = 4

OR

you can set this threshold on a particular table.

ALTER TABLE t SET (autovacuum_vacuum_scale_factor = 0);
ALTER TABLE t SET (autovacuum_vacuum_threshold = 10000);

In some worst cases auto vaccum will not work. Read why? The above parameters for Autovacuum for Ad-Hoc vacuum set something like,

vacuum_cost_delay = 20ms
vacuum_cost_limit = 200

vacuum_cost_page_hit = 1
vacuum_cost_page_miss = 10
vacuum_cost_page_dirty = 20

maintenance_work_mem is also a contributing for this