Postgres Patterns

A quick reference guide for PostgreSQL best practices pulled from Supabase's patterns. You get index selection rules (B-tree vs GIN vs BRIN), proper data type choices (bigint for IDs, timestamptz over timestamp), and common patterns like cursor pagination and queue processing with SKIP LOCKED. The real value is in the specific examples: composite index column ordering, covering indexes to avoid table lookups, and that RLS policy trick where you wrap auth.uid() in a SELECT. Also includes queries to find unindexed foreign keys and detect table bloat. It's meant as a cheat sheet when writing migrations or troubleshooting slow queries, not a tutorial. For deeper analysis, they point you to a database-reviewer agent.

skills.sh installs: 6.3K
GitHub stars: 300.3K
last updated: Aug 21, 2026

View on GitHub

Install

$ npx -y skills add affaan-m/ecc --skill postgres-patterns --agent claude-code

Installs into .claude/skills of the current project.

Files

PostgreSQL Patterns

Quick reference for PostgreSQL best practices. For detailed guidance, use the database-reviewer agent.

When to Activate

Writing SQL queries or migrations
Designing database schemas
Troubleshooting slow queries
Implementing Row Level Security
Setting up connection pooling

Quick Reference

Index Cheat Sheet

Query Pattern	Index Type	Example
`WHERE col = value`	B-tree (default)	`CREATE INDEX idx ON t (col)`
`WHERE col > value`	B-tree	`CREATE INDEX idx ON t (col)`
`WHERE a = x AND b > y`	Composite	`CREATE INDEX idx ON t (a, b)`
`WHERE jsonb @> '{}'`	GIN	`CREATE INDEX idx ON t USING gin (col)`
`WHERE tsv @@ query`	GIN	`CREATE INDEX idx ON t USING gin (col)`
Time-series ranges	BRIN	`CREATE INDEX idx ON t USING brin (col)`