Re: vacuum, performance, and MVCC - Mailing list pgsql-hackers

> After a long battle with technology, pgsql@mohawksoft.com ("Mark
> Woodward"), an earthling, wrote:
>>> Clinging to sanity, pgsql@mohawksoft.com ("Mark Woodward") mumbled into
>>> her beard:
[snip]
>>>
>>> 1.  The index points to all the versions, until they get vacuumed out.
>>
>> It can't point to "all" versions, it points to the last "current"
>> version
>> as  updated by vacuum, or the first version of the row.
>
> No, it points to *all* the versions.
>
> Suppose I take a table with two rows:
>
> INFO:  analyzing "public.test"
> INFO:  "test": 1 pages, 2 rows sampled, 2 estimated total rows
> VACUUM
>
> Then, over and over, I remove and insert one entry with the same PK:
>
> sample=# delete from test where id = 2;insert into test (id) values (2);
> DELETE 1

[snip]
>
> Now, I vacuum it.
>
> sample=# vacuum verbose analyze test;
> INFO:  vacuuming "public.test"
> INFO:  index "test_id_key" now contains 2 row versions in 2 pages
> DETAIL:  10 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO:  "test": removed 10 row versions in 1 pages
> DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO:  "test": found 10 removable, 2 nonremovable row versions in 1 pages
> DETAIL:  0 dead row versions cannot be removed yet.
> There were 0 unused item pointers.
> 0 pages are entirely empty.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO:  analyzing "public.test"
> INFO:  "test": 1 pages, 2 rows sampled, 2 estimated total rows
> VACUUM
>
> Notice that the index contained 10 versions of that one row.
>
> It pointed to *ALL* the versions.

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?

I am attaching some code that shows the problem with regard to
applications such as web server session management, when run, each second
the system can handle fewer and fewer connections. Here is a brief output:

markw@ent:~/pgfoo$ ./footest
1307 sessions per second, elapsed: 1
1292 sessions per second, elapsed: 2
1287 sessions per second, elapsed: 3
....
1216 sessions per second, elapsed: 25
1213 sessions per second, elapsed: 26
1208 sessions per second, elapsed: 27
....
1192 sessions per second, elapsed: 36
1184 sessions per second, elapsed: 37
1183 sessions per second, elapsed: 38
....
1164 sessions per second, elapsed: 58
1170 sessions per second, elapsed: 59
1168 sessions per second, elapsed: 60

As you can see, in about a minute at high load, this very simple table
lost about 10% of its performance, and I've seen worse based on update
frequency.  Before you say this is an obscure problem, I can tell you it
isn't. I have worked with more than a few projects that had to switch away
from PostgreSQL because of this behavior.

Obviously this is not a problem with small sites, but this is a real
problem with an enterprise level web site with millions of visitors and
actions a day. Quite frankly it is a classic example of something that
does not scale. The more and more updates there are, the higher the load
becomes. You can see it on "top" as the footest program runs.

There has to be a more linear way of handling this scenario.