PostgreSQL vs MySQL: The Data Store Battle for Norwegian Enterprises
It is 2012, and the default LAMP stack mentality is finally dying. For years, we simply apt-get install mysql-server and moved on. But with Oracle's acquisition of Sun Microsystems casting a shadow over MySQL's open-source future, and PostgreSQL 9.2 releasing just last month with game-changing performance improvements, the choice is no longer automatic. It is architectural.
As a Systems Architect deploying infrastructure across Oslo and Trondheim, I see too many developers treating the database as a black box. They deploy a high-traffic Magento store on a standard MyISAM setup and wonder why table locking kills their checkout process during traffic spikes. Or they try to force relational data into the new "NoSQL" hype (looking at you, MongoDB) when what they really need is a strict schema with transactional integrity.
This is not a flame war. This is a pragmatic look at choosing the right engine for your workload, specifically within the context of high-performance VPS hosting in Norway.
The Contenders: MySQL 5.5 vs. PostgreSQL 9.2
MySQL: The Read-Heavy Speedster
MySQL remains the king of the web. It is the engine behind Facebook, WordPress, and Drupal. The release of MySQL 5.5 brought InnoDB as the default storage engine, finally moving us away from the crash-prone MyISAM. InnoDB supports ACID transactions, row-level locking, and crash recovery—essential features that were previously MySQL's weak points.
When to use it: If your application is 90% reads and 10% writes (like most CMSs and blogs), MySQL is incredibly efficient. Replication is arguably simpler to set up than Postgres, making it a favorite for scaling read-heavy clusters.
PostgreSQL: The Integrity Fortress
PostgreSQL has long been the academic choice, but version 9.2 (released September 2012) is a beast. It introduces Index-Only Scans, which can significantly reduce disk I/O, and cascading replication. Postgres adheres strictly to SQL standards. If you care about data integrity, complex JOINs, or geospatial data (via PostGIS), this is your engine.
When to use it: Financial applications, complex data warehousing, or any system where data consistency is paramount. With the strict requirements from Datatilsynet (The Norwegian Data Inspectorate) regarding data handling, Postgres's robust constraints are a massive asset.
Technical Deep Dive: Tuning for Performance
Regardless of your choice, a default installation is bottlenecked by configuration. Most distributions ship with configs optimized for 512MB RAM servers. On a modern CoolVDS instance with 8GB or 16GB of RAM, you are wasting resources.
Optimizing MySQL (InnoDB)
The single most critical setting for MySQL 5.5 is the buffer pool size. This dictates how much data and how many indexes are cached in memory.
[mysqld]
# /etc/mysql/my.cnf
# Set this to 70-80% of available RAM on a dedicated DB server
innodb_buffer_pool_size = 6G
# Ensure transaction durability.
# Set to 2 for better performance if you can tolerate
# ~1 second of data loss on OS crash. 1 is safest.
innodb_flush_log_at_trx_commit = 1
# Separate table files for better disk management
innodb_file_per_table = 1
# Log slow queries to find bottlenecks
slow_query_log = 1
long_query_time = 2Optimizing PostgreSQL
Postgres handles memory differently, relying heavily on the OS file system cache. Therefore, you do not allocate as much RAM strictly to the database process as you do with MySQL.
# /etc/postgresql/9.2/main/postgresql.conf
# Set to 25% of system RAM
shared_buffers = 2GB
# Estimate of how much memory is available for disk caching.
# This helps the query planner make better decisions.
effective_cache_size = 6GB
# Maintenance work mem for VACUUM and CREATE INDEX operations
maintenance_work_mem = 512MB
# Checkpoint segments (write performance)
checkpoint_segments = 32
checkpoint_completion_target = 0.9