How to Use Postgres Pro Skill in Claude Code for Infrastructure Development

Feb 14, 2026

Purpose

This post demonstrates how to use the Postgres Pro skill in Claude Code for database development and infrastructure work.

Environment

Claude Code with claude-skills plugin
PostgreSQL 14+
Node.js/TypeScript or Python projects
Infrastructure as code workflows

What is Postgres Pro?

Postgres Pro is a skill in the claude-skills ecosystem that provides specialized patterns and best practices for PostgreSQL database development. When I work on database schemas, query optimization, or infrastructure setup, this skill helps me avoid common mistakes.

The skill focuses on:

Schema design and migrations
Query optimization patterns
Indexing strategies
Security best practices
Performance tuning

There are 4 main areas where Postgres Pro helps:

Schema design: Entity relationships and normalization
Query patterns: Efficient data retrieval and joins
Performance: Indexing, caching, and query planning
Security: Access control and data protection

I use this skill when I need to:

Design new database schemas
Optimize slow queries
Set up database infrastructure
Implement data access patterns

Installation and Setup

First, I need to install the claude-skills plugin. This plugin extends Claude Code with domain-specific skills.

# Install the claude-skills plugin
npm install -g @claude-skills/cli

# Or using pip
pip install claude-skills

Then I activate the Postgres Pro skill in my Claude Code configuration:

skills:
  - postgres-pro

Now I can verify the installation by asking Claude Code to use the skill:

# In Claude Code
/skill postgres-pro

The skill should activate and show me the available patterns and capabilities.

How to Use Postgres Pro

When I start a database-related task, I invoke the skill directly:

I need to design a schema for an e-commerce application. Use postgres-pro to help with the design.

The skill then provides patterns specific to PostgreSQL. For example, when I need to create an orders table:

-- PostgreSQL-specific pattern with proper indexing
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    total_amount DECIMAL(10, 2) NOT NULL CHECK (total_amount >= 0),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Partial index for active orders (PostgreSQL feature)
CREATE INDEX idx_orders_active ON orders (user_id, created_at)
WHERE status IN ('pending', 'processing');

-- Composite index for common query pattern
CREATE INDEX idx_orders_status_date ON orders (status, created_at DESC);

The skill helps me understand the key parts:

BIGSERIAL: Auto-incrementing 64-bit integer
TIMESTAMPTZ: Timestamp with timezone (best practice)
Partial index: Only indexes rows that match WHERE clause
CHECK constraint: Data validation at database level

Common Usage Patterns

When I work with Postgres Pro, I see consistent patterns across different scenarios.

Pattern 1: Query Optimization

I had a slow query joining users and orders:

-- Before: Full table scan
SELECT u.email, o.total_amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at > '2026-01-01';

The skill suggested I add a covering index:

-- Covering index includes all columns needed for the query
CREATE INDEX idx_orders_user_covering ON orders (user_id, created_at)
INCLUDE (total_amount);

Now the query completes in 2ms instead of 800ms.

Pattern 2: JSONB Operations

When I need flexible schema, Postgres Pro guides me to use JSONB:

-- JSONB for flexible attributes
ALTER TABLE products ADD COLUMN metadata JSONB DEFAULT '{}' NOT NULL;

-- GIN index for efficient JSONB queries
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);

-- Query: Find products with specific attribute
SELECT * FROM products
WHERE metadata @> '{"color": "red", "size": "large"}';

The skill explains:

JSONB: Binary JSON with indexing support
GIN index: Generalized Inverted Index for JSONB
@> operator: Contains operator for JSONB

Pattern 3: Connection Pooling

For Node.js applications, the skill shows me proper connection pooling:

import { Pool } from 'pg';

