PostgreSQL ist eine ausgezeichnete Datenbank, aber ohne richtige Optimierung kann sie langsam sein. Hier sind 15 Tricks.
1. EXPLAIN ANALYZE¶
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = ‘pending’;
2. Richtige Indizes¶
CREATE INDEX idx_orders_status ON orders(status, created_at DESC);
CREATE INDEX idx_active ON users(email) WHERE active = true;
CREATE INDEX idx_cover ON orders(user_id) INCLUDE (total, status);
3. pg_stat_statements¶
CREATE EXTENSION pg_stat_statements;
SELECT query, mean_exec_time, calls FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;
4. PgBouncer¶
Connection Pooling. PostgreSQL = neuer Prozess pro Verbindung (~10 MB). PgBouncer im Transaction Mode loest das.
5. VACUUM-Tuning¶
SELECT relname, n_dead_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;
6. work_mem¶
SET work_mem = ‘256MB’;
7. Partitionierung¶
CREATE TABLE events (…) PARTITION BY RANGE (created_at);
8. COPY statt INSERT¶
COPY users(name, email) FROM ‘/tmp/users.csv’ WITH (FORMAT csv, HEADER);
9-15: Quick Wins¶
- random_page_cost = 1.1 fuer SSD
- effective_cache_size = 75% RAM
- shared_buffers = 25% RAM
- Kein SELECT * verwenden
- Immer LIMIT setzen
- Batch UPDATE/DELETE mit 10K
- Unbenutzte Indizes loeschen
Fazit¶
Beginnen Sie mit EXPLAIN ANALYZE und pg_stat_statements. Verstehen Sie, was die DB tut, dann optimieren.