Re: vacuum, performance, and MVCC

On 6/22/06, Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
> > Hmm, OK, then the problem is more serious than I suspected.
> > This means that every index on a row has to be updated on every
> > transaction that modifies that row. Is that correct?
>
> Add an index entry, yes.

Again, this is a case for update-in-place. No need to write an extra
index entry and incur the WAL associated with it. Imagine a table
with 3 indexes on it... I would estimate that we perform at least 3 to
6 times more overhead than any commercial database on such an update.

> > There has to be a more linear way of handling this scenario.
>
> So vacuum the table often.

It's easy to say VACUUM often... but I'd bet that vacuuming is going
to lessen the throughput in his tests even more; no matter how it's
tuned.

--
Jonah H. Harris, Software Architect | phone: 732.331.1300
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 2nd Floor | jharris(at)enterprisedb(dot)com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/