From: | Thom Brown <thom(at)linux(dot)com> |
---|---|
To: | Josh Berkus <josh(at)agliodbs(dot)com> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Vacuum, Freeze and Analyze: the big picture |
Date: | 2013-05-30 19:01:01 |
Message-ID: | CAA-aLv7Uxpttr85fNxTvkTDuuW9vwKkLMGTLZ+sHie2PxGP2kQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Heikki Linnakangas | 2013-05-30 19:03:29 | Re: Freezing without write I/O |
Previous Message | Josh Berkus | 2013-05-30 18:48:12 | Vacuum, Freeze and Analyze: the big picture |