Re: Turning off HOT/Cleanup sometimes

On Thu, Apr 23, 2015 at 10:44 AM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
> On 4/23/15 8:25 AM, Robert Haas wrote:
>> Some users are partitioning tables just so that each
>> partition can be autovac'd separately.  That really shouldn't be
>> required.
>
> Are they doing this for improved heap scan performance? Index scan
> performance? If the table wasn't partitioned, would they need more than one
> pass through the indexes due to exhausting maintenance_work_mem?

I don't know of anyone with a properly-configured system who needs
more than one pass through the indexes due to exhausting
maintenance_work_mem.  The issue is that you have to vacuum a table
frequently enough to avoid accumulating bloat.  The frequency with
which you need to vacuum varies depending on the size of the table and
how frequently it's updated.  However, a large, heavily-updated table
can take long enough to vacuum that, by the time you get done, it's
already overdue to be vacuumed again.  That's a problem.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company