We all know that PostgreSQL suffers performance problems when rows are
updated frequently prior to a vacuum. The most serious example can be seen
by using PostgreSQL as a session handler for a busy we site. You may have
thousands or millions of active sessions, each being updated per page hit.

Each time the record is updated, a new version is created, thus
lengthening the "correct" version search each time row is accessed, until,
of course, the next vacuum comes along and corrects the index to point to
the latest version of the record.

Is that a fair explanation?

If my assertion is fundimentally true, then PostgreSQL will always suffer
performance penalties under a heavy modification load. Of course, tables
with many inserts are not an issue, it is mainly updates. The problem is
that there are classes of problems where updates are the primary
operation.

I was thinking, just as a hypothetical, what if we reversed the problem,
and always referenced the newest version of a row and scanned backwards
across the versions to the first that has a lower transacton number?

One possible implementation: PostgreSQL could keep an indirection array of
index to table ref for use by all the indexes on a table. The various
indexes return offsets into the array, not direct table refs. Because the
table refs are separate from the index, they can be updated each time a
transaction is commited.

This way, the newest version of a row is always the first row found. Also,
on a heavily updated site, the most used rows would always be at the end
of the table, reducing amount of disk reads or cache memory required to
find the correct row version for each query.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Clinging to sanity, pgsql@mohawksoft.com ("Mark Woodward") mumbled into her beard:
> We all know that PostgreSQL suffers performance problems when rows are
> updated frequently prior to a vacuum. The most serious example can be seen
> by using PostgreSQL as a session handler for a busy we site. You may have
> thousands or millions of active sessions, each being updated per page hit.
>
> Each time the record is updated, a new version is created, thus
> lengthening the "correct" version search each time row is accessed, until,
> of course, the next vacuum comes along and corrects the index to point to
> the latest version of the record.
>
> Is that a fair explanation?

No, it's not.

1. The index points to all the versions, until they get vacuumed out.

2. There may simultaneously be multiple "correct" versions. The
notion that there is one version that is The Correct One is wrong, and
you need to get rid of that thought.

> If my assertion is fundimentally true, then PostgreSQL will always suffer
> performance penalties under a heavy modification load. Of course, tables
> with many inserts are not an issue, it is mainly updates. The problem is
> that there are classes of problems where updates are the primary
> operation.

The trouble with your assertion is that it is true for *all* database
systems except for those whose only transaction mode is READ
UNCOMMITTED, where the only row visible is the "Latest" version.

> I was thinking, just as a hypothetical, what if we reversed the
> problem, and always referenced the newest version of a row and
> scanned backwards across the versions to the first that has a lower
> transacton number?

That would require an index on transaction number, which is an
additional data structure not in place now. That would presumably
worsen things.

> One possible implementation: PostgreSQL could keep an indirection array of
> index to table ref for use by all the indexes on a table. The various
> indexes return offsets into the array, not direct table refs. Because the
> table refs are separate from the index, they can be updated each time a
> transaction is commited.

You mean, this index would be "VACUUMed" as a part of each transaction
COMMIT? I can't see that turning out well...

> This way, the newest version of a row is always the first row
> found. Also, on a heavily updated site, the most used rows would
> always be at the end of the table, reducing amount of disk reads or
> cache memory required to find the correct row version for each
> query.

I can't see how it follows that most-used rows would migrate to the
end of the table. That would only be true in a database that is never
VACUUMed; as soon as a VACUUM is done, free space opens up in the
interior, so that new tuples may be placed in the "interior."
--
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne> rate me
http://linuxdatabases.info/info/lisp.html
"On a normal ascii line, the only safe condition to detect is a
'BREAK' - everything else having been assigned functions by Gnu
EMACS." -- Tarl Neustaedter

> Clinging to sanity, pgsql@mohawksoft.com ("Mark Woodward") mumbled into
> her beard:
>> We all know that PostgreSQL suffers performance problems when rows are
>> updated frequently prior to a vacuum. The most serious example can be
>> seen
>> by using PostgreSQL as a session handler for a busy we site. You may
>> have
>> thousands or millions of active sessions, each being updated per page
>> hit.
>>
>> Each time the record is updated, a new version is created, thus
>> lengthening the "correct" version search each time row is accessed,
>> until,
>> of course, the next vacuum comes along and corrects the index to point
>> to
>> the latest version of the record.
>>
>> Is that a fair explanation?
>
> No, it's not.
>
> 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.

