MySQL 5.1 vs PostgreSQL 8.4: The 2010 Database Showdown

It has been a confusing month for the open-source community. With Oracle officially closing its acquisition of Sun Microsystems in January, a lot of us in the system administration world are looking at our /etc/init.d/mysqld scripts with a bit of suspicion. Is the world's most popular open-source database still safe? While forks are being discussed on the mailing lists, we still have businesses to run and data to store.

As a sysadmin managing infrastructure across Oslo and greater Scandinavia, I get asked this weekly: "Should I stick with the LAMP stack default, or is it time to migrate to PostgreSQL?"

The answer isn't about preference; it's about architecture. Let's break down the technical realities of running these engines on virtualized hardware in 2010.

The Speed Demon: MySQL 5.1

MySQL remains the undisputed king of read-heavy web applications. If you are running WordPress, Joomla, or Drupal, you are likely using MySQL. The reason is simple: it is optimized for speed on simple queries. However, the default configuration on most Linux distributions is garbage.

The most critical decision you face here is the storage engine: MyISAM vs. InnoDB.

MyISAM is fast but dangerous. It relies on table-level locking. If you have a site with heavy writes, one user inserting a comment locks the entire table, making read requests wait. It also lacks transaction support. If your server loses power during a write, enjoy repairing your tables.

InnoDB (now becoming the standard for serious deployments) supports row-level locking and ACID transactions. If you are hosting on CoolVDS, we strongly recommend forcing InnoDB for data safety.

Pro Tip: Most VPS deployments fail because they leave the default my.cnf alone. If you have RAM, use it. On a 4GB VPS, set your buffer pool to utilize available memory rather than the default 8MB:

innodb_buffer_pool_size = 2G
innodb_flush_log_at_trx_commit = 2 (If you can tolerate 1 second of data loss for a massive speed boost)

The Architect's Choice: PostgreSQL 8.4

While MySQL is about speed, PostgreSQL is about correctness. The release of version 8.4 last year brought something massive: Window Functions. If you are doing complex analytics or financial reporting, doing this in the database is infinitely faster than pulling arrays into PHP or Python to process them.

PostgreSQL handles concurrency significantly better than MyISAM. Its implementation of MVCC (Multi-Version Concurrency Control) means readers don't block writers. For applications requiring strict data integrity—like compliant storage under the Norwegian Personal Data Act (Personopplysningsloven)—Postgres is the superior choice.

The I/O Bottleneck

Regardless of the database you choose, your bottleneck in 2010 is almost certainly Disk I/O. Virtual Private Servers often suffer from "noisy neighbors"—other tenants on the host machine thrashing the disk and stealing your IOPS.

This is where hardware selection matters. At CoolVDS, we have moved away from standard SATA drives for our database-class nodes. We utilize 15k RPM SAS drives in RAID 10 arrays. This setup provides the redundancy needed for uptime while delivering the low latency required for database seeking. A database that cannot read from the disk fast enough is a useless database.

Comparison: When to use what?

Network Latency and Location

If your target audience is in Norway, hosting your database in a datacenter in Texas is technical suicide. The speed of light is a hard limit. A round trip from Oslo to Dallas takes approx 130ms. If your application makes 10 database queries to render a page, you have added 1.3 seconds of load time before the server even starts processing.

CoolVDS infrastructure is peered directly at NIX (Norwegian Internet Exchange) in Oslo. By keeping your database close to your users, you reduce network latency to single-digit milliseconds. This is critical for keeping connections open during complex transactions without timing out.

The Verdict

If you are deploying a standard LAMP stack application, stick with MySQL 5.1, but switch to InnoDB immediately. If you are building a custom application that requires complex joins, strict data types, or advanced analytics, PostgreSQL 8.4 is the professional choice.

However, software tuning can only take you so far. You need raw I/O performance and low latency connectivity. Don't let your database choke on slow disks.

Ready to benchmark? Deploy a high-performance VPS Norway instance on CoolVDS today and test your queries on enterprise-grade hardware.