Thanks for the help everyone and sorry for not replying sooner, I was on a business trip.
@Hubert pg_reorg looks really interesting and from the first read it looks to be a very good solution for maintenance, but for now I would rather try to slow down, or remove this bloat, so I have to do as less maintenance as possible. @Mark So basically I should decrease the autovacuum nap time from 60s to 10s, reduce the scale factor from 0.2 to 0.1. log_autovacuum_min_duration is already set to 0, which means everything is logged. @Jeff I'm not sure if I understand what you mean? I know that we never reuse key ranges. Could you be more clear, or give an example please. Thanks in advance, Strahinja On Tue, Aug 14, 2012 at 6:14 AM, Jeff Janes <jeff.ja...@gmail.com> wrote: > On Fri, Aug 10, 2012 at 3:15 PM, Strahinja Kustudić > <strahin...@nordeus.com> wrote: > > > > For example, yesterday when I checked the database size on the production > > server it was 30GB, and the restored dump of that database was only 17GB. > > The most interesting thing is that the data wasn't bloated that much, but > > the indices were. Some of them were a few times bigger than they should > be. > > For example an index on the production db is 440MB, while that same index > > after dump/restore is 17MB, and there are many indices with that high > > difference. > > Could your pattern of deletions be leaving sparsely populated, but not > completely empty, index pages; which your insertions will then never > reuse because they never again insert values in that key range? > > Cheers, > > Jeff >
