The Great Database Schism: Choosing Your Backend in 2010
It has been a rough year for the open-source community. When Oracle finalized the acquisition of Sun Microsystems in January, a chill went down the spine of every SysAdmin managing a LAMP stack. Is MySQL still free? Will it survive? While forks like MariaDB are gaining traction, the uncertainty has forced many of us to look over the fence at the elephant in the room: PostgreSQL.
I have spent the last month migrating a high-traffic e-commerce client from a crumbling shared host to a dedicated CoolVDS instance in Oslo. The bottleneck wasn't PHP; it was the database locking up during backups. This forced a re-evaluation: Stick with the devil we know (MySQL) or migrate to the strict architect (PostgreSQL)?
The Case for MySQL 5.1 (and the InnoDB rise)
MySQL is the engine of the web. It is everywhere. If you are running Drupal, Joomla, or WordPress, you are likely using MySQL. Its replication setup (Master-Slave) is incredibly simple to configure compared to the competition.
However, the default storage engine, MyISAM, is a disaster for write-heavy applications due to table-level locking. If one user writes to the orders table, everyone else waits. The solution is InnoDB, which offers row-level locking and transactions (ACID compliance).
Pro Tip: If you are still running MySQL 5.0 or 5.1, stop using the defaults. Open your /etc/my.cnf and adjust the buffer pool to 70% of your available RAM if you use InnoDB exclusively:
[mysqld]
innodb_buffer_pool_size = 2G
innodb_flush_log_at_trx_commit = 2 # faster, slightly less safe
query_cache_size = 64M
The Case for PostgreSQL 8.4
PostgreSQL has always been the "academic" database. It respects data integrity above all else. While MySQL might silently truncate a long string to fit a VARCHAR, Postgres throws an error. In 2010, this strictness is a feature, not a bug.
With version 8.4 released last year, we finally got reasonable performance gains. It handles complex JOINs and subqueries significantly better than MySQL's optimizer. If your application requires complex reporting or geospatial data (PostGIS is lightyears ahead), Postgres is the only choice.
The downside? Replication. Setting up Slony-I for replication is painful. We are hearing rumors that the upcoming PostgreSQL 9.0 (expected later this month) will finally introduce built-in Hot Standby and Streaming Replication. Until then, MySQL wins on high-availability ease.
Head-to-Head: Latency and IOPS
We ran `pgbench` and `sysbench` on a CoolVDS Enterprise slice hosted in Oslo. We used a standard RAID 10 SAS array (15k RPM drives), which is the industry standard for performance hosting right now.
| Feature | MySQL 5.1 (InnoDB) | PostgreSQL 8.4 |
|---|---|---|
| Read Speed | Very Fast (Simple queries) | Fast (Complex queries) |
| Write Speed | Good (Row-locking) | Excellent (MVCC) |
| Data Integrity | Loose (unless strict mode) | Strict (ACID) |
| Replication | Native, Easy | Complex (Third-party tools) |
The Infrastructure Factor
Regardless of your database choice, disk I/O is usually the killer. You can tune your buffers all day, but if your disk queue length spikes, your site goes down. This is doubly true for those of us hosting in Norway where strict data laws (Personopplysningsloven) compel us to keep data within national borders.
This is why we architect CoolVDS differently. We don't oversell our storage backend. We use hardware RAID 10 controllers with battery-backed cache. When you commit a transaction in Postgres, it hits the disk controller cache instantly. This lowers the "iowait" time that plagues cheap VPS providers.
Conclusion: Which one?
If you are building a web application with simple reads and need easy scaling today, stick with MySQL 5.1 (using InnoDB). It is battle-tested and powers the largest sites on the internet.
If you are dealing with financial data, complex analytics, or cannot afford a single byte of data corruption, PostgreSQL 8.4 is superior. Just be prepared to spend more time on the replication setup.
Whatever you choose, don't run it on a slow disk. Test your database performance on a CoolVDS instance with low latency to NIX. Speed matters.