MySQL vs. PostgreSQL: The Database Battle for 2009

The uncertainty is palpable. With Oracle moving to acquire Sun Microsystems, the future of MySQL feels... complicated. For us SysAdmins managing servers from Oslo to Kyiv, the default "M" in our LAMP stack isn't the automatic choice it was in 2007. PostgreSQL 8.4 dropped just last month (July 2009), and it brings features that actually make me want to migrate away from MyISAM.

I've spent the last week benchmarking both on a CoolVDS Xen instance running CentOS 5. Here is the reality of the situation: you are likely trading data integrity for read-speed, or vice versa. Let's look at the config files.

MySQL 5.1: The Speed Demon (With Risks)

MySQL is still the king of read-heavy web applications. If you are running a news portal or a vBulletin forum, the sheer speed of simple SELECT queries is hard to beat. However, the default storage engine, MyISAM, is a ticking time bomb for data integrity. It supports table-level locking, meaning if one user writes to a table, nobody reads until they are done.

If you stick with MySQL, you need to be looking at InnoDB. It provides row-level locking and ACID compliance, bringing it closer to PostgreSQL standards. But you have to tune it, or it crawls.

Pro Tip: Stop leaving your my.cnf as default. On a 2GB VPS, you must adjust the buffer pool. If you don't, MySQL uses a tiny 8MB default.

[mysqld]
# Optimize for InnoDB performance
innodb_buffer_pool_size = 512M
innodb_flush_log_at_trx_commit = 2  # Riskier, but faster writes
query_cache_size = 64M

PostgreSQL 8.4: The Grown-Up Choice

While MySQL is web-native, PostgreSQL has always felt academic. That changed with version 8.4. We now have Window Functions and Common Table Expressions (CTEs). This allows us to write complex reporting queries inside the database rather than dragging massive arrays into PHP and running out of memory.

I recently migrated a financial tracking tool for a client in Bergen. We had locking issues with MySQL every time the nightly cron jobs ran. Moving to Postgres' MVCC (Multi-Version Concurrency Control) solved it instantly. Readers don't block writers. It just works.

The trade-off? Configuration complexity. Postgres is conservative out of the box.

# postgresql.conf optimization for a 2GB node
shared_buffers = 128M
effective_cache_size = 1024M
work_mem = 4M
maintenance_work_mem = 64M

The Hardware Bottleneck: I/O Wait

Whether you choose the speed of MySQL or the features of Postgres, your database will die if your disk I/O sucks. This is where most "budget" hosting falls apart. They put 50 users on a single hard drive.

When you see %wa (I/O wait) spike in top, your CPU is sitting idle waiting for the disk to spin. This is why at CoolVDS, we don't mess around with standard SATA for database nodes. We utilize high-performance 15k RPM SAS RAID-10 arrays and are experimenting with early Enterprise SSD caching. You need low latency on the block device, or your database tuning is useless.

Virtualization Matters: Xen vs. OpenVZ

Another critical factor in 2009 is the virtualization layer. Many providers use OpenVZ (containerization) and oversell the RAM. If your neighbor's PHP script goes rogue, your MySQL process gets killed by the OOM (Out of Memory) killer.

This is why CoolVDS uses Xen. It offers true hardware virtualization. If you buy 1GB of RAM, that RAM is reserved for you. It cannot be stolen by another user. For a database server, this stability is non-negotiable.

Data Sovereignty and The "Datatilsynet"

For those of us operating in Norway, the Personopplysningsloven (Personal Data Act of 2000) is strict. Storing customer data on cheap servers in the US might save you a few kroner, but the latency to Oslo (120ms+) ruins the user experience, and the legal gray area regarding Safe Harbor is worrying.

Hosting locally means 15ms latency to NIX (Norwegian Internet Exchange) and full compliance with Norwegian privacy laws. Don't risk your reputation to save $5 a month.

Verdict

• Choose MySQL 5.1 (InnoDB) if you are running a standard CMS like Joomla, Drupal, or WordPress and need raw read throughput.
• Choose PostgreSQL 8.4 if you are building a custom application, need complex analytics, or require absolute data integrity (ACID).

Whichever engine you pick, it needs a stable foundation. Don't let high latency or noisy neighbors kill your query performance. Spin up a CoolVDS Xen instance today and give your database the dedicated resources it deserves.