const pool = new Pool({
  host: process.env.DB_HOST,
  port: 5432,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  max: 20, // Maximum pool size
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

// Graceful shutdown
process.on('SIGTERM', () => pool.end());

The key configuration:

max: Pool size based on application load
idleTimeoutMillis: Reclaim unused connections
connectionTimeoutMillis: Fail fast if no connections available

Best Practices

DO

Use prepared statements

// Prevents SQL injection and enables query plan caching
const result = await pool.query(
  'SELECT * FROM users WHERE email = $1',
  [userEmail]
);

Add appropriate indexes

-- Index foreign keys for JOIN performance
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Index columns used in WHERE clauses
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

Use transactions for multi-step operations

await client.query('BEGIN');
try {
  await client.query('INSERT INTO orders ...');
  await client.query('UPDATE inventory ...');
  await client.query('COMMIT');
} catch (e) {
  await client.query('ROLLBACK');
  throw e;
}

DON’T

**Don’t use SELECT ***

-- Bad: Fetches all columns
SELECT * FROM users WHERE id = 1;

-- Good: Fetch only needed columns
SELECT id, email, name FROM users WHERE id = 1;

Don’t ignore connection management

// Bad: Forgetting to release connection
const client = await pool.connect();
const result = await client.query('SELECT ...');
// Missing: client.release()

// Good: Always release
const client = await pool.connect();
try {
  const result = await client.query('SELECT ...');
} finally {
  client.release();
}

Don’t skip migrations

# Always use versioned migrations
npx migrate up

Practical Examples

Example 1: Schema Design

When I design a blog platform, Postgres Pro helps me create a proper schema:

-- Users table
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Posts table with foreign key
CREATE TABLE posts (
    id BIGSERIAL PRIMARY KEY,
    author_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    published_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Indexes for common queries
CREATE INDEX idx_posts_author ON posts(author_id);
CREATE INDEX idx_posts_published ON posts(published_at DESC)
WHERE published_at IS NOT NULL;

-- Full-text search index
CREATE INDEX idx_posts_content ON posts USING GIN (to_tsvector('english', title || ' ' || content));

The key design decisions:

ON DELETE CASCADE: Automatically remove posts when user is deleted
Partial index: Only index published posts
GIN index: Fast full-text search
REFERENCES: Foreign key constraint for data integrity

Example 2: Query Refactoring

I had a complex query that was slow:

-- Before: Multiple subqueries
SELECT u.id, u.email,
  (SELECT COUNT(*) FROM posts WHERE author_id = u.id) as post_count,
  (SELECT MAX(published_at) FROM posts WHERE author_id = u.id) as last_post
FROM users u;

Postgres Pro showed me to refactor to a single query:

-- After: Single pass with JOIN
SELECT u.id, u.email, COUNT(p.id) as post_count, MAX(p.published_at) as last_post
FROM users u
LEFT JOIN posts p ON u.id = p.author_id
GROUP BY u.id, u.email;

This reduced execution time from 45ms to 8ms.

Example 3: Monitoring and Debugging

When I need to debug slow queries, the skill shows me how to use PostgreSQL’s statistics:

-- Find slow queries
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Check index usage
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- Find missing indexes
SELECT schemaname, tablename, attname, n_distinct, correlation
FROM pg_stats
WHERE correlation < 0.1  -- Poor correlation means random I/O
ORDER BY n_distinct DESC;

Postgres Pro works well with other skills in the claude-skills ecosystem:

Backend-patterns: API design and data access layers
Springboot-patterns: PostgreSQL with Spring Boot JPA
Security-review: Database security and access control

For official documentation and community resources:

Summary

In this post, I showed how to use the Postgres Pro skill in Claude Code for database development. The key point is knowing when to invoke this skill: whenever you work with PostgreSQL schemas, queries, or infrastructure. The skill provides patterns specific to PostgreSQL that help avoid common mistakes and improve performance.

Final Words + More Resources

My intention with this article was to help others share my knowledge and experience. If you want to contact me, you can contact by email: Email me

Here are also the most important links from this article along with some further resources that will help you in this scope:

Oh, and if you found these resources useful, don’t forget to support me by starring the repo on GitHub!