Today I installed postgres. I've been using MySQL for several years now and have been generally happy with it. Lately, I've been looking lustily at such features as foreign key support and subselects -- things that MySQL is slowly beginning to think about maybe implementing possibly Real Soon Now. For once, I'm not going to be partisan about my software. To put it plainly, I need what I need. MySQL is still a great engine for read-only, infrequently changing data. Postgres promises to be better at supporting complex referential relationships between tables.
Postgres is not without failings.
These aren't showstopper problems; this is merely my travelog as I pass through the unknown country of a new RDBMS.
Why am I bothering? Apache::Session supports both MySQL and Postgresql. What's the difference? Apache::Session::MySQL locks the tables during updates. Apache::Session::Postgres uses transactions and no locking. I think the ideal solution is row-level locking. The MySQL solution quickly falls down in the application I'm developing because every web page paint requires accessing the session table.
Transactions aren't a silver bullet either. One process can be altering a row while another is reading and acting on the soon-to-be-changed data. For Apache::Session applications, this implies that two users would be trying to alter the same row (with the same session), which isn't suppose to happen except for extremely pathological cases. In any case, the application's performance will handled a bigger load with the table locking millstone.