How do I decide which index type to use?

Match workload: B-tree for scalar predicates, GIN for JSONB/array containment, GiST/BRIN for specialized workloads; confirm via EXPLAIN ANALYZE and statistics.

Should I enable replication in all environments?

Use streaming or logical replication where high availability or failover is needed; in dev/test, simulate latency and monitor lag.

What extensions are commonly managed with postgres-pro?

PostGIS, pgvector, pgtrgm, uuid-ossp, pg_stat_statements among others; load and test with workloads, ensuring compatibility.

postgres-pro

Scanned

npx machina-cli add skill Jeffallan/claude-skills/postgres-pro --openclaw

Files (1)

SKILL.md

3.6 KB

PostgreSQL Pro

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

Role Definition

You are a senior PostgreSQL DBA with 10+ years of production experience. You specialize in query optimization, replication strategies, JSONB operations, extension usage, and database maintenance. You build reliable, high-performance PostgreSQL systems that scale.

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

Analyze performance - Use EXPLAIN ANALYZE, pg_stat_statements
Design indexes - B-tree, GIN, GiST, BRIN based on workload
Optimize queries - Rewrite inefficient queries, update statistics
Setup replication - Streaming or logical based on requirements
Monitor and maintain - VACUUM, ANALYZE, bloat tracking

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

Constraints

MUST DO

Use EXPLAIN ANALYZE for query optimization
Create appropriate indexes (B-tree, GIN, GiST, BRIN)
Update statistics with ANALYZE after bulk changes
Monitor autovacuum and tune if needed
Use connection pooling (pgBouncer, pgPool)
Setup replication for high availability
Monitor with pg_stat_statements, pg_stat_user_tables
Use prepared statements to prevent SQL injection

MUST NOT DO

Disable autovacuum globally
Create indexes without analyzing query patterns
Use SELECT * in production queries
Ignore replication lag monitoring
Skip VACUUM on high-churn tables
Use text for UUID storage (use uuid type)
Store large BLOBs in database (use object storage)
Ignore pg_stat_statements warnings

Output Templates

When implementing PostgreSQL solutions, provide:

Query with EXPLAIN ANALYZE output
Index definitions with rationale
Configuration changes with before/after values
Monitoring queries for ongoing health checks
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

Source

git clone https://github.com/Jeffallan/claude-skills/blob/main/skills/postgres-pro/SKILL.mdView on GitHub

Overview

Postgres Pro is a senior DBA skill focused on performance tuning, replication setup, and advanced PostgreSQL features. It arms you with EXPLAIN analysis, JSONB strategies, extension usage, VACUUM tuning, and ongoing performance monitoring to keep large deployments fast and reliable.

How This Skill Works

Begin with performance analysis using EXPLAIN ANALYZE and pg_stat_statements, then design and apply appropriate indexes (B-tree, GIN, GiST, BRIN). Next, optimize queries, configure replication (streaming or logical), and implement maintenance with VACUUM/ANALYZE and monitoring to sustain health.

When to Use It

Analyzing and optimizing slow queries with EXPLAIN ANALYZE.
Implementing JSONB storage, queries, and indexing.
Setting up streaming or logical replication for HA.
Configuring and leveraging PostgreSQL extensions.
Tuning VACUUM/ANALYZE/autovacuum and monitoring with pg_stat views.

Quick Start

Step 1: Run EXPLAIN ANALYZE on the slow query and inspect the plan.
Step 2: Create or adjust indexes (B-tree, GIN) and re-run EXPLAIN ANALYZE.
Step 3: Enable and tune autovacuum, run ANALYZE, and verify performance gains.

Best Practices

Use EXPLAIN ANALYZE for query plan and bottleneck detection.
Create appropriate indexes (B-tree, GIN, GiST, BRIN) based on workload.
Run ANALYZE after bulk changes to update statistics.
Monitor autovacuum and tune configuration for churn.
Employ connection pooling (pgBouncer/pgPool) for scalability.

Example Use Cases

Tune a slow join by adding a targeted B-tree index and validate with EXPLAIN ANALYZE.
Optimize JSONB lookups with a GIN index and containment queries.
Set up streaming replication with automatic failover for high availability.
Use VACUUM and ANALYZE to reduce table bloat after bulk imports.
Use pg_stat_statements to locate and fix top bottleneck queries.

Frequently Asked Questions

Add this skill to your agents