>
> 2. There may simultaneously be multiple "correct" versions. The
> notion that there is one version that is The Correct One is wrong, and
> you need to get rid of that thought.

Sorry, this is misunderstanding. By "correct version search" it was
implied "for this transaction." Later I mention finding the first row with
a transaction lower than the current.

>
>> If my assertion is fundimentally true, then PostgreSQL will always
>> suffer
>> performance penalties under a heavy modification load. Of course, tables
>> with many inserts are not an issue, it is mainly updates. The problem is
>> that there are classes of problems where updates are the primary
>> operation.
>
> The trouble with your assertion is that it is true for *all* database
> systems except for those whose only transaction mode is READ
> UNCOMMITTED, where the only row visible is the "Latest" version.

Not true. Oracle does not seem to exhibit this problem.

>
>> I was thinking, just as a hypothetical, what if we reversed the
>> problem, and always referenced the newest version of a row and
>> scanned backwards across the versions to the first that has a lower
>> transacton number?
>
> That would require an index on transaction number, which is an
> additional data structure not in place now. That would presumably
> worsen things.

All things being equal, perhaps not. It would proably be a loser if you
have a static database, but in a database that undergoes modification, it
would be the same or less work if the average row has two versions.
(assuming nothing else changes)
>
>> One possible implementation: PostgreSQL could keep an indirection array
>> of
>> index to table ref for use by all the indexes on a table. The various
>> indexes return offsets into the array, not direct table refs. Because
>> the
>> table refs are separate from the index, they can be updated each time a
>> transaction is commited.
>
> You mean, this index would be "VACUUMed" as a part of each transaction
> COMMIT? I can't see that turning out well...

No, it would not be vacuumed!!!

Right now, the indexes point to the lowest row version. When an index
returns the row ID, it is checked if there are newer versions, if so, the
newer versions are searched until the last one is found or exceeds the
current TID.

>
>> This way, the newest version of a row is always the first row
>> found. Also, on a heavily updated site, the most used rows would
>> always be at the end of the table, reducing amount of disk reads or
>> cache memory required to find the correct row version for each
>> query.
>
> I can't see how it follows that most-used rows would migrate to the
> end of the table.

Sorry, OK, as assumtion it ignores the FSM, but the idea is that there is
only one lookup.

> That would only be true in a database that is never
> VACUUMed; as soon as a VACUUM is done, free space opens up in the
> interior, so that new tuples may be placed in the "interior."


Regardless, the point is that you have to search the [N] versions of a row
to find the latest correct version of the row for your transacation. This
is done, AFAICT, from first to last version, meaning that the work
required to find a row increases with every update prior to vacuum.

PostgreSQL fails miserably as a web session handler because of this
behavior and it requires too frequent vacuums and inconsistent
performance.

OK, forget the version array, it was just an off the top idea. How about
this:

Currently a row does this:

row_TID[0] -> row_TID[1] ->row_TID[2] ./. row_TID[LAST-1] -> row_TID[LAST]

Pointing to each subsequent row. What if it did this:

row_TID[0] -> row_TID[LAST] -> row_TID[LAST-1] ./. -> row_TID[2] ->
row_TID[1]

The base tuple of a version chain gets updated to point to the latest
commited row. It should be fairly low impact on performance on a static
database, but REALLY speed up PostgreSQL on a heavily modified database
and provide more consistent performance between vacuums and require fewer
vacuums to maintain performance.


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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:
>>> We all know that PostgreSQL suffers performance problems when rows are
>>> updated frequently prior to a vacuum. The most serious example can be
>>> seen
>>> by using PostgreSQL as a session handler for a busy we site. You may
>>> have
>>> thousands or millions of active sessions, each being updated per page
>>> hit.
>>>
>>> Each time the record is updated, a new version is created, thus
>>> lengthening the "correct" version search each time row is accessed,
>>> until,
>>> of course, the next vacuum comes along and corrects the index to point
>>> to
>>> the latest version of the record.
>>>
>>> Is that a fair explanation?
>>
>> No, it's not.
>>
>> 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
INSERT 4842550 1
sample=# delete from test where id = 2;insert into test (id) values (2);
DELETE 1
INSERT 4842551 1
sample=# delete from test where id = 2;insert into test (id) values (2);
DELETE 1
INSERT 4842552 1
sample=# delete from test where id = 2;insert into test (id) values (2);
DELETE 1
INSERT 4842553 1
sample=# delete from test where id = 2;insert into test (id) values (2);
DELETE 1
INSERT 4842554 1
sample=# delete from test where id = 2;insert into test (id) values (2);
DELETE 1
INSERT 4842555 1
sample=# delete from test where id = 2;insert into test (id) values (2);
DELETE 1
INSERT 4842556 1
sample=# delete from test where id = 2;insert into test (id) values (2);
DELETE 1
INSERT 4842557 1
sample=# delete from test where id = 2;insert into test (id) values (2);
DELETE 1
INSERT 4842558 1
sample=# delete from test where id = 2;insert into test (id) values (2);
DELETE 1
INSERT 4842559 1

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.

