postgresql-expert-best-practices-code-review

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

PostgreSQL Expert Best Practices

PostgreSQL专家最佳实践

Simple, pragmatic, opinionated. Only what matters for writing production-grade PostgreSQL queries.

简洁、务实、有明确立场。仅涵盖编写生产级PostgreSQL查询所需的关键内容。

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

在以下场景中可参考本指南：

编写数据库迁移脚本或修改模式
创建或修改PostgreSQL表与列
添加索引、约束或外键
评审数据库模式以排查性能问题
重构现有数据库结构
优化查询性能或数据库设计

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`

优先级	类别	影响程度	前缀
1	索引管理	极高-高	`only-concurrent-indexes` , `add-index-for-foreign-key`
2	约束安全性	高	`unique-constraint` , `split-foreign-key` , `set-column-not-null`
3	模式设计	中	`only-jsonb` , `always-include-columns` , `limit-non-unique-index`
4	命名规范	低	`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

```
only-concurrent-indexes
```
- 创建索引时始终使用CONCURRENTLY，避免阻塞写入操作
```
add-index-for-foreign-key
```
- 为外键创建索引以提升查询性能
```
unique-constraint
```
- 将唯一约束的创建拆分为并发索引创建+约束添加两个步骤
```
split-foreign-key
```
- 先添加外键（不进行验证），再单独执行验证操作
```
set-column-not-null
```
- 设置NOT NULL前先使用检查约束，避免表锁
```
only-jsonb
```
- 使用JSONB而非JSON，以获得更优的性能与索引能力
```
always-include-columns
```
- 所有表中均需包含id、created_at和updated_at字段，以满足可审计性要求
```
limit-non-unique-index
```
- 非唯一索引最多包含3列，以保证效率
```
index-naming-standards
```
- 使用统一的索引命名规范：idx_tablename_columnname
```
column-naming-standards
```
- 保持统一的蛇形命名（snake_case），外键字段以id为后缀

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

阅读单个规则文件以获取详细说明与代码示例：

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

每个规则文件包含：

规则重要性的简要说明
影响级别与描述
错误的迁移示例及问题说明
符合最佳实践的正确迁移示例
额外背景信息与参考资料