postgresql-best-practices

PostgreSQL 高级最佳实践（PostgreSQL 18+）

架构一览

                        ┌─── PostgreSQL Database ──────────────────────────────┐
                        │                                                      │
                        │  ┌──────────────────┐    ┌───────────────────────┐   │
                        │  │   api schema      │    │   private schema      │   │
  ┌─────────────┐       │  │──────────────────│    │───────────────────────│   │
  │ Application │─EXECUTE─▶│ get_customer()   │───▶│ set_updated_at()     │   │
  └─────────────┘       │  │ insert_order()   │    │ hash_password()      │   │
        │               │  └────────┬─────────┘    └──────────┬────────────┘   │
        │               │           │                         │                │
        │               │           │ SECURITY DEFINER        │ triggers       │
        │               │           ▼                         ▼                │
        │               │  ┌──────────────────────────────────────────────┐    │
        │               │  │              data schema                     │    │
     BLOCKED            │  │──────────────────────────────────────────────│    │
        │               │  │  customers    orders    ...                  │    │
        └ ─ ─ ─ ✕       │  └──────────────────────────────────────────────┘    │
                        │                                                      │
                        └──────────────────────────────────────────────────────┘

技能内容

🚀 入门（请先阅读）

文档	用途
quick-reference.md	快速查阅 - 单页速查表（请打印！）
schema-architecture.md	从这里开始 - Schema 分离模式（data/private/api）
coding-standards-trivadis.md	编码标准与命名约定（l_、g_、co_）

📚 核心参考（每日使用）

文档	用途
plpgsql-table-api.md	表 API 函数、过程、触发器
schema-naming.md	所有对象的命名约定
data-types.md	数据类型选择（UUIDv7、text、timestamptz）
indexes-constraints.md	索引类型、策略、约束
migrations.md	原生迁移系统文档
anti-patterns.md	需要避免的常见错误
checklists-troubleshooting.md	项目检查清单与问题解决方案

🔧 高级主题（按需查阅）

文档	用途
testing-patterns.md	pgTAP 单元测试、测试工厂
performance-tuning.md	EXPLAIN ANALYZE、查询优化、JIT
row-level-security.md	RLS 模式、多租户隔离
jsonb-patterns.md	JSONB 索引、查询、验证
audit-logging.md	通用审计触发器、变更跟踪
bulk-operations.md	COPY、批量插入、Upsert
session-management.md	会话变量、连接池
transaction-patterns.md	隔离级别、锁定、死锁预防
full-text-search.md	tsvector、tsquery、排名、多语言
partitioning.md	范围、列表、哈希分区策略
window-functions.md	窗口帧、排名、累计计算
time-series.md	时间序列数据模式、BRIN 索引
event-sourcing.md	事件存储、投影、CQRS
queue-patterns.md	作业队列、SKIP LOCKED、LISTEN/NOTIFY
encryption.md	pgcrypto、列加密、TLS
vector-search.md	pgvector、嵌入向量、相似性搜索
postgis-patterns.md	空间数据、地理查询

🚀 DevOps 与迁移

文档	用途
oracle-migration-guide.md	PL/SQL 到 PL/pgSQL 的转换
cicd-integration.md	GitHub Actions、GitLab CI、Docker
monitoring-observability.md	pg_stat_statements、指标、告警
backup-recovery.md	pg_dump、pg_basebackup、PITR
replication-ha.md	流复制/逻辑复制、故障转移

📊 数据仓库

文档	用途
data-warehousing-medallion.md	Medallion 架构 - Bronze/Silver/Gold、数据沿袭、ETL
analytical-queries.md	分析型查询模式、OLAP 优化、GROUPING SETS

可执行脚本

脚本	用途
001_install_migration_system.sql	安装迁移系统（核心函数）
002_migration_runner_helpers.sql	辅助过程（run_versioned、run_repeatable）
003_example_migrations.sql	迁移模式示例
999_uninstall_migration_system.sql	完全移除迁移系统

---

核心架构

模式隔离模式

Application → api schema → data schema
                ↓
            private schema (triggers, helpers)

模式	包含内容	访问权限	用途
data	表、索引	无	数据存储
private	触发器、辅助函数	无	内部逻辑
api	函数、过程	应用程序	外部接口
app_audit	审计表	管理员	更改跟踪
app_migration	迁移跟踪	管理员	模式版本管理

安全模型

所有 api 函数都必须具备：

SECURITY DEFINER
SET search_path = data, private, pg_temp

