Re: Catalog bloat (again)

On Thu, 28 Jan 2016 00:37:54 +0100
Ivan Voras <ivoras(at)gmail(dot)com> wrote:

> On 28 January 2016 at 00:13, Bill Moran <wmoran(at)potentialtech(dot)com> wrote:
>
> > On Wed, 27 Jan 2016 23:54:37 +0100
> > Ivan Voras <ivoras(at)gmail(dot)com> wrote:
> >
> > > So, question #1: WTF? How could this happen, on a regularly vacuumed
> > > system? Shouldn't the space be reused, at least after a VACUUM? The issue
> > > here is not the absolute existence of the bloat space, it's that it's
> > > constantly growing for *system* tables.
> >
> > With a lot of activity, once a day probably isn't regular enough.
> >
> >
> I sort of see what you are saying. I'm curious, though, what goes wrong
> with the following list of expectations:
>
> 1. Day-to-day load is approximately the same
> 2. So, at the end of the first day there will be some amount of bloat
> 3. Vacuum will mark that space re-usable
> 4. Within the next day, this space will actually be re-used
> 5. ... so the bloat won't grow.
>
> Basically, I'm wondering why is it growing after vacuums, not why it exists
> in the first place?

To add to what others have said: are you 100% sure that vacuum is
completing successfully each time it runs? I.e. does your cron job
trap and report failures of vacuum to complete? If it fails occasionally
for whatever reason, it's liable to bloat a lot over 48 hours (i.e.
assuming it succeeds the next time).

Additionally, there's the problem with active transactions causing it to
not clean up quite everything.

Not to belabour the point, but these hiccups are best handled by enabling
autovacuum and allowing it to monitor tables and take care of them for you.
I'm curious of claims of autovacuum causing performance issues, as I've
never seen it have much impact. Generally, if you can't run autovacuum
due to performance issues, your hardware is undersized for your workload
and anything else you do is just going to have problems in a different way.

--
Bill Moran