PostgreSQL 9.1 vs MySQL 5.5: The Architect’s Dilemma in 2012

Stop me if you've heard this one before: a startup CTO picks MySQL because "it's what Facebook uses," only to wake up three months later with corrupted foreign keys and a replication slave that's drifting five minutes behind master. Conversely, the enterprise architect insists on PostgreSQL for its academic purity, but then wonders why simple SELECT COUNT(*) queries are eating up all the RAM on the web nodes.

It is February 2012, and the database landscape has shifted beneath our feet. With Oracle's acquisition of Sun Microsystems now fully settled, the community is nervous. Forking projects like MariaDB are gaining traction, but in production environments, the choice usually boils down to the two giants: MySQL 5.5 (now with InnoDB as default) and PostgreSQL 9.1 (with its shiny new synchronous replication).

I have spent the last week benchmarking both engines on our CoolVDS high-performance storage clusters here in Oslo. The results were not what I expected. If you are building for the Nordic market, where latency to NIX (Norwegian Internet Exchange) is measured in single-digit milliseconds, your database choice is the single biggest bottleneck in your stack.

The MySQL 5.5 Argument: Raw Read Speed

MySQL has long been the champion of read-heavy workloads. If you are running a content site, a blog, or a forum (like vBulletin or IP.Board), MySQL is often the default for a reason. With version 5.5, Oracle finally made InnoDB the default storage engine, replacing the archaic MyISAM. This means we finally get ACID compliance and row-level locking out of the box without hacking the configuration.

However, default configurations in MySQL are still woefully conservative. If you are deploying on a CoolVDS instance with 4GB+ RAM, the defaults will strangle your performance. You need to look at /etc/my.cnf immediately.

Critical MySQL 5.5 Tuning

The most common mistake I see on client servers is leaving the innodb_buffer_pool_size at the default 128MB. On a dedicated database node, this should be 70-80% of your available RAM.

[mysqld]
# /etc/my.cnf optimized for 4GB RAM VPS

# The most important setting for InnoDB performance
innodb_buffer_pool_size = 3G

# Stop the disk thrashing on commit
# 1 = safest (ACID), 2 = faster (risk of 1 sec data loss on OS crash)
innodb_flush_log_at_trx_commit = 2

# utilizing multi-core CPUs effectively
innodb_read_io_threads = 4
innodb_write_io_threads = 4

# Per-thread buffers - be careful not to set these too high!
sort_buffer_size = 2M
read_buffer_size = 2M

Pro Tip: If you are migrating a legacy MyISAM database to InnoDB on MySQL 5.5, remember that InnoDB requires significantly more disk space and RAM to achieve similar read speeds. Ensure your underlying storage can handle the random I/O. At CoolVDS, our storage backend is optimized for these high-IOPS scenarios, preventing the "iowait" death spiral common on budget hosting.

The PostgreSQL 9.1 Argument: Data Integrity & Complexity

PostgreSQL has always been the "grown-up" database. It supports complex queries, common table expressions (CTEs), and true serializable isolation levels that MySQL 5.5 still struggles with. The release of 9.1 in late 2011 brought us Synchronous Replication, a game-changer for financial applications where data loss is unacceptable.

In Norway, where the Personopplysningsloven (Personal Data Act) mandates strict controls over data integrity, PostgreSQL is often the safer legal choice. It doesn't silently truncate data if you try to insert a string that's too long; it throws an error. It forces you to be a better developer.

But it is hungry. PostgreSQL relies heavily on the OS for caching, meaning you need to tune the Linux kernel parameters alongside the database config.

Critical PostgreSQL 9.1 Tuning

Unlike MySQL, you don't give Postgres all your RAM. You give it to the OS file system cache. Here is a baseline for postgresql.conf on a CentOS 6 system:

# /var/lib/pgsql/9.1/data/postgresql.conf

# Keep this low (around 25% of RAM). 
# Postgres relies on the OS page cache.
shared_buffers = 1GB

# This tells the query planner how much RAM is actually available
# for disk caching. Set to 75% of total RAM.
effective_cache_size = 3GB

# Checkpoint segments - increase this for write-heavy loads
checkpoint_segments = 32
checkpoint_completion_target = 0.9

# Logging - vital for debugging slow queries
log_min_duration_statement = 250ms

Head-to-Head: The Decision Matrix

I ran a standard pgbench and sysbench suite against both engines on identical CoolVDS slices (2 vCPU, 4GB RAM, RAID-10 SAS Storage). Here is the breakdown:

The Infrastructure Factor: It's Not Just Software

You can tune your my.cnf until your fingers bleed, but if your host is overselling the physical CPU cores, your database will suffer from "noisy neighbor" syndrome. Database processes are extremely sensitive to CPU ready time (latency caused by waiting for the hypervisor to schedule the CPU).

This is why we architect CoolVDS differently. We use KVM (Kernel-based Virtual Machine) which is built directly into the Linux kernel (since 2.6.20). Unlike older container technologies like OpenVZ/Virtuozzo which share a single kernel, KVM allows us to isolate resources effectively. When you run a heavy JOIN operation on PostgreSQL, you are getting the dedicated compute cycles you paid for.

Data Sovereignty and Datatilsynet

For our Norwegian clients, physical location matters. Hosting your database on servers located in the US (under the Patriot Act) or even standard EU hubs can introduce legal gray areas regarding the processing of sensitive personal data. Keeping your database on CoolVDS infrastructure in Oslo ensures you are squarely under Norwegian jurisdiction and the oversight of Datatilsynet. Plus, the ping time from a standard Telenor fiber connection to our datacenter is often under 5ms.

Final Verdict

If you are migrating a legacy PHP application (WordPress, Joomla, Drupal), stick with MySQL 5.5. It is stable, predictable, and fast enough for 99% of web workloads. Just make sure you move off MyISAM tables immediately.

If you are building a new application in Python (Django) or Ruby (Rails), or if your data involves financial transactions, geospatial data (PostGIS is incredible), or complex analytics, PostgreSQL 9.1 is the superior choice. It requires more skill to manage, but it rewards you with bulletproof reliability.

Whichever engine you choose, do not let slow disk I/O be your bottleneck. Database performance lives and dies by disk latency.

Need a sandbox to test your replication setup? Spin up a CoolVDS high-performance instance in Oslo today and see the difference dedicated resources make for your database queries.