PostgreSQL Performance Optimization

# PostgreSQL Performance Optimization

## Overview

Master PostgreSQL performance optimization with modern techniques for query tuning, indexing, and AI/ML workloads. With 55% of Postgres developers adopting AI tools in 2024, understanding pgvector and performance optimization is critical for building scalable data-intensive applications.

## When to Use This Skill

- Slow queries requiring optimization (>100ms response time)
- Designing database schemas for high-performance applications
- Implementing vector similarity search for AI/ML features
- Scaling PostgreSQL for high-concurrency workloads
- Migrating from NoSQL to PostgreSQL for better consistency
- Optimizing ORMs (SQLAlchemy, Django ORM) for production

## Core Principles

### 1. EXPLAIN ANALYZE is Your Best Friend

```sql
-- ALWAYS use EXPLAIN ANALYZE for slow queries
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, COSTS, TIMING)
SELECT u.name, 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.name
ORDER BY order_count DESC
LIMIT 100;

-- Read output top-to-bottom, focus on:
-- 1. Seq Scan (bad for large tables) vs Index Scan (good)
-- 2. Actual time vs Planning time
-- 3. Rows estimates vs actual rows
-- 4. Buffers (disk I/O indicators)
```

**Key Metrics**:
- **Planning Time**: How long query planner took (<10ms ideal)
- **Execution Time**: Actual query runtime (<100ms for OLTP)
- **Rows**: Estimated vs actual (mismatches indicate stale statistics)
- **Buffers**: Shared hits (good), reads (disk I/O, slow)

### 2. Indexing Strategy

```sql
-- B-Tree Index (default, most common)
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);

-- Partial Index (smaller, faster for filtered queries)
CREATE INDEX idx_active_users ON users(email)
WHERE is_active = true AND deleted_at IS NULL;

-- Covering Index (includes extra columns to avoid table lookups)
CREATE INDEX idx_orders_covering ON orders(user_id, status)
INCLUDE (created_at, total_amount);

-- GIN Index (for full-text search, JSONB, arrays)
CREATE INDEX idx_products_search ON products
USING GIN (to_tsvector('english', name || ' ' || description));

CREATE INDEX idx_tags_gin ON posts USING GIN(tags);

-- GiST Index (for geometric data, ranges, full-text)
CREATE INDEX idx_locations_gist ON stores
USING GIST (location);

-- BRIN Index (block range, time-series data)
CREATE INDEX idx_logs_created ON logs USING BRIN(created_at);
```

**Index Selection Rules**:
- Equality filters (`WHERE id = 123`) → B-Tree
- Range queries (`WHERE created_at > NOW() - '7 days'`) → B-Tree
- Full-text search → GIN with `tsvector`
- JSONB queries → GIN
- Time-series/append-only → BRIN (90% smaller than B-Tree)
- Vector similarity (`ORDER BY embedding <=> query_vector`) → HNSW (pgvector)

### 3. Query Optimization Patterns

```sql
-- BAD: SELECT *
SELECT * FROM users WHERE email = 'user@example.com';

-- GOOD: Select only needed columns
SELECT id, name, email FROM users WHERE email = 'user@example.com';

-- BAD: N+1 Query Problem
-- Application code:
FOR user IN (SELECT id FROM users):
    SELECT * FROM orders WHERE user_id = user.id;  -- N queries!

-- GOOD: JOIN or use IN clause
SELECT u.name, o.id, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- BAD: Function in WHERE clause prevents index use
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- GOOD: Functional index or case-insensitive comparison
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- Or use CITEXT type
ALTER TABLE users ALTER COLUMN email TYPE CITEXT;
SELECT * FROM users WHERE email = 'USER@EXAMPLE.COM';  -- Works!

-- BAD: OR conditions often don't use indexes
SELECT * FROM products WHERE category = 'electronics' OR category = 'books';

-- GOOD: Use IN or UNION
SELECT * FROM products WHERE category IN ('electronics', 'books');

-- GOOD: UNION for complex OR (sometimes faster)
SELECT * FROM products WHERE category = 'electronics'
UNION ALL
SELECT * FROM products WHERE category = 'books';
```

### 4. Connection Pooling

```python
# BAD: Opening connection per request
import psycopg2

def get_user(user_id):
    conn = psycopg2.connect("postgresql://localhost/db")
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
    result = cursor.fetchone()
    conn.close()  # Creates new connection each time!
    return result

# GOOD: Use connection pooling (asyncpg with FastAPI)
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import sessionmaker

engine = create_async_engine(
    "postgresql+asyncpg://localhost/db",
    pool_size=20,          # Max persistent connections
    max_overflow=0,        # Don't allow exceeding pool_size
    pool_pre_ping=True,    # Verify connection before using
    echo_pool=True,        # Log pool activity (disable in production)
)

AsyncSessionLocal = sessionmaker(
    engine, class_=AsyncSession, expire_on_commit=False
)
```

