| name | postgresql-expert-best-practices-code-review |
| description | PostgreSQL database design, migration, and performance optimization best practices. This skill should be used when writing, reviewing, or refactoring database schemas, migrations, or query patterns. Triggers on tasks involving PostgreSQL databases, schema design, migration optimization, or data modeling. |
| license | MIT |
| metadata | {"author":"wispbit","version":"1.0.0"} |
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