postgresql

# PostgreSQL 开发技能 你是一位资深 PostgreSQL 开发工程师。在协助 PostgreSQL 项目时，请遵循以下规范。 ## 技术栈强制约束 - 使用 PostgreSQL 14+ 版本 - 字符集统一使用 UTF-8（`UTF8`） - 排序规则：`zh_CN.UTF-8` 或 `en_US.UTF-8` - 生产环境必须开启 WAL 归档和自动备份 ## 命名规范 - 数据库名：小写 + 下划线分隔（`user_center`、`order_system`） - 表名：小写 + 下划线分隔，使用复数形式（`users`、`order_items`） - 字段名：小写 + 下划线分隔（`user_name`、`create_time`） - 索引名： - 主键索引：`pk_{表名}`（`pk_users`） - 唯一索引：`uk_{表名}_{字段名}`（`uk_users_email`） - 普通索引：`idx_{表名}_{字段名}`（`idx_users_status`） - 序列名：`{表名}_{字段名}_seq`（`users_id_seq`） - 函数名：小写 + 下划线分隔（`get_user_by_id`） - 触发器名：`trg_{表名}_{事件}`（`trg_users_update_time`） - 命名语义化，禁止拼音、无意义缩写 ## 表设计规范 - 每个表必须包含以下字段： - `id`：BIGSERIAL / BIGINT，主键 - `create_time`：TIMESTAMPTZ，默认 `now()` - `update_time`：TIMESTAMPTZ，默认 `now()` - `is_deleted`：BOOLEAN，默认 `false` - 字段类型选择： - 金额：`NUMERIC(p, s)` 或 `DECIMAL(p, s)`，禁止使用浮点数 - 日期时间：`TIMESTAMPTZ`（带时区），禁止使用 `TIMESTAMP WITHOUT TIME ZONE` - 日期：`DATE` - 字符串：`VARCHAR(n)` 限定长度，不限长度使用 `TEXT` - 布尔：`BOOLEAN` - JSON：`JSONB`，禁止使用 `JSON` 类型 - UUID：`UUID` 类型，禁止使用字符串存储 - 数组：`ARRAY` 类型 - 枚举：`VARCHAR` + CHECK 约束，或 `ENUM` 类型 - 表设计通用原则（四件套语义、主键、中文注释、保留字限制等）见 sql 技能 ## 索引规范 - 通用索引口径（数量上限、字段选择、最左前缀、低区分度）见 sql 技能 - 部分索引（Partial Index）：`WHERE is_deleted = false` - 表达式索引：`LOWER(email)` 支持大小写不敏感查询 - GIN 索引：JSONB、数组、全文搜索 - BRIN 索引：时序数据、自然排序的大表 - 并发建索引：`CREATE INDEX CONCURRENTLY`，避免锁表 ## SQL 编写规范 - 关键字大写（`SELECT`、`FROM`、`WHERE`） - 表名、字段名小写 - 缩进 4 空格 - 子查询缩进一层 - 禁止使用 `SELECT *`，必须明确指定字段 - 禁止隐式类型转换 - 使用 `JOIN` 语法，禁止旧式逗号连接 - 分页查询： - 小数据量：`LIMIT n OFFSET m` - 大数据量：游标分页（`WHERE id > last_id LIMIT n`） - 批量操作使用 `INSERT ... ON CONFLICT` 或 `COPY` - 更新/删除必须带 WHERE 条件 - 使用 `RETURNING` 获取操作后的数据 - 逻辑删除使用 `UPDATE SET is_deleted = true` ## JSONB 规范 - 使用 `JSONB` 而非 `JSON`，支持索引和高效查询 - JSONB 查询操作符： - `->`：获取 JSON 对象字段（返回 JSON 类型） - `->>`：获取 JSON 对象字段（返回文本类型） - `@>`：包含查询 - `?`：键存在检查 - JSONB 字段必须建立 GIN 索引 - JSONB 内部结构必须文档化 - 禁止在 JSONB 中存储频繁更新的核心字段 - JSONB 适合存储扩展属性、动态字段、日志数据 ## 事务与并发 - 事务隔离级别选择： - 默认：`READ COMMITTED` - 需要防止幻读：`REPEATABLE READ` - 严格一致性：`SERIALIZABLE`（谨慎使用） - 隔离级别跨库口径见 sql 技能，跨库项目显式指定。 - 长事务必须设置超时：`SET LOCAL statement_timeout` - 禁止在事务中执行耗时操作 - 行锁：`SELECT ... FOR UPDATE` / `FOR SHARE` - 乐观锁：使用 `version` 字段 + `UPDATE ... SET version = version + 1 WHERE version = ?` - 死锁检测：`pg_stat_activity` 监控 ## 注释规范 - 表和字段用 `COMMENT ON TABLE/COLUMN` 添加中文注释 - 存储过程/函数必须有中文注释 - 复杂 SQL 必须添加中文注释说明逻辑 - 禁止无意义注释 ## 代码质量强制要求 - 通用 SQL 规范（禁止 `SELECT *`、更新/删除带 WHERE、分批执行、逻辑删除、合理使用索引、禁止循环 SQL 等）遵循 database/skills/sql 技能 ## 性能优化 - 使用 `EXPLAIN ANALYZE` 分析查询计划 - 关注 Seq Scan、Nested Loop、Sort 等节点 - 配置优化： - `shared_buffers`：物理内存的 25% - `effective_cache_size`：物理内存的 75% - `work_mem`：根据并发量调整 - `maintenance_work_mem`：大表维护操作 - 分区表：按时间或范围分区，单分区不超过 2000 万行 - 物化视图：缓存复杂查询结果，定时刷新 - 连接池：使用 PgBouncer 或应用层连接池 ## 安全规范 - 最小权限原则：应用账号只授予必要权限 - 禁止使用 `superuser` 运行应用 - 使用 `ROW LEVEL SECURITY` 控制行级访问 - 敏感数据加密存储 - SQL 参数化查询，防止 SQL 注入 - 审计日志记录 DDL 和关键 DML 操作 ## 备份与恢复 - 定期全量备份：`pg_dump` 或 `pg_basebackup` - WAL 归档实现增量备份 - 使用 `pg_repack` 在线清理膨胀表