Postgres Faster Vacuum-1

(Optimized Vacuum)

In my last article on VACUUM, I talked about it’s importance and a deep dive questionnaire. In this article we will continue to look at making VACUUM job more performant by tinkering various parameters and having a well designed table as at crunch times we wish super fast VACUUM jobs. That crunch time includes either anti wraparound vacuum where we are very close to database outage or a high priority dead tuples cleanup job to gain performance.

Change throttling parameters:
There are many throttling parameters on database and/or table level. Those are having default value set to avoid single vacuum job taking a lot of db/host resources. We can change those parameters to run more aggressive vacuum job.

autovacuum_vacuum_cost_limit, autovacuum_vacuum_cost_delay
maintenance_work_mem and autovacuum_work_mem
autovacuum_max_workers

We can put max run time limit on a query/user or terminate idle sessions after specified time.

statement_timeout
idle_in_transaction_session_timeout

There has been enough discussions on setting these parameters in various blogs, so i am not going into details.

Drop Unused Indexes:
Unused Indexes can be evil in Postgres as these Indexes need to be cleaned up as well along with main heap table and they may also be preventing HOT updates. Alternatively in Postgres12 we can defer Index cleanup phase. This will avoid slow random IO needed for Index cleanup.

VACUUM (INDEX_CLEANUP False, VERBOSE) test;

FILLFACTOR:
Fillfactor property of a table is very important to get HOT updates. These HOT updates helps in avoid cascade ctid changes to Indexes and that way helps in overall table level vacuum performance. I discussed regarding FILLFACTOR in quite detail in my earlier article.

Truncate Table:
It looks scary. But suppose there could be a time when we are in database outage due to all transactions ids are consumed and anti wraparound vacuum on a huge table will take hours. So based on that table use case and if business allows (Say that table is just used for logging purpose and not getting direct hits by application), we may truncate one table so that entire database can operate normal.

Increase vacuum_freeze_min_age:
We can increase vacuum_freeze_min_age parameter value so anti wraparound vacuum has to do less work for time being and those remaining unfrozen rows can be freeze at some later point of time once database becomes operational.

Small/Partitioned tables: Obviously if multiple small tables are there, we can run vacuum job on them in parallel manner as Postgres currently does not give many options to run parallel threads on heap tables. Also wraparound vacuum on small manageable tables are better compared to one monster table.

Future Optimization Possibilities:

• On parallel processing side definitely there is lot of work can be done. For Ex. multiple threads reads the visibility map and then clean up/freeze heap table.
• Currently a vacuum job can use maximum of 1 GB maintenance_work_mem. So increasing that could help in running vacuum faster.
• Currently for a vacuum job, Postgres allocates a 16Kb ring buffer and once that buffer space is full, it needs to write those changed buffers back to OS files. It does so to avoid shared buffer cleanup due to vacuum on a big table. That is good overall in a concurrent environment but with such a small memory buffer there are too many write calls. I hope sometime we see configurable ring buffer parameter so that we increase it in crunch times.
• Incremental wraparound vacuum: Currently vacuum job reset the transaction id age of a table only after a successful run is completed. For Ex. if a table age is reached to 500M then we can reduce the age directly from 500M to vacuum_freeze_min_age but for that all unfrozen blocks pointed by VM need to visit. I started a thread with the idea of incremental wraparound vacuum. Though I understand with scanning heap table through out of order B Tree Index could be slower due to more random reads instead of sequential reads.

Okay, that’s too much theory. I will cover up TOAST impact on VACUUM in part-2 and we will compare the run time as well.