PostgreSQL 9.1 vs. MySQL 5.5: Stop Defaulting to the Wrong Database

I see it every day in the logs. A developer spins up a fresh CentOS 6 box, runs yum install mysql-server, and calls it a day. They don't look at the I/O wait. They don't verify ACID compliance. They just assume MySQL is the internet's default backend. In 2008, that was forgivable. Today, in April 2012, it is negligence.

We are currently seeing a massive shift in how data is handled on the Norwegian web. With the release of PostgreSQL 9.1 and the maturation of MySQL 5.5 (finally making InnoDB the default), the choice is no longer about "speed vs. features." It is about data integrity vs. read scalability. If you are running a high-traffic e-commerce site targeting Oslo or a complex banking application compliant with the Personopplysningsloven, choosing the wrong engine will cripple your stack before you serve your first request.

The MySQL 5.5 Case: The Read-Heavy Workhorse

MySQL is still the king of read-heavy workloads. If you are running a standard WordPress installation, Drupal, or a forum software like vBulletin, MySQL 5.5 is likely your best bet. The big news with 5.5 is that Oracle finally made InnoDB the default storage engine, replacing the archaic MyISAM. This means we finally get row-level locking and crash recovery out of the box without hacking my.cnf.

However, performance on a Virtual Private Server (VPS) depends heavily on how you tune your buffer pool. On a standard shared host, you are fighting for RAM. On a proper KVM setup, you can allocate dedicated memory.

Here is the baseline configuration I use for high-traffic MySQL 5.5 instances on a 4GB RAM node:

[mysqld]
# MySQL 5.5 Specific Optimizations
innodb_buffer_pool_size = 2G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2  # Speed over strict ACID (risk of 1s data loss)
innodb_file_per_table = 1           # Critical for reclaiming disk space later
query_cache_type = 1
query_cache_limit = 2M
query_cache_size = 64M

Pro Tip: Never trust the default my-medium.cnf. Setting innodb_flush_log_at_trx_commit = 2 can boost write throughput by 500% if your application can tolerate losing the last second of transactions during a full OS crash. For a blog, this is acceptable. For a payment gateway, it is suicide.

The PostgreSQL 9.1 Case: The Data Fortress

PostgreSQL has always been the academic's choice, but version 9.1 (released late last year) changed the game with Synchronous Replication. This is the feature that makes Postgres a serious contender for financial and enterprise systems in Europe where data consistency is mandated by strict auditing standards.

Unlike MySQL, Postgres is strictly ACID compliant. It doesn't truncate data silently. It doesn't accept invalid dates. If you are building an application that handles sensitive user data—which falls under the jurisdiction of Datatilsynet (The Norwegian Data Protection Authority)—Postgres is your safety net.

The complexity comes in the tuning. Postgres relies heavily on the OS filesystem cache, unlike MySQL which prefers to manage its own memory. Here is a starting configuration for a read/write intensive Postgres 9.1 server:

# postgresql.conf
listen_addresses = '*'
max_connections = 100
shared_buffers = 1024MB             # 25% of RAM is the golden rule
effective_cache_size = 3GB          # Let the OS do its job
work_mem = 32MB                     # Per connection! Watch your RAM usage.
maintenance_work_mem = 256MB
wal_level = hot_standby             # Prep for replication
checkpoint_segments = 32            # Spread out the I/O spikes

The "Noisy Neighbor" Problem in Virtualization

Here is the dirty secret of the hosting industry in 2012: Database performance is rarely about CPU. It is about Disk I/O. Both MySQL and Postgres will choke if they are waiting for the hard drive platters to spin.

Most budget VPS providers use OpenVZ. In OpenVZ, the kernel is shared. If your neighbor on the physical node decides to run a massive backup or a dd command, your database latency spikes from 2ms to 200ms. Your queries pile up, your max_connections limit is hit, and your site goes down.

This is where architecture matters. You need KVM (Kernel-based Virtual Machine) virtualization. KVM provides full hardware virtualization. While it doesn't magically create more IOPS on a spinning SATA disk, it offers far better isolation than containers.

Hardware: The SSD Revolution

We are just beginning to see Solid State Drives (SSDs) enter the server market. They are expensive, yes. But for a database server, spinning rust (HDDs) is the bottleneck. A standard 7200 RPM SATA drive gives you roughly 75-100 IOPS (Input/Output Operations Per Second). A complex Magento homepage load can generate 50-100 SQL queries. Do the math.

If you put your database on a standard VPS with shared HDD storage, you are capping your concurrency. This is why at CoolVDS, we are aggressive about adopting SSD technology in our RAID arrays. We don't just cache; we store. When you combine KVM isolation with the near-zero seek time of SSDs, the difference between MySQL and Postgres becomes less about "who is faster" and more about "which features do I need."

Local Compliance: The Norwegian Context

Latency matters. If your customers are in Oslo, Bergen, or Trondheim, hosting your database in a US datacenter is a mistake. The speed of light is a hard limit. A round trip to Texas takes ~140ms. A round trip to a server in Norway or Northern Europe takes <30ms.

Furthermore, the Personal Data Act (Personopplysningsloven) places strict requirements on how you handle personal data. While the Safe Harbor framework currently allows transfer to the US, keeping data within the EEA (European Economic Area) simplifies your legal burden significantly with Datatilsynet. Owning your infrastructure on a CoolVDS KVM slice gives you the control to demonstrate exactly where your data lives and who has access to it.

The Final Verdict

• Choose MySQL 5.5 if: You are running a standard web application, your team knows it well, and you need raw read speed for simple queries.
• Choose PostgreSQL 9.1 if: You value data integrity above all else, you need complex JOINs, or you require synchronous replication for disaster recovery.

Regardless of your engine, do not let your infrastructure be the weak link. A database is only as fast as the disk it runs on.

Ready to benchmark your schema? Deploy a KVM SSD instance on CoolVDS today and see what happens when you remove I/O wait from the equation.