PostgreSQL Pro

What it does

When your agent uses it

• Finding out why a query is slow using EXPLAIN ANALYZE
• Choosing between B-tree, GIN, GiST and BRIN indexes
• Storing and indexing JSONB documents
• Setting up streaming or logical replication and watching lag
• Tuning VACUUM and autovacuum on tables with many dead tuples

Example prompts

• “Run EXPLAIN ANALYZE on the monthly report query and tell me which index would help.”
• “Add a GIN index for searching the payload JSONB column by containment.”
• “Check which tables have the most dead tuples and suggest autovacuum settings.”
• “Plan logical replication to a reporting replica and show how to monitor lag.”

Requirements

• Access to a PostgreSQL database

Workflow steps

5 steps, taken from the first numbered list in SKILL.md.

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

What it can do on your machine

Read from SKILL.md and the folder at commit 1be15d8. It shows what the files ask for, not the result of running them.

• Tool permissions

  Pre-approves nothing: there is no allowed-tools line, so your agent's usual permission prompts apply.

  From allowed-tools in the SKILL.md frontmatter.

• Runs code

  No scripts in the folder and no shell commands in SKILL.md (its code samples are sql).

  From the folder's file list and the shell code blocks in SKILL.md.

• Network

  Links to these hosts (documentation or services it may open):

  • github.com
  • synergetic.solutions
  • jeffallan.github.io

  From URLs in SKILL.md, links to its own repository left out.

• Credentials

  Names no API keys, tokens, secrets or passwords.

  From names ending in _API_KEY, _TOKEN, _SECRET, _KEY or _PASSWORD in SKILL.md.

Context cost

PostgreSQL Pro loads about 1.5k tokens when it runs, and up to ~13k if it reads all its reference files. Until then it costs about 56 tokens; SKILL.md has 392 words of instructions outside code blocks.

Always · name and description, kept in context so the agent knows when to use it

~56

When it runs · the whole SKILL.md, loaded when a task matches

~1.5k

With references · SKILL.md plus every file in references/, read only if the agent opens them

~13k

Estimates: characters ÷ 4, the usual rule of thumb; real counts depend on the model's tokenizer. Scripts and assets cost tokens only if the agent reads them.

Safety

Automated static check — not a guarantee. Review scripts before installing. It scans the text of SKILL.md for risky patterns (piping downloads into a shell, reading credential files, hidden Unicode, destructive commands); files beside SKILL.md are not scanned.

SKILL.md

The full file from Jeffallan/claude-skills at commit 1be15d8, republished under its MIT licence (© Jeffallan). 392 words, ~1,496 tokens.

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

metadata.company

https://synergetic.solutions

metadata.version

1.1.0

metadata.domain

infrastructure

metadata.triggers

PostgreSQL, Postgres, EXPLAIN ANALYZE, pg_stat, JSONB, streaming replication, logical replication, VACUUM, PostGIS, pgvector

metadata.role

specialist

metadata.scope

implementation

metadata.output-format

code

metadata.related-skills

database-optimizer, devops-engineer, sre-engineer

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

-- 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"}';

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

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

© Jeffallan, MIT. Rendered from Markdown: HTML in the file is shown as text, images as links, and headings moved down two levels. Raw file

Files

SKILL.md and 5 other files (references) in skills/postgres-pro of Jeffallan/claude-skills.

• SKILL.md
• references/extensions.md
• references/jsonb.md
• references/maintenance.md
• references/performance.md
• references/replication.md

Open the folder on GitHubat commit 1be15d8