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