MySQL vs PostgreSQL: The Architect's Dilemma in 2012

Let’s be honest: the database landscape in 2012 is a mess of FUD (Fear, Uncertainty, and Doubt). Since Oracle acquired Sun Microsystems, the open-source community has been holding its breath. We've seen the rise of MariaDB as a fork, and we've seen NoSQL solutions like MongoDB making a lot of noise about being "web scale."

But for those of us managing critical infrastructure—whether it's an e-commerce platform targeting Oslo consumers or a banking backend adhering to Norwegian compliance—relational databases (RDBMS) are still the bedrock. The question isn't "NoSQL or SQL?" It is, and remains: MySQL or PostgreSQL?

I have spent the last six months migrating high-traffic workloads from spinning rust to SSD-backed architectures, and the behavior of these two databases under I/O pressure is night and day. Here is the battle-hardened reality of choosing your backend in late 2012.

The Architecture: Threads vs. Processes

This is the fundamental difference that dictates how you scale.

MySQL is a multi-threaded daemon. When a client connects, it spawns a thread within the same process space. This is lightweight. Context switching is fast. On a standard CentOS 6 server with limited RAM, MySQL 5.5 is forgiving.

PostgreSQL, on the other hand, is process-based. Every connection forks a new process. In the old days, this was heavy. In 2012, with modern Linux kernels (2.6.32+) and copy-on-write memory management, it's less of an issue, but it still requires strict connection pooling. If you expose a Postgres instance directly to the web without something like pgbouncer, you will exhaust your memory fast.

The "War Story": The Magento Meltdown

Last month, a client came to us with a Magento store crashing during peak hours. They were running MySQL with the MyISAM engine (the default pre-5.5). MyISAM relies on table-level locking. Every time a customer wrote to the cart, the entire table locked. Read operations queued up. The server load spiked to 50+.

We migrated them to CoolVDS, switched the engine to InnoDB (row-level locking), and tuned the buffer pool. The result? Load dropped to 0.4. But if they had chosen PostgreSQL, we might have avoided the locking hell entirely, at the cost of more complex configuration.

Configuration Smackdown: Tuning for Performance

Out of the box, both databases are configured for computers from 2002. If you apt-get install or yum install them today, they will barely use your RAM. You have to tune them.

MySQL 5.5 Tuning (InnoDB Focus)

In MySQL 5.5, InnoDB is finally the default, but you need to ensure your my.cnf reflects your hardware. If you are on a CoolVDS plan with 8GB RAM, do not leave the defaults.

[mysqld]
# Allocate 70-80% of RAM to buffer pool on a dedicated DB server
innodb_buffer_pool_size = 6G

# Crucial for data integrity vs speed trade-off
# 1 = safest (fsync every commit), 2 = faster (fsync every second)
innodb_flush_log_at_trx_commit = 1

# Separate table files usually manage disk space better
innodb_file_per_table = 1

# CPU efficiency
innodb_thread_concurrency = 0

PostgreSQL 9.1/9.2 Tuning

Postgres assumes the OS handles file caching. This is a distinct philosophy. You need to tell Postgres how much RAM the OS has available for caching via effective_cache_size.

# postgresql.conf

# Only 25% of RAM usually. Postgres relies on OS cache.
shared_buffers = 2GB

# Tell the query planner how much file cache is available
effective_cache_size = 6GB

# Memory per operation (sorts, hashes). Watch out: this is per connection!
work_mem = 32MB

# Write Ahead Log (WAL) settings for heavy write loads
wal_buffers = 16MB
checkpoint_segments = 32

Feature Watch: JSON is coming

Here is where things get interesting. Released just this month (September 2012), PostgreSQL 9.2 has introduced native JSON support. This is a direct shot across the bow of MongoDB. While MySQL is strictly relational, Postgres is evolving into a hybrid beast. If your developers are begging for schema-less data storage but you refuse to give up ACID compliance, Postgres 9.2 is currently the only serious contender.

The Storage Bottleneck: Why SSDs Matter

You can tune sysctl.conf all day, but if your disk I/O is saturated, your database is dead. This is particularly true for database servers where random read/write operations (IOPS) are high.

Traditional SATA drives deliver maybe 100-150 IOPS. Under heavy join operations, the disk head is thrashing physically. This adds latency—latency that kills user experience.

Pro Tip: Check your I/O wait with iostat -x 1. If your %util is consistently near 100% while CPU is idle, your disk is the bottleneck. No amount of RAM will fix bad storage.

This is why at CoolVDS, we have standardized on enterprise SSD storage for our KVM instances. We see IOPS in the thousands, not hundreds. When a complex SQL query hits the disk, the response time is negligible. For a database hosted in Norway, where you are already fighting the speed of light to reach users in Bodø or Tromsø, you cannot afford disk latency on top of network latency.

Data Integrity & Norwegian Law

We operate under the Personal Data Act (Personopplysningsloven) of 2000. Datatilsynet (The Norwegian Data Protection Authority) is very clear about the responsibility of data handlers.

PostgreSQL has a reputation for being stricter about data integrity. It will refuse valid data if it violates a constraint, whereas MySQL (unless running in strict mode) might truncate data to make it fit. In a compliance scenario, silent data truncation is a nightmare.

Furthermore, hosting on servers physically located in Norway (like CoolVDS's Oslo zone) simplifies compliance with the Data Protection Directive (95/46/EC). You don't have to worry about Safe Harbor frameworks if the data never leaves the EEA.

Verdict: Which one to choose?

If you are running a standard WordPress or Drupal site, MySQL is ubiquitous. It is fast, tools like phpMyAdmin make it easy to manage, and it plays nice with limited resources.

However, if you are building a custom application that requires complex joins, transactional integrity, or the new JSON capabilities, PostgreSQL is the superior technical choice in 2012.

Whatever you choose, remember that a database is only as fast as the hardware underneath it. Don't let IOPS wait times kill your application's performance. Deploy a test instance on a CoolVDS SSD KVM server today and see how fast your queries should be running.