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
Standardmäßig ist der Prompt ausgewählt, der zuerst die Quelle prüft. Sie können zu einem direkten Befehl wechseln oder eine lokale Kopie herunterladen.
Quelldateien prüfen
Lesen Sie SKILL.md und alle von SkillsMP angezeigten Begleitdateien, bevor Sie sich für eine Installation entscheiden.
Mit Codex oder Claude installieren Kopieren Sie diesen Prompt, fügen Sie ihn in Codex, Claude oder einen anderen Assistant ein und lassen Sie die Skill-Seite prüfen und installieren.
Ein direkter Befehl überspringt den Prüf-Prompt. Prüfen Sie die Quelle, bevor Sie ihn ausführen.
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;
