postgresql-expert-best-practices-code-review

SKILL.md

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