On Wed, Sep 9, 2015 at 2:54 PM, Christian Ramseyer <rc@networkz.ch> wrote:
I have read some discussions about pending list bloat issues, but there it was suggested that vacuuming the table should reclaim the space, and this does not seem to the case. The only way I found to reduce the size is by doing a REINDEX.
Vacuuming will allow the space to be reused internally. It will not visibly shrink the index, but will mark that space as eligible for reuse.
If you have a 36GB index and a reindex would have reduced it to 15GB, then a vacuum will leave it at 36GB but with 21GB of that as free space. The index should then stop growing and remain at the same size for 4 days while it fills up the internally freed space, at which point it would start growing again at its usual rate (until you did another vacuum).
Your best bet for now might be to turn off fastupdate on that index. It will eliminate the re-occurrence of the bloat, but might cause your insertions to become too slow (on the other hand, it might make them faster on average, it is hard to know without trying it). If you can't turn it off, then you can set the table-specific autovacuum_analyze_scale_factor to a very small value (even zero) to get autoanalyze to process the table more often.
The string in msg can be quite large, but average to around 200 characters:
select avg(length(msg)) from (select msg from logs_09 where log_date between '2015-09-01' and '2015-09-08') x;
avg ---------------------- 199.3491688585874446
Any insights on what might be happening here? Can I somehow check if the space is used up in sparsely filled pages or this list structures?
You can use pg_freespacemap once the vacuum (or autoanalyze) completes to see how many pages have 0 space available, and how many of 8160 space available:
select avail, count(*) from pg_freespace('pgbench_accounts_gin_idx') group by avail;
This won't work while the bloat is actively happening, though. During those times, freespacemap is not aware that the space is free, which is the root of the problem. The newest version of pageinspect has gin_page_opaque_info which will show those pages as being deleted, but that version is not yet released.
