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.
I believe sessions should not be stored in a SQL database.
It makes no sense. Updates and Inserts to the database should only be
done where there is an interesting thing to record, when the user does an
action like posting to a forum, making a purchase, sending a message, etc.
I believe sessions should be stored in the memory of the application
server, as native objects of the whatever language the application is
written in. This way, sessions incur no serializing overhead and can be
quite large and complex, which allows storage of interesting things, like
the result of a complicated search query which is to be later paginated,
for instance. It really makes sense to use native language objects too, as
these have a lot more power and versatility than a database row. Think
about rights management, for instance.
When the framework used lacks this power (most do and this is sad), then
sessions incur serializing overhead ; but they should be serialized to
filesystem files, or better, to memory using memcached, for instance. It
makes no sense to pay the performance penalty of a COMMIT (disk seek delay
etc) and the database overhead for sessions, which are by nature volatile
data changing all the time.
I don't think postgres should be tweaked to allow better handling of
this. It would only make a better foot-gun.
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