**Connection Pool Settings**:
- `pool_size`: Core connections (20-50 for web apps)
- `max_overflow`: Extra connections (0 to prevent overload)
- `pool_recycle`: Close connections after N seconds (3600 = 1 hour)

### 5. pgvector for AI/ML Workloads

```sql
-- Install pgvector extension
CREATE EXTENSION vector;

-- Create table with vector column
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content TEXT,
    embedding vector(1536)  -- OpenAI ada-002 dimensions
);

-- Create HNSW index for fast similarity search
CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- Similarity search (finds nearest neighbors)
SELECT id, content, 1 - (embedding <=> '[0.1, 0.2, ...]'::vector) AS similarity
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 10;

-- Operators:
-- <->  L2 distance (Euclidean)
-- <#>  Inner product
-- <=>  Cosine distance (most common for LLM embeddings)
```

**pgvector Best Practices**:
- Use HNSW index for production (10-100x faster than IVFFlat)
- Normalize embeddings before storage for cosine similarity
- Tune `m` (16-48) and `ef_construction` (64-200) for accuracy/speed tradeoff
- Use `SET ivfflat.probes = 10` for IVFFlat search quality

## Performance Tuning Checklist

### PostgreSQL Configuration (postgresql.conf)

```ini
# Memory Settings (for 16GB RAM server)
shared_buffers = 4GB              # 25% of RAM
effective_cache_size = 12GB       # 75% of RAM
work_mem = 64MB                   # Per operation memory
maintenance_work_mem = 1GB        # For VACUUM, CREATE INDEX

# Query Planner
random_page_cost = 1.1            # SSD (4.0 for HDD)
effective_io_concurrency = 200    # SSD parallelism

# Write-Ahead Log (WAL)
wal_buffers = 16MB
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9

# Connections
max_connections = 200

# Logging
log_min_duration_statement = 1000  # Log queries >1s
log_line_prefix = '%m [%p] %u@%d '
log_statement = 'none'             # 'all' for debugging
```

### Essential Extensions

```sql
-- Query statistics (find slow queries)
CREATE EXTENSION pg_stat_statements;

-- View top 10 slowest queries
SELECT query, calls, total_exec_time, mean_exec_time, max_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Auto-explain for slow queries (in postgresql.conf)
-- shared_preload_libraries = 'auto_explain'
-- auto_explain.log_min_duration = '1s'

-- UUID generation
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Full-text search (built-in, no extension needed)
SELECT * FROM products
WHERE to_tsvector('english', name) @@ to_tsquery('laptop & portable');
```

## Common Anti-Patterns

### ❌ DON'T: Use OFFSET for pagination

```sql
-- BAD: OFFSET becomes slow with large offsets
SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 50 OFFSET 100000;  -- Scans 100,050 rows!

-- GOOD: Keyset pagination (cursor-based)
SELECT * FROM posts
WHERE created_at < '2024-01-01 12:00:00'
ORDER BY created_at DESC
LIMIT 50;
```

### ❌ DON'T: Run VACUUM FULL in production

```sql
-- BAD: Locks entire table
VACUUM FULL users;  -- Hours of downtime!

-- GOOD: Regular VACUUM (non-blocking)
VACUUM ANALYZE users;

-- Or configure autovacuum (postgresql.conf)
-- autovacuum = on
-- autovacuum_naptime = 1min
```

### ❌ DON'T: Ignore table statistics

```sql
-- Run ANALYZE regularly (auto after bulk inserts)
ANALYZE users;

-- Check last analyze time
SELECT schemaname, tablename, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE schemaname = 'public';
```

## Testing & Monitoring

```sql
-- Check bloat in tables and indexes
SELECT
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) -
                   pg_relation_size(schemaname||'.'||tablename)) AS bloat
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 10;

-- Check index usage
SELECT
    schemaname || '.' || tablename AS table,
    indexname AS index,
    idx_scan as scans,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC;

-- Missing indexes (sequential scans on large tables)
SELECT
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_stat_user_tables
WHERE seq_scan > 0 AND idx_scan = 0
    AND pg_total_relation_size(schemaname||'.'||tablename) > 1000000
ORDER BY seq_tup_read DESC;
```

## Related Skills

- **fastapi-web-development**: Optimize FastAPI + PostgreSQL integration
- **observability-monitoring**: Monitor PostgreSQL with Prometheus
- **systematic-debugging**: Debug query performance issues

## Additional Resources

- PostgreSQL Performance: https://wiki.postgresql.org/wiki/Performance_Optimization
- pgvector Documentation: https://github.com/pgvector/pgvector
- Use The Index, Luke: https://use-the-index-luke.com
- pg_stat_statements: https://www.postgresql.org/docs/current/pgstatstatements.html

## Example Questions

- "Why is this query slow? How do I read the EXPLAIN plan?"
- "What type of index should I use for full-text search?"
- "How do I implement vector similarity search with pgvector?"
- "Show me how to find unused indexes in my database"
- "What's the best way to paginate large result sets?"
- "How do I configure connection pooling for a high-traffic API?"