>> 2. There may simultaneously be multiple "correct" versions. The
>> notion that there is one version that is The Correct One is wrong, and
>> you need to get rid of that thought.
>
> Sorry, this is misunderstanding. By "correct version search" it was
> implied "for this transaction." Later I mention finding the first row with
> a transaction lower than the current.

Ah. Then you need for each transaction to spawn an index for each
table that excludes non-current values.

>>> If my assertion is fundimentally true, then PostgreSQL will always
>>> suffer performance penalties under a heavy modification load. Of
>>> course, tables with many inserts are not an issue, it is mainly
>>> updates. The problem is that there are classes of problems where
>>> updates are the primary operation.
>>
>> The trouble with your assertion is that it is true for *all* database
>> systems except for those whose only transaction mode is READ
>> UNCOMMITTED, where the only row visible is the "Latest" version.
>
> Not true. Oracle does not seem to exhibit this problem.

Oracle suffers a problem in this regard that PostgreSQL doesn't; in
Oracle, rollbacks are quite expensive, as "recovery" requires doing
extra work that PostgreSQL doesn't do.
--
output = ("cbbrowne" "@" "gmail.com")
http://cbbrowne.com/info/
Marriage means commitment. Of course, so does insanity.

> 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.


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Mark Woodward 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.

> 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:
> [...]
> There has to be a more linear way of handling this scenario.

So vacuum the table often.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On 6/22/06, Alvaro Herrera <alvherre@commandprompt.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@enterprisedb.com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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

Good advice, except if the table is huge :-)

Here we have for example some tables which are frequently updated but
contain >100 million rows. Vacuuming that takes hours. And the dead row
candidates are the ones which are updated again and again and looked up
frequently...

A good solution would be a new type of vacuum which does not need to do
a full table scan but can clean the pending dead rows without that... I
guess then I could vacuum really frequently those tables.

Cheers,
Csaba.



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Am Donnerstag, 22. Juni 2006 16:09 schrieb Csaba Nagy:
> > > [...]
> > > There has to be a more linear way of handling this scenario.
> >
> > So vacuum the table often.
>
> Good advice, except if the table is huge :-)
>
> Here we have for example some tables which are frequently updated but
> contain >100 million rows. Vacuuming that takes hours. And the dead row
> candidates are the ones which are updated again and again and looked up
> frequently...
>
> A good solution would be a new type of vacuum which does not need to do
> a full table scan but can clean the pending dead rows without that... I
> guess then I could vacuum really frequently those tables.

Now that there is autovaccum, why not think of something like continous
vacuum? A background process that gets info about potential changed tuples,
and vacuums them (only those tuples), possibly with honouring I/O needs of
backgrounds (not steealing I/O from busy backends).

For sure not that easy as autovacuum. I'm pretty sure I've read something
about partial vacuum lately, is somebody working on something like this?

Regards,
Mario

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Ühel kenal päeval, N, 2006-06-22 kell 09:59, kirjutas Mark Woodward:
> > After a long battle with technology, pgsql@mohawksoft.com ("Mark
> > Woodward"), an earthling, wrote:
> >>> Clinging to sanity, pgsql@mohawksoft.com ("Mark Woodward") mumbled into
> > 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?

Yes.

> 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.

You mean systems that are designed so exactly, that they can't take 10%
performance change ?

Or just that they did not vacuum for so long, that performance was less
than needed in the end?

btw, what did they switch to ?

> 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.

On such site you should design so that db load stays below 50% and run
vacuum "often", that may even mean that you run vacuum continuously with
no wait between runs. If you run vacuum with right settings,

> 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.

Yes, you understood correctly - the more updates, the higher the load

--
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings