The LAMP Stack is Evolving: Choosing the Right Engine
For the last decade, "M" in LAMP stood for MySQL. It was the default choice for every startup from Oslo to Silicon Valley. But let's be honest: the database landscape in March 2011 is not what it was two years ago. Oracle's acquisition of Sun Microsystems has sent shivers down the spine of the open-source community, and for good reason.
Meanwhile, PostgreSQL has quietly transformed from a strictly academic exercise into a production-ready beast with the release of version 9.0. If you are still running default MyISAM tables on a CentOS 5 box, you are playing Russian Roulette with your data integrity.
As a systems architect who has spent too many nights recovering corrupted tables, I’m here to tell you that the choice isn't simple anymore. It comes down to your specific workload, your need for transactional integrity, and the underlying hardware you deploy on.
MySQL 5.5: The New Standard
If you stick with MySQL, you absolutely must upgrade to 5.5. Released just a few months ago, it finally makes InnoDB the default storage engine. This is critical. We are done with table-level locking. If your application has high concurrency—like a busy e-commerce site targeting the Nordic market—InnoDB's row-level locking is mandatory.
However, MySQL 5.5 isn't magic. You need to tune it. The default my.cnf is still laughably small. On a CoolVDS 4GB RAM instance, you should be looking at something like this:
[mysqld]
innodb_buffer_pool_size = 2G
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
query_cache_size = 64M
The innodb_file_per_table directive is a lifesaver when you eventually need to reclaim disk space. Without it, your system tablespace never shrinks, even if you drop tables.
PostgreSQL 9.0: The "Oracle Killer"?
For years, the knock against Postgres was replication. It was messy, requiring third-party tools like Slony. With PostgreSQL 9.0, we finally have built-in Streaming Replication and Hot Standby. This brings it to feature parity with MySQL's master-slave setup but with significantly stricter data integrity guarantees.
I recently migrated a financial reporting tool for a client in Trondheim. They were suffering from silent data truncation in MySQL (inserting a string into a too-short VARCHAR just chopped it off with a warning). Postgres throws an error. In a banking context, you want the error. You want the ACID compliance.
Postgres requires a different mindset for optimization. It relies heavily on the OS file system cache. Therefore, your effective_cache_size should be set to estimate how much memory the kernel has available for disk caching.
The Hardware Factor: It's All About IOPS
You can tune your config files all day, but if your underlying disk I/O is slow, your database will crawl. This is where the "Virtual Private Server" market is currently split in two.
Most budget providers are stuffing hundreds of OpenVZ containers onto a single server with standard SATA 7.2k RPM drives. This is a recipe for the "noisy neighbor" effect. When one user runs a heavy backup script, your database latency spikes. I've seen IO wait times hit 40% on these oversold nodes.
At CoolVDS, we take a different approach. We use KVM virtualization. This means your RAM is allocated and reserved, not burstable shared memory. More importantly, we are aggressive adopters of Enterprise SSD storage and 15k SAS drives in RAID-10. For a database heavy on random reads, the difference between a spinning disk and an SSD is night and day.
Pro Tip: If you are serving users in Norway, keep your data in Norway. Latency matters. A ping from Oslo to a server in Amsterdam is ~25ms. To a local Norwegian datacenter, it's <5ms. That adds up on complex SQL queries. Plus, with the Data Inspectorate (Datatilsynet) becoming stricter about personal data handling, local hosting simplifies your compliance with the Personal Data Act.
Verdict: Which One to Choose?
| Feature | MySQL 5.5 | PostgreSQL 9.0 |
|---|---|---|
| Best For | Web Apps (CMS, Blogs), Read-Heavy loads | Complex queries, Financial Data, GIS (PostGIS) |
| Replication | Async Master-Slave (Mature) | Streaming Replication (New in 9.0) |
| Strictness | Forgiving (Auto-truncation) | Strict (ACID compliant) |
| License | GPL (Oracle owned) | BSD (Community owned) |
Final Thoughts
If you are running a standard WordPress or Joomla site, MySQL 5.5 on a KVM slice is efficient and familiar. But if you are building a custom application that handles sensitive transactional data, PostgreSQL 9.0 is the superior engineering choice in 2011.
Whichever engine you choose, don't let slow I/O be your bottleneck. Database performance is only as good as the disk it sits on. Experience the difference of true KVM isolation and high-performance storage.
Need low latency and reliable I/O? Deploy your database on a CoolVDS SSD instance today.