微信公众号原文

[TOC]

0x00背景

没有规矩,不成方圆。

PostgreSQL的功能非常强大,但是要把PostgreSQL用好,需要后端、运维、DBA的协力配合。

本文针对PostgreSQL数据库原理与特性,整理了一份开发/运维规约,希望可以减少大家在使用PostgreSQL数据库过程中遇到的困惑:你好我也好,大家都好。

本文第一版主要针对 PostgreSQL 9.4 - PostgreSQL 10 版本 ,当前最新版本针对 PostgreSQL 15/16 进行更新与调整。

0x01 命名规范

计算机科学只存在两个难题:缓存失效和命名

通用命名规则(Generic)

  • 本规则适用于所有数据库内对象,包括:库名、表名、索引名、列名、函数名、视图名、序列号名、别名等。
  • 对象名务必只使用小写字母,下划线,数字,其中首字母必须为小写字母。
  • 对象名长度不得超过63个字符,命名统一采用 snake_case 风格。
  • 禁止使用SQL保留字,使用select pg_get_keywords(); 获取保留关键字列表。
  • 禁止出现美元符号 $ ,禁止使用中文,不要以 pg 开头。
  • 提高用词品味,做到信达雅;不要使用拼音,不要使用生僻冷词,不要使用小众缩写。

集群命名规则 (Cluster)

  • PostgreSQL 集群的命名将作为集群资源的命名空间,必须为有效的 DNS 域名,不包含任何点号与下划线。
  • 集群名应当由小写字母开头,仅包含小写字母、数字、减号,符合正则表达式:[a-z][a-z0-9-]*
  • PostgreSQL 数据库集群命名通常以三段式结构:pg-<biz>-<tld>。数据库类型 / 业务名称 / 业务线或环境
  • biz 为最能代表业务特征的英文词语,应当仅由小写字母与数字组成,不得包含连字符 -
  • 使用备份集群搭建某一个现有集群的延迟从库时,biz 名应当为 <biz>delay,例如 pg-testdelay
  • 分支一个现有集群时,可以在 biz 尾部添加数字:例如从 pg-user1 可以分支出 pg-user2pg-user3
  • 水平分片集群,biz命名中应当包含 shard,并缀以分片号,例如 pg-testshard1pg-testshard2,……
  • <tld> 为顶层业务线,也可用于区分不同环境:例如 -tt-dev-uat-prod 等。无此需要可以省略。

服务命名规则(Service)

  • 每一套 PostgreSQL 集群会对外提供 2~6 种不等的服务,这些默认使用固定命名规则。
  • 服务名以集群名作为前缀,服务类型作为后缀,例如 pg-test-primarypg-test-replica
  • 读写服务统一以 primary 后缀命名,只读服务统一以 replica 后缀命名,这两个为必选服务。
  • ETL拉取/个人用户查询以 offline 后缀命名,直连主库/ETL写入以 default 后缀命名,为选配服务。
  • 同步读取服务以 standby 后缀命名,延迟从库服务以 delayed 后缀命名,少量核心库可提供此服务。

实例命名规则(Instance)

  • 一套 PostgreSQL 集群由至少一个实例组成,每个实例都有集群内从零或一开始唯一分配的实例号。
  • 实例名由集群名 + 实例号通过连字符 - 拼接而成,例如: pg-test-1pg-test-2
  • 实例号一经分配不得修改,持续到实例下线销毁,不得重新分配使用。
  • 实例名将作为监控系统数据的 ins 标签,附加到该实例的所有数据上。
  • 如果是用主机/数据库 1:1 独占式部署,节点 Hostname 可以使用数据库实例名。

数据库命名规则(Database)

  • 数据库库名应当与集群、应用保持一致,必须为具有高区分度的英文单词。
  • 命名以 <tld>_<biz> 的形式构建,<tld> 为顶层业务线,也可用于区分不同环境,不用可以省略。
  • <biz>为具体业务名称,例如 pg-test-tt 集群可以使用库名 tt_testtest。这一点不强制,即允许创建不同于集群 <biz> 名称的其他数据库。
  • 对于分片库,<biz> 部分必须以shard结尾,但不应当包含分片号,例如 pg-testshard1pg-testshard2 都用 testshard 即可。
  • 多个部分使用-连接。例如:<biz>-chat-shard<biz>-payment等,总共不超过三段。

角色命名规范(Role/User)

  • 数据库超级用户 dbsu 有且仅有一个:postgres,用于流复制的用户命名为replicator
  • 用于监控的用户统一命名为 dbuser_monitor,用于日常管理的超级用户为:dbuser_dba
  • 程序/服务使用的业务用户默认使用 dbuser_<biz> 作为用户名,例如 dbuser_test。来自不同服务的访问应当使用独立的业务用户区分访问。
  • 个人用户申请的数据库用户同意使用 dbp_<name>,其中 name 为 LDAP 中的标准用户名。
  • 默认权限组命名固定为: dbrole_readonlydbrole_readwritedbrole_admindbrole_offline

