postgresql-expert-best-practices-code-review

Original：🇺🇸 English

Translated

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.

2installs

Sourcewispbit-ai/skills

Added on2026-02-13

NPX Install

npx skill4agent add wispbit-ai/skills postgresql-expert-best-practices-code-review

SKILL.md Content

View Translation Comparison →

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