PostgreSQL 8.4 vs. MySQL 5.1: The Battle for Your Data
It is August 2009. The tech world is holding its breath as Oracle moves to acquire Sun Microsystems. For those of us managing critical data, this creates a distinct uneasiness about the future of MySQL. If you are building a new web application in Norway today, the default choice has always been the "M" in the LAMP stack. But with the release of PostgreSQL 8.4 last month, the equation has changed.
I have spent the last week migrating a high-traffic e-commerce client from a crumbling shared host to a proper VPS. Their database was a mess of corrupted MyISAM tables. This brings us to the core debate: Do you stick with the speed and ubiquity of MySQL 5.1, or do you move to the feature-rich, ACID-compliant fortress that is PostgreSQL 8.4?
The Case for MySQL 5.1: Speed vs. Safety
MySQL is everywhere. It is the engine behind Facebook and Wikipedia. However, out of the box, most distributions (like Debian Lenny or CentOS 5) still default to the MyISAM storage engine. MyISAM is incredibly fast for read-heavy workloads, but it has a fatal flaw: table-level locking.
If you have a script writing to a table, no one else can read from it until that write is finished. On a CoolVDS high-performance slice, you notice this immediately when your traffic spikes. The CPU isn't the bottleneck; the database locks are.
Pro Tip: If you use MySQL, stop using MyISAM. Switch to InnoDB. It supports row-level locking and transactions. But you must tune your my.cnf or it will crawl.
Essential InnoDB Tuning for 2009
On a server with 4GB RAM (a common high-end VPS config), the default settings are laughable. You need to allocate RAM to the buffer pool to reduce disk I/O.
[mysqld]
# Default is often just 8M. Crank this up.
innodb_buffer_pool_size = 2G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2 # 1 for ACID safety, 2 for speed
The Challenger: PostgreSQL 8.4
Released just weeks ago, PostgreSQL 8.4 is turning heads. It finally includes features that enterprise DBAs have been screaming for, like Window Functions and Common Table Expressions (CTEs). It is not just an alternative anymore; it is superior technology.
Unlike MySQL, Postgres is strictly ACID compliant by default. It doesn't let you insert invalid dates (no more 0000-00-00). In a banking or enterprise environment, this data integrity is non-negotiable. Furthermore, under high concurrency, Postgres often outperforms MySQL because it handles multi-version concurrency control (MVCC) better than MySQL's locking mechanisms.
| Feature | MySQL 5.1 (InnoDB) | PostgreSQL 8.4 |
|---|---|---|
| licensing | GPL (Oracle/Sun owned) | BSD (Community) |
| Joins | Nested Loop only | Hash, Merge, Nested Loop |
| Subqueries | Often poor performance | Excellent optimization |
The Hardware Reality: Disk I/O is King
Whether you choose MySQL or Postgres, your database performance will live or die by your disk subsystem. In 2009, many hosting providers are still cramming users onto single SATA drives. When the drive seeks, your site hangs.
This is where architecture matters. At CoolVDS, we don't oversell. We run our virtualization clusters on 15k RPM SAS drives in RAID-10 arrays. The Input/Output Operations Per Second (IOPS) on SAS RAID-10 destroys standard SATA setups. If you are doing serious database work, do not settle for standard storage.
Legal Compliance in Norway
We must also address the legal aspect. With the Personopplysningsloven (Personal Data Act of 2000) and the vigilant eye of Datatilsynet, where your data physically sits matters. Hosting your database in the US (like on AWS EC2) introduces latency and potential legal grey areas regarding EU Directive 95/46/EC.
Keeping your database on a VPS in Oslo ensures low latency to the NIX (Norwegian Internet Exchange) and compliance with local data retention laws. Latency within Norway on our network is typically under 10ms.
Verdict: Which One?
If you are running a simple WordPress blog or a forum (phpBB), MySQL 5.1 is fine. It is simple, and your CMS probably supports it best.
However, if you are building a custom application, a financial system, or anything requiring complex joins and data integrity, PostgreSQL 8.4 is the professional choice. The learning curve is steeper, but the stability is worth it.
Ready to compile your stack? Don't let slow I/O kill your queries. Deploy a CoolVDS instance with high-speed SAS storage today and see the difference raw power makes.