Skip to main content
skill CX Codex

postgresql-optimization · curiositech — Claude Code skill: install and usage

postgresql-optimization helps identify PostgreSQL bottlenecks: interprets EXPLAIN ANALYZE plans, selects indexing strategies, and tunes parameters like shared_buffers and work_mem.

MIT 190 installs ★ 274 updated 2026-10-30

Install into Claude Code · Skills CLI (Vercel) All methods and platforms ↓
npx skills add curiositech/some_claude_skills --skill postgresql-optimization -a claude-code
Open on GitHub

What postgresql-optimization does

The skill instructs the coding assistant on reading execution plans via EXPLAIN (ANALYZE, BUFFERS). It identifies sequential scans on large tables and rewrites N+1 query patterns using LATERAL JOIN or window functions. It also provides rules for building partial, expression, and covering indexes with the INCLUDE clause.

Beyond queries, it handles server parameters and connection pooling setups like PgBouncer. The agent adjusts shared_buffers and work_mem based on available server memory. To pinpoint the slowest queries in production, the database must have the pg_stat_statements extension enabled. Runtime stats remain inaccessible without it.

Partitioning guidance covers range, list, and hash strategies, alongside automation via pg_partman.

What it helps with

  • analyze EXPLAIN ANALYZE output to eliminate sequential scans
  • configure server memory settings like shared_buffers and work_mem
  • design partial and covering indexes for frequent queries
  • replace N+1 query patterns using LATERAL JOIN
  • configure connection pooling parameters in PgBouncer

Example prompts for the agent

  • Analyze this EXPLAIN (ANALYZE, BUFFERS) plan for the orders table and suggest indexes
  • Help me configure pgbouncer.ini for transaction pooling mode
  • Rewrite this N+1 query fetching posts and comments using a LATERAL JOIN

How to install postgresql-optimization

1Skills CLI (Vercel)

npx skills add curiositech/some_claude_skills --skill postgresql-optimization -a claude-code

2Manually: put the files in place

~/.claude/skills/postgresql-optimization/SKILL.md

Copy the whole skill folder; for one project use .claude/skills/postgresql-optimization/

Step by step, with commands for each platform: How to install skills in Claude Code: five ways

Questions about postgresql-optimization

How to install postgresql-optimization in Claude Code?

Run npx skills add curiositech/some_claude_skills --skill postgresql-optimization -a claude-code. You can also place the skill file directly at ~/.claude/skills/postgresql-optimization/SKILL.md.

What does postgresql-optimization do?

It guides the assistant through profiling slow SQL queries, selecting appropriate index types, and tuning production PostgreSQL settings.

Which PostgreSQL extensions are needed for full functionality?

Automated table partitioning relies on pg_partman, while tracking the slowest production queries requires enabling the pg_stat_statements extension.

Source text

SKILL.md · MIT
---
name: postgresql-optimization
description: Expert in PostgreSQL performance tuning, query optimization, and database administration. Specializes in EXPLAIN analysis, indexing strategies, connection pooling, partitioning, and production-grade
  PostgreSQL operations.
version: 1.0.0
metadata:
  category: database
  tags:
  - postgresql
  - sql
  - performance
  - indexing
  - query-optimization
  - database
  pairs-with:
  - skill: database-design-patterns
    reason: Schema design choices directly impact query performance and indexing effectiveness
  - skill: supabase-admin
    reason: Supabase runs PostgreSQL; optimization techniques apply directly to Supabase databases
  - skill: performance-profiling
    reason: Database query profiling identifies the slow queries that need PostgreSQL optimization
  - skill: drizzle-migrations
    reason: Index creation and schema changes through Drizzle must consider PostgreSQL performance impact
---

# PostgreSQL Optimization

## Overview

Expert in PostgreSQL performance tuning, query optimization, and database administration. Specializes in EXPLAIN analysis, indexing strategies, connection pooling, partitioning, and production-grade PostgreSQL operations.

## When to Use

- Diagnosing slow queries with EXPLAIN ANALYZE
- Creating optimal indexes for query patterns
- Designing database schemas for performance
- Configuring PostgreSQL for production workloads
- Implementing connection pooling (PgBouncer, Supavisor)
- Setting up partitioning for large tables
- Analyzing and reducing lock contention
- Migrating or upgrading PostgreSQL versions

## Capabilities

### Query Optimization
- EXPLAIN / EXPLAIN ANALYZE interpretation
- Query plan analysis and optimization
- Identifying sequential scans vs index scans
- Join optimization and query rewriting
- CTE vs subquery performance trade-offs
- Window function optimization

### Indexing Strategies
- B-tree, GIN, GiST, BRIN index selection
- Partial indexes for filtered queries
- Expression indexes for computed values
- Covering indexes (INCLUDE clause)
- Index-only scans optimization
- Concurrent index creation

### Schema Design
- Normalization vs denormalization trade-offs
- JSONB column design and indexing
- Array columns and operations
- Enum types vs lookup tables
- Foreign key cascade strategies
- Table inheritance and partitioning

