PostgreSQL: 15 Optimization Tricks¶
PostgreSQL is an excellent database, but without proper optimization it can be slow. Here are 15 tricks.
1. EXPLAIN ANALYZE¶
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = ‘pending’;
2. Proper Indexes¶
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 = a new process per connection (~10 MB). PgBouncer in transaction mode solves this.
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 Instead of INSERT¶
COPY users(name, email) FROM ‘/tmp/users.csv’ WITH (FORMAT csv, HEADER);
9-15: Quick Wins¶
- random_page_cost = 1.1 for SSD
- effective_cache_size = 75% RAM
- shared_buffers = 25% RAM
- Do not use SELECT *
- Always use LIMIT
- Batch UPDATE/DELETE in chunks of 10K
- Delete unused indexes
Conclusion¶
Start with EXPLAIN ANALYZE and pg_stat_statements. Understand what the DB is doing, then optimize.