PostgreSQL vs MySQL: Choosing Your Database in the Post-Sun Era
It has been a few months since Oracle closed the deal on Sun Microsystems, and if you are a systems administrator like me, you are probably watching the mailing lists with a nervous eye. The open-source database landscape feels shaky. MySQL has been the default choice for the LAMP stack for a decade, but the fork to MariaDB is gaining traction, and PostgreSQL is looking more attractive by the day.
I have spent the last week migrating a high-traffic e-commerce client from a cluttered shared host to a dedicated VPS setup here in Norway. The bottleneck wasn't PHP; it was the database locking up under load. This brings us to the eternal question: In mid-2010, which relational database management system (RDBMS) should you be running?
The Contenders: MySQL 5.1 vs. PostgreSQL 8.4
Let's cut through the fanaticism. Both are mature, but they serve different masters. MySQL is built for speed and read-heavy workloads. PostgreSQL is built for data integrity and complex writes.
MySQL: The Web Standard
MySQL is everywhere. It is the 'M' in LAMP. If you are running Drupal, Joomla, or WordPress, you are likely using MySQL. The current stable version, 5.1, introduced partitioning and row-based replication, which were huge steps forward.
The Good: It is incredibly fast for simple READ operations. Replication is built-in and generally works well (Master-Slave setups are standard). The community support is massive.
The Bad: The default storage engine is often still MyISAM, which supports full-text search but lacks transactions and uses table-level locking. If you have a write-heavy application, MyISAM will kill your performance as one write locks the entire table. You must switch to InnoDB for row-level locking and ACID compliance.
PostgreSQL: The Academic Tank
PostgreSQL (currently version 8.4) has always been the "strict" teacher. It forces you to write better SQL. It supports features that MySQL is still dreaming about, like complex joins across massive datasets without choking.
The Good: True ACID compliance by default. MVCC (Multi-Version Concurrency Control) means readers don't block writers. It handles complex queries much more efficiently than MySQL's query optimizer.
The Bad: It is heavier. A fresh Postgres install consumes more RAM than MySQL. Historically, replication has been a pain (Slony-I is a nightmare to configure), though 9.0 (currently in Beta) promises streaming replication which might finally solve this.
War Story: The Table Lock Nightmare
Last month, we had a client running a custom forum software on a generic "unlimited" web host. Every time a backup script ran or a heavy user posted a thread, the site hung for 10-15 seconds. The diagnosis? MyISAM table locking.
We moved them to a CoolVDS instance running CentOS 5. We didn't just migrate; we optimized. We switched the tables to InnoDB and tuned the `my.cnf`. The difference was night and day.
Here is the critical config change for MySQL 5.1 users moving to InnoDB:
[mysqld]
# default-storage-engine = InnoDB
innodb_buffer_pool_size = 512M
innodb_log_file_size = 128M
innodb_flush_log_at_trx_commit = 2
Pro Tip: Setting `innodb_flush_log_at_trx_commit` to 2 instead of 1 can give you a massive speed boost by flushing to the OS cache rather than the disk every second. You risk losing 1 second of data in a power failure, but with the stable grid here in Norway and our battery-backed RAID controllers at CoolVDS, it is a calculated risk worth taking.
Performance is Geography
You can tune your `shared_buffers` in Postgres all day, but if your server is in Texas and your users are in Trondheim, your latency is going to be garbage. The speed of light is a hard limit.
Hosting your database within Norway isn't just about ping times to NIX (Norwegian Internet Exchange); it is about compliance. With Datatilsynet (The Data Inspectorate) enforcing the Personal Data Act strictly, keeping user data within national borders simplifies your legal headaches significantly. You do not want to explain to a auditor why your customer database is sitting on a server in a jurisdiction with lax privacy laws.
The Verdict: What Should You Install?
| Scenario | Recommendation | Why? |
|---|---|---|
| WordPress / Blogs | MySQL (InnoDB) | Standard support, caching plugins are optimized for it. |
| Financial Data / ERP | PostgreSQL | Strict types, transactional integrity is non-negotiable. |
| Geospatial Data | PostgreSQL + PostGIS | MySQL's spatial extensions are primitive in comparison. |
| High Concurrency | CoolVDS KVM + Postgres | True virtualization isolates your I/O from noisy neighbors. |
Why Virtualization Architecture Matters
Whether you choose MySQL or Postgres, your database lives and dies by Disk I/O. This is where most VPS providers fail you. They use OpenVZ or Virtuozzo to oversell resources. If another user on the same physical node decides to compile a kernel or run a backup, your database latency spikes.
At CoolVDS, we are moving towards KVM (Kernel-based Virtual Machine). Unlike container-based virtualization, KVM provides true hardware virtualization. Your RAM is yours. Your disk scheduler is yours. When you allocate 2GB for your `innodb_buffer_pool`, that memory isn't being swapped out by the host node.
We also utilize enterprise-grade SSDs for our high-performance tiers. In 2010, SSDs are still expensive, but for database hosting, the IOPS (Input/Output Operations Per Second) advantage over traditional SAS 15k drives is undeniable. If you are serious about performance, spindle drives are dead to you.
Final Advice
If you are starting a new project today, July 13th, 2010:
- Use PostgreSQL 8.4 if you value data over raw read speed.
- Use MySQL 5.1 (with InnoDB) if you are building a standard web app.
- Ensure your host isn't overselling I/O.
Don't let a slow database destroy your user experience. Spin up a KVM instance with SSD storage on CoolVDS today and see what true isolation does for your query times.