PostgreSQL开发规范

• 0x00 背景
• 0x01 命名规范
• 0x02 设计规范
• 0x03 索引规范
• 0x04 查询规范
• 0x05 管理规范

0x00 背景

没有规则，就没有方块。

PostgreSQL的功能很强大，但要放在PostgreSQL使用好，需要后端，运维，DBA合作。

本文针对PostgreSQL根据数据库的原理和特点，编制了开发规范，希望减少使用人数。PostgreSQL在数据库过程中遇到的混乱。

0x01 命名规范

无名，万物之始，有名，万物之母。

【强制】 通用命名规则

• 此规则适用于所有对象名称，包括：库名、表名、表名、列名、函数名、视图名、序列号名、别名等。
• 对象名称只能使用小写字母、下划线和数字，但首字母必须为小写字母。常规表禁止_打头。
• 对象名称的长度不超过63字符，统一命名。snake_case。
• 禁止使用SQL保留字，使用select pg_get_keywords(); 获取保留关键字的列表。
• 禁止使用美元符号，禁止使用中文符号，并且不要使用它们。pg开头。
• 提高文字品位，做人要老实、讲究。不使用拼音，不使用生僻冷冰冰的词语，不使用少数民族缩略语。

【强制】 库命名规则

• 图书馆名称最好与应用或服务一致，并且必须是具有高度差异化的英文单词。
• 命名必须为pgdb_从一个具体的行号开始，如果是一个子库，就必须是。_shard结尾。
• 使用多个部件_连接。例如：_chat_shard，_payment等等，总共不超过三段。

【强制】 角色命名规范

• 数据库su只有一个：postgres用于流复制的用户名为。replication。
• 名为Use的生产用户_作为前缀的特定函数被用作后缀。
• 默认情况下，所有数据库都有三个基本角色： _read，_write，_usage分别对所有表具有只读、只写、函数执行权限。
• 生产用户、ETL用户，单个用户通过继承相应的底层角色来获得权限。
• 更精细的权限控制使用单独的角色和用户，具体取决于业务。

【强制】 模式命名规则

• 统一使用业务< >作为图案名称，< >必须设置为企业定义的名称search_path第一要素。
• dba，monitor，trash是保留的模式名称。
• 使用切分图案命名规则：rel_<partition_total_num>_<partition_index>。
• 不应在其他模式下创建对象没有特殊原因。

【推荐】 关系命名规则

• 关系命名以明确表达为第一要义，不使用含糊的缩略语，也不能太长，遵循共同的命名规则。
• 表名应使用复数名词，符合历史惯例，但应尽量避免使用不规则复数形式的词语。
• 视图以v_作为命名前缀，实例化视图使用mv_使用临时表作为命名前缀。tmp_作为命名前缀。
• 继承或分区的表应该以父表名称和子表特征(规则、分片范围等)作为前缀。作为后缀。

【推荐】 索引命名规则

• 创建索引时，如果有条件，则应指定索引名。PostgreSQL默认命名规则是一致的，以避免在重复执行时创建重复索引。
• 用于主键的索引_pkey结束，唯一索引_key结束，EXCLUDED约束的索引_excl结束时，正常索引为_idx结尾。

【推荐】 函数命名规则

• 以select,insert,delete,update,upsert从操作的类型开始。
• 可以获得重要的参数_by_ids, _by_user_ids后缀反映在函数名中。
• 避免函数重载。尽量只保留一个同名的函数。
• 禁止通过BIGINT/INTEGER/SMALLINT如果整数类型是重载的，则在调用时可能会出现歧义。

【推荐】 字段命名规则

• 请勿使用系统列保留字段名称：oid, xmin, xmax,cmin, cmax, ctid等。
• 主键列通常命名为id，或以id作为后缀。
• 创建时间通常命名为created_time，修改时间通常命名为updated_time
• 建议使用布尔型字段。is_，has_以此类推，作为前缀。
• 其余的字段名称需要与现有的表命名约定保持一致。

【推荐】 变量命名规则

• 存储过程和函数中的变量使用命名参数，而不是位置参数。
• 如果参数名称与对象名称冲突，请将其添加到参数之后。_，例如user_id_。

【推荐】 注释规范

