First, I haven't seen major problems of database bloat in a long time which is why I find this case strange. I wanted to ask here what may be causing it.
Problem:
==========
Database is in the 100GB to 200GB size range, running on btrfs (not my choice) with nodatacow enabled (which I set up to fix a performance issue). The workload is a very heavy batch-update workload.
The database bloats linearly. I have measured this on one table (of 149M rows).
After vacuum full this table is (including indexes): 17GB
Every 24 hrs, seems to add its original space in size to the file system +/-.
Bloat seems to be affecting both indexes and underlying tables.
Vacuum verbose does not indicate a disproportionate number of rows being unremovable. So autovacuum is keeping up without too much difficulty.
Troubleshooting so far
=======================
filefrag finds a single extent on each file, so copy-on-write is not the culprit
Selecting the smallest 10 values of ctid from one of the bloating tables shows the first page used is around page 35 with one row per used page (and large gaps in between).
Questions
===========
I assume that it is the fact that rows update frequently which is the problem here? But why doesn't Postgres re-use any of the empty disk pages?
More importantly, is there anything that can be done to mitigate this issue other than a frequent vacuum full?
Two points I think I forgot to mention:
This is PostgreSQL 9.5.1
Last I saw something similar was a more "minor" case on a larger db, on PostgreSQL 9.3.x
The more minor case was a small table (maybe 20k rows) which had bloated to 1GB in size due to this same sort of problem but we ignored it because the table was cached all the time and at the RAM we were using, it wasn't a significant drain on performance. However, here it is.
First 20 CTIDs from one table:
(35,25)
(48,15)
(76,20)
(77,20)
(83,20)
(96,19)
(100,19)
(103,13)
(111,9)
(115,12)
(124,11)
(120,12)
(131,12)
(137,12)
(150,14)
(152,12)
(157,20)
(162,14)
--
Best Wishes,
Chris Travers
Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.