模式命名规则(Schema)

  • 业务统一使用一个全局的 <prefix> 作为模式名,尽可能简短,默认设置为search_path 首位元素。
  • <prefix> 不得使用 publicmonitor ,不得与任何 PostgreSQL 扩展使用的模式名冲突,例如: timescaledbcitusrepackgraphqlnetcron,…… 不宜使用特殊名称:dbatrash
  • 分片模式命名规则采用:rel_<partition_total_num>_<partition_index>。中间为总分片数,目前固定使用 8192 ,后缀是分片号,从0开始计数。如 rel_8192_0,…… ,rel_8192_11,等等。
  • 创建额外的模式,或者使用 <prefix> 之外的模式名需要由研发解释其必要性。

关系命名规则(Relation)

  • 关系命名以表意清晰为第一要义,不要使用含混的缩写,也不应过分冗长,遵循通用命名规则。
  • 表名应当使用复数名词,并与历史惯例保持一致,应尽量避免带有不规则复数形式的单词。
  • 视图以v_作为命名前缀,物化视图使用mv_作为命名前缀,临时表以tmp_作为命名前缀。
  • 继承或分区表应当以父表表名作为前缀,并以子表特性(规则,分片范围等)作为后缀。
  • 时间范围分区使用起始区间作为命名后缀,首个分区如果无上界则由研发指定一个足够久远的时间点:年级分区:tbl_2023,月级分区 tbl_202304,天级分区 tbl_20230405,小时级分区 tbl_2023040518 ,默认分区以 _default 结尾。
  • 哈希分区命名以余数作为分区表名的后缀,列表分区由研发手工指定与列表项对应的合理分区表名。

索引命名规则(Index)

  • 创建索引时,应当显式指定索引名称,并与PostgreSQL默认命名规则保持一致。
  • 索引名称以表名作为前缀,主键索引以 _pkey 结尾,唯一索引以 _key 结尾,普通索引以 _idx 结尾,用于EXCLUDED约束的索引以_excl结尾。
  • 使用条件索引/函数索引时,应当在索引名称中体现使用的函数与条件内容。例如 tbl_md5_title_idxtbl_ts_ge_2023_idx,但不可超出长度限制。

字段命名规则(Attribute)

  • 禁止使用系统列保留字段名:oidxminxmaxcmincmaxctid
  • 主键列通常命名为id,或以id作为后缀。
  • 创建时间字段惯用名为created_time,最后修改时间惯用名为 updated_time
  • 布尔型字段建议使用is_has_ 等作为前缀。
  • 额外的灵活 JSONB 字段固定使用 extra 作为列名。
  • 其余各字段名需与已有表命名惯例保持一致,任何打破惯例的字段命名都应当做出书面设计说明与解释。

枚举项命名 (Enum)

  • 枚举项默认应当使用 camelCase,但也允许其他风格。

函数命名规则(Function)

  • 函数命名以动词起头: selectinsertdeleteupdateupsertcreate ,……。
  • 重要参数可以通过_by_ids_by_user_ids的后缀在函数名中体现。
  • 避免函数重载,同名函数尽量只保留一个。
  • 禁止通过 BIGINT/INTEGER/SMALLINT 等整型进行函数签名重载,调用时可能产生歧义。
  • 存储过程与函数中的变量使用命名参数,避免位置参数($1$2,…)。
  • 如果参数名与对象名出现冲突,在参数前添加 _,例如_user_id

注释规范(Comment)

  • 尽最大可能为各种对象提供注释(COMMENT),注释使用英文,言简意赅,一行为宜。
  • 对象的模式或内容语义发生变更时,请务必一并更新注释,并与实际情况保持同步。

0x02 设计规范

Suum cuique

建表注意事项

  • 建表 DDL 语句需要使用标准格式,SQL 关键词大写,其他小写。
  • 在字段名/表名/别名中统一使用小写,尽量不要区分大小写。如果遇到大小写混用的情况,或者与 SQL 关键词冲突的名称,需要使用双引号扩起进行引用。
  • 能使用专有类型的,不使用字符串。(数值,枚举,网络地址,货币,JSON,UUID等):使用正确的数据类型,能显著提高数据存储,查询,索引,计算的效率,并提高可维护性。
  • 优化列的布局,对齐类型可以有额外的性能/存储空间收益。
  • 唯一约束须由数据库保证,任何唯一列须有对应的唯一约束。EXCLUDE约束是泛化的唯一约束,可以在低频更新场景下用于保证数据完整性。

分区表注意事项

  • 如果单表超过百TB量级,或者每月增量数据超过十几GB量级,可以考虑进行表分区。
  • 分区的指导原则是,让每个分区的大小尽可能落在 1GB ~ 64GB 的舒适范围内。
  • 有条件按照时间范围分区的表优先按时间范围分区,通常使用的粒度包括: decade,year,month,day,hour,应当至少提前三个月创建好未来所需的分区。
  • 对于极端倾斜的数据分布,可以组合使用不同的时间粒度,例如: 1900 - 2000 一个大分区,2000 - 2020 按年分区,2020 后按月分区。使用时间分区时,表名使用分区下限界的值(无穷大则选用一个足够久远的值)。

