postgres-patternslisted

# PostgreSQL Patterns ## Problem Statement Alembic generates migrations but doesn't understand PostgreSQL performance implications. This skill covers reviewing migrations for PostgreSQL-specific issues and writing efficient queries. --- ## Pattern: Index Review ### When to Add Indexes ```sql -- ✅ ADD INDEX: Foreign keys (almost always) CREATE INDEX ix_assessments_user_id ON assessments (user_id); -- ✅ ADD INDEX: Frequently filtered columns CREATE INDEX ix_assessments_status ON assessments (status); -- ✅ ADD INDEX: Columns in WHERE + ORDER BY together CREATE INDEX ix_assessments_user_status ON assessments (user_id, status); -- ✅ ADD INDEX: Columns used in JOIN conditions CREATE INDEX ix_answers_question_id ON answers (question_id); ``` ### When NOT to Add Indexes ```sql -- ❌ SKIP: Small tables (< 1000 rows) -- ❌ SKIP: Write-heavy tables with rare reads -- ❌ SKIP: Low cardinality columns alone (boolean, status with 3 values) -- ❌ SKIP: Columns rarely used in WHERE/JOIN/ORDER BY ``` ### Index Column Order Matters ```sql -- For query: WHERE user_id = ? AND status = ? ORDER BY created_at -- ✅ CORRECT: Most selective first, ORDER BY column last CREATE INDEX ix_assessments_user_status_created ON assessments (user_id, status, created_at); -- ❌ WRONG: Order doesn't match query pattern CREATE INDEX ix_assessments_created_status_user ON assessments (created_at, status, user_id); ``` --- ## Pattern: Partial Indexes **Problem:** Full index on column where you only query