---

快速参考

创建表模式

CREATE TABLE data.{table_name} (
    id              uuid PRIMARY KEY DEFAULT uuidv7(),
    -- columns...
    created_at      timestamptz NOT NULL DEFAULT now(),
    updated_at      timestamptz NOT NULL DEFAULT now()
);

CREATE TRIGGER {table}_bu_updated_trg
    BEFORE UPDATE ON data.{table_name}
    FOR EACH ROW EXECUTE FUNCTION private.set_updated_at();

API 函数模式

CREATE FUNCTION api.{action}_{entity}(in_param type)
RETURNS TABLE (col1 type, col2 type)
LANGUAGE sql STABLE
SECURITY DEFINER
SET search_path = data, private, pg_temp
AS $$
    SELECT col1, col2 FROM data.{table} WHERE ...;
$$;

API 过程模式

CREATE PROCEDURE api.{action}_{entity}(
    in_param type,
    INOUT io_id uuid DEFAULT NULL
)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = data, private, pg_temp
AS $$
BEGIN
    INSERT INTO data.{table} (...) VALUES (...) RETURNING id INTO io_id;
END;
$$;

迁移模式

SELECT app_migration.acquire_lock();

CALL app_migration.run_versioned(
    in_version := '001',
    in_description := 'Description',
    in_sql := $mig$ ... $mig$,
    in_rollback_sql := '...'
);

SELECT app_migration.release_lock();

---

命名约定

Trivadis 风格的变量前缀

前缀	类型	示例
l_	局部变量	l_customer_count
g_	会话／全局变量	g_current_user_id
co_	常量	co_max_retries
in_	IN 参数	in_customer_id
out_	OUT 参数（仅函数）	out_total
io_	INOUT 参数（过程）	io_id
c_	游标	c_active_orders
r_	记录	r_customer
t_	数组／表	t_order_ids
e_	异常	e_not_found

注意：PostgreSQL 过程仅支持 INOUT 参数，不支持 OUT。所有过程输出参数都使用 io_ 前缀。

数据库对象

对象	模式	示例
表	snake_case，复数	orders、order_items
列	snake_case	customer_id、created_at
主键	id	id
外键	{table_singular}_id	customer_id
索引	{table}_{cols}_idx	orders_customer_id_idx
唯一约束	{table}_{cols}_key	users_email_key
函数	{action}_{entity}	get_customer、select_orders
过程	{action}_{entity}	insert_order、update_status
触发器	{table}_{timing}{event}_trg	orders_bu_trg

---

数据类型建议

使用	替代
text	char(n)、varchar(n)
numeric(p,s)	money、float
timestamptz	timestamp
boolean	integer 标志
uuidv7()	serial、uuid_generate_v4()
GENERATED ALWAYS AS IDENTITY	serial、bigserial
jsonb	json、EAV 模式

---

关键反模式

1. ❌ 应用程序直接访问表
2. ❌ RETURNS SETOF table（会暴露所有列）
3. ❌ 使用 SECURITY DEFINER 却未设置 SET search_path
4. ❌ 使用不带时区的 timestamp
5. ❌ 在子查询中使用 NOT IN（应使用 NOT EXISTS）
6. ❌ 对时间戳使用 BETWEEN（应使用 >= AND <）
7. ❌ 外键缺少索引
8. ❌ 使用 serial/bigserial（应使用 IDENTITY）
9. ❌ 使用 varchar(n) 设置任意长度限制（应使用 text）
10. ❌ 使用 SELECT FOR UPDATE 却未指定 NOWAIT/SKIP LOCKED

---

PostgreSQL 18+ 功能

功能	用法
uuidv7()	id uuid DEFAULT uuidv7() - 按时间排序的 UUID
虚拟生成列	col type GENERATED ALWAYS AS (expr) - 在查询时计算
RETURNING 中的 OLD/NEW	UPDATE ... RETURNING OLD.col, NEW.col
时态约束	PRIMARY KEY (id) WITHOUT OVERLAPS
NOT VALID 约束	添加约束时无需完整扫描表

---

文件组织

db/
├── migrations/
│   ├── V001__create_schemas.sql
│   ├── V002__create_tables.sql
│   └── repeatable/
│       ├── R__private_triggers.sql
│       └── R__api_functions.sql
├── schemas/
│   ├── data/           # Table definitions
│   ├── private/        # Internal functions
│   └── api/            # External interface
└── seeds/              # Reference data