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

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