pg使用规范

本规范旨在帮助开发人员逐步建立合理使用数据库的意识，对数据库相关的资源申请、业务规范使用等提供规范性的指导，从而为公司业务系统稳定、健康地运行提供保障。

以下所有规范会按照【强制】、【建议】两个级别进行标注，对于【强制】级别的设计需强制修改调整。

---

[TOC]

基础规范

• 【强制】禁止开通公网地址，禁止部署在公网可访问的服务器上
• 【强制】新实例强制版本 PostgreSQL 15.3
• 【强制】新实例强制端口 1921，默认5432

命名规范

对象命名规范

所有对象命名应该遵循下述原则：

• 【强制】凡是需要命名的对象，其标识符不能超过30个字符；
• 【强制】名称必须以英文字母开头，不得以 _(下划线) 作为起始和终止字母；
• 【强制】所有名称的字符范围为：a-z, 0-9 和_(下划线)，禁用大写、特殊符号、保留字、汉字和空格；

数据库命名规范

• 【强制】数据库名称统一使用小写，采用下划线_分割，禁止以小数点.分割
• 【强制】数据库以 xxx_ 开头，再加业务系统代号，再加上具体业务名称，比如支付网关：xxx_pay_gateway；

注：xxx 可以是公司的英文标识，请根据实际修改。

建库示例

先创建账号，再创建数据库

CREATE USER user_pay_gateway WITH PASSWORD '***';
CREATE DATABASE xxx_pay_gateway WITH OWNER=user_pay_gateway ENCODING='UTF-8';

示例解析

• 【强制】字符集设置 UTF-8
• 【建议】创建数据库的同时绑定账号

设计规范

• 多表中的相同列，必须保证列名一致，数据类型一致
• btree索引字段不建议超过2000字节，如果有超过2000字节的字段需要建索引，建议使用函数索引（例如哈希值索引），或者使用分词索引。
• 禁止使用外键
• 对于频繁更新的表，建议建表时指定表的fillfactor=85，每页预留15%的空间给HOT更新使用。

create table test123(id int, info text) with(fillfactor=85);

• 对于固定条件的查询，可以使用部分索引，减少索引的大小，同时提升查询效率。

select * from tbl where id=1 and name=?; -- 其中id=1为固定的条件  
create index idx_name_id on tbl (name) where id=1;

• 不要使用count(列名)或count(常量)来替代count()，count()就是SQL92定义的标准统计行数的语法，跟数据库无关，跟NULL和非NULL无关。

  说明：count(*)会统计NULL值（真实行数），而count(列名)不会统计。

• count(distinct col) 计算该列的非NULL不重复数量，NULL不被计数。
• NULL是UNKNOWN的意思，也就是不知道是什么。 因此NULL与任意值的逻辑判断都返回NULL
• DDL操作必须设置锁等待，可以防止堵塞所有其他与该DDL锁对象相关的QUERY。

begin;
set local lock_timeout = '10s';
-- DDL query;
end;

• 创建索引时加CONCURRENTLY关键字，可以并行创建，且不会堵塞DML操作，否则会堵塞DML操作。

create index CONCURRENTLY idx_id on tbl(id);

• 如何加快创建索引的速度，调大maintenance_work_mem，可以提升创建索引的速度，但是需要考虑实际的可用内存。

begin;
set local maintenance_work_mem='2GB';
create index idx_id on tbl(id);
end;

• 如果有大批量的数据入库，建议使用copy语法，或者 insert into table values (),(),...(); 的方式。 提高写入速度。
• 应用程序一定要开启autocommit，同时避免应用程序自动begin事务
• 高并发的应用场合，务必使用绑定变量(prepared statement)，防止数据库硬解析消耗过多的CPU资源。
• 不要使用hash index，目前hash index不写REDO，在备库只有结构，没有数据，并且数据库crash后无法恢复。同时不建议使用unlogged table ，道理同上，但是如果你的数据不需要持久化，则可以考虑使用unlogged table来提升数据的写入和修改性能。
• 秒杀场景，一定要使用 advisory_lock先对记录的唯一ID进行锁定，拿到AD锁再去对数据进行更新操作。 拿不到锁时，可以尝试重试拿锁。
• 在函数或程序中，不要使用count(*)判断是否有数据，很慢。 建议的方法是limit 1;

select 1 from tbl where xxx limit 1;
if found -- 存在
else  -- 不存在

• 对于高并发的应用场景，务必使用程序的连接池，否则性能会很低下。如果程序没有连接池，建议在应用层和数据库之间架设连接池，例如使用pgbouncer或者pgpool-II作为连接池。
• 可以预估SQL执行时间的操作，建议设置语句级别的超时，可以防止雪崩，也可以防止长时间持锁。例如设置事务中执行的每条SQL超时时间为10秒

begin;
set local statement_timeout = '10s';
-- query;
end;

安全方面

创建账号

创建业务账号

CREATE USER user_pay_gateway WITH PASSWORD '***';
GRANT ALL PRIVILEGES ON DATABASE xxx_pay_gateway TO user_pay_gateway;
\c xxx_pay_gateway
GRANT ALL PRIVILEGES ON all tables in schema public TO user_pay_gateway;

创建只读账号

\c xxx_pay_gateway
GRANT SELECT ON TABLE mytable TO user_pay_gateway;

云数据库使用建议

• 冷热分离

  当数据库非常庞大（例如超过2TB）时，建议使用阿里云PGSQL的OSS_EXT外部表插件，将冷数据存入OSS。通过建立OSS外部表，实现对OSS数据的透明访问。

• 对RT要求高的业务，请使用SLB链路 或 PROXY透传模式连接数据库
• 链路选择
• UDF & RT
• 为RDS设置合适的白名单，加固数据访问的安全性
• 尽量避免数据库被公网访问，如果真的要访问，一定要设置白名单
• RDS的地域选择与应用保持一致

  比如应用环境在上海，数据库选择上海region，避免应用和数据库出现跨区域访问

• 可用区的选择，尽量不要把所有节点放到同一个区
• 为RDS报警设置多位接收人，并设置合适的报警阀值