Postgresql Expert Best Practices Code Review

---
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