Guide · Agent skill

How to use the PostgreSQL Pro agent skill: setup and examples

Updated 2026-10-29 · written with AI from the project's own documentation and checked against our catalogue data

PostgreSQL Pro is an agent skill, a set of instructions and SQL examples that an AI agent such as Claude loads when your request matches its topic. It gives the agent a structured PostgreSQL specialist workflow covering query tuning, replication, JSONB, extensions and VACUUM. This guide explains what the skill does, how to install it, how to trigger it and how to use it safely against real databases.

PostgreSQL ProJeffallan/claude-skills · MIT Download the skill

Quick start

1Download the ZIP. Download the postgres-pro skill ZIP from the page where you found this guide.
2Unzip into a skills folder. Unzip into ~/.claude/skills/ for a personal install, or into .claude/skills/ inside a project for a project-only install. Alternatively, upload it under Settings → Capabilities → Skills in the Claude apps.
3Confirm your PostgreSQL version and access. Make sure you have access to a PostgreSQL 12-16 database and that pg_stat_statements is available if you plan to follow the slow-query workflow.
4Describe the problem in plain language. Ask about a specific slow query, replication setup, JSONB storage, extension, or VACUUM issue, and include table names and symptoms.
5Review the EXPLAIN output. Ask the agent to run or interpret EXPLAIN (ANALYZE, BUFFERS) and check for sequential scans and high buffer counts.
6Test changes outside production first. Run suggested CREATE INDEX or VACUUM statements on a staging copy, and confirm the planner uses new indexes before applying anything to production.

What the PostgreSQL Pro skill makes the agent do

When you ask about a slow query, replication setup, JSONB storage, an extension such as PostGIS or pgvector, or VACUUM and bloat, the agent follows a defined workflow. For slow queries it runs EXPLAIN (ANALYZE, BUFFERS), proposes indexes, and checks that the planner actually uses them. For health checks it looks at pg_stat views.

The skill does not run anything on its own. It contains instructions and SQL examples. The agent loads separate reference notes on performance, JSONB, extensions, replication and maintenance only when the topic calls for them.

Analyze queries with EXPLAIN (ANALYZE, BUFFERS) and look for sequential scans and high buffer counts
Design B-tree, GIN, GiST or BRIN indexes and verify them with EXPLAIN before deploying
Set up streaming or logical replication and monitor lag through pg_stat_replication
Work with JSONB operators and GIN indexes, and configure PostgreSQL extensions
Tune VACUUM, ANALYZE and autovacuum, and monitor bloat with pg_stat_user_tables

Install the postgres-pro skill from the ZIP download

Download the ZIP for the postgres-pro skill from the page where you found this guide. Unzip it so that the skill folder sits in the skills directory your agent reads. You have two options.

For a personal install available across your projects, unzip into ~/.claude/skills/. To limit it to one project, unzip into the .claude/skills/ folder inside that project. The skill folder should contain SKILL.md and the references/ folder.

If you use the Claude apps instead of local files, you can upload the skill under Settings → Capabilities → Skills.

Personal: ~/.claude/skills/
Single project: .claude/skills/ in the project root
Claude apps: Settings → Capabilities → Skills, then upload

Check what you need before you start

The skill targets PostgreSQL 12 to 16. You need database access for the work you want the agent to do. The slow-query example relies on the pg_stat_statements extension, which must be installed and listed in shared_preload_libraries on the server.

The skill runs no scripts and uses no network access. Everything it does happens through the SQL you or the agent run against your database.

PostgreSQL 12-16 database access
pg_stat_statements extension for the slow-query workflow
Runs scripts: no
Uses network: no

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Trigger the skill with natural requests

You do not need special syntax. Describe the database problem in plain language and name PostgreSQL or the topic. The skill's description covers EXPLAIN analysis, JSONB operations, extension usage, VACUUM tuning and performance monitoring, so requests on those topics are the most likely to load it.

Include concrete details such as the table names, the query text, the row counts and the symptoms. The more specific the request, the more useful the plan the agent can produce.

"This query on orders is slow in Postgres. Walk me through EXPLAIN ANALYZE and suggest an index."
"Help me store webhook payloads as JSONB and query nested fields efficiently."
"Check whether autovacuum is keeping up on my high-churn tables."
"Our replica lag keeps growing. Show me how to monitor it with pg_stat_replication."

