postgres-best-practices

name	postgres-best-practices
description	Supabase PostgreSQL best practices library — 33 impact-rated rules (CRITICAL→LOW) covering connection pooling, RLS, locking, VACUUM, full-text search, and JSONB indexing. Use when writing, reviewing, or optimizing PostgreSQL queries, schema, or configuration.
argument-hint	[category or specific rule name]
allowed-tools	Read
context	fork
metadata	{"triggers":"connection pooling, pgbouncer, RLS row level security, SKIP LOCKED, queue workers, deadlock, advisory lock, VACUUM ANALYZE, full-text search tsvector, JSONB GIN index, pg_stat_statements, connection limits, max_connections, idle timeout, prepared statements, transaction mode pooling","related-skills":"database-schema-designer, sql-optimization-patterns, postgresql, sql-pro, vector-database","domain":"infrastructure","role":"optimizer","scope":"implementation","output-format":"document"}
last-reviewed	2026-03-15

Iron Law

NEVER TUNE WHAT YOU HAVEN'T MEASURED — CHECK pg_stat_statements AND pg_stat_activity BEFORE CHANGING CONFIGURATION OR ADDING INDEXES

When to Use This Skill

• Configuring connection pooling (PgBouncer, Prisma pool, asyncpg pool, R2DBC pool)
• Implementing Row Level Security (RLS) for multi-tenant data isolation
• Designing worker queues with SKIP LOCKED and advisory locks
• Preventing deadlocks in concurrent write workloads
• Configuring VACUUM/ANALYZE and autovacuum tuning
• Adding full-text search with tsvector/tsquery
• Indexing JSONB columns (jsonb_ops vs jsonb_path_ops)
• Reviewing any PostgreSQL schema or query against the 8-category rule library

Do Not Use This Skill When

• You need query EXPLAIN analysis and index pattern SQL (use sql-optimization-patterns)
• You need schema migration design or ERDs (use database-schema-designer)
• You need cloud analytics or HTAP (use sql-pro)

Rule Categories by Priority

Priority	Category	Impact	Rules
1	Query Performance	CRITICAL	rules/query-*.md (5 rules)
2	Connection Management	CRITICAL	rules/conn-*.md (4 rules)
3	Security & RLS	CRITICAL	rules/security-*.md (3 rules)
4	Schema Design	HIGH	rules/schema-*.md (6 rules)
5	Concurrency & Locking	MEDIUM-HIGH	rules/lock-*.md (4 rules)
6	Data Access Patterns	MEDIUM	rules/data-*.md (4 rules)
7	Monitoring & Diagnostics	LOW-MEDIUM	rules/monitor-*.md (3 rules)
8	Advanced Features