Hi,
I have a table with a really small number of rows, usually about 1500, sometimes may be up to 5000. The usage pattern of that table is such that rows are inserted and kept for a while, mostly seconds or minutes but theoretically up to 1 year. After that they are deleted. No updates, just insert, delete. The table is pretty actively written, sometimes >100 transactions per second.
Although the table is frequently auto-vacuumed, its size is growing over time. The size of the table corresponds to the remaining number of pages in the autovacuum log.
pages: 0 removed, 30069 remain, 0 skipped due to pins, 29985 skipped frozen
tuples: 136 removed, 1643 remain, 15 are dead but not yet removable
buffer usage: 44327 hits, 0 misses, 21 dirtied
avg read rate: 0.000 MB/s, avg write rate: 0.036 MB/s
system usage: CPU 0.06s/0.05u sec elapsed 4.59 sec
tuples: 136 removed, 1643 remain, 15 are dead but not yet removable
buffer usage: 44327 hits, 0 misses, 21 dirtied
avg read rate: 0.000 MB/s, avg write rate: 0.036 MB/s
system usage: CPU 0.06s/0.05u sec elapsed 4.59 sec
What does "29985 skipped frozen" mean? I presume these skipped buffers is the table bloat.
My way to get rid of the bloat is to cluster the table. That takes less than half a second and the next autovacuum log then looks like this:
pages: 0 removed, 3493 remain, 0 skipped due to pins, 0 skipped frozen
tuples: 0 removed, 58873 remain, 57470 are dead but not yet removable
buffer usage: 9425 hits, 0 misses, 0 dirtied
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
system usage: CPU 0.02s/0.03u sec elapsed 1.01 sec
tuples: 0 removed, 58873 remain, 57470 are dead but not yet removable
buffer usage: 9425 hits, 0 misses, 0 dirtied
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
system usage: CPU 0.02s/0.03u sec elapsed 1.01 sec
For a number of autovacuum cycles "skipped frozen" remains 0 until eventually:
pages: 0 removed, 4822 remain, 0 skipped due to pins, 4433 skipped frozen
tuples: 227 removed, 2609 remain, 13 are dead but not yet removable
buffer usage: 5269 hits, 0 misses, 315 dirtied
avg read rate: 0.000 MB/s, avg write rate: 2.065 MB/s
system usage: CPU 0.03s/0.01u sec elapsed 1.19 sec
tuples: 227 removed, 2609 remain, 13 are dead but not yet removable
buffer usage: 5269 hits, 0 misses, 315 dirtied
avg read rate: 0.000 MB/s, avg write rate: 2.065 MB/s
system usage: CPU 0.03s/0.01u sec elapsed 1.19 sec
Is there anything I can do to prevent that bloat from accumulating in the first place?
Another thing that seems odd is the number of dead but not removable tuples in the record in the middle. Sifting through the log, I can see numbers up to 80000. One of our replicas is configured with hot_standby_feedback. Can that be the culprit?
This is 9.6.10.
Thanks,
Torsten
