Re: Vacuum, Freeze and Analyze: the big picture

On 30 May 2013 19:48, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> Folks,
>
> 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.
> Otherwise, we're liable to repeat the 8.4 problem of making one
> operation better (background vacuum) while making another one worse
> (freezing).
>
> 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.
>
> 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.
>
> So I've set up a wiki page to document the various problems that force
> users to think about vacuum and analyze and try to troubleshoot it:
>
> https://wiki.postgresql.org/wiki/VacuumHeadaches
>
> We can also collect suggested solutions here. I'm looking to create a
> long-term development target which removes most of these vacuum
> headaches over the next 3 or 4 releases, without making the unremoved
> headaches siginficantly worse.

Great collection of issues.

I'm not sure I understand this:

"Problem: As of 9.3, there's a significant benefit to vacuum freezing
tables early so that index-only scan is enabled, since freezing also
updates the visibility map. However, with default settings, such
freezing only happens for data which is very old. This means that
index-only scan is less effective than it could be for tables which
have relatively infrequent updates and deletes."

Why specifically VACUUM FREEZE rather than regular VACUUM? I thought
regular VACUUM updated the visibility map too? And why as of 9.3
instead of 9.2?

--
Thom