PostgreSQL Performance Optimization Guide

This guide covers advanced techniques for optimizing PostgreSQL performance, from query optimization to system-level tuning.

Query Optimization

Understanding Query Plans

-- Get query plan
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

-- Get detailed query plan
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) 
SELECT * FROM users WHERE email = 'user@example.com';

Common Query Optimizations

Use Indexes Effectively

-- Create index for frequently queried columns
CREATE INDEX idx_users_email ON users(email);

-- Create partial index for specific conditions
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

**Avoid SELECT ***

-- Instead of
SELECT * FROM users;

-- Use specific columns
SELECT id, email, name FROM users;

Use LIMIT with ORDER BY

-- Add index for the sort
CREATE INDEX idx_users_created_at ON users(created_at);

-- Use limit with order by
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

Indexing Strategies

Types of Indexes

B-tree Index (Default)

CREATE INDEX idx_users_name ON users(name);

Best for equality and range queries
Default index type
Good for most use cases

Hash Index

CREATE INDEX idx_users_email_hash ON users USING hash(email);

Best for equality comparisons
Smaller than B-tree
No support for range queries

GiST Index

CREATE INDEX idx_geometries ON geometries USING gist(geom);

For geometric data
Full-text search
Custom data types

GIN Index

CREATE INDEX idx_users_tags ON users USING gin(tags);

For array values
Full-text search
JSON/JSONB data

Index Maintenance

-- Rebuild index
REINDEX INDEX idx_users_name;

-- Analyze table
ANALYZE users;

-- Vacuum analyze
VACUUM ANALYZE users;

Connection Optimization

Connection Pooling

# Install pgBouncer
sudo apt install pgbouncer

# Configure pgBouncer
# /etc/pgbouncer/pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

Connection Settings

1
2
3

# postgresql.conf
max_connections = 100
superuser_reserved_connections = 3

Storage Optimization

Table Partitioning

-- Create partitioned table
CREATE TABLE orders (
    id SERIAL,
    order_date DATE,
    customer_id INTEGER,
    amount DECIMAL
) PARTITION BY RANGE (order_date);

-- Create partitions
CREATE TABLE orders_2023_q1 PARTITION OF orders
    FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');

TOAST Tables

Large values are automatically compressed
Stored in separate TOAST tables

Consider column storage strategy:

ALTER TABLE my_table ALTER COLUMN large_text SET STORAGE EXTENDED;

Monitoring and Analysis

Performance Views

-- Check table statistics
SELECT * FROM pg_stat_user_tables;

-- Check index usage
SELECT * FROM pg_stat_user_indexes;

-- Check query statistics
SELECT * FROM pg_stat_statements;

Logging Slow Queries

1 2	`# postgresql.conf log_min_duration_statement = 1000 # milliseconds`

Common Performance Issues

1. Slow Queries

Use EXPLAIN ANALYZE
Check index usage
Optimize query structure

2. High I/O

Increase shared_buffers
Use SSDs
Implement connection pooling

3. Memory Pressure

Adjust work_mem
Monitor connection count
Use connection pooling

4. Lock Contention

-- Check for locks
SELECT * FROM pg_locks;

-- Check for blocked queries
SELECT blocked_locks.pid AS blocked_pid,
       blocked_activity.usename AS blocked_user,
       blocking_locks.pid AS blocking_pid,
       blocking_activity.usename AS blocking_user
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks 
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;

Best Practices

Regular Maintenance
Run VACUUM regularly
Update statistics with ANALYZE
Monitor index usage
Query Design
Use appropriate indexes
Avoid SELECT *
Use LIMIT with ORDER BY
Configuration
Start with conservative settings
Monitor and adjust gradually
Document changes
Monitoring
Set up regular monitoring
Track slow queries
Monitor system resources
Configuration Guide
Monitoring and Maintenance
Indexing Strategies