宽表注意事项

  • 宽表(例如有几十个字段的表)可以考虑进行纵向拆分,通过相同的主键与主表相互引用。
  • 因为PostgreSQL MVCC机制,宽表的写放大现象更为明显,减少对宽表的频繁更新。
  • 互联网场景中,允许适当降低规范化等级,减少多表连接以提高性能。

主键注意事项

  • 每个表都必须身份列,原则上必须有主键,最低要求为拥有非空唯一约束

  • 身份列用于唯一标识表中的任一元组,逻辑复制与诸多三方工具有赖于此。

  • 主键如果包含多列,应当在建表DDL的字段列表之后,使用 PRIMARY KEY(a,b,...) 单列指定。

  • 主键原则上建议使用整型,可以谨慎使用 UUID 与长度受限的文本类型,使用其他类型需要显式说明与评估。

  • 主键通常使用单一整型列,原则上建议使用 BIGINT,谨慎使用 INTEGER,不允许使用 SMALLINT

  • 主键应使用 GENERATED ALWAYS AS IDENTITY 生成唯一主键;SERIALBIGSERIAL 仅当需要兼容 PG 10 以下版本时允许使用。

  • 主键可以使用 UUID 类型作为主键,建议用 UUID v1/v7;谨慎使用 UUIDv4 作为主键,随机 UUID 的局部性较差且有碰撞概率。

  • 使用字符串列作为主键时,应当添加长度限制。通常使用 VARCHAR(64),使用更长的字符串时应当进行说明与评估。

  • INSERT/UPDATE 时原则上禁止修改主键列的值,INSERT RETURNING 可用于返回自动生成的主键值。

外键注意事项

  • 定义外键时引用必须显式设置相应的动作:SET NULLSET DEFAULTCASCADE,慎用级联操作。
  • 外键引用的列,需要为其他表/本表上的主键列。
  • 互联网类业务,特别是分区表、水平分片库慎用外键,可以在应用层解决。

空值/默认值注意事项

  • 字段语义上没有零值与空值区分的,不允许空值存在,须为列配置NOT NULL约束。
  • 字段语义上带有默认值的,应当配置 DEFAULT 默认值。

数值类型注意事项

  • 常规数值字段使用INTEGER。容量拿不准的数值列使用BIGINT
  • 无特殊理由不要用SMALLINT,性能与存储提升甚小,但会有很多额外的问题。
  • 注意SQL标准不提供无符号整型,超过INTMAX但没超过UINTMAX的值需要升格存储。不要存储超过INT64MAX的值到BIGINT列中,会溢出为负数。
  • REAL 表示4字节浮点数,FLOAT 表示8字节浮点数。浮点数仅可用于末尾精度无所谓的场景,例如地理坐标。切记不要对浮点数使用等值判断,零值除外
  • 精确数值类型使用NUMERIC,如果可行,请用 NUMERIC(p)NUMERIC(p,s) 设置有效数字位数以及小数部分的有效位数。例如摄氏气温(37.0)可以用 NUMERIC(3,1) 类型来存储3位有效数字与1位小数。
  • 货币数值类型使用MONEY

文本类型注意事项

  • PostgreSQL的文本类型包括 char(n)varchar(n)text。默认情况下,可以使用 text 类型 ,不限制字符串长度,但受字段最大长度1GB限制。
  • 如果条件许可,优先使用 varchar(n) 类型来设置一个最大字符串长度,这会引入极微小的额外检查开销,但能规避一些脏数据与极端情况。
  • 避免使用char(n),该类型为了与SQL标准兼容,存在不合直觉的行为表现(补齐空格与截断),且并没有存储和性能优势。

时间类型注意事项

  • 时间只有两种存储方式:带时区的 TIMESTAMPTZ,不带时区的 TIMESTAMP
  • 建议使用带时区的 TIMESTAMPTZ,如果使用 TIMESTAMP 存储,必须使用0时区标准时。
  • 生成0时区时间请使用 now() AT TIME ZONE 'UTC' ,不能直接截断时区 now()::TIMESTAMP
  • 统一使用 ISO-8601 格式输入输出时间类型:2006-01-02 15:04:05,避免DMY与MDY问题。
  • 中国区域用户可以统一使用 Asia/Hong_Kong +8 时区,因为上海时区缩写 CST 有歧义。

枚举类型注意事项

  • 较稳定的,取值空间较小(几十到几百内)的字段应当使用枚举类型,不要使用整型与字符串表示。
  • 枚举内部使用动态整型实现,相比整型有可读性优势,相比字符串有性能、存储、可维护性上的优势。
  • 枚举项只能添加,无法删除,但是可以重命名现有枚举值。ALTER TYPE <enum_name> 用于修改枚举。

UUID类型注意事项

  • 请注意,全随机的 UUIDv4 用作主键时局部性太差,尽可能考虑用 UUIDv1 / v7 代替。
  • 一些 UUID 生成/处理函数需要额外的扩展插件,例如 uuid-ossppg_uuidv7 等,有此需求请在配置时指明。

JSON类型注意事项

  • 如无特殊原因,总是使用二进制存储的 JSONB 类型与相关函数,而非文本版本的 JSON
  • 请注意 JSON 中的原子类型与 PostgreSQL 对应类型的细微差别:与 JSON 字符串对应的text 类型中不允许出现 \u0000 零字符,与 JSON 数值类型对应的 numeric 中不允许出现 NaNinfinity。布尔值只接受小写的 truefalse 字面值。
  • 请注意JSON标准中的null对象和 SQL 标准中的空值 NULL 并非同一个概念。

