postgres-patterns

name	postgres-patterns
type	reference
description	PostgreSQL database patterns for query optimization, schema design, indexing, and security. Based on Supabase best practices.
origin	ECC
paths	["**/*.sql","**/migrations/**","**/schema.prisma","**/drizzle.config.*"]
when_to_use	When writing SQL queries, designing PostgreSQL schemas, optimizing indexes, or reviewing database security patterns
allowed-tools	Read, Glob, Grep
user-invocable	true
effort	2

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)

Data Type Quick Reference

Use Case	Correct Type	Avoid
IDs	bigint	int, random UUID
Strings	text	varchar(255)
Timestamps	timestamptz	timestamp
Money	numeric(10,2)	float
Flags	boolean	varchar, int

Common Patterns

Composite Index Order:

 INDEX idx  orders (status, created_at);