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