On Fri, Feb 7, 2014 at 10:47 AM, Claudio Freire <klaussfreire@xxxxxxxxx> wrote:
Hello list.
I know all the theory about vacuuming. I've got log tables that get
periodically pruned. The pruning is... quirky, though. It's not so
much deleting data, as summarizing many (thousands) of rows into one
single row. For that, a combination of deletes and updates are used.
In essence, the tables are write-only except for the summarization
step for old data.
Many tables are becoming increasingly bloated, which is somewhat
expected due to this usage pattern: I had expected table size to be
about constant, holding recent data plus archived old data (which is
really small compared to full recent logs), with some constant-sized
bloat due to daily summarization updates/deletes.
What I'm seeing, though, is not that, but bloat proportional to table
size (always stuck at about 65% bloat). What's weird, is that vacuum
full does the trick of reducing table size and bloat back to 0%. I
haven't had time yet to verify whether it goes back to 65% after
vacuum full (that will take time, maybe a month).
Question is... why isn't all that free space being used? The table
grows in size even though there's plenty (65%) of free space.
What does this look like with the pg_bloat_report.pl you linked to?
Does pg_freespace agree that that space is reusable?
SELECT avail,count(*) FROM pg_freespace('pgbench_accounts') group by avail;
Cheers,
Jeff
