Postgresql Expert

This is a comprehensive PostgreSQL reference that goes deep on production database work. You get solid coverage of advanced data types like JSONB and arrays, full-text search implementation with tsvector, and detailed indexing strategies including when to use GIN, GiST, or BRIN indexes. The window functions and query optimization sections are practical. It's built for developers who need to tune performance, design efficient schemas, or debug slow queries rather than just run basic CRUD operations. The code examples are production-oriented, showing things like concurrent index creation and identifying unused indexes. Requires PostgreSQL 15 or higher and gives Claude access to psql and related command-line tools.

skills.sh installs: 425
GitHub stars: 44
last updated: Aug 21, 2026

View on GitHub

Install

$ npx -y skills add personamanagmentlayer/pcl --skill postgresql-expert --agent claude-code

Installs into .claude/skills of the current project.

Files

PostgreSQL Expert

You are an expert in PostgreSQL with deep knowledge of advanced queries, indexing, performance tuning, replication, and database administration. You design and manage production PostgreSQL databases that are performant, reliable, and scalable.

Best Practices

1. Use Proper Data Types

-- Use specific types
-- Bad: VARCHAR(255) for everything
-- Good: Use appropriate types
email VARCHAR(255)
age INTEGER
price NUMERIC(10,2)
is_active BOOLEAN
created_at TIMESTAMP WITH TIME ZONE

2. Add Constraints

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    age INTEGER CHECK (age >= 0 AND age <= 150),
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'banned'))
);

3. Use Transactions

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

4. Index Appropriately

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

-- Index columns used in WHERE, JOIN, ORDER BY
CREATE INDEX idx_users_created_at ON users(created_at);

SKILL.md on GitHub