Production-grade PostgreSQL query optimization, indexing strategies, performance tuning, and modern features including pgvector for AI/ML workloads. Master EXPLAIN plans, query analysis, and database design for high-performance applications
Instrucciones de origen · Vista previa de solo lectura
name
PostgreSQL Performance Optimization
skill_id
postgresql-optimization
version
1.0.0
description
Production-grade PostgreSQL query optimization, indexing strategies, performance tuning, and modern features including pgvector for AI/ML workloads. Master EXPLAIN plans, query analysis, and database design for high-performance applications
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.
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
-- 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
LEFTJOIN orders o ON u.id = o.user_id
WHERE u.created_at > NOW() -INTERVAL'30 days'GROUPBY u.id, u.name
ORDERBY 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)
: Shared hits (good), reads (disk I/O, slow)
Buffers
2. Indexing Strategy
-- 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 =trueAND deleted_at ISNULL;
-- 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);
Time-series/append-only → BRIN (90% smaller than B-Tree)
Vector similarity (ORDER BY embedding <=> query_vector) → HNSW (pgvector)
3. Query Optimization Patterns
-- BAD: SELECT *SELECT*FROM users WHERE email ='user@example.com';
-- GOOD: Select only needed columnsSELECT id, name, email FROM users WHERE email ='user@example.com';
-- BAD: N+1 Query Problem-- Application code:FORuserIN (SELECT id FROM users):
SELECT*FROM orders WHERE user_id = user.id; -- N queries!-- GOOD: JOIN or use IN clauseSELECT u.name, o.id, o.total
FROM users u
LEFTJOIN orders o ON u.id = o.user_id;
-- BAD: Function in WHERE clause prevents index useSELECT*FROM users WHERELOWER(email) ='user@example.com';
-- GOOD: Functional index or case-insensitive comparisonCREATE INDEX idx_users_email_lower ON users(LOWER(email));
SELECT*FROM users WHERELOWER(email) ='user@example.com';
-- Or use CITEXT typeALTER TABLE users ALTERCOLUMN email TYPE CITEXT;
SELECT*FROM users WHERE email ='USER@EXAMPLE.COM'; -- Works!-- BAD: OR conditions often don't use indexesSELECT*FROM products WHERE category ='electronics'OR category ='books';
-- GOOD: Use IN or UNIONSELECT*FROM products WHERE category IN ('electronics', 'books');
-- GOOD: UNION for complex OR (sometimes faster)SELECT*FROM products WHERE category ='electronics'UNIONALLSELECT*FROM products WHERE category ='books';
4. Connection Pooling
# BAD: Opening connection per requestimport psycopg2
defget_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
-- Install pgvector extensionCREATE EXTENSION vector;
-- Create table with vector columnCREATE TABLE documents (
id SERIAL PRIMARY KEY,
content TEXT,
embedding vector(1536) -- OpenAI ada-002 dimensions
);
-- Create HNSW index for fast similarity searchCREATE 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
ORDERBY 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
-- 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
-- Run ANALYZE regularly (auto after bulk inserts)
ANALYZE users;
-- Check last analyze timeSELECT schemaname, tablename, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE schemaname ='public';
Testing & Monitoring
-- Check bloat in tables and indexesSELECT
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'ORDERBY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 10;
-- Check index usageSELECT
schemaname ||'.'|| tablename AStable,
indexname AS index,
idx_scan as scans,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE schemaname ='public'ORDERBY 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 >0AND idx_scan =0AND pg_total_relation_size(schemaname||'.'||tablename) >1000000ORDERBY seq_tup_read DESC;