数组类型注意事项

  • 当存储元素数量较少时,可以使用数组字段代替单独。
  • 适合用于存储元素数量相对较少且变化不频繁的数据。如果数组中的元素数量非常大或经常变化,考虑使用单独的表来存储数据,并使用外键关联。
  • 高维度的浮点数组,可以考虑使用 pgvector 扩展提供的专用数据类型。

GIS类型注意事项

  • GIS 类型默认使用 srid=4326 参考坐标系。
  • 经纬度坐标点应当使用 Geography 类型,无需显式指定参考系坐标 4326

触发器注意事项

  • 触发器会提高数据库系统的复杂度与维护成本,原则上不鼓励使用。禁止使用规则系统,此类需求应当使用触发器替代。

  • 触发器的典型场景是,在修改某一行后自动修改 updated_time 为当前时间戳,或者将表的增删改动作记录到另一张日志表中,或者维持两张表在业务上的一致性。

  • 触发器中的操作是事务性的,意味着如果触发器或触发器中的操作失败,整个事务都会回滚,所以请充分测试并证明触发器的正确性。对于递归调用、执行复杂查询死锁,多个触发器执行顺序等情况需要特别关注。

存储过程/函数注意事项

  • 函数/存储过程适用于封装事务,减少并发冲突,减少网络往返,减少返回数据量,执行少量自定义逻辑。

  • 存储过程不适合进行复杂计算,不适合进行平凡/频繁的类型转换与包装。在关键高负载系统中,应当移除数据库中不必要的计算密集型逻辑,例如在数据库中使用SQL进行WGS84到其他坐标系的换算。与数据获取、筛选密切关联的计算逻辑可以使用函数/存储过程:例如PostGIS中的几何关系判断。

  • 不再使用的,被替换的函数与存储过程应当及时下线,避免与未来的函数发生冲突。

  • 使用统一的函数创建语法格式,签名单独占用一行(函数名与参数),返回值单启一行,语言为第一个标签。一定要标注函数易变性等级:IMMUTABLE, STABLE, VOLATILE。添加属性标签,如:RETURNS NULL ON NULL INPUTPARALLEL SAFEROWS 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$;
    

使用合理的Locale选项

使用合理的字符编码与本地化配置

  • 必须使用 UTF8 字符编码,严格禁止使用其他任何字符编码。
  • 必须使用 C 作为 LC_COLLATE 默认排序规则,有特殊需求必须在DDL/查询子句中显式指定来实现。
  • 字符集 LC_CTYPE 默认使用 en_US.UTF8,一些扩展依赖字符集信息方可正常工作,如 pg_trgm

索引相关注意事项

  • 所有在线查询必须针对其访问模式设计相应索引,除极小表外不允许全表扫描。
  • 索引有代价,不允许创建不使用的索引,应当及时清理不再使用的索引。
  • 建立联合索引时,应当将区分度,选择率高的列放在前面,例如 ID,时间戳等。
  • GiST索引可用于解决近邻查询问题,传统B树索引无法提供对KNN问题的良好支持。
  • 对于值与堆表的存储顺序线性相关的数据,如果通常的查询为范围查询,建议使用BRIN索引。最典型场景如仅追加写入的时序数据,BRIN索引相比Btree更为高效。
  • 针对 JSONB / 数组字段进行检索时,可以使用 GIN 索引加速查询。

明确B树索引空值的顺序

  • 如在可空列上有排序需求,需要在查询与索引中明确指定NULLS FIRST还是NULLS LAST
  • 注意,DESC排序的默认规则是NULLS FIRST,即空值会出现在排序的最前面,通常这不是期望行为。
  • 索引的排序条件必须与查询匹配,如:CREATE INDEX ON tbl (id DESC NULLS LAST);

禁止在大字段上建立索引

  • 被索引字段大小无法超过2KB(1/3的页容量),在文本类型上创建索引需要谨慎,被索引的文本应当使用带有长度约束的 varchar(n) 类型。
  • 文本类型用作主键时,必须设置最大长度。原则上长度不应当超过 64 个字符,特殊情况需显式说明评估。
  • 如有大字段索引需求,可以考虑对大字段取哈希,并建立函数索引。或使用其他类型的索引(GIN)。

充分利用函数索引

  • 任何可以由同一行其他字段推断得出的冗余字段,可以使用函数索引替代。
  • 对于经常使用表达式作为查询条件的语句,可以使用表达式或函数索引加速查询。
  • 典型场景:建立大字段上的哈希函数索引,为需要左模糊查询的文本列建立 reverse 函数索引。

充分利用部分索引

  • 查询中查询条件固定的部分,可以使用部分索引,减小索引大小并提升查询效率。
  • 查询中某待索引字段若只有有限几种取值,也可以建立几个相应的部分索引。
  • 如果部分索引中的列会被频繁更新,请关注这些索引的膨胀情况

0x03 查询规范

The limits of my language mean the limits of my world.

