The Database Wars: MySQL vs PostgreSQL in 2010
Let’s address the elephant in the server room: Oracle has officially acquired Sun Microsystems. As of last month, the fate of MySQL is technically in the hands of a proprietary database giant. For those of us managing critical infrastructure in Oslo and across Europe, this adds a layer of anxiety to our architectural decisions.
I have spent the last week migrating a high-traffic e-commerce cluster from a shared host to a dedicated VPS environment. The bottleneck wasn't CPU; it never is. It was disk I/O and locking contention. This brings us to the eternal debate: do you stick with the speed of MySQL or graduate to the strict integrity of PostgreSQL?
Here is the reality check for February 2010.
MySQL 5.1: The Speed Demon (With Caveats)
MySQL powers the web. It is the 'M' in LAMP. If you are running a standard CMS like Joomla or Drupal, or even a custom PHP application, MySQL is the default. It is incredibly fast for read-heavy workloads. However, the default configuration in most distributions (CentOS 5, Debian Lenny) is dangerous for serious business data.
The default storage engine, MyISAM, does not support transactions. If your server crashes during a write, say goodbye to data integrity. You end up running myisamchk and praying. For any production system on CoolVDS, we strictly enforce the use of InnoDB.
Pro Tip: Don't rely on default my.cnf settings. They are tuned for systems with 64MB of RAM. If you are on a CoolVDS plan with 1GB+ RAM, you need to adjust your buffer pool to cache data in memory, not disk.
[mysqld]
# Stop using MyISAM for critical data
default-storage-engine = InnoDB
# Allocate 50-70% of RAM here if it is a dedicated DB server
innodb_buffer_pool_size = 512M
# Crucial for data durability (ACID)
innodb_flush_log_at_trx_commit = 1
PostgreSQL 8.4: The Architect's Choice
PostgreSQL has always been the "academic" database, but version 8.4 (released last year) changed the game. It introduced Window Functions and Common Table Expressions (CTEs). If you are doing complex analytics or financial reporting, MySQL's GROUP BY limitations will drive you insane. Postgres 8.4 handles these queries natively.
Postgres is strictly ACID compliant by default. It uses MVCC (Multi-Version Concurrency Control), which means readers don't block writers. In a heavy OLTP environment, this prevents the table-locking hell often seen with MyISAM.
The Configuration Reality
Postgres is conservative out of the box. To get it flying on a virtualized server, you need to tell it about your OS cache.
# postgresql.conf
shared_buffers = 256M
# Tell the query planner how much OS cache is available
effective_cache_size = 768M
checkpoint_segments = 32
The Hardware Factor: Why I/O Matters
You can tune my.cnf until you are blue in the face, but if your underlying disk subsystem is slow, your database will crawl. This is where the hosting platform makes or breaks your application.
Most budget VPS providers oversell their storage arrays. They put hundreds of containers on a single SATA drive. When one neighbor decides to run a backup, your database latency spikes from 2ms to 500ms. In the database world, that is an outage.
At CoolVDS, we use hardware RAID-10 arrays with SAS 15k RPM drives. We also strictly isolate resources using Xen virtualization. Unlike OpenVZ, where memory is often "burstable" (read: fake), Xen guarantees that your RAM is yours. This is non-negotiable for database caching.
Local Latency and Compliance
If your users are in Norway, hosting your database in Texas is technical suicide. The latency over the Atlantic adds 100ms+ to every TCP handshake. For a PHP application making 20 SQL queries per page load, that adds 2 full seconds of wait time.
Furthermore, we must respect the Personopplysningsloven (Personal Data Act of 2000). While Safe Harbor exists for US transfers, the Norwegian Data Inspectorate (Datatilsynet) looks much more favorably on data that stays within the EEA. Keeping your database on Norwegian soil, connected directly to NIX (Norwegian Internet Exchange), solves both the latency issue and the legal headache.
Verdict
- Choose MySQL (InnoDB) if: You have a read-heavy web app, you need developers who know the stack cheaply, and you need simple replication.
- Choose PostgreSQL 8.4 if: You handle financial transactions, need complex reporting (Window Functions), or require strict data integrity over raw read speed.
Whatever you choose, do not run it on shared storage. Database I/O demands dedicated throughput. If you are ready to stop fighting with wait-states, deploy a Xen-based instance on CoolVDS today and see what proper disk I/O does for your query times.