Postgresql Performance Optimization

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 Use when this capability is needed.

tomevault-io Updated Sep 17, 2026

Install

Press C to copy

$ npx get-skill add tomevault-io/postgresql-performance-optimization

Installs into ~/.claude/skills (or .claude/skills inside a project). Restart Claude Code to pick it up.

＋ Add to plugin

README badge

[![SkillMD](https://skillmd.com/badge/tomevault-io/postgresql-performance-optimization.svg)](https://skillmd.com/skills/tomevault-io/postgresql-performance-optimization)

Trust & safety

2 of 3 audits passed

Last audited Sep 25, 2026 · commit c5a0a63

Works with: Claude Code Claude.ai OpenAI Codex +

File contents

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

-- 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

-- 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

-- 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

# 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

-- 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)

# 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

-- 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

-- 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

-- 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 time
SELECT schemaname, tablename, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE schemaname = 'public';

Testing & Monitoring

-- 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?"

Converted and distributed by TomeVault — claim your Tome and manage your conversions.

---name: postgresql-performance-optimizationdescription: 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 Use when this capability is needed.---# PostgreSQL Performance Optimization## OverviewMaster 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 queriesEXPLAIN (ANALYZE, BUFFERS, VERBOSE, COSTS, TIMING)SELECT u.name, COUNT(o.id) as order_countFROM users uLEFT JOIN orders o ON u.id = o.user_idWHERE u.created_at > NOW() - INTERVAL '30 days'GROUP BY u.id, u.nameORDER BY order_count DESCLIMIT 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 productsUSING 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 storesUSING 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 columnsSELECT 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 clauseSELECT u.name, o.id, o.totalFROM users uLEFT JOIN orders o ON u.id = o.user_id;-- BAD: Function in WHERE clause prevents index useSELECT * FROM users WHERE LOWER(email) = 'user@example.com';-- GOOD: Functional index or case-insensitive comparisonCREATE INDEX idx_users_email_lower ON users(LOWER(email));SELECT * FROM users WHERE LOWER(email) = 'user@example.com';-- Or use CITEXT typeALTER TABLE users ALTER COLUMN 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'UNION ALLSELECT * FROM products WHERE category = 'books';```### 4. Connection Pooling```python# BAD: Opening connection per requestimport psycopg2def 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, AsyncSessionfrom sqlalchemy.orm import sessionmakerengine = 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 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 documentsUSING 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 similarityFROM documentsORDER BY embedding <=> '[0.1, 0.2, ...]'::vectorLIMIT 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 RAMeffective_cache_size = 12GB # 75% of RAMwork_mem = 64MB # Per operation memorymaintenance_work_mem = 1GB # For VACUUM, CREATE INDEX# Query Plannerrandom_page_cost = 1.1 # SSD (4.0 for HDD)effective_io_concurrency = 200 # SSD parallelism# Write-Ahead Log (WAL)wal_buffers = 16MBmax_wal_size = 4GBmin_wal_size = 1GBcheckpoint_completion_target = 0.9# Connectionsmax_connections = 200# Logginglog_min_duration_statement = 1000 # Log queries >1slog_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 queriesSELECT query, calls, total_exec_time, mean_exec_time, max_exec_timeFROM pg_stat_statementsORDER BY mean_exec_time DESCLIMIT 10;-- Auto-explain for slow queries (in postgresql.conf)-- shared_preload_libraries = 'auto_explain'-- auto_explain.log_min_duration = '1s'-- UUID generationCREATE EXTENSION IF NOT EXISTS "uuid-ossp";-- Full-text search (built-in, no extension needed)SELECT * FROM productsWHERE to_tsvector('english', name) @@ to_tsquery('laptop & portable');```## Common Anti-Patterns### ❌ DON'T: Use OFFSET for pagination```sql-- BAD: OFFSET becomes slow with large offsetsSELECT * FROM postsORDER BY created_at DESCLIMIT 50 OFFSET 100000; -- Scans 100,050 rows!-- GOOD: Keyset pagination (cursor-based)SELECT * FROM postsWHERE created_at < '2024-01-01 12:00:00'ORDER BY created_at DESCLIMIT 50;```### ❌ DON'T: Run VACUUM FULL in production```sql-- BAD: Locks entire tableVACUUM 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 timeSELECT schemaname, tablename, last_analyze, last_autoanalyzeFROM pg_stat_user_tablesWHERE schemaname = 'public';```## Testing & Monitoring```sql-- 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 bloatFROM pg_tablesWHERE schemaname = 'public'ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESCLIMIT 10;-- Check index usageSELECT schemaname || '.' || tablename AS table, indexname AS index, idx_scan as scans, pg_size_pretty(pg_relation_size(indexrelid)) AS sizeFROM pg_stat_user_indexesWHERE 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 sizeFROM pg_stat_user_tablesWHERE seq_scan > 0 AND idx_scan = 0 AND pg_total_relation_size(schemaname||'.'||tablename) > 1000000ORDER 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?"---> Converted and distributed by [TomeVault](https://tomevault.io/claim/bobmatnyc) — claim your Tome and manage your conversions.

tomevault-io/skills-registry/tree/main/bobmatnyc--mcp-skillset--postgresql-optimization commit c5a0a6389f

Frequently asked questions

npx get-skill add tomevault-io/postgresql-performance-optimization