| 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);