### Configuration Tuning
- Memory settings (shared_buffers, work_mem, effective_cache_size)
- Connection limits and pooling
- WAL and checkpoint tuning
- Autovacuum configuration
- Statistics collection settings

### Advanced Features
- Partitioning (range, list, hash)
- Materialized views with refresh strategies
- Full-text search with tsvector/tsquery
- PostGIS geospatial queries
- Logical replication setup
- pg_stat_statements analysis

## Dependencies

Works well with:
- `database-modeler` - Schema design and ERD creation
- `data-pipeline-engineer` - ETL and data processing
- `site-reliability-engineer` - Database monitoring and alerting
- `nextjs-app-router-expert` - Full-stack data fetching

## Examples

### Reading EXPLAIN ANALYZE Output
```sql
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.*, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id;

-- Key metrics to look for:
-- - "Seq Scan" on large tables → needs index
-- - "Rows Removed by Filter" high → filter before join
-- - "Sort Method: external merge" → increase work_mem
-- - "Buffers: shared hit" vs "shared read" → cache efficiency
```

### Creating Effective Indexes
```sql
-- Basic B-tree for equality and range queries
CREATE INDEX CONCURRENTLY idx_orders_user_created
ON orders (user_id, created_at DESC);

-- Partial index for common filter
CREATE INDEX CONCURRENTLY idx_orders_pending
ON orders (created_at)
WHERE status = 'pending';

-- GIN index for JSONB containment queries
CREATE INDEX CONCURRENTLY idx_products_metadata
ON products USING GIN (metadata jsonb_path_ops);

-- Covering index to enable index-only scans
CREATE INDEX CONCURRENTLY idx_users_email_covering
ON users (email) INCLUDE (name, created_at);

-- Expression index for case-insensitive search
CREATE INDEX CONCURRENTLY idx_users_email_lower
ON users (LOWER(email));
```

### Optimizing N+1 Queries
```sql
-- BAD: N+1 pattern (1 + N queries)
SELECT * FROM posts WHERE user_id = $1;
-- Then for each post: SELECT * FROM comments WHERE post_id = $1;

-- GOOD: Single query with lateral join
SELECT p.*, c.comments
FROM posts p
LEFT JOIN LATERAL (
  SELECT json_agg(c.*) as comments
  FROM comments c
  WHERE c.post_id = p.id
) c ON true
WHERE p.user_id = $1;

-- GOOD: Window function for aggregates
SELECT
  p.*,
  COUNT(*) OVER (PARTITION BY p.user_id) as user_post_count
FROM posts p
WHERE p.user_id = $1;
```

### Table Partitioning
```sql
-- Create partitioned table by date range
CREATE TABLE events (
  id BIGSERIAL,
  event_type TEXT NOT NULL,
  payload JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- Create monthly partitions
CREATE TABLE events_2024_01 PARTITION OF events
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE events_2024_02 PARTITION OF events
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Automate partition creation with pg_partman
CREATE EXTENSION pg_partman;
SELECT partman.create_parent('public.events', 'created_at', 'native', 'monthly');
```

### Connection Pooling Config (PgBouncer)
```ini
; pgbouncer.ini

[databases]
myapp = host=localhost dbname=myapp

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

; Pool settings
pool_mode = transaction        ; Recommended for most apps
max_client_conn = 1000
default_pool_size = 20
reserve_pool_size = 5

; Timeouts
server_idle_timeout = 600
client_idle_timeout = 0
```

### Performance Configuration
```sql
-- Check current settings
SHOW shared_buffers;        -- ~25% of RAM
SHOW effective_cache_size;  -- ~75% of RAM
SHOW work_mem;              -- Per-operation, start small (64MB)
SHOW maintenance_work_mem;  -- For VACUUM, CREATE INDEX (512MB-1GB)

-- Recommended production settings (for 32GB RAM server)
ALTER SYSTEM SET shared_buffers = '8GB';
ALTER SYSTEM SET effective_cache_size = '24GB';
ALTER SYSTEM SET work_mem = '64MB';
ALTER SYSTEM SET maintenance_work_mem = '1GB';
ALTER SYSTEM SET random_page_cost = 1.1;  -- For SSD storage
ALTER SYSTEM SET effective_io_concurrency = 200;  -- For SSD

-- Reload configuration
SELECT pg_reload_conf();
```

### Finding Slow Queries
```sql
-- Enable pg_stat_statements
CREATE EXTENSION pg_stat_statements;

-- Top 10 slowest queries by total time
SELECT
  round(total_exec_time::numeric, 2) as total_ms,
  calls,
  round(mean_exec_time::numeric, 2) as avg_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER())::numeric, 2) as pct,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Queries with most I/O
SELECT
  round(shared_blks_read::numeric, 2) as disk_reads,
  round(shared_blks_hit::numeric, 2) as cache_hits,
  round(100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0), 2) as cache_hit_ratio,
  query
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 10;
```

