PostgreSQL Performance Tuning: A Practical Guide

PostgreSQL is a powerful database, but like most tools, performance doesn't come by accident. This guide covers practical techniques that have proven effective in production systems handling millions of queries per day.

Understanding Query Execution

Before you can optimize queries, you must understand how PostgreSQL executes them. The

EXPLAIN ANALYZE

command is your primary weapon.

EXPLAIN ANALYZE
SELECT u.id, u.email, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > NOW() - INTERVAL '30 days'
GROUP BY u.id, u.email
ORDER BY order_count DESC
LIMIT 100;

This output tells you:

Seq Scan vs Index Scan: Are you scanning the entire table or using an index?
Rows: How many rows does PostgreSQL estimate vs. actually process?
Cost: The relative expense of each operation
Time: Actual execution time (most important metric)

Large discrepancies between estimated and actual rows indicate missing statistics. Run

ANALYZE table_name

to refresh them.

Index Strategies

Single Column Indexes

Start with simple indexes on frequently filtered columns:

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at);

These are essential for any column used in WHERE clauses or JOIN conditions.

Multi-Column Indexes

For queries filtering on multiple columns, a multi-column index can outperform multiple single-column indexes:

-- For queries like: WHERE status = 'active' AND region = 'US'
CREATE INDEX idx_users_status_region ON users(status, region);

The column order matters. Put the most selective column first (the one that eliminates the most rows).

Covering Indexes (Include Clause)

When your query only needs columns from the index, PostgreSQL doesn't need to fetch the table. Use INCLUDE to add columns to the index:

CREATE INDEX idx_orders_user_created 
ON orders(user_id, created_at) 
INCLUDE (status, total_amount);

Now queries selecting those columns can use an index-only scan.

Partial Indexes

For queries that frequently filter by a condition, a partial index is more efficient than indexing everything:

-- Only index active users (saves space and improves performance)
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

This index is smaller and faster than indexing all users.

Query Optimization Techniques

N+1 Problem

The classic problem: fetching a parent and then querying for children in a loop.

-- Bad: N+1 queries
SELECT * FROM orders;
-- Then in application code:
for each order:
  SELECT items FROM order_items WHERE order_id = order.id;

-- Good: Single query with JOIN
SELECT o.*, i.*
FROM orders o
LEFT JOIN order_items i ON o.id = i.order_id
ORDER BY o.id, i.id;

Use JOINs to fetch related data in one query.

Avoiding Full Table Scans

-- Bad: Creates a function call for every row
SELECT * FROM orders WHERE DATE(created_at) = '2024-01-01';

-- Good: Let PostgreSQL use the index
SELECT * FROM orders 
WHERE created_at >= '2024-01-01' 
AND created_at < '2024-01-02';

Functions on indexed columns prevent index usage. Use range conditions instead.

Pagination

-- Bad: OFFSET is slow because it scans N rows to skip them
SELECT * FROM users ORDER BY id LIMIT 20 OFFSET 1000000;

-- Good: Use keyset pagination (seek method)
SELECT * FROM users 
WHERE id > :last_id 
ORDER BY id 
LIMIT 20;

For large offsets, keyset pagination is dramatically faster.

Connection Pooling with PgBouncer

Even with optimal queries, connection overhead adds up. PgBouncer reduces PostgreSQL connection count.

; pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
min_pool_size = 10
reserve_pool_size = 5

Configure it as a proxy between your application and PostgreSQL:

Application -> PgBouncer (on port 6432) -> PostgreSQL (port 5432)

With transaction mode, each client can share a single PostgreSQL connection. This reduces PostgreSQL connection overhead significantly.

Monitoring and Alerting

Query Performance View

Create a view to identify slow queries:

CREATE VIEW slow_queries AS
SELECT query, calls, mean_exec_time, max_exec_time
FROM pg_stat_statements
WHERE mean_exec_time > 100
ORDER BY mean_exec_time DESC;

Use this weekly to find optimization opportunities.

Table Size Monitoring

SELECT schemaname, tablename, 
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Growing tables may need partitioning or cleanup.

Cache Hit Ratio

SELECT 
  sum(heap_blks_read) as heap_read, 
  sum(heap_blks_hit) as heap_hit,
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM pg_statio_user_tables;

Aim for 99%+ cache hit ratio. If lower, you need more RAM or better indexing.

Configuration Tuning

Key settings in

postgresql.conf

# Memory
shared_buffers = 256MB          # 25% of system RAM
effective_cache_size = 1GB      # 50-75% of system RAM
work_mem = 4MB                  # RAM per operation

# Connections
max_connections = 200
max_prepared_transactions = 100

# Query Planning
random_page_cost = 1.1          # For SSD (default 4.0 for HDD)
effective_io_concurrency = 200  # For SSD

After changing config, reload with:

SELECT pg_reload_conf();

Real-World Example

A system processing 100k orders/day had slow reports. Analysis revealed:

Missing index on
```
created_at
```
in orders table
N+1 problem: fetching order items separately per order
No connection pooling: PostgreSQL had 500+ connections
```
work_mem
```
set too low, causing disk-based sorts

Fixes:

CREATE INDEX idx_orders_created ON orders(created_at);
CREATE INDEX idx_order_items_order ON order_items(order_id);

Combined with query rewrites, connection pooling, and config tuning, query time dropped from 12 seconds to 150ms.

Conclusion

PostgreSQL performance tuning isn't magic. It's a systematic process:

Measure with
```
EXPLAIN ANALYZE
```
Add indexes strategically
Rewrite queries to use them
Monitor production behavior
Iterate

Start with the basics: proper indexing and query optimization. Connection pooling is table stakes for any production system. Advanced techniques like partitioning and materialized views solve specific problems but aren't always necessary.

The best time to think about performance is during design. The second best time is now, with these tools in hand.