PostgreSQL Expert Best Practices

Simple, pragmatic, opinionated. Only what matters for writing production-grade PostgreSQL queries.

When to Apply

Reference these guidelines when:

• Writing database migrations or schema changes
• Creating or modifying PostgreSQL tables and columns
• Adding indexes, constraints, or foreign keys
• Reviewing database schema for performance issues
• Refactoring existing database structures
• Optimizing query performance or database design

Rule Categories by Priority

Priority	Category	Impact	Prefix
1	Index Management	CRITICAL-HIGH	only-concurrent-indexes, add-index-for-foreign-key
2	Constraint Safety	HIGH	unique-constraint, split-foreign-key, set-column-not-null
3	Schema Design	MEDIUM	only-jsonb, always-include-columns, limit-non-unique-index
4	Naming Standards	LOW	index-naming-standards, column-naming-standards

Quick Reference

• only-concurrent-indexes - Always use CONCURRENTLY to prevent blocking writes during index creation
• add-index-for-foreign-key - Create indexes for foreign keys to improve query performance
• unique-constraint - Split unique constraint creation into concurrent index + constraint steps
• split-foreign-key - Add foreign keys without validation first, then validate separately
• set-column-not-null - Use check constraints before setting NOT NULL to avoid table locks
• only-jsonb - Use JSONB instead of JSON for better performance and indexing capabilities
• always-include-columns - Include id, created_at, and updated_at in all tables for auditability
• limit-non-unique-index - Limit non-unique indexes to maximum three columns for efficiency
• index-naming-standards - Use consistent index naming: idx_tablename_columnname
• column-naming-standards - Maintain consistent snake_case naming and use id suffix for foreign keys

How to Use

Read individual rule files for detailed explanations and code examples:

rules/only-concurrent-indexes.md
rules/add-index-for-foreign-key.md
rules/unique-constraint.md
rules/split-foreign-key.md
rules/set-column-not-null.md
rules/only-jsonb.md
rules/always-include-columns.md
rules/limit-non-unique-index.md
rules/index-naming-standards.md
rules/column-naming-standards.md

Each rule file contains:

• Brief explanation of why it matters
• Impact level and description
• Incorrect migration example with explanation
• Correct migration example with best practices
• Additional context and references