On 10/09/15 06:40, Jeff Janes wrote:
> 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).
>
Hi Jeff
Thanks, I didn't think about that. I tried a manual analyze 4 days ago
(10.09.) when it was at 41 GB, and it stayed the same size since then,
so this works as expected.
> 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.
>
>
Yeah the default autovacuum settings are what allowed the index to go
unchecked to about 120 GB and fill our disk, I'll tune this for these
tables.
The data sometimes arrives in bursts and I'm a bit affraid of making the
inserts slower, but I'll see if I can do a benchmark of fastupdate vs.
nofastupdate and will post it here if I get to it.
Thanks for your help
Christian
