PostgreSQL Performance Tips That Actually Work

PostgreSQL 性能调优

I have spent countless hours optimizing PostgreSQL queries. Here are the tips that actually moved the needle.

1. Indexing: The Low-Hanging Fruit

Most important indexes:

CREATE INDEX idx_posts_author_id ON posts(author_id);
CREATE INDEX idx_posts_published_at ON posts(published_at DESC);
CREATE INDEX idx_comments_post_id ON comments(post_id);

Composite indexes for common queries:

-- If you often query by author AND status
CREATE INDEX idx_posts_author_status ON posts(author_id, status);

Partial indexes for filtered queries:

-- Only index published posts (saves space)
CREATE INDEX idx_posts_published ON posts(published_at)
WHERE status = 'published';

2. Query Optimization

Use EXPLAIN ANALYZE:

EXPLAIN ANALYZE SELECT * FROM posts WHERE author_id = 'abc';

Look for:

• Sequential scans on large tables
• High "rows removed by filter" counts
• Sort operations on unindexed columns

Avoid N+1 queries:

-- Bad: N+1 queries
for post in posts:
    comments = db.query("SELECT * FROM comments WHERE post_id = $1", post.id)

-- Good: Single query with JOIN
SELECT p.*, c.* FROM posts p
LEFT JOIN comments c ON c.post_id = p.id
WHERE p.author_id = $1

3. Configuration Tuning

Default PostgreSQL config is designed for a Raspberry Pi. Tune it for your hardware:

# In postgresql.conf
shared_buffers = 25% of RAM
effective_cache_size = 75% of RAM
work_mem = 32MB
maintenance_work_mem = 256MB
random_page_cost = 1.1  -- for SSD

4. The One Query That Changed Everything

The single biggest performance improvement I ever made:

-- Before: 2.3 seconds
SELECT * FROM posts ORDER BY published_at DESC;

-- After: 12 milliseconds
SELECT * FROM posts
WHERE published_at < $1
ORDER BY published_at DESC
LIMIT 20;

Pagination with keyset (cursor-based) instead of OFFSET. Always use cursor pagination for large datasets.

5. Monitoring

What I check weekly:

• Slow query log — anything over 100ms
• Index usage — unused indexes waste space
• Table bloat — VACUUM regularly
• Connection count — too many connections = trouble

The fastest query is the one you don't run. Cache aggressively, query minimally.