—Ludwig Wittgenstein

使用服务接入

  • 生产数据库接入必须通过域名接入服务,严禁使用 IP 地址直连。
  • 服务与接入使用 VIP,LVS/HAProxy 屏蔽集群实例成员的角色变化,主从切换无需应用重启。

读写分离

  • 互联网业务场景:写请求必须走主库,通过 Primary 服务访问。
  • 读请求原则上走从库,通过 Replica 服务访问。
  • 例外情况:需要读己之写的一致性保证,且检测到显著的复制延迟时,只读请求可以访问主库;或向DBA申请提供 Standby 服务。

快慢分离

  • 生产中1毫秒以内的查询称为快查询,生产中超过1秒的查询称为慢查询。
  • 慢查询必须走离线从库 —— Offline 服务/实例,应当在执行时设置超时。
  • 生产中的在线普通查询执行时长原则上应当控制在 1ms 内。
  • 生产中的在线普通查询执行时长,超过10ms需修改技术方案,优化达标后再上线。
  • 在线查询应当配置10ms 数量级或更快的 Timeout,避免堆积造成雪崩。
  • 禁止从 Primary 上 ETL 数据,应当使用 Offline 服务从专用实例取数。

使用连接池

  • 生产应用必须通过连接池访问数据库,通过 1:1 部署的 Pgbouncer 代理访问 PostgreSQL 数据库。Offline 服务,个人用户严禁直接使用连接池。
  • Pgbouncer 连接池默认使用 Transaction Pooling 模式,一些会话级别的功能可能无法使用(比如Notify/Listen),需要特别注意。在此模式下,1.21 以前的 Pgbouncer 不支持使用 Prepared Statements。特殊场景可以使用 Session Pooling 或绕开连接池直接访问数据库,需要 DBA 审核特批。
  • 使用连接池时禁止修改连接状态,包括修改连接参数,修改搜索路径,更换角色,更换数据库。万不得已修改后必须彻底销毁连接,将状态变更后的连接放回连接池会导致污染扩散。严禁使用 pg_dump 通过 Pgbouncer 转储数据。

为查询语句配置主动超时

  • 应用应当为所有的语句配置主动超时,超时后主动取消请求,避免雪崩。(Go context)
  • 周期性执行的语句,必须配置小于执行周期的超时 Timeout,避免雪崩。
  • HAProxy 配置有 24 小时连接默认超时,用于滚动过期长连接。请不要在离线实例上运行执行时间超过1天的 SQL,有此需求由DBA特批调整。

关注复制延迟

  • 应用必须意识到主从之间的同步延迟,并妥善处理好复制延迟超出合理范围的情况。
  • 常规情况下,复制延迟在 100µs / 几十KB 的数量级,但是在极端情况下,从库可能会出现分钟/小时级的复制延迟,应用应当知晓这种现象,并有相应的降级方案 —— 选择从主库读取,稍后重试,或直接报错。

重试失败的事务

  • 查询可能因为并发争用,管理员命令等原因被杀死,应用需要意识到这一点,并在必要时重试。
  • 应用在数据库大量报错时可以触发断路器熔断,避免雪崩。但要注意区分错误的类型与性质。

掉线重连

  • 数据库连接可能因为各种原因被中止,应用必须有掉线重连机制。
  • 可以使用SELECT 1作为心跳包查询,检测连接的有消息,并定期保活。

在线服务应用代码禁止执行DDL

  • 生产应用严禁执行 DDL,不要在应用代码里搞个大新闻。
  • 例外场景:为分区表创建新的时间分区,可由应用谨慎管理。
  • 特殊例外:办公系统使用的数据库,例如 Gitlab / Jira/ Confluence 等可以授予应用 DDL 权限。

SELECT语句显式指定列名

  • 避免使用SELECT *,或在RETURNING子句中使用*。请使用具体的字段列表,不要返回用不到的字段。当表结构发生变动时(例如,新值列),使用列通配符的查询很可能会发生列数不匹配的错误。
  • 一些表的字段经过维护之后,顺序会发生变化,例如:将 INTEGER 主键 id 升级为 BIGINT 后,id 的列顺序会到最后一列。此问题只能在维护迁移时择机修复,研发应当克制调整列顺序的强迫症,并在 SELECT 语句中显式指定列的顺序
  • 例外:当存储过程返回具体的表行类型时,允许使用通配符。

禁止在线查询全表扫描

  • 例外情况:常量极小表,极低频操作,表/返回结果集很小(百条记录/百KB内)。
  • 在首层过滤条件上使用诸如!=, <>的否定式操作符会导致全表扫描,必须避免。

禁止在事务中长时间等待

  • 开启事务后必须尽快提交或回滚,超过10分钟的IDEL IN Transaction将被强制杀死。
  • 应用应当开启 AutoCommit,避免BEGIN之后没有配对的ROLLBACKCOMMIT
  • 尽量使用标准库提供的事务基础设施,不到万不得已不要手动控制事务。

使用 count 计数时的注意事项

  • 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,是一个例外。
  • 如果聚集函数返回空并不是期望的结果,使用 coalesce 来设置缺省值。

