PostgreSQL 9.0 vs MySQL 5.5: The Database War for High-Traffic Sites
There is nothing quite like the smell of a crashed database server at 3 AM on a Tuesday. If you have been in the hosting game as long as I have, you know the specific pain of a MyISAM table lock bringing a high-traffic e-commerce site to its knees. For years, the choice was simple: use MySQL for speed and websites, use PostgreSQL for "serious" data and academic projects.
But it is 2011, and the rules have changed. Oracle has swallowed Sun Microsystems, making many of us nervous about the future of MySQL. Meanwhile, PostgreSQL 9.0 just dropped with built-in streaming replication, finally addressing the elephant in the room. If you are deploying a new backend on a VPS in Norway today, the decision isn't as binary as it used to be.
The MySQL 5.5 Standard: Speed with a Caveat
Let’s be honest: MySQL is the engine of the web. It powers WordPress, Drupal, and likely 90% of the sites hosted in Oslo right now. With version 5.5, InnoDB became the default storage engine. This is critical. We are finally moving away from the table-locking nightmares of MyISAM to true row-level locking and transactions.
However, MySQL 5.5 still requires significant tuning to behave correctly under load. Out of the box, the defaults are often tailored for small shared hosting accounts, not dedicated performance.
Key Optimization: The Buffer Pool
If you are running MySQL on a dedicated instance, you need to edit your /etc/my.cnf immediately. The most important variable is innodb_buffer_pool_size. If you have a CoolVDS instance with 4GB of RAM dedicated to the DB, do not leave this at the default 8MB.
[mysqld]
innodb_buffer_pool_size = 3G
innodb_flush_log_at_trx_commit = 2
query_cache_type = 1
query_cache_size = 64M
Note: Setting flush_log to 2 gives you a massive speed boost, but you risk losing one second of data during a power failure. On stable grids like we have in Norway, it's a calculated risk I often take.
PostgreSQL 9.0: The New Contender
PostgreSQL has always been the "correct" database. It respects ACID compliance strictly. But until version 9.0 released last September, setting up replication was a disaster involving trigger-based hacks like Slony-I.
Now, with Hot Standby and Streaming Replication, Postgres is ready for the heavy web loads. If your application handles complex queries, geospatial data (PostGIS is incredible), or financial transactions where you cannot afford to lose a single byte, Postgres is the superior choice.
Pro Tip: PostgreSQL handles concurrency differently. It uses a process-per-connection model, whereas MySQL uses threads. This means Postgres can eat RAM quickly if you have thousands of idle connections. Use a connection pooler like PgBouncer if you expect high concurrency.
The Hardware Reality: I/O is the Bottleneck
You can tune your config files all day, but if your underlying disk system is slow, your database will crawl. In 2011, mechanical SAS drives are still standard in many datacenters, but they are the death of database performance.
This is where the infrastructure matters. A database server constantly performs random read/write operations. When you host on CoolVDS, we utilize SSD storage caching and high-performance RAID arrays. The difference in latency is palpable. We are talking about reducing I/O wait times from 15ms down to sub-1ms.
| Feature | MySQL 5.5 (InnoDB) | PostgreSQL 9.0 |
|---|---|---|
| Replication | Asynchronous (mature, easy setup) | Streaming Replication (New, robust) |
| Complex Queries | Struggles with complex joins | Excellent optimizer |
| Write Speed | Very High (Simple inserts) | High (MVCC overhead) |
| Reliability | Good (with InnoDB) | Rock Solid (Strict ACID) |
Data Sovereignty and The "Datatilsynet" Factor
Here in Norway, we take privacy seriously. The Personopplysningsloven (Personal Data Act) places strict requirements on how we handle user data. If you are storing customer data for Norwegian businesses, keeping that data within national borders is not just a technical preference; it is often a legal necessity.
Using a US-based cloud provider introduces latency across the Atlantic and legal ambiguity regarding the Patriot Act. Hosting on managed hosting or VPS infrastructure located directly in Oslo ensures you meet local compliance standards while benefiting from direct peering at NIX (Norwegian Internet Exchange). Your ping times to local users will drop from 30ms+ to <5ms.
Which One Should You Choose?
Choose MySQL 5.5 if:
- You are running a standard CMS like WordPress or Joomla.
- Your team is small and needs a "set and forget" solution.
- Read speed is your primary metric.
Choose PostgreSQL 9.0 if:
- You are building a custom application with complex data relationships.
- Data integrity is more valuable to you than raw read throughput.
- You need advanced features like partial indexes or JSON support (via HStore).
The Infrastructure Foundation
Regardless of your database choice, it needs a stable home. Databases are resource hogs; they hate "noisy neighbors" stealing their CPU cycles. This is why CoolVDS uses KVM virtualization. Unlike OpenVZ, KVM provides true hardware isolation. If another user on the node spikes their load, your database won't feel it.
Don't let legacy hardware kill your query performance. Whether you go with the ubiquity of MySQL or the robustness of PostgreSQL, ensure it runs on metal that can keep up.
Ready to benchmark your schema? Spin up a CoolVDS instance with SSD-accelerated storage today and see the difference low latency makes.