• 尝试为对象提供注释(COMMENT)，注释为英文，简明全面，一种行为得体。
• 当对象的模式或内容语义发生变化时，一定要一起更新注释，以跟上实际情况。

0x02 设计规范

Suum cuique

【强制】 字符编码必须为UTF8

• 禁止任何其他字符编码。

【强制】 容量规划

• 一个表记录的记录超过1亿条，甚至更多10GB在规模较大的情况下，考虑启动一个子表。
• 单桌容量超过1T，单个存储库容量超过2T。需要考虑分片。

【强制】 不要滥用存储过程

• 存储过程适合于封装事务、减少并发冲突、减少网络往返、减少返回的数据量以及执行少量的定制逻辑。
• 存储过程不适合复杂的计算和琐碎的计算/频繁的类型转换和包装。

【强制】 存储计算分离

• 从数据库中删除不必要的计算密集型逻辑，例如在数据库中使用它。SQL进行WGS84转换为其他坐标系。
• 例外：与数据采集和过滤密切相关的计算逻辑允许数据库内，如。PostGIS中的几何关系判断。

【强制】 主键和标识列

• 每个表必须有一个标识列，原则上必须有一个主键，最低要求是有一个非空的唯一约束。
• 标识列用于唯一标识表中的任何元组，逻辑复制和许多三向工具依赖于此。

【强制】 外键

• 不建议使用外键，建议在应用层解决。使用外键时，引用必须设置适当的操作：SET NULL, SET DEFAULT, CASCADE，在级联操作中要谨慎。

【强制】 慎用宽表

• 字段数超过15表被认为是宽表，应该考虑将宽表用于垂直拆分，具有相同的主键和相互引用的主表。
• 因为MVCC机制方面，宽表写入放大现象比较明显，尽量减少了宽表的频繁更新。

【强制】 配置适当的默认值

• 必须添加具有缺省值的列DEFAULT子句指定缺省值。
• 您可以使用缺省值中的函数来动态生成缺省值(如主键颁发者)。

【强制】 对空值的合理响应

• 如果字段中的零值和空值在语义上没有区别，则不允许空值，必须为列配置空值。NOT NULL约束。

【强制】 唯一约束由数据库强制执行。

• 唯一约束必须由数据库保证，并且任何唯一列都必须具有唯一约束。
• EXCLUDE约束是唯一可用于在低频率更新方案中确保数据完整性的通用约束。

【强制】 请注意整数溢出风险

• 注意SQL该标准不提供无符号整数，超过INTMAX但没超过UINTMAX的价值需要升级为存储。
• 不要储存更多INT64MAX的值到BIGINT列中，溢出为负数。

【强制】 统一时区

• 使用TIMESTAMP存储时间，使用utc时区。
• 统一使用ISO-8601格式化输入输出时间类型：2006-01-02 15:04:05，避免DMY与MDY问题。
• 使用TIMESTAMPTZ时，采用GMT/UTC时间，0时区标准时间。

【强制】 及时清理过时的功能

• 如果不再使用，更换的功能应及时离线，避免与未来的功能发生冲突。

【推荐】 主键类型

• 主键通常为整数，建议使用。BIGINT，不允许使用更多64字节字符串。
• 主键允许使用Serial自动生成，推荐。Default next_id()发送者功能。

【推荐】 选择适当的类型