谨慎处理空值

  • 明确区分零值与空值,空值使用IS NULL进行等值判断,零值使用常规的=运算符进行等值判断。
  • 空值作为函数输入参数时应当带有类型修饰符,否则对于有重载的函数将无法识别使用何者。
  • 注意空值比较逻辑:任何涉及到空值比较运算结果都是unknown,需要注意unknown参与布尔运算的逻辑:
    • andTRUE or UNKNOWN会因为逻辑短路返回TRUE
    • orFALSE and UNKNOWN会因为逻辑短路返回FALSE
    • 其他情况只要运算对象出现UNKNOWN,结果都是UNKNOWN
  • 空值与任何值的逻辑判断,其结果都为空值,例如NULL=NULL返回结果是NULL而不是TRUE/FALSE
  • 涉及空值与非空值的等值比较,请使用``IS DISTINCT FROM 进行比较,保证比较结果非空。
  • 空值与聚合函数:聚合函数当输入值全部为NULL时,返回结果为NULL。

注意序列号空缺

  • 当使用Serial类型时,INSERTUPSERT等操作都会消耗序列号,该消耗不会随事务失败而回滚。
  • 当使用整型 INTEGER 作为主键,且表存在频繁插入冲突时,需要关注整型溢出的问题。

使用游标后必须及时关闭

重复查询使用准备语句

  • 重复的查询应当使用准备语句(Prepared Statement),消除数据库硬解析的CPU开销。低于 1.21 版本的 Pgbouncer 无法在事务池化模式中支持此功能,请特别注意。
  • 准备语句会修改连接状态,请注意连接池对于准备语句的影响。

选择合适的事务隔离等级

  • 默认隔离等级为读已提交,适合大多数简单读写事务,普通事务选择满足需求的最低隔离等级。
  • 需要事务级一致性快照的写事务,请使用可重复读隔离等级。
  • 对正确性有严格要求(例如与钱有关)的写入事务,使用可序列化隔离等级。
  • 在RR与SR隔离等级出现并发冲突时,应用应当视错误类型进行积极的重试。

r h 09判断结果存在性不要使用count

  • 使用SELECT 1 FROM tbl WHERE xxx LIMIT 1判断是否存满足条件的列,要比Count快。
  • 可以使用SELECT exists(SELECT * FROM tbl WHERE xxx LIMIT 1)将存在性结果转换为布尔值。

使用RETURNING子句一次性取回修改后的结果

  • RETURNING 子句可以在 INSERTUPDATEDELETE 语句后使用,有效减少数据库交互次数。

使用UPSERT简化逻辑

  • 当业务出现插入-失败-更新的操作序列时,考虑使用UPSERT替代。

利用咨询锁应对热点并发

  • 针对单行记录的极高频并发写入(秒杀),应当使用咨询锁对记录ID进行锁定。
  • 如果能在应用层次解决高并发争用,就不要放在数据库层面进行。

优化IN操作符

  • 使用 EXISTS 子句代替IN操作符,性能更佳。
  • 使用 =ANY(ARRAY[1,2,3,4]) 代替 IN (1,2,3,4),效果更佳。
  • 控制参数列表的大小,原则上不要超过1万个,超过时可以考虑分批处理。

不建议使用左模糊搜索

  • 左模糊搜索WHERE col LIKE '%xxx'无法充分利用B树索引,如有需要,可用reverse表达式函数索引。

使用数组代替临时表

  • 考虑使用数组替代临时表,例如在获取一系列ID的对应记录时。=ANY(ARRAY[1,2,3]) 要比临时表JOIN好。

0x04 管理规范

使用 Pigsty 搭建 PostgreSQL 集群与基础设施

  • 生产环境统一使用 Pigsty 主干版本,在 x86_64 机器, CentOS 7.9 / RockyLinux 8.8 操作系统上部署数据库。
  • pigsty.yml 配置文件通常包含了高度敏感的重要机密信息,应当使用 git 进行版本化管理,并严格控制访问权限。
  • files/pki 内生成的 CA 私钥与其他证书应当妥善保管,定期将备份至安全区域存储归档,并严格控制访问权限。
  • 所有密码都不允许使用默认值,确保都已经修改为强度足够的新密码。
  • 严格控制管理节点与配置代码仓库的的访问权限,仅限 DBA 登陆与访问。

监控系统是必选项

  • 任何部署必须有一套监控系统,生产环境至少使用两套 Infra 节点以提供冗余。

根据需求合理规划集群架构

  • 任何由DBA管理的生产数据库集群,必须带有至少一个在线从库,用于在线故障切换。
  • 默认使用 oltp 模板,分析类数据库使用 olap 模板,财务库使用 crit 模板,小微虚拟机(四核内)使用 tiny 模板。
  • 年增数据量超过1TB的业务,或者写入 TPS 超过3~5万的集群,可以考虑搭建水平分片集群。

使用 Patroni 与 Etcd 配置集群高可用

  • 生产数据库集群使用 Patroni 作为高可用组件,使用 etcd 作为 DCS。
  • etcd 使用专用虚拟机集群,3 ~ 5 个节点,严格打散分布在不同机柜上。
  • 必须开启 Patroni Failsafe 模式,确保 etcd 故障时集群主库可以继续工作。

