---
name: "postgresql"
description: "PostgreSQL开发专家助手。当用户需要进行PostgreSQL数据库设计、SQL优化、存储过程、JSONB查询或高级数据库特性开发时调用。"
---

# 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` 类型
- 每个表必须有主键
- 每个表必须有中文注释（`COMMENT ON TABLE`）
- 每个字段必须有中文注释（`COMMENT ON COLUMN`）
- 禁止使用保留字作为表名/字段名

## 索引规范

- 单表索引数不超过 8 个
- 建立索引的字段选择：
  - 高频查询条件字段
  - 排序字段
  - JOIN 关联字段
  - 外键字段
- 复合索引遵循最左前缀原则
- 低区分度字段（如布尔值）不单独建索引
- 使用 `EXPLAIN ANALYZE` 验证索引使用情况
- 部分索引（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`（谨慎使用）
- 长事务必须设置超时：`SET LOCAL statement_timeout`
- 禁止在事务中执行耗时操作
- 行锁：`SELECT ... FOR UPDATE` / `FOR SHARE`
- 乐观锁：使用 `version` 字段 + `UPDATE ... SET version = version + 1 WHERE version = ?`
- 死锁检测：`pg_stat_activity` 监控

## 注释规范

- 每个表必须有中文注释
- 每个字段必须有中文注释
- 存储过程/函数必须有中文注释
- 复杂 SQL 必须添加中文注释说明逻辑
- 禁止无意义注释

## 代码质量强制要求

- 禁止 `SELECT *`，必须指定字段
- 更新/删除必须带 WHERE 条件
- 禁止在 WHERE 条件中对字段使用函数（导致索引失效）
- 大批量操作分批执行，避免长事务
- 禁止物理删除，使用逻辑删除
- 查询必须使用索引，避免全表扫描
- 禁止在循环中执行 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` 在线清理膨胀表