• 可以使用专有类型，而不是字符串。(值、枚举、网络地址、货币、JSON，UUID等）
• 使用正确的数据类型可以显著提高数据存储、查询、索引、计算的效率，并提高可维护性。

【推荐】 使用枚举类型

• 具有较小值空间(十几个以内)的更稳定的字段应该使用枚举类型，而不是整型和字符串表示。
• 使用枚举类型具有性能、存储和可维护性优势。

【推荐】 选择适当的文本类型

• PostgreSQL文本的类型包括 char(n), varchar(n), text。
• 通常建议使用varchar或text，带有(n)修饰符的类型检查字符串的长度，导致较小的额外开销，当字符串的长度有限制时，应该使用该额外开销。varchar(n)，避免插入过长的脏数据。
• 避免使用char(n)，为了与SQL此类型与标准兼容，具有不直观的行为(填充空格和截断)，并且没有存储和性能优势。

【推荐】 选择适当的数字类型

• 使用常规数字字段。INTEGER。主键、容量值列使用不准确。BIGINT。
• 不要无缘无故地使用它。SMALLINT，性能和存储提升不大，还会有很多额外的问题。
• REAL表示4字节浮点，FLOAT表示8字节浮点数
• 浮点数只能在结束精度不是很高的场景中使用，例如地理坐标。不要对浮点数使用等价判断。
• 使用精确的数字类型。NUMERIC，请注意精度和刻度设置。
• 货币数字类型使用MONEY。

【推荐】 创建具有统一功能的语法。

• 签名占一行(函数名和参数)，返回值从一行开始，语言是第一个标签。
• 请务必标记功能可变性级别：IMMUTABLE, STABLE, VOLATILE。

• 添加确定的属性标签，例如：RETURNS NULL ON NULL INPUT,PARALLEL SAFE,ROWS 1，请注意版本兼容性。

  CREATE OR REPLACE FUNCTION nspname.myfunc(arg1 TEXT, arg2 INTEGER) RETURNS VOID LANGUAGE SQL STABLE PARALLEL SAFE ROWS 1 RETURNS NULL ON NULL INPUT AS $function$ SELECT 1; $function$;

【推荐】 为可进化性而设计

• 在设计表格时，应充分考虑未来的扩展需求，可以在构建表格时适当添加这些需求。1~3保留字段。
• 对于变量，可以使用非关键字字段。JSON类型。

【推荐】 选择合理的正规化水平。

• 允许您适当降低标准化级别并减少多表连接以提高性能。

【推荐】 使用新版本

• 新版本具有高性价比的性能改进、更高的稳定性和更多新功能。
• 利用新功能降低设计复杂性。

【推荐】 谨慎使用触发器

• 触发器增加了系统的复杂性和维护成本，不鼓励使用。

0x03 索引规范

Wer Ordnung hält, ist nur zu faul zum Suchen.

【强制】 在线查询必须有配套索引。

• 所有在线查询都必须为其访问模式编制索引，除了非常小的表之外，不允许进行全表扫描。
• 索引是有代价的，并且不允许创建未使用的索引。

【强制】 禁止为大型字段编制索引。

• 索引字段的大小不能超过2KB（1/3页面容量)，原则上禁止超过64个字符。
• 如果需要较大的字段索引，请考虑对较大的字段进行散列并构建功能指数。或使用其他类型的索引(GIN）。

【强制】 清除空值排序规则

• 如果对空列有排序要求，则需要在查询和索引中显式指定。NULLS FIRST还是NULLS LAST。
• 注意，DESC排序的默认规则是NULLS FIRST也就是说，空值出现在排序的前面，这通常不是所需的行为。
• 索引的排序标准必须与查询匹配，例如：create index on tbl (id desc nulls last);

【强制】 利用GiST索引应该能够处理邻居查询问题。

• 传统B树索引不能提供对KNN很好的支持问题，应该使用GiST索引。

【推荐】 使用函数索引

• 可以从同一行中的其他字段推断出的任何冗余字段都可以用函数索引来替换。
• 对于经常使用表达式作为查询条件的语句，可以使用表达式或函数索引来加快查询速度。
• 典型场景：在较大的字段上建立哈希函数索引，以创建需要左模糊查询的文本列。reverse功能指数。

【推荐】 使用部分索引

• 对于查询条件固定的部分，可以使用部分索引来减小索引大小，提高查询效率。
• 如果要在查询中为某个字段编制索引的值的数量有限，则还可以建立几个相应的部分索引。

【推荐】 使用范围索引

• 对于其值与堆表的存储顺序线性相关的数据，如果通常的查询是范围查询，则建议使用。BRIN索引。
• 最典型的情况是仅附加写入的定时数据，BRIN索引的效率更高。

【推荐】 注重联合指数的差异化。

• 分化程度较高的柱子放在前面。

0x04 查询规范

The limits of my language mean the limits of my world.
—Ludwig Wittgenstein

【强制】 读写分离

• 原则上，写请求发往主库，读请求发往库。
• 异常：您需要读取自己的写一致性保证，并且检测到显著的复制延迟。

【强制】 快慢分离

• 生产中1毫秒内的查询称为快速查询，更多的是1第二个查询称为慢查询。
• 慢查询必须从库中脱机，并且必须设置相应的超时。
• 生产中的在线普通查询执行时长原则上应控制在1ms内。
• 生产中的在线正常查询执行需要更长的时间10ms上线前有必要对技术方案进行修改，对标准进行优化。
• 应配置在线查询10ms数量级或更快的超时，以避免累积导致雪崩。
• Master与Slave不允许字符大批量拉取数据，计算仓库ETL程序应源自Offline正在从库拉获取数据

【强制】 主动超时

• 为所有语句配置活动超时，超时后主动取消请求，避免雪崩。
• 定期执行的语句的配置超时时间必须小于执行周期。

【强制】 关注复制延迟

• 应用程序必须知道主从之间的同步延迟，并正确处理复制延迟超出合理限制的情况。
• 平时在0.1ms延误，在极端情况下可达十多分钟甚至几个小时的量级。应用程序可以选择从主库中读取、稍后重复读取或报告错误。

【强制】 使用连接池

• 应用程序必须通过连接池CONNECT访问数据库。6432端口的pgbouncer而不是5432的postgres。
• 请注意使用连接池和直接连接数据库之间的区别，某些功能可能不可用(例如Notify/Listen)，也可能存在连接污染问题。

【强制】 不修改连接状态

• 使用公共连接池时不修改连接状态，包括修改连接参数，修改搜索路径，更换角色，更换数据库。
• 作为最后手段，必须在修改后完全销毁连接。将状态改变后的连接放回连接池，会导致污染扩散。

【强制】 重试失败的事务

• 查询可能会由于并发争用、管理员命令等而终止。应用程序需要意识到这一点，并在必要时重试。
• 当报告大量数据库错误时，应用程序可以触发断路器熔断器以避免雪崩。但要注意区分错误的类型和性质。

【强制】 掉线重连

• 连接可能由于各种原因被挂起，并且应用程序必须具有丢弃重新连接机制。
• 可以使用SELECT 1作为心跳包查询，检测连接的消息并定期保持其活动状态。

【强制】 在线服务申请代码禁止执行DDL

• 不要在应用程序代码中制造大新闻。

【强制】 明确指定列名

• 避免使用SELECT ，或在RETURNING用在从句中 。请使用特定的字段列表，并且不要返回不可用的字段。当表结构更改时(例如，新的值列)，使用列通配符的查询可能在列数上不匹配。
• 例外：当存储过程返回特定的表行类型时，允许使用通配符。

【强制】 禁止在线查询全表扫描

• 例外：恒定极小表、极低频率操作、表/返回的结果集很小(100条记录)。/百KB内）。
• 使用第一层过滤条件等。!=, <>负运算符导致全表扫描，必须避免。

【强制】 禁止在事务中长时间等待

• 事务必须在打开后尽快提交或回滚，超过10分钟的IDEL IN Transaction将被迫杀戮。
• 应用程序应该会打开AutoCommit，避免BEGIN在那之后，就没有配对了ROLLBACK或COMMIT。
• 尝试使用标准库提供的事务基础结构，并且在使用之前不要手动控制事务。

【强制】 必须在使用游标后及时关闭

【强制】 科学计数

• count(*)是计算行数的标准语法，与空值无关。
• count(col)统计的是col列中的非空记录数。在本专栏中NULL值不计算在内。
• count(distinct col) 对col列除权重计数时，也忽略空值，即只有非空值的个数不同。
• count((col1, col2))对于多列，即使所有要统计的列都为空，(NULL,NULL)有效。
• a(distinct (col1, col2))即使所有要计数的列都是空的，也要分割多列并对其计数，(NULL,NULL)有效。

【强制】 请注意聚合函数的空值问题。

• 除了count所有其他聚合函数都会忽略空输入，因此当所有输入值都为空时，结果为NULL。但count(col)在这种情况下，将返回0，是一个例外。
• 如果聚合函数返回NULL并且不是所需的结果，请使用coalesce若要设置默认值，请执行以下操作。

[FORMAND]谨慎处理空值

• 对零值和空值进行了明确的区分，并使用空值。IS NULL等价判断，零值采用常规。=操作员可以做出等价的判断。
• 空值应将类型修饰符作为函数输入参数，否则将无法确定将哪个类型修饰符用于重载函数。
• 请注意空比较的逻辑：任何涉及空比较结果的操作都是unknown，需要注意unknown参与布尔运算的逻辑：
  and：TRUE or UNKNOWN会因为逻辑上的短路而返回。TRUE。
  or：FALSE and UNKNOWN会因为逻辑上的短路而返回。FALSE
  在其他情况下，只要操作对象出现。UNKNOWN，结果是UNKNOWN
• 空值和任意值的逻辑判断，例如，结果是空值。NULL=NULL返回结果为NULL而不是TRUE/FALSE。
• 使用涉及空值和非空值的等价比较。``IS DISTINCT FROM 进行比对，确保比对结果不为空。
• 空值和聚合函数：当所有输入值均为NULL时，返回结果为NULL。

【强制】 请注意，序列号是空的。

• 当使用Serial类型时，INSERT，UPSERT这样的操作使用序列号，该序列号在事务失败时不会回滚。
• 当使用INTEGER作为主键时，在表中频繁出现插入冲突时，需要注意整数溢出问题。

【推荐】 重复查询使用准备语句

• 重复查询应使用准备语句(Prepared Statement)，消除了数据库硬解析。CPU开销。
• 准备语句修改连接状态。请注意连接池对准备语句的影响。

【推荐】 选择适当的事务隔离级别

• 默认隔离级别为已提交读，这适用于大多数简单的读写事务。普通事务选择满足要求的最低隔离级别。
• 对于需要事务级别一致性快照的写入事务，请使用重新读取隔离级别。
• 对于对正确性有严格要求的写事务，请使用可序列化隔离级别。
• 在RR与SR当隔离级别出现并发冲突时，应根据错误类型主动重试。

【推荐】 确定结果是否存在，并且不要使用它们。count

• 使用SELECT 1 FROM tbl WHERE xxx LIMIT 1判断是否有符合条件的栏目比较好Count快。
• 可以使用select exists(select * FROM app.sjqq where xxx limit 1)将存在结果转换为布尔值。

【推荐】 使用RETURNING子句

• 如果用户需要在插入、删除数据后立即获取插入、删除或修改的数据，建议使用。RETURNING子句来减少数据库交互的数量。

【推荐】 使用UPSERT简化逻辑

• 当业务看起来被插入时。-失败-在更新操作顺序时，请考虑使用UPSERT替代。

【推荐】 使用咨询锁来处理热点并发。

• 对于单行记录的非常频繁的并发写入(秒杀)，应该对记录使用建议锁。ID锁定。
• 如果高并发争用可以在应用程序级别得到解决，则不要将其置于数据库级别。

[推荐]优化IN操作符

• 使用EXISTS子句代替IN接线员，效果更好。
• 使用=ANY(ARRAY[1,2,3,4])代替IN (1,2,3,4)，效果更好。

【推荐】 不推荐使用左侧模糊搜索。

• 左模糊搜索WHERE col LIKE %xxx不能被充分利用B树索引，如果需要，可用reverse表达式功能指数。

【推荐】 使用数组而不是临时表

• 考虑使用数组而不是临时表，例如获取一个系列ID当相应的记录。=ANY(ARRAY[1,2,3])而不是临时表JOIN好

0x05 管理规范

【强制】 关注备份

• 每日完整备份，连续归档段文件

【强制】 关注年龄

• 注意数据库和表的时代，避免事情发生。ID回卷。

【强制】 关注老龄化和扩张

• 注意表和索引的扩容速度，避免性能下降。

【强制】 关注复制延迟

• 监控复制延迟，使用复制槽时一定要多加注意。

【强制】 遵循最低权限原则

强制：并发创建和删除索引。

• 对于生产表，您必须使用CREATE INDEX CONCURRENTLY并发创建索引。

【强制】 新的从属图书馆数据预热

• 使用pg_prewarm，或逐渐接入流量。

【强制】 谨慎的模式转变

• 添加新列时，必须使用不带缺省值的语法，以避免全表覆盖。
• 更改类型时，如有必要，应重新生成依赖于该类型的所有函数。

【推荐】 分条大批量作业

• 应将大容量写入操作分成小批处理，以避免一次生成大量写入操作。WAL。

【推荐】 加速数据加载

• 关闭autovacuum，使用COPY加载数据。
• 在事后建立约束和指标。
• 调大maintenance_work_mem，增大max_wal_size。
• 完成后执行vacuum verbose analyze table。