使用 pgBackRest 与 MinIO 配置集群PITR

  • 生产数据库集群使用 pgBackRest 作为备份恢复/PITR方案,使用 MinIO 作为备份存储仓库。
  • MinIO 使用多节点多盘集群,亦可使用 S3 / OSS / COS 服务代替,冷备份必须设置密码加密。
  • 所有数据库集群每天进行一次本地全量备份,保留最近一周的备份与WAL,每隔一月存留一个全备。
  • 出现 WAL 归档错误时,应当及时检查备份仓库并排查问题。

核心业务数据库配置注意事项

  • 核心业务集群至少需要配置两个在线从库,其中一个为专用离线查询实例。
  • 核心业务集群需要搭建一套延迟24小时的延迟从库集群,用于应急数据恢复。
  • 核心业务集群通常采用异步提交,与钱有关则采用同步提交。

财务数据库配置注意事项

  • 财务数据库集群需要至少两个在线从库,其中一个为专用同步 Standby 实例,并启用 Standby 服务接入。
  • 与钱有关的库必须使用 RPO = 0 的 crit 模板,启用同步提交确保数据零丢失,视情况启用 Watchdog。
  • 与钱有关的库必须强制打开数据校验和,视情况打开全量 DML 日志。

使用合理的字符编码与本地化配置

  • 必须使用 UTF8 字符编码,严格禁止使用其他任何字符编码。
  • 必须使用 C 作为 LC_COLLATE 默认排序规则,有特殊需求必须在DDL/查询子句中显式指定来实现。
  • 字符集 LC_CTYPE 默认使用 en_US.UTF8,一些扩展依赖字符集信息方可正常工作,如 pg_trgm

业务数据库管理注意事项

  • 同一个集群内允许创建多个不同的数据库,必须使用 Ansible 剧本新建业务数据库。
  • 所有业务数据库都必须同步存在于 Pgbouncer 连接池中。

业务用户管理注意事项

  • 不同的业务/服务必须使用不同的数据库用户,必须使用 Ansible 剧本新建业务用户。
  • 所有生产业务用户都必须同步存在于 Pgbouncer 连接池的用户列表文件中。
  • 个人用户应当设置默认有效期为 90 天的密码并定时更换。
  • 个人用户只允许从跳板机访问有权限的集群 Offline 实例,或带有 pg_offline_query 的从库。

扩展插件管理注意事项

  • 安装新扩展时,必须先在集群所有实例中使用 yum/apt 安装对应大版本的扩展插件二进制软件包。
  • 启用扩展前,需要确认扩展是否需要加入 shared_preload_libraries ,如果需要应当安排滚动重启。
  • 注意 shared_preload_libraries 优先级顺序, citustimescaledbpgml 通常要放在最前面。
  • pg_stat_statementsauto_explain 是必选插件,必须在所有集群中启用。
  • 安装扩展统一使用 dbsu 进行,在业务数据库中 CREATE EXTENSION 执行创建。

数据库XID与年龄注意事项

  • 关注数据库与表的年龄,避免XID事务号用尽。使用超过 20% 应当关注,超过 50% 应当立即介入处理。
  • 处理 XID 时,按年龄从大到小顺序挨个对表执行 VACUUM FREEZE

数据库表与索引膨胀注意事项

  • 关注表与索引的膨胀率,避免索引性能劣化,使用 pg_repack 在线处理表/索引膨胀问题。
  • 一般情况下,膨胀率超过 50% 的索引与表可以考虑进行重整处理。
  • 处理超过 100GB 的表膨胀时,应当特别注意,并挑选业务低谷时进行。

数据库重启注意事项

  • 重启数据库前,执行 CHECKPOINT 两次,强制脏页刷盘,可加速重启过程。
  • 重启数据库前,执行 pg_ctl reload 重载配置确认配置文件正常可用。
  • 重启数据库可使用 pg_ctl restart 或 patronictl 同时重启整个集群。
  • 严禁使用 kill -9 关闭任何数据库进程。

复制延迟注意事项

  • 监控复制延迟,使用复制槽时更必须十分留意。

新从库数据预热

  • 高负载业务集群添加新从库实例时,应当对新数据库实例进行预热,逐步调整并应用 HAProxy 实例权重,分梯度上量:4,8,16,32,64,100。可以使用 pg_prewarm 将热数据加载至内存。

数据库发布流程

  • 线上数据库发布需要经过研发自测,主管审核,QA审核(可选),DBA审核几个评估阶段。
  • 研发自测阶段,应当由研发确保变更在开发、预发环境执行正确无误。
    • 如果是新建表,应当给出记录数量级,数据日增量预估值,读写吞吐量级预估。
    • 如果是新建函数,应当给出平均执行时间与极端情况说明。
    • 如果是模式变更,必须梳理清楚所有上下游依赖。
    • 如果是数据变更,记录订正,必须给出回滚 SQL。
  • 研发 Team Leader 需要对变更进行评估与审核,对变更内容负责。
  • DBA对发布的形式与影响进行评估与审核,提出审核意见,打回或统一执行

