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)
maintenance_work_mem
– Jasen Jul 5 '18 at 7:10