Re: Vacuum, Freeze and Analyze: the big picture

On 30 May 2013 19:48, Josh Berkus <josh(at)agliodbs(dot)com> wrote:

> There's currently some great ideas bouncing around about eliminating the
> overhead associated with FREEZE. However, I wanted to take a step back
> and take a look at the big picture for VACUUM, FREEZE and ANALYZE.

That is a very commendable approach. We should do that more often.

> The big, big picture is this:
>
> 90% of our users need to think about VACUUM/ANALYZE
> at least 10% of the time
> and 10% of our users need to think about it
> almost 90% of the time.

When you say stuff like that, you should add "speculating from my
personal experience". People might get the impression you'd measured
this somehow and it could confuse the issue if you try to assemble a
high level viewpoint and then add in factoids that are just opinions.

We should strive to measure such things.

> That's considerably better than was the case 5 years ago, when vacuum
> management was a daily or weekly responsibility for nearly 100% of our
> users, but it's still not good enough. Our target should be that only
> those with really unusual setups should have to *ever* think about
> vacuum and analyze.

I think that's where we already are given that 1000s of users have
quite small databases.

The problem increases with scale. Larger databases have bigger
problems and make it easier to notice things are happening.

I think you should mention that the evidence for these issues is
anecdotal and take careful notes of the backgrounds in which they
occurred. Saying things occur in all cases wouldn't be accurate or
helpful to their resolution.

We should be seeking to contrast this against other databases to see
if we are better or worse than other systems. For example, recording
the moans of someone who is currently managing a 1 TB database, but
yet hasn't ever managed anything else that big is less valuable than a
balanced, experienced viewpoint (if such exists).

Anyway, I support this approach, just wanted to make sure we do it in
sufficient detail to be useful.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services