数据工单格式

  • 数据库变更通过平台进行,每个变更一个工单。
  • 标题清晰:某某业务需在 xx 库执行 yy 动作。
  • 目标明确:每个步骤需要在哪些实例上执行哪些操作,结果如何校验。
  • 回滚方案:任何变更都需要提供回滚方案,新建也需要提供清理脚本。
  • 任何变更都需要记录归档,有完善的审批记录,首先由研发上级TL Review 审批通过后由 DBA 审批。

数据库变更发布注意事项

  • 使用统一的发布窗口,每天 16:00 统一收集当日变更依次执行;16:00点后TL确认的需求将顺延至第二天执行。19:00 后不允许数据库发布,紧急发布请TL做特殊说明,抄送CTO审批同意后执行。

  • 数据库 DDL 变更 DML 变更统一使用管理员用户 dbuser_dba 远程执行,确保默认权限正常工作。

  • 业务管理员在自行执行 DDL 时,必须SET ROLE dbrole_admin 后再执行发布,确保默认权限。

  • 任何变更都需要有回滚预案方可执行,极个别无法回滚的操作需要特别谨慎处理(例如枚举加值)

  • 数据库变更使用 psql 命令行工具,连接到集群主库执行,使用 \i 执行脚本或 \e 手工分批执行。

删除表注意事项

  • 生产数据表 DROP 应当首先重命名,冷却 1~3 天确认没有访问后再移除。
  • 清理表时必须梳理所有依赖,包括直接间接依赖的对象:触发器,外键引用等。
  • 待删除的临时表通常放置于 trash Schema 中,通过 ALTER TABLE SET SCHEMA 修改模式名。
  • 高负载业务集群中,移除特别大的表 (> 100G) 时挑选业务低谷进行,避免抢占 I/O 。

创建与删除索引注意事项

  • 必须使用 CREATE INDEX CONCURRENTLY 并发创建索引,使用 DROP INDEX CONCURRENTLY 并发移除索引。
  • 重建索引时,总是先创建新索引,再移除旧索引,并修改新索引名与旧索引保持一致。
  • 创建索引失败后,应当及时移除 INVALID 的索引,修改索引后,使用 analyze 重新收集表上的统计数据。
  • 业务空闲时,可以启用并行索引创建,并设置 maintenance_work_mem 为更大的值加速索引创建。

审慎地进行模式变更

  • 尽可能避免整表重写式的变更,1GB 以内的表允许全表重写,DBA 应当在变更时告知所有相关业务方。
  • 向现有表中添加新列时,应当避免在默认值中使用 VOLATILE 的函数,避免全表重写。
  • 变更列类型时,必要时应当重建所有依赖该类型的函数,视图,并 ANALYZE 刷新统计信息。

控制数据写入的批次规模

  • 大批量写入操作应当切分为小批量进行,避免一次产生大量WAL或占用 I/O。
  • 大批量 UPDATE 后,执行 VACUUM 回收死元组占用的空间。
  • 执行 DDL 语句本质是对系统目录的修改,同样需要控制一个批次内的DDL语句数量。

数据加载注意事项

  • 使用COPY加载数据,如有需要可以并行执行。
  • 加载数据前可以临时关闭autovacuum,按需禁用触发器,并在加载完后再建立约束与索引。
  • 调大 maintenance_work_mem,增大max_wal_size
  • 加载完成后执行vacuum verbose analyze table

数据库迁移、大版本升级注意事项

  • 生产环境统一使用标准迁移搭建剧本逻辑,通过蓝绿部署实现不停机集群迁移、大版本升级等需求。
  • 对于停机时间没有要求的集群,可以使用 pg_dump | psql 逻辑导出导入的方式停机升级。

数据误删/误更新处理流程

  • 事故发生后,立即评估是否需要停机止血,评估影响规模,决定处理手段。
  • 研发侧如有办法恢复,优先由研发自行通过 SQL 发布进行订正;否则使用 pageinspectpg_dirtyread 从坏表中抢救数据。
  • 若有延迟从库,从延时从库中抽取数据进行修复。首先确认误删时间点,推进延迟从库至该 XID 后抽取数据。
  • 大面积误删误写,经与业务沟通同意后,执行原地 PITR 回滚至特定时间。

数据腐坏处理流程

  • 确认从库数据是否可用于恢复,若从库数据无恙可先 Switchover 至从库。
  • 临时关闭 auto_vacuum ,定位错误根因,替换故障磁盘并补充新从库。
  • 若系统目录损坏,或使用 pg_filedump 从表二进制文件中恢复数据。
  • 若 CLOG 损坏,使用 dd 生成仿造提交记录。

数据库连接打满注意事项

  • 出现连接打满现象(雪崩)时,立即使用杀连接查询治标止损:pg_cancel_backendpg_terminate_backend
  • 使用 pg_terminate_backend 中止所有普通后端进程,从每秒一次(psql \watch 1)开始。并从监控系统确认连接情况,如果继续堆积,则不断提高杀连接查询的执行频次,例如每 0.1 秒一次,直到不再堆积为止。
  • 从监控系统确认止血后,尝试停止杀连接,若重新出现堆积则立即恢复杀连接。立即分析根因并进行相应处理(升配,限流,加索引等)