java21-template/docs/database.md

数据库设计与迁移 (Database)

---

1. 技术选型

项	选择	说明
数据库	PostgreSQL 15+	JSONB 支持、RLS、分区表
连接池	HikariCP（Spring Boot 默认）	高性能、低开销
迁移工具	Flyway	Forward-only、版本化迁移
ORM	Spring Data JPA + Hibernate	通过 MapStruct 转换 PO ↔ Domain

---

2. Schema 设计原则

2.1 多租户隔离

所有业务表必须包含 tenant_id BIGINT NOT NULL 列。所有查询条件必须包含 tenant_id，通过 Repository 方法签名强制（如 findByTenantIdAndId）。

建议在生产环境启用 PostgreSQL Row-Level Security (RLS) 作为最后防线：

ALTER TABLE t_order ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON t_order
  USING (tenant_id = current_setting('app.current_tenant')::BIGINT);

2.2 审计字段

所有表通过 JPA @MappedSuperclass（BaseAuditPO）自动维护：

字段	类型	说明
create_time	TIMESTAMP NOT NULL DEFAULT now()	创建时间
update_time	TIMESTAMP NOT NULL DEFAULT now()	最后更新时间
deleted	BOOLEAN NOT NULL DEFAULT false	软删除标记

2.3 软删除

业务表使用 deleted 字段实现软删除。所有查询默认排除已删除记录（通过 Hibernate @Where 或手动条件）。物理删除仅在数据归档场景下使用。

---

3. 索引策略

3.1 通用规则

每张表的主键使用自增 BIGSERIAL 或 UUID（根据业务选择）。tenant_id 必须出现在复合索引的最左列。常用查询条件组合创建复合索引，遵循最左前缀原则。

3.2 t_order 表索引示例

-- 主键索引（自动）
-- 租户 + 订单号唯一索引
CREATE UNIQUE INDEX uk_order_tenant_no ON t_order(tenant_id, order_no);

-- 租户 + 状态（列表查询）
CREATE INDEX idx_order_tenant_status ON t_order(tenant_id, status);

-- 租户 + 创建时间（分页排序）
CREATE INDEX idx_order_tenant_create_time ON t_order(tenant_id, create_time DESC);

3.3 索引维护

定期检查未使用的索引：

SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

---

4. Flyway 迁移规范

4.1 文件命名

V{版本号}__{描述}.sql       -- 版本化迁移（不可修改）
R__{描述}.sql               -- 可重复迁移（如视图、存储过程）

版本号格式：V1__create_order_table.sql、V2__add_order_status_index.sql。

描述使用下划线分隔的蛇形命名（snake_case）。

4.2 编写规则

每个迁移文件只做一件事（一张表、一个索引、一次 ALTER）。所有 DDL 必须是幂等的（使用 IF NOT EXISTS、IF EXISTS）。禁止在迁移中使用 DROP TABLE（除非有审批的变更单号且附带数据备份计划）。DML 迁移（数据修复）必须包含 WHERE 条件，禁止无条件 UPDATE / DELETE。

4.3 不可逆性说明

Flyway 迁移是 forward-only 的。一旦迁移文件被合并到 main 分支并在任何环境执行过，就不得修改其内容（会导致 checksum 不匹配）。

若需要回退 schema 变更，必须创建新的迁移文件（新版本号）执行反向 DDL。建议采用 expand-then-contract 模式：先在新迁移中添加新列 → 部署新代码 → 确认稳定后在后续迁移中移除旧列。

4.4 迁移测试

所有迁移脚本在 CI 中通过 Testcontainers 验证：自动启动 PostgreSQL 15 容器 → 应用全部迁移 → 运行集成测试。参见 testing.md。

---

5. 连接池配置

参数	dev	staging	prod	说明
maximumPoolSize	5	10	20	最大连接数
minimumIdle	2	5	10	最小空闲连接
connectionTimeout	30000ms	20000ms	10000ms	获取连接超时
idleTimeout	600000ms	300000ms	300000ms	空闲连接存活时间
maxLifetime	1800000ms	1800000ms	1800000ms	连接最大生命周期

注意：maximumPoolSize × Pod 副本数不得超过数据库 max_connections。 建议预留 20% 给运维工具和 Flyway 迁移。

---

6. 数据备份策略（建议）

类型	频率	保留期
全量备份 (pg_dump)	每日	30 天
WAL 归档	持续	7 天
逻辑备份 (关键表)	每周	90 天

建议使用 PgBackRest 或云厂商托管备份服务。恢复演练至少每季度执行一次。