PostgreSQL je skvělá databáze, ale bez správné optimalizace může být pomalá. Tady je 15 triků.
1. EXPLAIN ANALYZE¶
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = ‘pending’;
2. Správné indexy¶
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 = nový proces na spojení (~10 MB). PgBouncer v transaction mode to řeší.
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. Partitioning¶
CREATE TABLE events (…) PARTITION BY RANGE (created_at);
8. COPY místo INSERT¶
COPY users(name, email) FROM ‘/tmp/users.csv’ WITH (FORMAT csv, HEADER);
9-15: Quick wins¶
- random_page_cost = 1.1 pro SSD
- effective_cache_size = 75% RAM
- shared_buffers = 25% RAM
- Nepoužívejte SELECT *
- LIMIT vždy
- Batch UPDATE/DELETE po 10K
- Smažte nepoužívané indexy
Conclusion¶
Začněte EXPLAIN ANALYZE a pg_stat_statements. Pochopte co DB dělá, pak optimalizujte.