Analyze and optimize PostgreSQL queries for OLTP and OLAP workloads with AI-assisted performance tuning, indexing strategies, and execution plan analysis. Interpret EXPLAIN ANALYZE plans, identify bottlenecks (sequential scans, nested loops, hash joins), design optimal indexes (B-tree, GIN, BRIN, composite, partial), rewrite queries for better performance, and tune database configuration parameters. Includes pg_stat_statements integration for slow query identification, parallel query execution optimization, JIT compilation tuning, advanced partitioning strategies, and vacuum/autovacuum configuration for high-write workloads.

Content

PostgreSQL Query Optimization Skill

What This Skill Enables

Claude can analyze PostgreSQL query performance, interpret EXPLAIN plans, design optimal indexes, and tune database configurations for specific workloads (OLTP, OLAP, or hybrid). With expertise in PostgreSQL 16+ features including parallel query execution, JIT compilation, and advanced partitioning strategies, Claude helps achieve sub-millisecond query times for high-traffic applications.

Compatibility

Native

• Claude Code / Claude: native skill usage via SKILL.md.
• Codex/OpenAI workflows: compatible with Agent Skills-style SKILL.md content as reusable workflow instructions.

Manual Adaptation

• Gemini CLI: native skill usage via .gemini/skills/<skill-name>/SKILL.md or .agents/skills/<skill-name>/SKILL.md where supported.
• Cursor: use the generated .cursor/rules/*.mdc adapter for project rules.
• OpenClaw and similar agents: use the same skill content as a reusable prompt/workflow file when native skill import is unavailable.

Prerequisites

Required:

• Claude Pro subscription or Claude Code CLI
• PostgreSQL 14+ installed (16+ recommended)
• Access to database with slow queries or performance issues
• Basic SQL knowledge

What Claude handles automatically:

• Analyzing EXPLAIN ANALYZE output
• Identifying missing or inefficient indexes
• Suggesting query rewrites for better performance
• Recommending configuration parameter tuning
• Detecting N+1 query problems
• Proposing table partitioning strategies
• Analyzing vacuum and autovacuum settings
• Identifying connection pooling needs

How to Use This Skill

Analyze Slow Query

Prompt: "My PostgreSQL query is taking 5 seconds. Here's the query: [paste query]. Analyze the execution plan and suggest optimizations."

Claude will:

1. Run EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
2. Identify bottlenecks (seq scans, nested loops)
3. Suggest missing indexes with CREATE INDEX statements
4. Rewrite query if needed (JOIN order, subquery optimization)
5. Estimate performance improvement
6. Provide before/after comparison

Index Strategy Design

Prompt: "Design an optimal indexing strategy for a high-traffic e-commerce database with 10M products. Include queries: product search by name, filter by category and price range, sort by popularity."

Claude will:

1. Analyze query patterns and access patterns
2. Create B-tree indexes for equality/range queries
3. Add GIN indexes for full-text search
4. Design composite indexes for multi-column filters
5. Include partial indexes for filtered queries
6. Add BRIN indexes for time-series data
7. Calculate index maintenance overhead

Database Configuration Tuning

Prompt: "Tune PostgreSQL configuration for a 32GB RAM server running high-write OLTP workload with 200 concurrent connections."

Claude will:

1. Set shared_buffers (25% of RAM = 8GB)
2. Configure work_mem per connection
3. Adjust max_connections and connection pooling
4. Tune WAL settings for write performance
5. Configure autovacuum for high-write scenarios
6. Set effective_cache_size
7. Enable parallel query workers

Query Rewriting for Performance

Prompt: "Rewrite this slow query to eliminate the N+1 problem: [paste ORM-generated query with multiple subqueries]."

Claude will:

1. Identify N+1 or N+M pattern
2. Convert subqueries to JOINs
3. Use CTEs for readability
4. Apply window functions for ranking
5. Implement LATERAL joins where appropriate
6. Add proper indexes for new query
7. Validate result correctness

Tips for Best Results

1. Always Use EXPLAIN ANALYZE: Share full EXPLAIN (ANALYZE, BUFFERS) output with Claude. The BUFFERS option reveals I/O patterns crucial for optimization.

2. Provide Table Schemas: Include CREATE TABLE statements and existing indexes. Claude needs column types and constraints for accurate recommendations.

3. Share Query Frequency: Mention if a query runs once per day or 10,000 times per second. Optimization strategies differ dramatically.

4. Workload Type Matters: OLTP (many small transactions) and OLAP (complex analytics) require opposite tuning. Specify your workload.

5. Include Real Data Volume: "1000 rows" vs "100M rows" changes everything. Share actual table sizes with pg_size_pretty.

6. Monitor After Changes: Ask Claude to generate monitoring queries to verify improvements don't cause regressions elsewhere.

Common Workflows

Complete Performance Audit

"Perform a comprehensive PostgreSQL performance audit:
1. Identify top 10 slowest queries from pg_stat_statements
2. Analyze EXPLAIN plans for each
3. Detect missing indexes with pg_stat_user_tables
4. Find bloated tables needing VACUUM FULL
5. Review configuration parameters
6. Check for long-running transactions blocking others
7. Provide prioritized optimization action plan"

Time-Series Optimization

"Optimize PostgreSQL for time-series data:
1. 100M rows of sensor data per month
2. Queries filter by device_id and time range
3. Need 90-day retention with automated archival
4. Implement declarative partitioning by month
5. Add BRIN indexes on timestamp columns
6. Configure autovacuum for partition management
7. Create aggregate materialized views"

Full-Text Search Tuning

"Build high-performance full-text search:
1. Search across title, description, tags fields
2. Support phrase queries and ranking
3. Handle 50M documents
4. Sub-100ms query response time
5. Use GIN indexes with tsvector
6. Implement trigram similarity for typo tolerance
7. Add weighted search across columns"

Replication Lag Analysis

"Debug PostgreSQL replication lag:
1. Replica is 30 seconds behind primary
2. Analyze pg_stat_replication metrics
3. Check for long-running queries on replica
4. Identify write-heavy tables causing lag
5. Tune max_wal_senders and wal_keep_size
6. Recommend synchronous vs asynchronous replication
7. Implement connection pooling strategy"

Troubleshooting

Issue: Query still slow after adding index Solution: Index may not be used. Check EXPLAIN plan shows Index Scan (not Seq Scan). Run ANALYZE to update statistics. Consider index-only scans with INCLUDE columns or covering indexes.

Issue: Database running out of connections Solution: Implement connection pooling with PgBouncer or pgpool-II. Reduce max_connections and increase per-connection work_mem. Fix application connection leaks.

Issue: Autovacuum not keeping up Solution: Lower autovacuum_vacuum_scale_factor and autovacuum_vacuum_threshold for high-write tables. Increase autovacuum_max_workers. Consider manual VACUUM during maintenance windows.

Issue: Query fast in development, slow in production Solution: Production has different data distribution. Run ANALYZE on production. Check if production has proper indexes. Compare EXPLAIN plans between environments.

Issue: Disk I/O bottleneck Solution: Increase shared_buffers for caching. Use NVMe SSDs. Consider table partitioning to reduce I/O per query. Implement read replicas for read-heavy workloads.

Issue: Connection pool exhausted Solution: Tune pool size based on connections = ((core_count * 2) + effective_spindle_count). Implement queue_timeout. Add monitoring for pool saturation.

Learn More

• PostgreSQL Performance Tuning Guide
• Use The Index, Luke! - SQL Indexing Guide
• Depesz EXPLAIN Visualizer
• PGTune Configuration Calculator
• pgAdmin Query Tool
• pg_stat_statements Extension

Features

• EXPLAIN plan analysis and visualization with ANALYZE, BUFFERS, VERBOSE options
• Automatic index recommendation (B-tree, GIN, BRIN, composite, partial, covering indexes)
• Workload-specific tuning (OLTP vs OLAP configurations)
• Query rewriting for performance (JOIN optimization, subquery elimination, CTEs, window functions)
• pg_stat_statements integration for slow query identification and monitoring
• Parallel query execution optimization with worker configuration
• JIT compilation tuning for complex analytical queries
• Advanced partitioning strategies (range, list, hash, composite partitioning)

Use Cases

• Optimize slow database queries with EXPLAIN plan analysis
• Design indexing strategies for high-traffic applications
• Tune PostgreSQL configuration for specific workloads (OLTP/OLAP)
• Identify and fix N+1 query problems in ORM-generated queries
• Optimize time-series data queries with BRIN indexes and partitioning
• Performance audit and monitoring with pg_stat_statements

• Source repositorygithub.com 2025-10-16
• Documentationpostgresql.org
• Package (SHA-256 pinned)/downloads/skills/postgresql-query-optimization.zip