Re: [HACKERS] HOT WIP Patch - version 2

On Tue, 2007-02-20 at 09:48 +0200, Hannu Krosing wrote:

> I'm not sure about the "we are more concerned about the large tables"
> part. I see it more as a device for high-update tables. This may not
> always be the same as "large", so there should be some fallbacks for
> case where you can't get the lock. Maybe just give up and move to
> another page ?

Every design favours one or other of the various use cases.

The worst performance is caused by large tables with random updates,
because that causes substantially more I/O than a smaller table.

A table with substantially more updaters than rows will be unlikely to
ever yield a vacuum-level lock on the block, so the table will
inevitably grow. But because its fairly small, it won't grow that much
before an autovacuum is triggered to clean it up. The index entries will
still be minimised in this case.

The case of a small number of rows being very heavily updated in an
otherwise very large table will not be well optimised by this simplified
version of HOT. However, that case can still benefit from a Dead Space
Map approach. In view of other work on DSM it was felt that simplifying
HOT was the right thing to do. So DSM is still required.

If no other DSM approaches happen, it should be possible to implement an
80/20 version of DSM by simply running a VACUUM using the current FSM
implementation as the input blockids. In many cases that will yield a
good proportion of the benefits of a full VACUUM. I hope that will be
agreed if there isn't any other agreement on a full DSM solution; it
would be a pity to ignore such a low complexity solution.

Note also that Alvaro's multi-vacuum solution will also be required to
allow VACUUMs to be effective against heavily updated, yet smaller
tables.

So the comment about "more concerned with large tables" is really a
trade-off to allow a simpler solution, yet in an area that minimises the
performance disadvantages.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com