MySQL vs PostgreSQL: The Database Architect’s Dilemma in 2012

It has been two years since Oracle acquired Sun Microsystems, and the tremors are still being felt in server rooms across Oslo. The database landscape, once a quiet dominion of the LAMP stack, has fractured. On one side, we have the ubiquitous MySQL, now under corporate stewardship but fighting back with version 5.5. On the other, the academic heavyweight PostgreSQL is entering a golden age with version 9.1.

I have spent the last week migrating a high-traffic e-commerce cluster from a legacy dedicated server to a virtualized environment. The bottleneck wasn't CPU; it was disk I/O and locking contention. This forced a re-evaluation: is MySQL still the default, or has Postgres finally become the pragmatic choice for web applications?

The State of MySQL 5.5: Speed Meets Maturation

For years, MySQL was the speed demon that played loose with data integrity. MyISAM tables were fast but prone to corruption if a server crashed. With MySQL 5.5, InnoDB is finally the default storage engine. This is critical. We now have ACID compliance out of the box without hacking my.cnf extensively.

However, MySQL's replication is still asynchronous by default. If your master node fails before the binlog flushes to the slave, you lose transactions. For a blog, this is annoying. For a payment gateway, it is a career-ending event.

Optimizing InnoDB for Virtualized Hardware

Running MySQL 5.5 on a VPS requires specific tuning to avoid swapping, especially when you don't have direct control over the physical RAM. The most critical setting is the buffer pool.

# /etc/mysql/my.cnf
[mysqld]

# Set this to 70-80% of available RAM on a dedicated DB server.
# On a 4GB CoolVDS instance, we allocate 2.5G.
innodb_buffer_pool_size = 2560M

# Ensure transaction durability. 
# Set to 2 for higher performance if you can tolerate 1 second of data loss.
# Set to 1 for strict ACID compliance (essential for financial data).
innodb_flush_log_at_trx_commit = 1

# Use one file per table to reclaim disk space when dropping tables.
innodb_file_per_table = 1

Pro Tip: If you are still using MyISAM for read-heavy workloads, stop. The table-level locking in MyISAM will kill your concurrency under load. InnoDB's row-level locking is superior for almost every modern web application.

PostgreSQL 9.1: The "Enterprise" Open Source

PostgreSQL has historically been criticized for being slower and harder to configure. In 2012, that reputation is outdated. Version 9.1 introduced Synchronous Replication. This allows us to guarantee that a write is confirmed on at least one standby server before returning success to the client. This level of data safety used to cost six figures in Oracle licensing fees.

Postgres also shines with complex queries. Its query planner is significantly smarter than MySQL's when dealing with multiple JOINs or subqueries. Furthermore, if you are handling geospatial data, PostGIS is lightyears ahead of MySQL's spatial extensions.

Tuning Postgres for Linux

Out of the box, Postgres is configured for broad compatibility, effectively assuming it's running on a calculator. To make it fly on a modern Linux kernel (like the 2.6.32 kernel we use on CentOS 6), you need to adjust shared memory settings.

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

# Typically 25% of system RAM.
shared_buffers = 1024MB

# A hint to the query planner about how much RAM is available for disk caching.
# Set this to 75% of total RAM.
effective_cache_size = 3GB

# Write Ahead Log (WAL) settings for performance
wal_buffers = 16MB
checkpoint_segments = 32
checkpoint_completion_target = 0.9

You will also need to adjust your kernel's shared memory limits (`shmmax`) in `sysctl.conf` to accommodate larger `shared_buffers`, otherwise Postgres won't start.

The Hardware Factor: Why KVM and SSDs Matter

You can tune my.cnf all day, but if your underlying storage subsystem is slow, your database will crawl. This is where the hosting platform becomes part of your architecture.

Many VPS providers in Europe are still pushing OpenVZ containers on SATA drives. For a database, this is a trap. OpenVZ resources are often "burstable," meaning your RAM isn't guaranteed. If a neighbor abuses the node, your database process gets killed by the OOM (Out of Memory) killer.

At CoolVDS, we rely on KVM (Kernel-based Virtual Machine). KVM provides full hardware virtualization. When we allocate 4GB of RAM to your instance, it is locked to your kernel. No noisy neighbors stealing your buffer pool.

Data Sovereignty in Norway

We cannot ignore the legal reality. With the USA's Patriot Act causing concern regarding data privacy, hosting your database outside the EEA (European Economic Area) is becoming a liability. The Norwegian Data Inspectorate (Datatilsynet) enforces strict rules under the Personal Data Act.

Hosting your database on a US-controlled cloud means your data could technically be accessed by foreign authorities. By keeping your data on servers physically located in Oslo, connected directly to NIX (Norwegian Internet Exchange), you reduce latency to milliseconds for your Nordic users and simplify your compliance with Norwegian privacy laws.

Verdict: Which One to Choose?

If you are deploying a standard WordPress site or a simple forum, MySQL 5.5 remains the pragmatic choice. It is supported by everything, and the ecosystem of tools (like phpMyAdmin) is vast.

However, if you are building a custom application requiring complex transactions, strict data integrity, or geospatial features, PostgreSQL 9.1 is the superior technical solution.

Regardless of your engine choice, disk I/O will be your primary bottleneck. Don't let slow spinning rust kill your query performance. Test your database on a platform built for low latency.

Need raw database performance? Deploy a KVM instance on CoolVDS today and experience the difference of pure SSD storage.