Re: [COMMITTERS] pgsql: Reduce the size of memory allocations by lazy vacuum when

On Mon, 2007-09-24 at 10:02 +0100, Heikki Linnakangas wrote:
> Alvaro Herrera wrote:
> > Log Message:
> > -----------
> > Reduce the size of memory allocations by lazy vacuum when processing a small
> > table, by allocating just enough for a hardcoded number of dead tuples per
> > page. The current estimate is 200 dead tuples per page.
>
> 200 sounds like a badly chosen value. With a 8k block size, that's a bit
> less than MaxHeapTuplesPerPage, which means that in the worst case you
> don't allocate enough space to hold all dead tuples, and you end up
> doing 2 index cleanups, no matter how large you set
> maintenance_work_mem.

Agreed. Tables with 2-4 columns easily fall into that category.
Assoication tables are often like this and can have 2 indexes on them.

> Note that as the patch stands, the capping is not limited to small
> tables. Doing extra index passes on a relatively big table with lots of
> indexes might be cause a lot of real extra I/O.
>
> How about just using MaxHeapTuplesPerPage? With the default 8K block
> size, it's not that much more than 200, but makes the above gripes
> completely go away. That seems like the safest option at this point.

It would be much better to use a value for each table. Any constant
value will be sub-optimal in many cases.

Let's use our knowledge of the table to calculate a sensible value. We
often have average row length available from last VACUUM, don't we? Use
that, plus 10%.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com