PostgreSQL Versus...........Everyone Else

Recently, I have been pushing for us to move our databases for our web-based systems from MySQL to Postgres. This has finally been happening with very little trouble or drama, although many of our systems are now based on Rails and ActiveRecord so the change is transparent.

Things have finally got to a head where the features MySQL are adding are already there in Postgres, MySQL isn't quite as stable as we though it would be for a database system we will leave unattended for months at a time and the advantage of performance that MySQL supposedly once had has all but evaporated. From where I sit, it's becoming much easier for PostgreSQL to work away on getting their many features more efficient than it is for MySQL to add some of the features it lacks reliably. This isn't really a solid reason, but now that MySQL has been taken over by Sun I'm also not sure of their future direction. I've seen first hand how Sun have managed takeovers before, particularly in the case of Cobalt, and it doesn't inspire confidence. Sun always seem to pay over the odds for companies that don't really fit well with them or their current customers and market.

Additionally, I'm also pushing (begging on occasion) for one of our major clients (no names!) to update a system from using Access..............to just about anything else. They're not swallowing this at the moment because the current system works just about OK with the hacks we've put into it (we only keep a few days of the most recent data in the main database and then back up to another) and we get the odd corruption. This is a hangover from how the system was initially developed via another third-party. MTS was going to be used, and this was then dispensed with as they discovered they were trying to store stateful objects in it and the thing died, and then they had all the clients connecting directly to the Access database over a network share as a last resort. Surprisingly, with a few clients this seems to work, although Access 97 seems to be far better at concurrent connections than any later versions. The performance, as you can imagine, is terrible and getting meaningful data out of these databases ,which are kept on different sites, is a nightmare.

I wouldn't care, but this is a financial system! It just shows how temporary hacks that just about work turn into permanent solutions. Postgres would be an absolutely ideal replacement for this, would increase performance markedly, we'd have no corruption trouble, we could introduce proper transactions which would help enormously, we wouldn't need to back up data into another Access database (some of which are now creaking under the stress of their size and historical data), it's far easier to get access to the databases over a leased line with limited bandwidth, it would make support and new systems development far easier for us - and then there's the cost. The rest of the organisation pretty much uses Oracle, and installing and configuring Oracle at all these remote sites would be a really significant cost in terms of licensing and support for the Oracle DBAs.

I've read a lot of arguments about how Postgres is not suitable as a replacement for Oracle, but for all but the really, really, really high end features that pretty much no one uses, it's actually an improvement. I laughed at the comments in that article about editing the pg_hba.conf file being unintuitive (most things in Oracle are unintuitive) and crashing under extreme load. The first time I saw Oracle 9i and 10g start on a Windows box I was shocked. It took a full two or three minutes for it to come up, and that's when there was no data running on it yet. You needed a good gigabyte of memory on the server before it did anything. In contrast, the Windows ports of Postgres and MySQL start up in no time and take up far less resources than any Oracle installation.

I've been really impressed with how much Postgres has improved through the 8.x cycle, and the performance ideas and improvements that has come through such as asynchronous commit, autovacuum is now on by default in 8.3, full text search, lots of procedural language support and a native Windows port that is now truly excellent. These kind of steady improvements increase my confidence in Postgres still further.