Work through an example: slow query to verified fix

The skill's core workflow starts by finding slow statements, then examining one plan. The example below identifies the slowest queries by mean execution time, runs EXPLAIN on a query that filters orders by customer and status, adds a partial index, and then checks the plan again.

Read the plan before accepting a fix. Look for Seq Scan on large tables, high Buffers hit counts and nested loops over large sets. After the change, confirm the plan shows an Index Scan on the new index and a lower actual time.

Step 1: list the top queries from pg_stat_statements
Step 2: run EXPLAIN (ANALYZE, BUFFERS) on the slow query
Step 3: create the index with CREATE INDEX CONCURRENTLY
Step 4: re-run EXPLAIN to confirm the index is used
Step 5: run ANALYZE if the data changed in bulk

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';

CREATE INDEX CONCURRENTLY idx_orders_customer_status
  ON orders (customer_id, status)
  WHERE status = 'pending';

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';

Use the skill for JSONB, VACUUM and replication checks

For JSONB, the skill recommends the JSONB type over json, creating a GIN index for containment queries using @>, and extracting nested values with operators such as ->> and #>>. For maintenance, it provides a query that ranks tables by dead tuple count and shows when autovacuum last ran. For replication, it provides a query on pg_stat_replication that compares sent and replayed WAL positions on the primary.

The maintenance reference notes that VACUUM FULL locks the table while it rewrites it, and suggests pg_repack for a non-blocking alternative in production.

JSONB containment: payload @> '{"type": "login"}' with a GIN index
Dead tuples: query pg_stat_user_tables ordered by n_dead_tup
Replication lag: query pg_stat_replication on the primary

CREATE INDEX idx_events_payload ON events USING GIN (payload);

SELECT * FROM events WHERE payload @> '{"type": "login", "success": true}';

What people use it for

Finding and fixing a slow reporting query by adding a partial index and verifying it with EXPLAIN
Storing event payloads as JSONB and querying nested fields with a GIN index
Checking which high-churn tables have many dead tuples and whether autovacuum is keeping up
Reviewing standby lag with pg_stat_replication during a replication setup
Adding pgvector or PostGIS to an existing PostgreSQL database and checking installed extensions

Frequently asked questions

Is the PostgreSQL Pro agent skill free to use?

The skill is published under the MIT licence, as stated in its SKILL.md metadata. Check the licence file in the repository for the full terms before you redistribute it.

What do I need to run the postgres-pro skill?

You need access to a PostgreSQL 12-16 database and an agent that loads skills, such as Claude. The slow-query example also needs the pg_stat_statements extension. The skill runs no scripts and uses no network access.

Where do I install the postgres-pro skill?

Unzip the downloaded ZIP into ~/.claude/skills/ for personal use, or into .claude/skills/ in a project folder. In the Claude apps you can upload it under Settings → Capabilities → Skills.

Will the skill change my database automatically?

No. The skill contains instructions and SQL examples, and it does not run anything by itself. Any CREATE INDEX or VACUUM statement the agent suggests is only executed if you or the agent run it against your database, so review it first.

Does it work with MySQL or SQL Server?

No. The skill is written for PostgreSQL, and its syntax and tools such as pg_stat views differ from MySQL or SQL Server. Use a database-specific skill or guide for those systems.

How does it compare with asking a general AI assistant about Postgres?

The skill gives the agent a fixed workflow and separate reference notes on performance, JSONB, extensions, replication and maintenance. It also includes rules such as using CREATE INDEX CONCURRENTLY and verifying indexes with EXPLAIN. A general prompt may not follow those steps consistently.

New agent skills, repos and AI tools — every week

The best new skills and open-source projects, the fastest-growing repos, and one free prompt. Five minutes to read. Unsubscribe anytime.

Our verdict

Backend developers diagnosing slow PostgreSQL queries, DBAs reviewing replication lag or autovacuum, and teams adding JSONB or pgvector to an existing Postgres database will get the most from this skill. Do not use it to run changes straight against production, and pick a different resource if you are not on PostgreSQL or need a full database design course.

Download the skill