I researched this problem a few months ago, I got this result: During the working of storage node we have a lot of writes to the database, this writes increase fragmentation of database files (extremely increase) and we will get performance degradation (a lot of IOPS for simply query). Calling vacuum is not a silver bullet but it really helps because during this operation we rewrite the whole file sequentially (like defragmentation on disk), the same result we will have when just move a database to /tmp and back to the original place (rewrite the whole file sequentially).
So, root cause is - file fragmentation on HDD (not a database structure fragmentation).
I very happy that we have a possibility to move databases on this release to the SSD, for me it best solution for keep database always “defragmented” (we just will not care about defragmentation) I will put it to my mirrored system SSD and will monitor how it working. I expect a lot of benefits at the same time:
- Solve issue with “locks”
- No database performance degradation during a time (one month with active ingress is enough to heavy performance impact)
- We offload main storage for pieces and improve IOPS