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.
- The command line client psql works slightly differently than mysql. I didn’t expect a learning curve there.
- The quoting rules for the SQL postgres expects is, er, unexpected. That double quotes denote column names and single quotes denote strings is an unexpected wrinkle.
- I’m not fond the postgres authorization system. I prefer the MySQL style of internal SQL tables to manage access to the databases rather than an external, UNIXy text file database. SIGHUPs to postmaster for authorization changes? Eek.
- I noticed that the maximum number of TCP/IP connections was a compile time setting. I assume this can be overridden by a runtime configuration option.
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.