PostgreSQL ist die fortschrittlichste Open-Source-Datenbank. JSONB, Window Functions, Partitionierung — Features, die Sie in MySQL nicht finden.
JSONB¶
– Speicherung und Abfrage CREATE TABLE events (id SERIAL, data JSONB); INSERT INTO events (data) VALUES (‘{“type”: “click”, “page”: “/home”}’); SELECT data->>’type’ as event_type FROM events; SELECT * FROM events WHERE data @> ‘{“type”: “click”}’; CREATE INDEX idx_events_data ON events USING GIN(data);
Window Functions¶
– Ranking SELECT name, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank, AVG(salary) OVER (PARTITION BY department) as dept_avg FROM employees; – Laufende Summe SELECT date, amount, SUM(amount) OVER (ORDER BY date) as running_total FROM transactions;
CTE (Common Table Expression)¶
WITH monthly_stats AS ( SELECT DATE_TRUNC(‘month’, created_at) as month, COUNT(*) as total, SUM(amount) as revenue FROM orders GROUP BY 1 ) SELECT month, total, revenue, LAG(revenue) OVER (ORDER BY month) as prev_revenue FROM monthly_stats;
Wichtigste Erkenntnis¶
PostgreSQL JSONB ersetzt MongoDB für die meisten Anwendungsfälle. Window Functions für Analytik. CTE für Lesbarkeit.