How much table bloat is acceptable before it affects performance in PostgreSQL?
How big is the table? (For small tables, it doesn't matter.) How active is it? How frequently are records updated?
Hi
Table size 100gb
I use pgstattuple_approx to get Table bloat is about 16gb as of now since after repack is done on 27th of January
Fillfactor already in place
It's very critical application with updates on non partitioned table
What did you set the fillfactor to?
Have you minimized the number of indexes? (That lets HOT work better.)
How long does it take to VACUUM the table?
Hi
Fillfactor 80
3 composite and pkey on one column as queries use those
Vacuum 3min to complete
Here autovacuum 5min to complete during load even with param tuning
1. What is autovacuum_vacuum_scale_factor set to?
2. How often does the autovacuum run? (pg_stat_user_tables will tell you.)
3. Do you update any of those indexed columns?
4. How often do queries/reports need to read large chunks of the table (aka sequentially scan it)?
5. Is performance currently suffering, or are you proactively worrying?
Note: Regular vacuuming eliminates bloat.
Hi
Periodic maintenance activity already enabled that runs for everyday once
1).sclae factor for toast 0.06 and non toast 0.1
Good.
2).observers that autovacuum runs for every 1hour
Good.
3).2indexed columns are being updated but I think it shouldn't be
Interesting. As you seemingly suspect, fewer index updates speed things up.
4).most of the time index scan but not sequential scan
Well, as you probably know, bloat makes sequential scans slower, since there's more file to scan. Sometimes, though, you've got to choose "faster updates" or "faster sequential scans".
5).Seem to be good average latency is less for queries
But trying to optimize better than now
If it's heavy on the updates, then lowering that fill factor after eliminating updates of indexed fields will definitely speed UPDATE statements at the expense of table sequential scans.
