PostgreSQL vs. MySQL: Der Vergleich

Beide sind exzellente relationale Datenbanken. Dieser Vergleich hilft Ihnen, die richtige Wahl für Ihr Projekt zu treffen.

Übersicht

PostgreSQL-Stärken

-- Erweiterte Datentypen
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    tags TEXT[],              -- Array
    metadata JSONB,           -- Binary JSON
    price NUMERIC(10,2),
    location POINT,           -- Geometrie
    ip_range INET,            -- IP-Adressen
    created_at TIMESTAMPTZ
);

-- Array-Abfragen
SELECT * FROM products WHERE 'sale' = ANY(tags);
SELECT * FROM products WHERE tags @> ARRAY['sale', 'new'];

-- JSONB-Abfragen
SELECT * FROM products WHERE metadata @> '{"color": "red"}';
SELECT * FROM products WHERE metadata->>'brand' = 'Apple';
CREATE INDEX idx_metadata ON products USING GIN (metadata);

-- Window Functions (auch MySQL 8+)
SELECT name, price,
    AVG(price) OVER (PARTITION BY category) as avg_category_price,
    RANK() OVER (PARTITION BY category ORDER BY price DESC)
FROM products;

-- CTEs (Common Table Expressions)
WITH recent_orders AS (
    SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30 days'
)
SELECT customer_id, COUNT(*) FROM recent_orders GROUP BY customer_id;

-- Rekursive CTEs
WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id FROM employees WHERE id = 1
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

MySQL-Stärken

-- Einfachheit und Verbreitung
-- Viele Hosting-Provider, WordPress, etc.

-- Schnelle Lese-Performance (einfache Queries)
SELECT * FROM users WHERE id = 123;

-- JSON (seit 5.7)
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data JSON
);

SELECT JSON_EXTRACT(data, '$.name') FROM products;
SELECT data->>'$.name' FROM products;  -- Kurzform

-- Fulltext Search (einfach)
CREATE FULLTEXT INDEX idx_search ON articles(title, content);
SELECT * FROM articles WHERE MATCH(title, content) AGAINST('search term');

-- Generated Columns
CREATE TABLE orders (
    id INT PRIMARY KEY,
    subtotal DECIMAL(10,2),
    tax_rate DECIMAL(5,2),
    tax DECIMAL(10,2) GENERATED ALWAYS AS (subtotal * tax_rate) STORED
);

-- MySQL 8: Window Functions
SELECT name, salary,
    SUM(salary) OVER (ORDER BY hire_date) as running_total
FROM employees;

Performance-Vergleich

# Generelle Beobachtungen:

PostgreSQL:
+ Komplexe Queries (JOINs, Subqueries)
+ Große Datenmengen
+ Parallele Queries
+ JSONB-Operationen
+ Write-Heavy Workloads

MySQL:
+ Einfache Read-Heavy Workloads
+ Weniger Ressourcen bei kleinen DBs
+ Replikation Setup einfacher
+ Point-Lookups (Primary Key)

# Moderne Versionen: Beide sehr schnell!
# Wahl oft andere Faktoren als pure Performance

Feature-Vergleich

Wann welche DB?

Migration

# MySQL → PostgreSQL: pgloader
pgloader mysql://user:pass@host/db postgresql://user:pass@host/db

# PostgreSQL → MySQL: Komplexer, manuell oder Tools
# - mysqldump/pg_dump + Konvertierung
# - AWS DMS
# - Eigene Scripts

# Typische Anpassungen:
# AUTO_INCREMENT → SERIAL
# DATETIME → TIMESTAMP
# TINYINT(1) → BOOLEAN
# ENUM → CHECK oder eigene Tabelle
# JSON-Funktionen anpassen

Weitere Informationen

• 🐘 PostgreSQL Grundlagen
• 🐬 MySQL Backup
• 📊 Enjyn Status Monitor