PostgreSQL vs MySQL: Stop Guessing, Start Benchmarking
It is March 2010. The dust from Oracle's acquisition of Sun Microsystems is still settling, and if you are a systems administrator in Oslo or Stockholm, you are likely nervous. For years, the LAMP stack (Linux, Apache, MySQL, PHP) has been our bread and butter. But with the database landscape shifting under our feet, the old default choice isn't necessarily the safe one anymore.
I have spent the last week migrating a high-traffic Norwegian e-commerce site from a crumbling shared host to a dedicated VPS. The bottleneck? It wasn't PHP. It was the database locking up during peak hours. This brings us to the eternal debate: MySQL 5.1 vs. PostgreSQL 8.4.
If you are building a simple blog, use whatever you want. But if you are handling transactional data under strict Norwegian privacy laws (Personopplysningsloven), you need to understand the architecture beneath the SQL shell.
The Elephant in the Server Room: Architecture
The fundamental difference lies in how these two handle concurrency. This isn't just academic; it dictates how your server responds when 500 concurrent users hit your checkout page.
MySQL: The Speed Demon (With a Catch)
MySQL is the default for a reason. It is incredibly fast for read-heavy operations. However, most default installations on CentOS 5 or Debian Lenny still default to the MyISAM storage engine. MyISAM is fast, but it uses table-level locking.
Pro Tip: If you perform a huge UPDATE on a MyISAM table, MySQL locks the entire table. No one can read from it until the write is done. On a busy site, this looks like downtime. Always force InnoDB for transactional data.
To fix this in /etc/my.cnf, you need to tune the buffer pool. Don't leave it at the default 8MB:
[mysqld]
# Use InnoDB as default
default-storage-engine = InnoDB
# Set this to 70-80% of your available RAM on a dedicated DB server
innodb_buffer_pool_size = 2G
innodb_flush_log_at_trx_commit = 1 # ACID compliant
PostgreSQL: The Academic Tank
PostgreSQL 8.4 doesn't care about being the fastest at simple reads. It cares about being right. It uses MVCC (Multi-Version Concurrency Control). When you update a row, Postgres creates a new version of that row rather than overwriting it immediately. Readers read the old version; writers write the new one. Readers don't block writers, and writers don't block readers.
This makes PostgreSQL superior for complex applications with heavy read-write mixing. However, it requires more RAM and faster disk I/O to handle the vacuuming processes that clean up those old row versions.
The Hardware Reality: I/O Wait is the Enemy
You can tune postgresql.conf until you are blue in the face, but if your underlying storage is slow, your database will crawl. In the virtualization market right now, many providers in Norway are overselling their disk arrays. They put 50 tenants on a single SATA 7.2k RPM drive.
When you run top and see %wa (I/O wait) spiking above 20%, your CPU is essentially doing nothing, waiting for the disk to spin.
This is why at CoolVDS, we refuse to use standard consumer drives for our host nodes. We utilize high-performance SAS RAID-10 arrays and are beginning to roll out Enterprise SSD tiering. For a database, random I/O performance is everything. A CoolVDS instance running on our KVM infrastructure guarantees that your disk operations aren't fighting for air against a noisy neighbor.
Comparison: Which one fits your project?
| Feature | MySQL 5.1 (InnoDB) | PostgreSQL 8.4 |
|---|---|---|
| Integrity | Good (ACID compliant) | Excellent (Strict typing) |
| Replication | Master-Slave (Easy setup) | WAL Shipping / Slony (Complex) |
| Joins | Fast for simple joins | Superior query planner for complex joins |
| License | GPL (Oracle owned) | BSD (Community owned) |
Data Sovereignty and Latency
Hosting your database outside of Norway introduces two problems: latency and law.
- Latency: If your web server is in Oslo but your database is in a cheap US datacenter, the round-trip time (RTT) will kill your application performance. Every SQL query adds 100ms+ overhead. Hosting on CoolVDS ensures your data sits on the Norwegian backbone, often routing through NIX (Norwegian Internet Exchange) for sub-10ms latency to local users.
- Privacy: Under the Personal Data Act (Personopplysningsloven), you are responsible for sensitive user data. While the US Safe Harbor agreement exists, keeping data within the EEA (European Economic Area) is the only way to be absolutely sure you aren't running afoul of Datatilsynet.
The Verdict
If you are deploying a standard CMS like Joomla or WordPress, stick with MySQL 5.1. It is well-documented, memory-efficient, and easy to find support for. Just make sure you switch your tables to InnoDB to avoid table-locking disasters.
If you are building a custom financial application, a complex CRM, or anything requiring strict data types and heavy concurrent writes, PostgreSQL 8.4 is the professional choice. It forces you to write better code.
Whatever you choose, do not cripple it with cheap I/O. A database is only as fast as the disk it lives on.
Need to benchmark the difference? Deploy a high-performance Linux VPS on CoolVDS today. We offer pure KVM virtualization with dedicated RAM allocation—no overselling, no nonsense.