PostgreSQL Development Skill

A Claude Code skill for building applications with PostgreSQL — covering schema design, query optimization, indexing strategies, migrations, connection pooling, JSON operations, and full-text search.

When to Use This Skill

Choose this skill when:

• Designing PostgreSQL database schemas and migrations
• Writing and optimizing complex SQL queries
• Implementing full-text search with PostgreSQL
• Working with JSONB data types and operations
• Setting up connection pooling and performance tuning
• Implementing row-level security and access control

Consider alternatives when:

• You need a serverless database (use Neon or Supabase)
• You need a NoSQL document store (use MongoDB)
• You need an ORM-specific guide (use Prisma/Drizzle skill)

Quick Start

Copy
# Connect to PostgreSQL
psql postgresql://user:pass@localhost:5432/mydb

# Or with environment variable
export DATABASE_URL="postgresql://user:pass@localhost:5432/mydb"

Copy
-- Create a well-structured table
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(255) NOT NULL,
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- Create an index for common queries
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_users_metadata ON users USING GIN (metadata);

-- Auto-update updated_at
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_updated
  BEFORE UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION update_timestamp();

Core Concepts

Index Types

Type	Syntax	Best For
B-tree	CREATE INDEX idx ON t(col)	Equality, range queries
GIN	CREATE INDEX idx ON t USING GIN(col)	JSONB, arrays, full-text
GiST	CREATE INDEX idx ON t USING GiST(col)	Geometry, range types
Partial	CREATE INDEX idx ON t(col) WHERE condition	Filtered subsets
Composite	CREATE INDEX idx ON t(col1, col2)	Multi-column queries
Expression	CREATE INDEX idx ON t(lower(email))	Computed values

JSONB Operations

Copy
-- Query JSONB fields
SELECT * FROM users WHERE metadata->>'role' = 'admin';
SELECT * FROM users WHERE metadata @> '{"role": "admin"}';

-- Update nested JSONB
UPDATE users SET metadata = jsonb_set(metadata, '{preferences,theme}', '"dark"');

-- Aggregate JSONB
SELECT metadata->>'department' AS dept, COUNT(*)
FROM users GROUP BY metadata->>'department';

Full-Text Search

Copy
-- Add search column
ALTER TABLE posts ADD COLUMN search_vector tsvector;
UPDATE posts SET search_vector = to_tsvector('english', title || ' ' || body);
CREATE INDEX idx_posts_search ON posts USING GIN(search_vector);

-- Search with ranking
SELECT title, ts_rank(search_vector, query) AS rank
FROM posts, plainto_tsquery('english', 'database optimization') query
WHERE search_vector @@ query
ORDER BY rank DESC LIMIT 10;

Configuration

Parameter	Type	Default	Description
max_connections	number	100	Maximum database connections
shared_buffers	string	"128MB"	Shared memory for caching
work_mem	string	"4MB"	Memory per query operation
effective_cache_size	string	"4GB"	OS cache estimate for planner
maintenance_work_mem	string	"64MB"	Memory for maintenance ops
random_page_cost	number	1.1	Planner cost for random I/O (SSD)

Best Practices

1. Use EXPLAIN ANALYZE to understand query performance — always check the execution plan before and after optimization; assumptions about query performance are frequently wrong.

2. Index columns in WHERE, JOIN, and ORDER BY clauses — but don't over-index; each index slows writes. Start with the columns that filter the most rows and add more based on slow query logs.

3. Use JSONB for semi-structured data, not for everything — JSONB is powerful but loses the schema validation and referential integrity that relational columns provide; use it for truly flexible fields.

4. Set random_page_cost to 1.1 for SSD storage — the default assumes spinning disks; lowering this value helps the planner choose index scans over sequential scans on fast storage.

5. Use connection pooling in production — each PostgreSQL connection uses ~10MB of memory; use PgBouncer or application-level pooling to share connections across requests.

Common Issues

Query performance degrades over time — Table bloat from dead rows. Run VACUUM ANALYZE regularly or configure autovacuum settings. Check pg_stat_user_tables for tables with high dead tuple counts.

Connection limit exceeded under load — Each connection consumes memory and a process slot. Use connection pooling (PgBouncer) and set application pool sizes to max_connections / number_of_app_instances.

JSONB queries are slow without GIN index — JSONB containment operators (@>, ?, ?|) require a GIN index for efficient execution. Without it, PostgreSQL scans every row.