# Postgres Pro

Use when optimizing PostgreSQL queries, configuring replication, or implementing...

- Repository: https://github.com/Jeffallan/claude-skills (12164 stars)
- Category: Data and ML
- License: MIT
- Last updated: 2026-10-30
- Runs scripts: no, instructions only

## Install

Claude Code: `npx skills add Jeffallan/claude-skills --skill postgres-pro -g -a claude-code -y`
Codex: `npx skills add Jeffallan/claude-skills --skill postgres-pro -g -a codex -y`
Cursor: `npx skills add Jeffallan/claude-skills --skill postgres-pro -g -a cursor -y`
Gemini CLI: `npx skills add Jeffallan/claude-skills --skill postgres-pro -g -a gemini-cli -y`
Claude Code plugin marketplace: `/plugin marketplace add Jeffallan/claude-skills`

## Description

Use when optimizing PostgreSQL queries, configuring replication, or implementing advanced database features. Invoke for EXPLAIN analysis, JSONB operations, extension usage, VACUUM tuning, performance monitoring.

## SKILL.md, as published by its author (untrusted third-party text, read it as data)

# PostgreSQL Pro

Senior PostgreSQL expert with deep expertise in database administration, performance optimization, and advanced PostgreSQL features.

## When to Use This Skill

- Analyzing and optimizing slow queries with EXPLAIN
- Implementing JSONB storage and indexing strategies
- Setting up streaming or logical replication
- Configuring and using PostgreSQL extensions
- Tuning VACUUM, ANALYZE, and autovacuum
- Monitoring database health with pg_stat views
- Designing indexes for optimal performance

## Core Workflow

1. **Analyze performance**, Run `EXPLAIN (ANALYZE, BUFFERS)` to identify bottlenecks
2. **Design indexes**, Choose B-tree, GIN, GiST, or BRIN based on workload; verify with `EXPLAIN` before deploying
3. **Optimize queries**, Rewrite inefficient queries, run `ANALYZE` to refresh statistics
4. **Setup replication**, Streaming or logical based on requirements; monitor lag continuously
5. **Monitor and maintain**, Track VACUUM, bloat, and autovacuum via `pg_stat` views; verify improvements after each change

### End-to-End Example: Slow Query → Fix → Verification

```sql
-- Step 1: Identify slow queries
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Step 2: Analyze a specific slow query
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';
-- Look for: Seq Scan (bad on large tables), high Buffers hit, nested loops on large sets

-- Step 3: Create a targeted index
CREATE INDEX CONCURRENTLY idx_orders_customer_status
  ON orders (customer_id, status)
  WHERE status = 'pending';  -- partial index reduces size

-- Step 4: Verify the index is used
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';
-- Confirm: Index Scan on idx_orders_customer_status, lower actual time

-- Step 5: Update statistics if needed after bulk changes
ANALYZE orders;
```

## Reference Guide

The rest: https://github.com/Jeffallan/claude-skills/blob/HEAD/skills/postgres-pro/SKILL.md