<< All versions
Skill v1.0.1
currentAutomated scan96/100affaan-m/everything-claude-code/postgres-patterns
4 files
──Details
PublishedMay 15, 2026 at 01:56 AM
Content Hashsha256:2b07d5f7de1b68a9...
Git SHA375d750b4c14
Bump Typepatch
──Files
Files (1 file, 3.7 KB)
SKILL.md3.7 KBactive
SKILL.md · 149 lines · 3.7 KB
version: "1.0.1" name: postgres-patterns description: PostgreSQL database patterns for query optimization, schema design, indexing, and security. Based on Supabase best practices. origin: ECC
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:
sql
-- Equality columns first, then range columnsCREATE INDEX idx ON orders (status, created_at);-- Works for: WHERE status = 'pending' AND created_at > '2024-01-01'
Covering Index:
sql
CREATE INDEX idx ON users (email) INCLUDE (name, created_at);-- Avoids table lookup for SELECT email, name, created_at
Partial Index:
sql
CREATE INDEX idx ON users (email) WHERE deleted_at IS NULL;-- Smaller index, only includes active users
RLS Policy (Optimized):
sql
CREATE POLICY policy ON ordersUSING ((SELECT auth.uid()) = user_id); -- Wrap in SELECT!
UPSERT:
sql
INSERT INTO settings (user_id, key, value)VALUES (123, 'theme', 'dark')ON CONFLICT (user_id, key)DO UPDATE SET value = EXCLUDED.value;
Cursor Pagination:
sql
SELECT * FROM products WHERE id > $last_id ORDER BY id LIMIT 20;-- O(1) vs OFFSET which is O(n)
Queue Processing:
sql
UPDATE jobs SET status = 'processing'WHERE id = (SELECT id FROM jobs WHERE status = 'pending'ORDER BY created_at LIMIT 1FOR UPDATE SKIP LOCKED) RETURNING *;
Anti-Pattern Detection
sql
-- Find unindexed foreign keysSELECT conrelid::regclass, a.attnameFROM pg_constraint cJOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)WHERE c.contype = 'f'AND NOT EXISTS (SELECT 1 FROM pg_index iWHERE i.indrelid = c.conrelid AND a.attnum = ANY(i.indkey));-- Find slow queriesSELECT query, mean_exec_time, callsFROM pg_stat_statementsWHERE mean_exec_time > 100ORDER BY mean_exec_time DESC;-- Check table bloatSELECT relname, n_dead_tup, last_vacuumFROM pg_stat_user_tablesWHERE n_dead_tup > 1000ORDER BY n_dead_tup DESC;
Configuration Template
sql
-- Connection limits (adjust for RAM)ALTER SYSTEM SET max_connections = 100;ALTER SYSTEM SET work_mem = '8MB';-- TimeoutsALTER SYSTEM SET idle_in_transaction_session_timeout = '30s';ALTER SYSTEM SET statement_timeout = '30s';-- MonitoringCREATE EXTENSION IF NOT EXISTS pg_stat_statements;-- Security defaultsREVOKE ALL ON SCHEMA public FROM public;SELECT pg_reload_conf();
Related
- Agent:
database-reviewer- Full database review workflow - Skill:
clickhouse-io- ClickHouse analytics patterns - Skill:
backend-patterns- API and backend patterns
Based on Supabase Agent Skills (credit: Supabase team) (MIT License)