MySQL vs PostgreSQL: The Architect’s Dilemma for 2010

It has been a turbulent year for the open-source community. Since Oracle finalized its acquisition of Sun Microsystems earlier this year, I’ve sat in countless meetings with CTOs in Oslo and Bergen asking the same nervous question: "Is MySQL still safe?"

While forks like Drizzle and the fledgling MariaDB are interesting experiments, the immediate choice for production systems remains a binary one: do you stick with the ubiquity of MySQL 5.1, or do you finally make the jump to the strictly ACId-compliant PostgreSQL, especially with version 9.0 dropping just this week?

I have managed backend infrastructure for high-traffic Norwegian news sites and e-commerce platforms. I have seen MyISAM tables corrupt under load, and I have seen PostgreSQL save data that would have otherwise vanished. Here is the technical reality of choosing your database engine in late 2010.

The Case for MySQL: Read Speed and Ubiquity

MySQL is the default for a reason. It is the 'M' in LAMP. If you are running Drupal, Joomla, or WordPress, it is what the software expects. Its replication is simple to set up, and its read performance on simple queries is blistering.

However, the default storage engine, MyISAM, is a relic. It relies on table-level locking. If you have a long-running write operation, every read request queues up behind it. Your site hangs. The solution is InnoDB, which offers row-level locking and transactions.

Sysadmin Note: If you are running MySQL 5.1 on a VPS, stop what you are doing. Check your my.cnf. If you haven't enabled innodb_file_per_table, you are going to end up with a massive, unshrinkable ibdata1 file that will eat your disk space. Fix it now.

The Case for PostgreSQL: Data Integrity and Features

For years, the knock against PostgreSQL was that replication was a nightmare, usually requiring Slony-I. That changed literally days ago. PostgreSQL 9.0 has introduced built-in streaming replication and Hot Standby. This is massive.

PostgreSQL creates a strict environment. It validates data types rigorously. If you try to insert a string into an integer field, MySQL (depending on strict mode) might truncate it and warn you; PostgreSQL will throw an error and reject the transaction. For financial applications or systems handling sensitive user data subject to the Personopplysningsloven (Personal Data Act), this rigidity is a feature, not a bug.

Configuration Showdown

The default configurations for both databases are laughably conservative, seemingly tuned for a machine with 256MB of RAM. When deploying on a modern CoolVDS instance with dedicated RAM, you must tune these values.

MySQL (my.cnf):

[mysqld]
# Allocating 70-80% of RAM for InnoDB is standard on a dedicated DB server
innodb_buffer_pool_size = 2G
innodb_flush_log_at_trx_commit = 1  # Set to 2 for speed, 1 for ACID safety
query_cache_size = 64M

PostgreSQL (postgresql.conf):

# Shared buffers is usually set to 25% of RAM, letting the OS cache handle the rest
shared_buffers = 512M
effective_cache_size = 1536M
work_mem = 16M
checkpoint_segments = 32

The Hidden Bottleneck: Disk I/O

You can tweak buffers all day, but databases live and die by Disk I/O. This is where the hosting platform matters more than the software.

In a virtualized environment, "Noisy Neighbors" are the enemy. If another VM on the same physical host is thrashing the disk, your database latency spikes. This is why we avoid OpenVZ containers for serious database workloads at CoolVDS. We utilize KVM (Kernel-based Virtual Machine) hardware virtualization.

With KVM, your RAM is reserved, and we can better isolate I/O operations. For high-performance clusters, we are deploying RAID-10 arrays of 15k RPM SAS drives and beginning to roll out Enterprise SSD storage in select zones. The difference in random read/write operations (IOPS) between a standard SATA drive and a SAS RAID array is the difference between your site loading in 0.5 seconds or 5 seconds during peak traffic.

Latency and Sovereignty

If your users are in Norway, physics dictates that your database should be too. Hosting in the US or even Germany adds milliseconds to every round-trip. For a complex application performing 50 SQL queries per page load, 40ms of latency becomes 2 seconds of waiting.

CoolVDS infrastructure is peered directly at the NIX (Norwegian Internet Exchange) in Oslo. Furthermore, keeping data within Norwegian borders simplifies compliance with Datatilsynet regulations regarding the handling of personal information.

The Verdict

• Choose MySQL (InnoDB) if: You are running standard CMS software (WordPress/Joomla), your team knows it well, and your workload is 90% reads.
• Choose PostgreSQL 9.0 if: You are building a custom application, you require complex queries (JOINs, Views, Triggers), you need Geospatial data (PostGIS), or data integrity is paramount.

Whatever engine you choose, do not let it starve on shared hosting. A database needs dedicated resources.

Ready to test the new PostgreSQL 9.0 replication? Deploy a high-performance KVM instance on CoolVDS today and get root access in under 2 minutes.