MySQL vs PostgreSQL: The Architect's Dilemma in 2012

The 3:00 AM Wake-Up Call

It is the sound no sysadmin wants to hear. The vibration of a phone on a nightstand at 3:00 AM. Your master database has locked up. Again. If you are running a high-traffic Magento store or a custom SaaS application targeting the Nordic market, you know the drill. The iowait is spiking, the connection pool is exhausted, and the web server is throwing 502 Bad Gateway errors.

In 2012, the landscape of relational databases is shifting beneath our feet. Oracle's acquisition of Sun Microsystems has cast a long shadow over MySQL, driving many of us toward Percona Server or the emerging MariaDB. Meanwhile, PostgreSQL has shaken off its academic reputation with the release of 9.1 and the brand new 9.2, finally offering performance that rivals its simpler cousin.

I have spent the last decade architecting backends for everything from Oslo-based media houses to financial tech startups in Stavanger. The question is never just "which database is better?" It is about which database survives the traffic spike without corrupting your data.

The Case for MySQL (and Percona)

MySQL is the devil we know. It is everywhere. It is the 'M' in LAMP. If you are deploying a standard CMS like WordPress or Drupal, you use MySQL. Period. But for custom applications, the decision is harder.

With the default MyISAM engine effectively dead for serious transactional work, InnoDB is the standard. It provides ACID compliance and row-level locking. However, the default configuration on most Linux distributions (CentOS 6, Debian 6) is laughable. It is tuned for a server with 512MB of RAM, not the modern multi-core VPS instances we use today.

Pro Tip: Never trust the defaults. The single most important setting for MySQL performance is the buffer pool size. It should be 70-80% of your available RAM on a dedicated database node.

Here is a snippet from a production my.cnf I deployed last week for a high-traffic e-commerce site hosted on CoolVDS:

[mysqld]
# Basic Settings
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql

# InnoDB Tuning for 8GB RAM Instance
innodb_buffer_pool_size = 6G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2  # Trade slight durability for massive speed
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1

# Connection Handling
max_connections = 500
thread_cache_size = 50
query_cache_type = 0  # Disable query cache on high write loads
query_cache_size = 0

Notice innodb_flush_log_at_trx_commit = 2. This is a controversial setting. Strictly speaking, you lose ACID compliance because you might lose one second of transactions if the OS crashes. But if you are fighting I/O bottlenecks on a spinning HDD, this setting can save your life. Of course, if you are on CoolVDS SSD RAID10 storage, you can leave this at 1 (full ACID) and still get blazing fast commits.

The Case for PostgreSQL

If MySQL is a Honda Civic—reliable, tunable, and ubiquitous—PostgreSQL is a Volvo truck. It is built for heavy lifting. Historically, it was slower. That is no longer true in 2012. With the release of PostgreSQL 9.2 just this month, we are seeing linear scalability up to 64 cores.

PostgreSQL shines where data integrity is paramount. If you are handling financial data under the Norwegian Personal Data Act (Personopplysningsloven), you need constraints that actually work. MySQL often lets you insert 0000-00-00 into a date field or silently truncates strings. Postgres throws an error.

Furthermore, PostgreSQL 9.2 introduces native JSON support (albeit basic). This is the bridge to the NoSQL world without abandoning SQL.

To get Postgres running smoothly, we need to adjust shared memory settings. Linux kernels often default to very low shared memory limits. You might see an error like this:

To fix this, you need to edit /etc/sysctl.conf before even starting the DB:

# /etc/sysctl.conf tuning for PostgreSQL
kernel.shmmax = 68719476736
kernel.shmall = 4294967296

# Optimize network stack for high throughput
net.ipv4.tcp_window_scaling = 1
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216

Performance Benchmarks: Sysbench

I recently ran sysbench 0.4.12 against both engines on a standard KVM VPS. The workload was OLTP (Online Transaction Processing). The results were illuminating. While MySQL 5.5 had a slight edge in simple read-only queries, PostgreSQL 9.1 maintained consistent throughput under heavy write concurrency, whereas MySQL's throughput jittered significantly once the table grew larger than the buffer pool.

Here is the command I used for the test:

And the run command:

The Storage Bottleneck

Here is the brutal truth: Your database configuration matters less than your disk I/O.

You can tune checkpoint_segments in Postgres or innodb_io_capacity in MySQL all day long. If your VPS is sitting on an overloaded SAN with spinning disks, your database will crawl. Database performance is I/O performance.

This is where virtualization technology becomes critical. Many budget providers use OpenVZ. In OpenVZ, you are sharing the kernel with every other customer on the node. If a neighbor decides to run a massive backup script, your database latency spikes. This