SkillAgentSearch skills...

postgres-pro

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

Install / Use

npx skills add Jeffallan/claude-skills --skill postgres-pro

Installs into whichever agent you are using.

About this skill
📄

SKILL.md

Installable skill definition

Quality Score

93/100

Category

Operations

Supported Platforms

Universal

Our assessment of postgres-pro

postgres-pro scores 93/100 on our quality scale, 137th of 745 Operations skills we index (top 19%).

Its SKILL.md is 5.7 KB long, well organised into 14 sections with 4 code examples: a solid amount of guidance for an agent.

With 12,363 GitHub stars, it is one of the more widely adopted skills in the catalogue.

Substance
26/30
Structure
20/20
Description
15/15
Adoption
17/20
Freshness
15/15

Maintenance, license and trust

  • The repository was last updated 5 days ago, so postgres-pro is actively maintained.
  • Our last check on 2026-10-25 found the source still online.
  • It is released under the MIT license, a permissive license that allows use, modification and commercial use with attribution.
  • Its trust signals score 100/100, with no cautions. These come from repository metadata, not a code audit — read the skill file before letting an agent act on it.

Safety scan

No issues found

Our scan of the whole file found no instruction hijacking, hidden characters, credential access, data exfiltration or destructive commands. An AI review of the same text found nothing harmful.

AI review by kimi-k2.7-code on 2026-09-28. Automated pattern scan on 2026-09-28. It catches known dangerous patterns, not every risk — read a skill before letting an agent act on it.

postgres-pro compared with similar skills

All 4 of these similar skills score higher than postgres-pro; compare them before choosing.

SkillScoreStarsUpdatedFormat
postgres-pro (this skill)by Jeffallan9312.4k5d agoSKILL.md
claude-memby thedotmack100117.6ktodayCLAUDE.md
Agent-Reachby Panniantong100112.3k8d agoCLAUDE.md
x402by browser-use100121.0k3d agoSKILL.md
algorithmic-artby anthropics100177.9k55d agoSKILL.md

Frequently asked questions

How do I install postgres-pro?
Run npx skills add Jeffallan/claude-skills --skill postgres-pro. The install tabs above show the steps for each supported agent.
Which AI agents does postgres-pro work with?
It is written for Universal, as a SKILL.md file. Other agents that read the same format can often use it too.
Is postgres-pro safe to use?
Our scan of the whole file found no instruction hijacking, hidden characters, credential access, data exfiltration or destructive commands. An AI review of the same text found nothing harmful. It is MIT-licensed and scores 100/100 on trust signals. Skills are instructions an agent will follow, so read the file before installing it and do not approve commands you do not understand.
Is postgres-pro still maintained?
The repository was last updated 5 days ago, so postgres-pro is actively maintained.

name: postgres-pro 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. license: MIT metadata: author: https://github.com/Jeffallan version: "1.1.0" domain: infrastructure triggers: PostgreSQL, Postgres, EXPLAIN ANALYZE, pg_stat, JSONB, streaming replication, logical replication, VACUUM, PostGIS, pgvector role: specialist scope: implementation output-format: code related-skills: database-optimizer, devops-engineer, sre-engineer

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:

| Topic | Reference | Load When | |-------|-----------|-----------| | Performance | references/performance.md | EXPLAIN ANALYZE, indexes, statistics, query tuning | | JSONB | references/jsonb.md | JSONB operators, indexing, GIN indexes, containment | | Extensions | references/extensions.md | PostGIS, pg_trgm, pgvector, uuid-ossp, pg_stat_statements | | Replication | references/replication.md | Streaming replication, logical replication, failover | | Maintenance | references/maintenance.md | VACUUM, ANALYZE, pg_stat views, monitoring, bloat |

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 autovacuum_vacuum_scale_factor for high-churn tables
  • Use connection pooling (pgBouncer, pgPool)
  • Monitor replication lag via pg_stat_replication
  • 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, pg_stat views, PostGIS, pgvector, pg_trgm, WAL archiving, PITR

Documentation

Related Skills

View on GitHub
GitHub Stars12.4k
CategoryOperations
Updated5d ago
Forks1.2k

Languages

JavaScript

Trust signals

100/100

From repository metadata: license, adoption, age and documentation. Not a code audit — see the Safety scan above for what the skill file itself contains.

No cautions