适用于 PostgreSQL 11 及以上版本
1. 设计原则
1.1. 关于范式
如无性能上的必要原因，应该遵循关系数据库理论，达到较高的范式匹配（3NF），避免数据冗余，明确数据间的关系。
如果对性能有较高要求，或者在特定场景达成业务目标的便利性收益高于数据管理影响，可以设计适当的突破范式要求。
1.2. 字符集和编码
应当采用 Unicode 字符集和 UTF8 编码，此为 PostgreSQL 数据库服务器默认设置，并且，如果在创建数据库（实例）时没有特别指定，也将是数据库（实例）的默认设置。
如果有强烈的中华多文字支持要求，如简体汉字、繁体汉字、少数民族文字、日文、韩文等，可以使用 GB18030 字符集和编码，不建议使用 GB2312、GBK。
1.3. 数据库服务器和数据库
一个操作系统中只部署 1 个数据库服务器软件。
一个数据库服务器中可以创建多个数据库。
1.4. 表空间
对于 PostgreSQL 来说，在 同一个 磁盘分区上建立多个表空间没有太多实际意义。
从合理利用磁盘性能和空间角度，可以分别建立不同的表空间，如：
- 在高 IO 性能的磁盘分区上创建的表空间，可以用来存放经常访问的表和索引。
- 在便宜和较低 IO 性能的磁盘分区上创建的表空间，可以用来存放很少使用或性能要求不高的归档数据的表。
对于容器部署的数据库，容器内可以使用默认表空间 pg_default（路径 $PGDATA/base），并映射到容器外宿主机的特定路径下。
非容器部署的数据库，建议在指定的路径下创建表空间。
多个数据库可以共用同一个表空间。
注意：
PostgreSQL 中的表空间与 Oracle 不一样，创建 PostgreSQL 表空间只要指定名称与数据库文件的目录，而没有具体的大小。
PostgreSQL 表空间不适用“自动扩容”这个概念，存储不足时可以通过扩展表空间所在存储容量，或者在不同存储设备/分区中新建表空间并指定新表使用新表空间来达到扩容目的。
1.5. Schema
建议为子系统、业务模块或用户分配对应的 schema。
不建议使用 public schema，即便是不同业务共享的对象也不建议选择 public schema。
1.6. 表和字段
表要求有主键。
对于关联两个表的字段，一般应该分别建立主键、外键。
是建立外键约束还是在代码访问时遵循外键逻辑，根据对数据完整性的要求、性能要求等多方面因素决定，可参考文末链接资料。
一般来说：
- 不要为引用的字典表字段建立外键约束，字典表常常被多个表引用，外键检查引起的锁等待在数据量大、访问频繁的情况下更容易造成死锁。
- 表有 4 个以上外键约束时，请特别注意是否确有必要，是否应该转为逻辑外键而非外键约束。
- 表被其他 4 个以上表引用为外键关联表时，请特别注意外键检查锁等待可能造成的死锁问题。
有唯一性要求的字段必须建立唯一性约束。
建议为主要业务表增加创建时间字段 create_time 和最后更新时间字段 update_time，用于乐观锁和问题排查。
1.7. 分区表
分区表有一些优点:
- 改善性能：对大表的查询、更新、备份恢复、建索引等操作可以分解到表的不同分区来并行执行，可使效率更高；
- 维护轻松：如果需要重建表，独立管理每个分区比管理单个大表要轻松得多；
- 均衡I/O：可以把表的不同分区分配到不同的磁盘来平衡I/O改善性能；
- 增强可用性：如果表的一个分区由于系统故障而不能使用，表的其余好的分区仍然可以使用；
- 减少关闭时间：如果系统故障只影响表的一部分分区，那么只有这部分分区需要修复，故能比整个大表修复花的时间更少；
- 分区对用户透明，最终用户感觉不到分区的存在。
是否应用分区表功能设计，可能参考以下几个因素：
- 大于 2GB 的表
- 含有 1000 万条记录以上的表
- 将会含有大量数据的表。
- 强行拆分后可利于并行操作的表。
- 含有需要定期归档日志或删除部分的表。
对于数据量比较大的表，根据表数据的属性进行分区，以得到较好的性能。
- 如果表按某些字段进行增长，则采用按字段值范围进行 Range 范围分区。
- 如果表按某个字段的几个关键值进行分布，则采用 List 列表分区。
- 对于静态表，则采用 Hash 哈希分区或 List 列表分区。
- 在范围分区中，如果数据按某关键字段均衡分布，则采用子分区的复合分区方法。
1.8. 视图
无。
1.9. 物化视图
物化视图是包括一个查询结果的数据库对像，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据，也可以称为快照。
物化视图可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。
物化视图有很多方面和索引很相似：
- 使用物化视图的目的是为了提高查询性能；
- 物化视图对应用透明，增加和删除物化视图不会影响应用程序中SQL语句的正确性和有效性；
- 物化视图需要占用存储空间；
- 当基表发生变化时，物化视图也应当刷新。
物化视图可以查询表、视图和其它的物化视图。
物化视图有一些优点：
- 能够提高查询速度，这主要是因为物化视图存储了实际的数据，其次具有查询重写功能。最后，物化视图具有实体表，你也可以在上面建立索引，总之大体上当作一个表用就可以了。
- 简化了开发任务,意思是开发的人员有的时候，无需直接关注部分sql的性能，而通过dba的努力，使用查询重写来完成性能的提升。
- 减少了工作量，因为物化视图可以定义两种刷新方式，可以根据设备情况、应用情况和需求来控制刷新的方式。
- 立即刷新：即视图主表发生变化的时候，视图立即刷新内容
- 按需刷新：就是手动刷新，或者是定时刷新
- 刷新量的灵活限制，你可以快速是刷新（只刷新变化的），也可以全刷新。
1.10. 索引
对于查询中需要作为查询条件的字段，可以考虑建立索引。最终根据性能的需要决定是否建立索引。
对于复合索引，索引字段顺序比较关键，把查询频率比较高的字段排在索引组合的最前面。
为了提高性能，建议对外键字段建立索引。
1.11. 数值
-
标志字段，如性别，建议用 char(1) 类型，取值如 ‘M’ ‘F’ 等，比整数值更能表意。
如非确实必要，避免一个字段中存储多个标志的做法。如
11101数值中每个数字分别代表 5 个标志位的取值。
这对数据库查询来说往往是增加复杂度，降低性能的地方。一些简单的标志字段建议使用
char(1)类型，取值如 ‘Y’ 和 ‘N’。 -
NULL 值
数据库中不建议出现可为空 NULL 字段。应该在 SQL 建表脚本中明确指明缺省值。
由于 NULL 值参加任何运算结果均为 NULL，所以在应用程序中必须利用 coalesce() 函数
把可能为 NULL 值的字段或变量转换为非 NULL 的默认值。例如：coalesce(item_num, 0)。
2. 命名规范
2.1. 命名原则
所有数据库对象遵循如下命名原则。
| 规则项 | 说明 |
|---|---|
| 字符 | 所有对象名采用 26 个英文字母（区分大小写）和 0-9 这十个自然数，加上下划线 _ 组成，共 63 种字符，不能出现其他字符（注释除外）。 |
| 大小写 | 名称统一小写（或统一大写）且不能加引号，以方便不同数据库移植，以及避免 SQL 编写时强制区分大小写。 |
| 单复数 | 英文单词使用单数形式。 |
| 保留字 | 命名不要以 pg 开头，不允许使用数据库或 SQL 的保留字、关键字，不要与数据库内置对象重名。 |
| 命名 | 命名推荐使用英文单词，可以使用拼音首字母组合。命名不要以数字开头，但可以数字结尾。 |
| 单词组合 | 当一个单词不能表达对象含义时，使用单词组合，单词之间使用下划线连接。除了前缀，组成单词一般控制在 3 个单词之内。 |
| 命名长度 | 表名、字段名、函数名、视图名、序列名长度应限制在 30 个字符内(含前缀)。变量名的长度限制为 29 （不包括标识字符 @）。命名过长时，单词采用简写或缩写，缩写要基本能表达原单词的意义。 |
| 语义唯一 | 使用同一个名称命名相同含义的对象，避免一个语义出现多个英语单词。比如 telephone 在一个表中代表“电话号码”的意思，在另外一个表中就不能代表“手机号码”的意思。 |
命名首选完整的单词，如果“表名+字段名”长度超过 60 个字符时，则从最后一个单词开始，依次向前采用该单词的缩写。
缩写约定为 3-4 个字符，与完整单词保持唯一对应。
名字缩写可参考如下规则：
- APPLICATION = APPL (4)
- APPLICATION_FUNCTION = APFU (2:2)
- APPLICATION_FUNCTION_ROLE = APFR (2:1:1)
- APPLICATION_FUNCTION_ROLE_BANANA = AFRB (1:1:1:1)
中文词汇的缩写采用相应文字拼音的首字母缩写，如：
- 保护目标 = BaoHu MuBiao = BHMB
- 积水深度 = JiShui ShenDu DanWei = JSSDDW
还有一些常见词汇的缩写示例如下：
| 单词 | 缩写 | 含义 |
|---|---|---|
| information | info | 信息 |
2.2. 数据库和 Schema
数据库命名格式：<应用系统标识>_db 或者 <应用系统标识>
范例：
- erp
- erp_db
- erp_hr_db
数据库名不超过 20 个字符。
Schema 命名分几种情况。
单实例多 Schema，如：
- 数据库实例名：erp
- Schema 名：erp_hr，erp_audit，…
多实例单 Schema，如：
- 数据库实例名：erp_hr，erp_audit，…
- Schema 名：public
2.3. 数据库连接
连接命名格式：conn_<应用系统标识>。
2.4. 表空间
表空间的名称不能以 ‘pg_’ 开头，它们是系统表空间的保留名称。
表空间命名格式：tbs_<应用系统标识>
范例：
- tbs_erp
- tbs_erp_attachment
表空间对应的操作系统目录名为表空间名，建议放在 /data/pg_data/* 目录下，如：
| 表空间名 | 用途 | 操作系统路径 |
|---|---|---|
| tbs_erp | 业务数据 | /data/pg_data/tbs_erp |
| tbs_erp_attachment | 非关键的附件数据 | /data/pg_data/tbs_erp_attachment （不同卷或分区） |
2.5. Schema
Schema 命名格式：<应用系统标识>_<业务标识>
范例：
- erp_hr
- erp_audit
- erp_common（用于归集共用表）
2.6. 表和字段
表名和字段名遵循命名基本原则，参见上文。
2.6.1. 表
可以根据需要对表名添加统一前缀区分。
| 前缀/后缀 | 含义 |
|---|---|
| 无 | 业务表 |
r_* | 关联表 |
d_* | 字典表 |
tmp_* | 临时表 |
在没有使用 Schema 的情况下，可以根据系统业务划分，在表名中加上适当小节，如：
- b_hr_employee
- b_hr_training
- b_audit_plan
2.6.2. 字段
主键字段采用 id 或 code（主要用于字典表）结尾，如：
- id
- user_id
- district_code
引用字典表的外键字段名应该以 code 结尾。
引用业务表的外键字段采用引用表的表名缩写加相关字段名（一般为 id），命名格式：
<引用表名/缩写>_id<引用表名/缩写>_<含义>_id（来源于同一主表的多个外键）
2.7. 分区表
分区表命名格式：<主表名>_<分区逻辑>
范例：
按年分区的主表如果为 b_post, 则子表为 b_post_2021，b_post_2022。
2.8. 视图和视图字段
视图命名规则与表命名规则基本一致，需要额外注意以下约定。
视图命名格式：v_<视图标识>
- 视图也可以定义 3-4 个字符长度的缩写，以便需要的时候使用。
- 视图字段名，直接从表获取的字段保留原名，其它字段可以
<视图名缩写>_<标识名>命名，如果含义明确<视图名缩写>可省略。
2.9. 主键约束
命名格式：pk_<表名>
范例：
- pk_employee
2.10. 外键约束
命名格式：fk_<表名>_<引用表名>
范例：
- fk_salary_employee
如果有来源于同一主表的多个外键，则
命名格式：fk_<表名>_<引用表名>_<字段名>
2.11. 唯一性约束（Constraint）
命名格式：uk_<表名缩写>_<uk标识>
范例：uk_employee_email
<表名缩写>可以根据实际情况决定是否保留，但名字应直观可读。
2.12. NOT NULL 约束（Constraint）
命名格式：数据库自动生成非空约束名。
2.13. Check 约束（Constraint）
命名格式：数据库自动生成检查约束名。
2.14. 索引
命名格式：idx_<表名缩写>_<字段名>
范例：idx_employee_email
多字段索引：idx_<表名缩写>_<含义>
其中<含义>可由多字段组合而成,即为 idx_<表名缩写>_<Col1>_<Col2>_…，
<Col1>是数据库表中（第一个）索引字段的名称或名称简写；
<Col2>是数据库表中（第二个）索引字段的名称或名称简写；
索引名的总长必需符合数据库的规定。
2.15. 序列
序列命名规则：seq_<表名>
范例：seq_employee
2.16. 函数
按业务逻辑命名函数，下划线连接单词，长度应符合数据库命名的总体规则。
范例：most_productive_employee
3. 开发规范
3.1. 德哥的 PostgreSQL 数据库开发规范
推荐参考
3.2. 补充约定
SQL 代码编写遵循如下约定。
-
字符类型
数据 SQL 中的字符类型数据应该统一使用单引号。
特别对纯数字的字串，必须用单引号，否则会导致内部转换而引起性能问题或索引失效问题。
可以利用trim()，lower()等函数格式化匹配条件。 -
复杂 SQL
对于非常复杂的 SQL（特别是有多层嵌套，带子句或相关查询的），应该先考虑是否设计不当引起的。
-
高效性
条件查询时，将高过滤的属性字段放在条件的左边。
避免In子句：使用 In 或 Not In 子句时，特别是当子句中有多个值时，且查询数据表数据较多时，速度会明显下降，应可采用连接查询或外连接查询来提高性能。
避免嵌套的select子句：这个实际上是In子句的特例。
避免使用select *语句：如果不是必要取出所有数据，不要用 * 来代替，应给出字段列表。
避免不必要的排序：不必要的数据排序大大降低系统性能。
避免使用not null条件：Not NULL 条件会使查询索引失效。 -
健壮性
使用 Insert 语句一定要给出插入值的字段列表，这样即使更改了表结构加了字段也不会影响现有系统的运行。
-
安全性
Where条件：无论在使用 Select 还是在使用破坏力极大的 Update 和 Delete 语句时，一定要检查 Where 条件判断的完整性，不要在运行时出现数据的重大丢失。
如果不确定，最好先用 Select 语句带上相同条件来查一下结果集，以此来检验查询条件是否正确。
4. 数据类型使用约定
| 数据类型 | 说明 |
|---|---|
| 布尔型 | PostgreSQL 支持 SQL 标准的 boolean 数据类型。 |
| 字符型 | 固定长度的字串类型采用 char(n)，长度不固定的字串类型采用 varchar(n)。避免在长度不固定的情况下采用 char 类型。1GB 以上的字符型数据使用 text。 |
| 数字型 | 数字型字段尽量采用长度为 4 字节的 int 类型，特殊情况下考虑使用 8 字节的 bigint。浮点数使用 double。不建议使用 decimal 类型。 |
| 日期和时间 | 业务逻辑产生的时间：首选数据库的日期型，如 timestamp, date 类型。外部时间：由数据导入或外部应用程序产生的日期时间类型采用 varchar 类型，数据格式采用：YYYYMMDDHH24MISS。 |
| UUID | 可以用作业务表主键，字典表中尽量不使用 UUID 作为主键。特别不建议使用 UUID 的字符串形式作为主键。 |
| BLOB, CLOB | PostgreSQL 处理 LOB 数据有些特殊，与 JPA 的逻辑有差异，建议避免使用 blob 类型，而应使用 bytea；PostgreSQL 中没有 CLOB， NCLOB 类型。 |
| json, jsonb | 用于存储 json 类型的数据。 |
| 几何类型 | 使用 PostGIS 为表添加名为 shape 的几何字段。 |
PostgreSQL 中没有 CLOB，NCLOB 等类型, 对应有 TEXT 等。
更多数据类型信息参考：https://www.runoob.com/postgresql/postgresql-data-type.html
5. 参考资料
- PostgreSQL 数据库开发规范：https://developer.aliyun.com/article/60899
- PostgreSQL 的表空间：https://developer.aliyun.com/article/661642
- 字符集和字符编码：https://www.runoob.com/w3cnote/charset-encoding.html
- PostgreSQL 数据类型：https://www.runoob.com/postgresql/postgresql-data-type.html
- PostgreSQL 关于CLOB, BLOB及JSON类型的处理：https://www.cnblogs.com/wggj/p/7809832.html
- Geometry - PostgreSQL ＆ PostGIS 的使用：https://www.cnblogs.com/oddcat/articles/10722065.html
- 为什么说数据库不应该使用外键：https://zhuanlan.zhihu.com/p/252840511
- 数据库是否应该使用外键约束：https://zhuanlan.zhihu.com/p/447922429
- https://github.com/dongxuyang1985/postgresql_dev_guide
扫一扫
769
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