### Analyzing Table Bloat
```sql
-- Check table bloat
SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) as total_size,
  pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) as table_size,
  n_dead_tup,
  n_live_tup,
  round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) as dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- Manual VACUUM for critical tables
VACUUM (VERBOSE, ANALYZE) orders;

-- Reclaim space (requires exclusive lock)
VACUUM FULL orders;  -- Use during maintenance window
```

## Best Practices

1. **Always use EXPLAIN ANALYZE** - Don't guess, measure actual query performance
2. **Create indexes CONCURRENTLY** - Avoid blocking writes during index creation
3. **Partial indexes for hot paths** - Index only the rows you query frequently
4. **Use connection pooling** - PgBouncer or Supavisor for production
5. **Monitor pg_stat_statements** - Track query performance over time
6. **Regular ANALYZE** - Keep statistics current for query planner
7. **Avoid SELECT *** - Only fetch columns you need
8. **Batch large updates** - Process in chunks to avoid lock contention
9. **Use prepared statements** - Reduce parsing overhead for repeated queries

## Common Pitfalls

- **Missing indexes** - Check for sequential scans on large tables
- **Over-indexing** - Too many indexes slow down writes
- **work_mem too low** - Causes disk-based sorts and hash joins
- **Connection exhaustion** - Not using connection pooling
- **Stale statistics** - Autovacuum not running frequently enough
- **Bloated tables** - Not vacuuming after large deletes/updates
- **N+1 queries** - Fetching related data in loops instead of joins
- **SELECT * everywhere** - Fetching unnecessary columns

© curiositech · MIT · curiositech/some_claude_skills

Similar skills

All skills for Data and databases →

postgresql-optimization by other authors comes first.

postgresql-optimization official github postgresql-optimization helps developers use PostgreSQL features such as JSONB, arrays, indexes, full-text search, window functions, and extensions. CX Codex +1 16,436installs postgresql-database-engineering manutej postgresql-database-engineering guides database design, index selection, and query optimization in PostgreSQL. Useful when tuning slow queries, setting up table partitioning, or configuring replication. CX Codex +1 1,800installs postgresql-best-practices Mindrally postgresql-best-practices — a skill that feeds your agent PostgreSQL rules: data types, schema design, indexing, query plans and database maintenance. CX Codex +1 1,600installs postgresql-expert PCL Team Turns the agent into a PostgreSQL expert: advanced queries, indexes, performance tuning and production operations. Useful whenever a task touches schema, SQL or query plans. CX Codex +1 427installs PostgreSQL performance Tuner MCP Server isdaniel PostgreSQL performance Tuner MCP Server — an MCP server that diagnoses PostgreSQL slow queries and tests hypothetical indexes using HypoPG without writing to disk. CX Codex +1 ★ 31repo stars sql-query-optimization aj-geddes sql-query-optimization is a Claude Code skill that analyzes slow SQL queries and suggests faster rewrites for PostgreSQL and MySQL. CX Codex +1 529installs postgresql PatrickJS Pins down how the agent writes PostgreSQL: parameterized queries, TIMESTAMPTZ, indexes on foreign keys. Useful once the agent starts touching your SQL. CX Codex +1 ★ 41,178repo stars

From the same set180

The whole set →
vibe-matcher vibe-matcher translates abstract brand feelings and adjectives into concrete design parameters like color palettes, typefaces, layouts, and interaction speeds. CX Codex +1 194installs hrv-alexithymia-expert hrv-alexithymia-expert is a Claude Code skill that adds expertise in heart rate variability metrics and alexithymia, connecting biometric data with emotional states. CX Codex +1 193installs frontend-architect frontend-architect — a skill for choosing a frontend framework, deploying to Cloudflare Pages, and building internal tool architectures. Recommends tech stacks, configures shadcn/ui, and bridges design tokens to components. CX Codex +1 191installs launch-readiness-auditor launch-readiness-auditor audits codebases for production readiness before launch. It calculates a health score across eight areas and creates a prioritized two-week sprint plan. CX Codex +1 190installs wedding-immortalist The wedding-immortalist skill outlines a workflow for turning wedding photos and video into 3D scenes, a guest roster, and selected highlights. Use it to plan an interactive wedding archive. CX Codex +1 190installs 2000s-visualization-expert 2000s-visualization-expert — a skill for rebuilding 2000s music visualizers — Milkdrop, AVS, Geiss — with WebGL and Web Audio. Use it when a player needs an audio-reactive backdrop or generative art driven by music. CX Codex +1 190installs cloudflare-worker-dev The cloudflare-worker-dev skill guides Cloudflare Workers work for edge APIs, KV caching, request limits, and real-time coordination. CX Codex +1 189installs clip-aware-embeddings clip-aware-embeddings guides AI coding agents to pick CLIP or dedicated computer vision models. It stops agents from misusing CLIP for counting or spatial reasoning. CX Codex +1 188installs

Project sponsors

Companies that keep this analytics open