Mmcp.market

postgres-pro skill

by Jeffallan·Jeffallan/claude-skills·12k stars·MIT

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

A100/100content scan

Is the postgres-pro skill safe?

Clean: nothing in its files matched our rules. We read 6 files in the folder on 2026-09-28.

No findings.

Install the postgres-pro skill

A skill is a folder. Copy it into your agent's skills folder and the agent loads it when the task matches its description. 

git clone --depth 1 https://github.com/Jeffallan/claude-skills.git /tmp/claude-skills
mkdir -p ~/.claude/skills
cp -r /tmp/claude-skills/skills/postgres-pro ~/.claude/skills/postgres-pro
available in every project

In the Claude apps, zip the folder and upload it from the Skills settings. The folder on GitHub

The instructions your agent would load

SKILL.md as published, without the frontmatter. Read it on GitHub

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

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

Load detailed guidance based on context:

Common Patterns

JSONB — GIN Index and Query

-- Create GIN index for containment queries
CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- Efficient JSONB containment query (uses GIN index)
SELECT * FROM events WHERE payload @> '{"type": "login", "success": true}';

-- Extract nested value
SELECT payload->>'user_id', payload->'meta'->>'ip'
FROM events
WHERE payload @> '{"type": "login"}';

VACUUM and Bloat Monitoring

-- Check tables with high dead tuple counts
SELECT relname, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Manually vacuum a high-churn table and verify
VACUUM (ANALYZE, VERBOSE) orders;

Replication Lag Monitoring

-- On primary: check standby lag
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       (sent_lsn - replay_lsn) AS replication_lag_bytes
FROM pg_stat_replication;

Constraints

MUST DO

  • Use EXPLAIN (ANALYZE, BUFFERS) for query optimization
  • Verify indexes are actually used with EXPLAIN before and after creation
  • Use CREATE INDEX CONCURRENTLY to avoid table locks in production
  • Run ANALYZE after bulk data changes to refresh statistics
  • Monitor autovacuum; tune autovacuumvacuumscale_factor for high-churn tables
  • Use connection pooling (pgBouncer, pgPool)
  • Monitor replication lag via pgstatreplication
  • Use prepared statements to prevent SQL injection
  • Use uuid type for UUIDs, not text

MUST NOT DO

  • Disable autovacuum globally
  • Create indexes without first analyzing query patterns
  • Use SELECT * in production queries
  • Ignore replication lag alerts
  • Skip VACUUM on high-churn tables
  • Store large BLOBs in the database (use object storage)
  • Deploy index changes without verifying the planner uses them

Output Templates

When implementing PostgreSQL solutions, provide:

  1. Query with EXPLAIN (ANALYZE, BUFFERS) output and interpretation
  2. Index definitions with rationale and pre/post verification
  3. Configuration changes with before/after values
  4. Monitoring queries for ongoing health checks
  5. Brief explanation of performance impact

Knowledge Reference

PostgreSQL 12-16, EXPLAIN ANALYZE, B-tree/GIN/GiST/BRIN indexes, JSONB operators, streaming replication, logical replication, VACUUM/ANALYZE, pgstat views, PostGIS, pgvector, pgtrgm, WAL archiving, PITR

Documentation

More skills from Jeffallan/claude-skills

  • Aangular-architectGenerates Angular 17+ standalone components, configures advanced routing with lazy loading and guards, implements NgRx state management, applies RxJS patterns, and optimizes bundle performance. Use when building Angular 17+ applications with standalone components or signals, setting up NgRx stores, establishing RxJS reactive patterns, performance tuning, or writing Angular tests for enterprise apps.
  • Aapi-designerUse when designing REST or GraphQL APIs, creating OpenAPI specifications, or planning API architecture. Invoke for resource modeling, versioning strategies, pagination patterns, error handling standards.
  • Aarchitecture-designerUse when designing new high-level system architecture, reviewing existing designs, or making architectural decisions. Invoke to create architecture diagrams, write Architecture Decision Records (ADRs), evaluate technology trade-offs, design component interactions, and plan for scalability. Use for system design, architecture review, microservices structuring, ADR authoring, scalability planning, and infrastructure pattern selection — distinct from code-level design patterns or database-only design tasks.
  • Aatlassian-mcpIntegrates with Atlassian products to manage project tracking and documentation via MCP protocol. Use when querying Jira issues with JQL filters, creating and updating tickets with custom fields, searching or editing Confluence pages with CQL, managing sprints and backlogs, setting up MCP server authentication, syncing documentation, or debugging Atlassian API integrations.
  • Achaos-engineerDesigns chaos experiments, creates failure injection frameworks, and facilitates game day exercises for distributed systems — producing runbooks, experiment manifests, rollback procedures, and post-mortem templates. Use when designing chaos experiments, implementing failure injection frameworks, or conducting game day exercises. Invoke for chaos experiments, resilience testing, blast radius control, game days, antifragile systems, fault injection, Chaos Monkey, Litmus Chaos.
  • Acli-developerUse when building CLI tools, implementing argument parsing, or adding interactive prompts. Invoke for parsing flags and subcommands, displaying progress bars and spinners, generating bash/zsh/fish completion scripts, CLI design, shell completions, and cross-platform terminal applications using commander, click, typer, or cobra.
  • Acloud-architectDesigns cloud architectures, creates migration plans, generates cost optimization recommendations, and produces disaster recovery strategies across AWS, Azure, and GCP. Use when designing cloud architectures, planning migrations, or optimizing multi-cloud deployments. Invoke for Well-Architected Framework, cost optimization, disaster recovery, landing zones, security architecture, serverless design.
  • Acode-documenterGenerates, formats, and validates technical documentation — including docstrings, OpenAPI/Swagger specs, JSDoc annotations, doc portals, and user guides. Use when adding docstrings to functions or classes, creating API documentation, building documentation sites, or writing tutorials and user guides. Invoke for OpenAPI/Swagger specs, JSDoc, doc portals, getting started guides.
  • Acode-reviewerAnalyzes code diffs and files to identify bugs, security vulnerabilities (SQL injection, XSS, insecure deserialization), code smells, N+1 queries, naming issues, and architectural concerns, then produces a structured review report with prioritized, actionable feedback. Use when reviewing pull requests, conducting code quality audits, identifying refactoring opportunities, or checking for security issues. Invoke for PR reviews, code quality checks, refactoring suggestions, review code, code quality. Complements specialized skills (security-reviewer, test-master) by providing broad-scope review across correctness, performance, maintainability, and test coverage in a single pass.
  • Acpp-proWrites, optimizes, and debugs C++ applications using modern C++20/23 features, template metaprogramming, and high-performance systems techniques. Use when building or refactoring C++ code requiring concepts, ranges, coroutines, SIMD optimization, or careful memory management — or when addressing performance bottlenecks, concurrency issues, and build system configuration with CMake.
  • Acsharp-developerUse when building C# applications with .NET 8+, ASP.NET Core APIs, or Blazor web apps. Builds REST APIs using minimal or controller-based routing, configures database access with Entity Framework Core, implements async patterns and cancellation, structures applications with CQRS via MediatR, and scaffolds Blazor components with state management. Invoke for C#, .NET, ASP.NET Core, Blazor, Entity Framework, EF Core, Minimal API, MAUI, SignalR.
  • Adatabase-optimizerOptimizes database queries and improves performance across PostgreSQL and MySQL systems. Use when investigating slow queries, analyzing execution plans, or optimizing database performance. Invoke for index design, query rewrites, configuration tuning, partitioning strategies, lock contention resolution.

All agent skills → · MCP servers