MySQL vs PostgreSQL: The 2012 Architect's Guide to Database Selection

Let's be honest. Ever since Oracle acquired Sun Microsystems back in 2010, the open-source community has been holding its breath. Is MySQL going to die? Is MariaDB the future? Meanwhile, PostgreSQL has been quietly shedding its "academic project" reputation and turning into a production beast. I've spent the last week migrating a high-traffic e-commerce cluster from a spinning-rust dedicated server in Germany to a local setup here in Oslo, and the difference in database behavior is night and day.

If you are deploying on CoolVDS today, you aren't limited by the mechanical seek times of traditional HDDs. We are talking about pure SSD storage here. That changes how we tune our buffers, our write-ahead logs, and ultimately, which database engine we pick. Let's cut through the FUD (Fear, Uncertainty, and Doubt) and look at the technical reality of May 2012.

The Fundamental Architecture: Threads vs. Processes

This is where the religious wars usually start. MySQL uses a multi-threaded architecture. Every connection is a thread within the same process. It’s lightweight, it’s fast at context switching, and it’s generally lighter on memory for massive numbers of idle connections.

PostgreSQL uses a multi-process architecture (using fork()). Each connection is a separate OS process. Historically, this made Postgres heavy. But in 2012, with RAM becoming cheaper and Linux kernels (like the 3.2 kernel in the new Ubuntu 12.04 LTS) getting better at memory management, this penalty is negligible for most workloads—provided you use a connection pooler like pgBouncer.

Pro Tip: Never expose a raw PostgreSQL port to the internet without a connection pooler if you expect thousands of concurrent users. The fork overhead will kill your CPU context switching. On CoolVDS instances, we recommend installing pgBouncer on the same node to keep latency over the loopback interface near zero.

Storage Engines: The MyISAM Trap

If you are still running MyISAM tables in MySQL 5.5 because "it's faster for reads," stop. Just stop. Table-level locking is a disaster for concurrency. If one user writes to the session table, every other user on your site waits.

The standard now is InnoDB. It supports row-level locking, foreign keys, and genuine ACID compliance. However, tuning InnoDB for the SSD storage we use at CoolVDS requires specific flags in your my.cnf. You need to tell the database that it doesn't need to be gentle on the disk.

Optimizing MySQL 5.5 for SSD

Here is the configuration I drop into /etc/mysql/my.cnf for a 4GB RAM VPS running a Magento store:

[mysqld]
# Use 70-80% of available RAM for the pool
innodb_buffer_pool_size = 3G

# The default is 4MB, which is too small for heavy logs
innodb_log_file_size = 256M

# Crucial for SSD! Disable the neighbor page flushing meant for HDDs
innodb_flush_neighbors = 0

# Push the IO limits. SSDs can handle it.
innodb_io_capacity = 2000

The innodb_flush_neighbors = 0 setting is vital. On a mechanical drive, it makes sense to write adjacent data blocks together to minimize head movement. On the flash storage used by CoolVDS, seek time is virtually zero (0.1ms). Grouping writes just wastes CPU cycles.

PostgreSQL 9.1: The Integrity King

PostgreSQL 9.1, released late last year, introduced Synchronous Replication. This is massive for anyone dealing with financial data or strict SLAs. In the past, if your master crashed, you might lose the last few milliseconds of transactions. With Sync Rep, the transaction isn't confirmed to the client until it's written to the standby.

Why choose Postgres in 2012?

• Complex Queries: If you use JOINs across 5+ tables, the Postgres query optimizer generally beats MySQL's optimizer hands down.
• Reliability: I have seen MySQL silently truncate data if a string exceeds the column length (unless you run strict SQL mode). Postgres will throw an error. It forces you to write better code.
• Extensions: HStore is gaining traction as a way to store key-value pairs within a relational DB—our answer to the MongoDB hype without losing ACID compliance.

Tuning PostgreSQL for Linux

Postgres defaults are notoriously conservative. Open /etc/postgresql/9.1/main/postgresql.conf and change these immediately:

# 25% of total RAM is a good starting point
shared_buffers = 1GB

# Effective cache size tells the planner how much RAM the OS has available for caching
# Set this to 75% of total RAM
effective_cache_size = 3GB

# For SSDs, random reads are cheap. Lower this cost.
random_page_cost = 1.1

# Maintenance work mem speeds up autovacuum and index creation
maintenance_work_mem = 256MB

Latency, IOPS, and The Norwegian Context

Why does hosting location matter? Speed of light. If your customers are in Oslo or Bergen, hosting in the US adds ~120ms of latency. For a database-heavy application doing 10 sequential queries to render a page, that's 1.2 seconds of waiting just on network overhead.

At CoolVDS, our racks are connected directly to the NIX (Norwegian Internet Exchange). We see pings of 2-5ms to local ISPs like Telenor and Altibox. Furthermore, strict adherence to the Personopplysningsloven (Personal Data Act) means keeping data within national borders is becoming a compliance necessity, not just a technical preference. With the Data Protection Directive getting stricter, you don't want your user data sitting on a server in a jurisdiction with loose privacy laws.

Benchmarking I/O Wait

Regardless of whether you choose MySQL or Postgres, your bottleneck will eventually be Disk I/O. When your dataset exceeds your RAM, the server starts swapping pages to disk.

Use iostat (part of the sysstat package on CentOS/Debian) to watch your storage health:

# Install on Debian/Ubuntu
apt-get install sysstat

# Monitor disk every 2 seconds
iostat -x 2

Look at the %util column. If it's constantly near 100%, your disk queue is full. On a traditional VPS provider using shared HDDs, this happens constantly because of "noisy neighbors." Because CoolVDS isolates I/O performance on enterprise-grade SSDs, you can actually push your database to the limit of the CPU, rather than waiting on the disk.

The Verdict

Choose MySQL if: You are running WordPress, Joomla, or Drupal. The ecosystem is built for it, and InnoDB is robust enough for 99% of web workloads.

Choose PostgreSQL if: You value data integrity above all else, you need complex geospatial queries (PostGIS is unrivaled), or you are building a custom application where data types matter.

Whichever engine you choose, remember that a database is only as fast as the storage underneath it. Don't let IO wait kill your query performance. Deploy a high-performance SSD instance on CoolVDS today and see what 0.1ms access times do for your page loads.