MySQL 5.5 vs. PostgreSQL 9.2: The Architectural Battle for Your Data

Stop me if you've heard this one before: A developer deploys a schema change on a Friday afternoon. The ALTER TABLE statement hangs. The site goes down. You spend your weekend restoring from a dump because MySQL doesn't support transactional DDL. If you are still running mission-critical workloads on default MySQL configurations in late 2012, you are playing Russian Roulette with your data availability.

The landscape of database hosting has shifted dramatically this year. With the recent release of PostgreSQL 9.2 in September and the maturation of MySQL 5.5 (finally making InnoDB the default), the choice isn't just about "ease of use" anymore. It's about data integrity versus raw read speed. It's about whether you trust your data to a storage engine that silently truncates strings or one that screams at you when you violate a constraint.

As a systems architect working with high-traffic Norwegian e-commerce sites, I've seen servers melt under the load of holiday traffic. I've seen MyISAM tables corrupt after a hard power cycle. Today, we aren't just comparing software; we are comparing philosophies.

The MySQL 5.5 Approach: Speed, Ubiquity, and the InnoDB Revolution

For years, MySQL was the wild west. Defaulting to MyISAM meant you had fast reads but disastrous table-level locking on writes. If one user wrote to the `users` table, everyone else waited. In 2012, if you are not using InnoDB, you are doing it wrong.

MySQL 5.5 brought us substantial performance gains on multi-core CPUs—essential as we start seeing more powerful 8-core nodes in our CoolVDS infrastructure. The biggest win is the scalability of the InnoDB buffer pool.

Optimizing MySQL 5.5 for SSD Storage

With Solid State Drives (SSDs) becoming more accessible in the enterprise hosting market, your I/O constraints are changing. You are no longer limited by the seek time of a spinning 15k RPM SAS drive. However, MySQL needs to be told to use this power. A standard `my.cnf` file in CentOS 6 is woefully inadequate.

Here is the configuration I deploy for high-traffic LAMP stacks running on CoolVDS SSD instances:

[mysqld]
# The most critical setting. Set to 70-80% of total RAM on a dedicated DB server.
innodb_buffer_pool_size = 4G

# Essential for SSD performance. Increases I/O capacity.
innodb_io_capacity = 2000

# Stop the system tablespace bloat. 
innodb_file_per_table = 1

# ACID compliance. Set to 2 for better performance if you can tolerate 
# 1 second of data loss, but strict banking apps must use 1.
innodb_flush_log_at_trx_commit = 1

# utilization of multi-core processors
innodb_read_io_threads = 4
innodb_write_io_threads = 4

PostgreSQL 9.2: The "Oracle Killer" Comes of Age

While MySQL runs the web, PostgreSQL runs the business. The release of version 9.2 has been a revelation. We finally have Cascading Replication and, crucially, Index-Only Scans. This allows Postgres to retrieve data directly from the index without touching the heap, significantly closing the read-performance gap with MySQL.

But the real killer feature for developers in 2012 is hstore (and the nascent JSON support appearing in 9.2). With the NoSQL hype train screaming about MongoDB, Postgres quietly allows you to store schema-less data inside a strict relational database. You get the flexibility of a document store with the ACID guarantees of a bank vault.

A War Story: The Silent Failure

In a recent project for a client in Oslo, we migrated a legacy payment system from MySQL to Postgres. Why? Because MySQL (prior to strict mode enforcement) would happily insert `0000-00-00` into a DATE column if the input was invalid. Postgres refused the transaction. That strictness saved the client thousands of Kroner in billing errors during a Datatilsynet audit. Correctness is a feature.

Here is how you tune `postgresql.conf` for a modern Linux kernel (2.6.32+):

# 25% of RAM is a good starting point for shared_buffers.
# The OS cache handles the rest.
shared_buffers = 2GB

# Write Ahead Log tuning. 
wal_buffers = 16MB
checkpoint_segments = 32
completion_target = 0.9

# Allow queries to use more RAM for sorting before spilling to disk.
work_mem = 64MB
maintenance_work_mem = 512MB

# Effective IO concurrency for SSD arrays
effective_io_concurrency = 2

Latency, Virtualization, and The "Noisy Neighbor" Problem

Database performance is inextricably linked to the underlying storage and virtualization technology. This is where the "Managed Hosting" label often hides ugly truths. Many budget providers in Europe use OpenVZ (container-based virtualization). In an OpenVZ environment, the kernel is shared. If your neighbor decides to compile a massive kernel, your MySQL latency spikes because CPU time is stolen.

Furthermore, databases are I/O hungry. On spinning rust (HDD), a random read operation takes 5-10 milliseconds. On the pure SSD arrays we deploy at CoolVDS, that drops to sub-millisecond latencies. This matters immensely when you are performing complex `JOIN` operations across millions of rows.

Legal & Reliability Context: Serving Norway

Operating in Norway adds another layer: compliance. Under the Personal Data Act (Personopplysningsloven), you have a responsibility to secure user data. Data integrity—ensuring a backup actually restores correctly—is part of that legal obligation. Postgres's Write Ahead Log (WAL) architecture is arguably more robust against corruption during power failures than MyISAM, and rivals InnoDB.

For Norwegian businesses, network latency to the continent can also be a bottleneck. Hosting your database in a datacenter in Dallas or even London adds millisecond penalties to every query round-trip. Using a local high-performance VPS ensures your application feels instant to users in Oslo and Bergen.

Conclusion: Choose Your Weapon

If you are running a standard WordPress blog or a read-heavy Drupal site, MySQL 5.5 on a well-tuned stack is efficient and familiar. It is the path of least resistance.

However, if you are building a custom application, handling financial transactions, or require complex geospatial queries, PostgreSQL 9.2 is the superior engineering choice. It forces you to write better code.

Regardless of your engine choice, a database is only as fast as the disk it sits on. Don't let IOwait kill your application's responsiveness. Test your schema on a platform designed for heavy I/O.

Ready to ditch the spinning rust? Deploy a CoolVDS SSD instance today and see your query times drop.