操作速查表

简介

本页面旨在从 PostgreSQL 社区的智慧中学习。

参与 PostgreSQL 社区的个人和组织在博客、维基和网站上发布了大量有用的信息。但是，它们分散在各个地方，要找到你想要的信息并不容易。

因此，本页面收集了一些链接，指向编辑们认为有参考价值的文章，这些文章来自 Planet PostgreSQL 上注册的数百个博客网站，以及 PostgreSQL 维基和网站。这里摘录了这些文章中的一些值得关注的主题，并试图组织和总结它们以供介绍目的。请随时添加你认为对其他人有帮助的文章的链接。除了链接之外，添加这些文章的摘要也是值得鼓励的。

注意事项

不要指望能从这些文章中提取所有信息！强烈建议直接深入阅读原始文章。
某些配置参数、函数、统计视图和系统表/视图可能只有在某些主要版本之后才可用。请查阅 PostgreSQL 文档以了解可用性。

架构

客户端-服务器架构

服务器
- 数据库服务器：postgres
- 服务器应用程序：例如，initdb、pg_ctl、pg_upgrade
客户端
- 客户端接口：例如，libpq、ECPG、pgJDBC、psqlODBC、Npgsql
- 客户端应用程序：例如，psql、pgbench、pg_dump、pg_restore
前端/后端协议
- 前端=客户端，后端=服务器
- 用于前端和后端之间通过 TCP/IP 和 Unix 域套接字进行通信的基于消息的协议
- 自 2003 年 PostgreSQL 7.4 以来，当前版本一直是 3.0
客户端和服务器之间的兼容性
- psql 最适合与相同或更旧主要版本的服务器一起使用。它也可以与更新主要版本的服务器一起使用，尽管反斜杠命令可能无法执行。
- pg_dump 可以从比自身更旧的服务器进行转储（支持从 9.2 及更早版本转储）。它不能从更新主要版本的服务器进行转储。
- 驱动程序与服务器版本无关：始终使用最新的驱动程序版本
- 例如，pgJDBC 支持 8.2+ 服务器，Npgsql 在支持的 5 个主要服务器版本上进行了测试。

逻辑数据库结构

数据库集群是数据库、角色和表空间的集合
数据库集群在 initdb 后最初包含一些数据库
- template1：除非指定了其他模板数据库，否则新数据库将从该模板克隆
- template0：template1 的原始内容的原始副本
- postgres：实用程序和用户使用的默认数据库
每个数据库都包含它自己的系统目录，用于存储其本地数据库对象的元数据
数据库集群包含一些共享的系统目录，用于存储集群范围的全局对象的元数据
- 共享的系统目录可以从每个数据库内部访问
- 获取共享系统目录的查询：SELECT relname FROM pg_class WHERE relisshared AND relkind = 'r';
- 例如，pg_authid、pg_database、pg_tablespace
表空间
- pg_global ($PGDATA/global/)：存储共享的系统目录
- pg_default ($PGDATA/base/)：存储 template0、template1、postgres。其他数据库的默认表空间。
- 用户表空间：使用 CREATE TABLESPACE name LOCATION 'dir_path'; 创建

数据库对象层次结构

数据库
- 访问方法
- 强制转换
- 事件触发器
- 扩展
- 外部数据包装器
- 外部服务器
- 过程语言
- 发布
- 行级安全策略（名称必须与表的任何其他策略的名称不同）
- 规则（名称必须与同一表的任何其他规则的名称不同）
- 模式
  - 聚合函数
  - 排序规则
  - 转换
  - 数据类型
  - 域
  - 扩展统计信息
  - 外部表
  - 函数
  - 索引
  - 物化视图
  - 运算符
  - 运算符类
  - 运算符族
  - 过程
  - 序列
  - 表
  - 文本搜索配置
  - 文本搜索词典
  - 文本搜索解析器
  - 文本搜索模板
  - 触发器（继承其表的模式）
  - 视图
- 订阅
- 转换
- 用户映射
角色
表空间

对象标识符 (OID)

OID 在 PostgreSQL 内部用作各种系统表的
- 例如，SELECT oid, relname FROM pg_class WHERE relname = 'mytable';
类型 oid 表示 OID。
oid 是一个无符号的四字节整数。
OID 从一个单一的集群范围的计数器分配，因此它不够大，无法提供数据库范围的唯一性。
- pg_depend 和 pg_shdepend 中的两个 OID（classid 和 objid）标识了特定对象。

物理数据库结构

数据目录 ($PGDATA)
- base/：包含每个数据库子目录的子目录
- global/：包含集群范围表的子目录，例如 pg_database
- pg_multixact/：包含多事务状态数据的子目录（用于共享行锁）
- pg_subtrans/：包含子事务状态数据的子目录
- pg_tblspc/：包含指向表空间的符号链接的子目录
- pg_wal/：包含 WAL（预写式日志）文件的子目录
- pg_xact/：包含事务提交状态数据的子目录
配置文件目录（可选）
表空间目录（可选）
WAL 目录（可选）

数据目录中的文件

配置文件（postgresql.conf、pg_hba.conf、pg_ident.conf）：可以存储在其他目录中
控制文件（global/pg_control）：存储控制信息，例如集群状态、检查点日志位置、下一个 OID、下一个 XID
常规关系数据文件
- 关系是一组元组：表、索引、序列、物化视图等。
- 每个关系都有一组自己的文件。
- 每个文件都由 8 KB 的块组成。
- 延迟分配：新的堆表文件包含 0 个块，而新的 B 树索引文件包含 1 个块。
- 存在一些类型的数据文件（分支）：main、FSM、VM、初始化
- Main 分支（base/<database_OID>/<relation_filenode_no>）
  - 例如，"SELECT pg_relation_filepath('mytable');" 返回 base/17354/32185，其中 17354 是数据库的 OID，而 32185 是 mytable 的文件节点号
  - 存储元组数据。
- FSM（空闲空间映射）分支（base/<database_OID>/<relation_filenode_no>_fsm）
  - 跟踪关系中的空闲空间。
  - 条目以树的形式组织，其中每个叶节点条目存储一个关系块中的空闲空间。
  - pg_freespacemap 和 pageinspect 可用于检查其内容。
- VM（可见性映射）分支（base/<database_OID>/<relation_filenode_no>_vm）
  - 跟踪
    - 哪些页面只包含已知对所有活动事务可见的元组
    - 哪些页面只包含已冻结的元组
  - 每个堆关系都具有可见性映射；索引没有。
  - 为每个堆页面存储两个位
    - 全可见位：如果设置，则页面不包含任何需要真空的元组。还用于仅索引扫描以使用仅索引元组来回答查询。
    - 全冻结位：如果设置，则页面上的所有元组都已冻结，因此真空可以跳过该页面。
  - pg_visibility 可用于检查其内容。
- 初始化分支（base/<database_OID>/<relation_filenode_no>_init）
  - 每个未记录的表和索引都有一个初始化分支。
  - 内容为空：表为 0 个块，索引为 1 个块。
  - 在恢复期间重置未记录的关系：初始化分支将复制到 main 分支，而其他分支将被擦除。
临时关系数据文件
- base/<database_OID>/tBBB_FFF
  - BBB 是创建该文件的后端的后端 ID，FFF 是文件节点号
  - 例如，base/5/t3_16450
- 具有 main、FSM 和 VM 分支，但不具有初始化分支。
大型关系将划分为 1 GB 的段文件。
- 例如，12345、12345.1、12345.2、...

页面（= 块）

每个页面为 8 KB。在构建 PostgreSQL 时可配置。
关系具有相同的格式。
内存和存储中的内容相同。
每个页面存储多个称为项的数据值。在表中，项是一行；在索引中，项是一个索引条目。
pageinspect 可用于检查内容。
页面的布局
1. 页面头：24 字节
2. 指向实际项的项标识符数组：每个条目都是一个 (偏移量，长度) 对。每个项 4 字节。
3. 空闲空间
4. 项目
5. 特殊空间：表为 0 字节，索引类型（btree、GIN、GiST 等）为不同的字节。

表行

[1] pageinspect] 可用于检查内容。
行的布局
1. 标题：23 字节
2. 空位图（可选）：每列 1 位
3. 用户数据：行的列

实例

实例是服务器端进程组、它们的本地内存和共享内存。

流程

单线程：postmaster 为每个客户端连接启动一个后端进程。因此，每次 SQL 执行仅使用一个 CPU 内核。并行查询、索引构建、VACUUM 等可以通过运行多个服务器进程来利用多个 CPU 内核。
postmaster：所有服务器进程的父进程。控制服务器启动和关闭。创建共享内存和信号量。启动其他服务器进程并回收已死亡的进程。在 TCP 端口和/或 Unix 域套接字上打开并监听，接受连接请求，并生成客户端后端以将连接请求传递给它们。
（客户端）后端：代表客户端会话执行操作并处理其请求，即执行 SQL 命令。
后台进程
- logger：通过管道捕获来自其他进程的所有 stderr 输出，并将它们写入日志文件。
- checkpointer：处理所有检查点。
- 后台写入器：定期醒来并写入脏共享缓冲区，以便其他进程在需要释放共享缓冲区以读取另一个页面时不必写入它们。
- startup：执行崩溃和时间点恢复。一旦恢复完成，它就会结束。
- 统计收集器：通过 UDP 套接字接收来自其他进程的消息，累积有关服务器活动的统计信息，并将它们写入文件。可以使用 pg_stat... 视图查看这些统计信息。此进程从 PostgreSQL 15 开始消失。
- walwriter：定期醒来并写入 WAL 缓冲区，以减少其他进程必须写入的 WAL 数量。此外，它还确保异步提交事务的提交 WAL 记录的写入。
- archiver：存档 WAL 文件。
- autovacuum 启动器：在启用 autovacuum 时始终运行。安排 autovacuum 工作器运行。
- autovacuum 工作器：连接到启动器中确定的数据库，检查系统目录以选择表，并对其进行 vacuum 操作。
- 并行工作器：执行并行查询计划的一部分。
- walreceiver：在备用服务器上运行。从 walsender 接收 WAL，将其存储在磁盘上，并告诉启动进程根据它继续恢复。
- walsender：在主服务器上运行。将 WAL 发送到单个 walreceiver。
- 逻辑复制启动器：在订阅者上运行。协调逻辑复制工作器运行。
- 逻辑复制工作器：在订阅者上运行。每个订阅一个应用工作器从发布者上的 walsender 接收逻辑更改并应用它们。一个或多个 tablesync 工作器执行每个表的初始表复制。
后台工作器：运行系统提供的或用户提供的代码。例如，用于并行查询和逻辑复制。

记忆

共享内存
- 共享缓冲区：存储数据文件（主、FSM 和 VM 分支）的缓存副本。
- WAL 缓冲区：事务将 WAL 记录放在这里，然后再将其写入磁盘。
- 其他各种区域：一个大型共享内存段被划分为用于特定用途的区域。
- 可以使用 pg_shmem_allocations 检查区域的分配。
本地记忆
- 工作内存：为查询操作（例如排序和哈希）分配。使用 work_mem 和 hash_mem_multiplier 参数配置。
- 维护工作内存：为维护操作分配，例如 VACUUM、CREATE INDEX 和 ALTER TABLE。使用 maintenance_work_mem 参数配置。
- 临时缓冲区：为缓存临时表块分配。使用 temp_buffers 参数配置。
- 其他各种区域：为特定用途分配内存上下文（例如，来自客户端的消息、事务、查询计划、执行状态）。每个会话可能存在数百个内存上下文。
- 可以使用 pg_backend_memory_contexts 视图检查当前会话的内存上下文的分配和使用情况，并使用函数 pg_log_backend_memory_contexts(backend_pid) 检查其他会话的内存上下文。

读取和写入数据库数据

读取
- 首先，在共享缓冲区中搜索包含目标块的缓冲区。如果找到，则将其返回给请求者。
- 否则，从空闲缓冲区列表中分配一个缓冲区，并将目标块从数据文件读取到缓冲区中。
- 如果没有空闲缓冲区，则逐出并使用已用缓冲区。如果它很脏，则将缓冲区写入磁盘。
写入
- 找到目标共享缓冲区，修改其内容，并将更改写入 WAL 缓冲区。
- 修改事务将其 WAL 记录从 WAL 缓冲区写入磁盘，包括提交 WAL 记录。
- 修改后的脏共享缓冲区由后台写入器、检查点或任何其他进程刷新到磁盘。这与事务完成是异步的。
任何后端都可以读取和写入共享缓冲区、WAL 缓冲区、数据和 WAL 文件。与其他一些 DBMS 不同，写入不是由特定后台进程独占执行的。
数据库数据文件一次读取和写入一个块。没有多块 I/O。
一些操作绕过共享缓冲区：索引创建期间的索引写入、CREATE DATABASE、ALTER TABLE ... SET TABLESPACE

查询处理

客户端连接到数据库，将查询（SQL 命令）发送到服务器，并接收结果。
解析器首先检查查询的语法是否正确。然后，它解释查询的语义以了解引用了哪些表、视图、函数、数据类型等。
重写系统（重写器）根据存储在系统目录 pg_rewrite 中的规则转换查询。一个例子是视图：访问视图的查询被重写为使用基表。
计划器/优化器创建一个查询计划。
执行器执行查询计划并将结果集返回给客户端。

注意事项

每个会话都在与单个数据库的连接上运行。因此，它无法访问其他数据库上的表。但是，一个会话可以连接到另一个数据库，并通过像 postgres_fdw 这样的外部数据包装器创建另一个会话，并访问那里的表。例如，一个 SQL 命令可以将本地数据库上的一个表与远程数据库上的另一个表联接。
每个 SQL 命令基本上只使用一个 CPU 内核。并行查询和一些实用程序命令（如 CREATE INDEX 和 VACUUM）可以通过运行后台工作器来使用多个 CPU 内核。

参考

PostgreSQL 文档

其他资源

可靠性和可用性

连接

排查连接问题时要检查的内容

数据库服务器是否可以访问？
- telnet <host> <port> 或
- nc -zv <host> <port>
- 使用 traceroute（Unix/Linux）或 tracert（Windows），指定主机和中间路由器允许的协议。
主机和端口是否正确？
服务器是否正在运行？
- pg_ctl status
服务器端防火墙是否允许通过该端口进行通信？
客户端防火墙是否允许与服务器端口进行通信？
pg_hba.conf 中是否有任何条目允许 SSL/非 SSL、客户端主机、数据库和用户的组合？
listen_addresses 参数是否配置为允许通过所需的 IP 地址（包括 IPv4 和/或 IPv6）进行连接？
数据库、用户名和密码是否正确？
用户是否有权连接到数据库？
- 使用 psql 的 \l 或 pg_database.datacl 检查权限
数据库服务器是否有足够的 CPU 和内存资源？
是否已达到最大连接限制？
- max_connections 和 superuser_reserved_connections 参数（在实例级别）
- CREATE/ALTER DATABASE CONNECTION LIMIT（在数据库级别）
- CREATE/ALTER ROLE CONNECTION LIMIT（在用户级别）

连接终止和查询取消

关闭连接时，任何未完成的事务都会回滚。
终止连接 (pg_terminate_backend()) 和取消查询 (pg_cancel_backend()) 并不总是有效。例如，它们在后端进程在不可中断部分运行时不起作用，例如等待获取轻量级锁、对网络存储设备进行读写系统调用，以及没有取消点的循环。
在适当的级别（语句、用户、数据库、实例）设置 statement_timeout。在较宽的级别上不建议使用短超时，因为它会取消有意进行的长时间运行的查询。
适当地设置客户端超时。
适当地设置服务器端超时。
- tcp_keepalives_idle、tcp_keepalives_interval、tcp_keepalives_count
  - TCP 保活在 TCP 连接空闲时有效。当套接字连接正在建立时，或者已发送一些数据并在等待其 ACK 时，它不起作用。
  - 有效超时是 tcp_keepalives_idle + tcp_keepalives_interval * tcp_keepalives_count。
- tcp_user_timeout
  - 设置 TCP 重传超时。在 Linux 上相对较新。
  - 在 TCP 保活无济于事时派上用场。即当套接字连接正在建立时，或者已发送一些数据并在等待其 ACK 时。
  - 与 TCP 保活一起使用时会令人困惑，因为这会更改 TCP 保活超时的时间。将 tcp_user_timeout 设置为 tcp_keepalives_idle + tcp_keepalives_interval * tcp_keepalives_count 会比较安全。
- authentication_timeout
- idle_in_transaction_session_timeout
- idle_session_timeout
- client_connection_check_interval

连接故障转移

客户端驱动程序允许在连接字符串中指定多个主机。
- 连接超时应用于连接字符串中的每个主机。因此，如果在列表中运行的主机之前有很多失败的主机，则建立连接可能需要意外的长时间。
故障转移后恢复会话状态：会话变量、准备好的语句、临时表、可保持游标（使用 DECLARE CURSOR WITH HOLD 创建）、建议锁、会话用户（使用 SET SESSION AUTHORIZATION 设置）、当前用户（使用 SET ROLE 设置）。
小心事务重试：在事务提交期间发生数据库服务器故障转移时，事务是否已提交或回滚是未知的。
- pg_xact_status( xid8 ) 可用于确定事务结果。

WAL

WAL 的作用

崩溃恢复、存档恢复（时间点恢复：PITR）和复制
无论事务是否已提交，更新都会被重做。
与其他流行的 DBMS 不同，没有撤消日志（前镜像）或操作。
- 因此，事务回滚和崩溃恢复速度很快。
- 中止事务所做的更改会保留在内存和磁盘中，但由于 MVCC，它们对其他事务是不可见的。

WAL 结构

8 KB 块的序列。
每个块可以包含多个 WAL 记录。此外，每个 WAL 记录可以跨越多个块。
内存和存储中的内容相同。
WAL 缓冲区是内存中连续的块数组。它以循环方式使用。
存储上的 WAL 被分成 WAL 段文件。每个 WAL 段文件默认大小为 16 MB，可以通过 `initdb` 的 `--wal-segsize=size` 选项进行配置。

写入 WAL

在内存中，修改共享缓冲区中的数据页，然后将更改写入 WAL 缓冲区。
WAL 缓冲区始终按顺序写入 WAL 文件（没有随机写入）。
在将共享缓冲区中的脏数据页写入磁盘之前，首先写入所有 WAL 记录，直到影响数据页的最新记录。此规则是 WAL（预写日志）。
- 每个数据页在其页头中包含代表对其最新更新的 WAL 记录的位置（LSN）。这是页 LSN。
- LSN（日志序列号）：一个无符号的 8 字节整数。它表示 WAL 段、块以及该块中的偏移量。
如果写入 WAL 文件失败，实例将崩溃并显示 PANIC 消息。
由于以下原因，WAL 容量可能会超过 max_wal_size：
- 大量写入，例如使用 COPY 加载数据
- 无法归档 WAL 文件
- wal_keep_size 的值很大
- 未使用的复制槽
SELECT 在以下情况下可以修改数据页并写入 WAL：
- 获取行锁，例如 SELECT FOR UPDATE。它们在元组头中设置 xmax，并且可能更新 MultiXact 数据结构。
- 修剪行指针并对页进行碎片整理。
- 将提示位设置为元组头。在启用页校验和时会发出 WAL。

事务

ACID：它们的属性

原子性：事务回滚和数据库恢复
一致性：完整性约束和触发器，例如非 NULL、检查、主键/唯一/外键约束
隔离性：MVCC 和锁
持久性：WAL

事务 ID（XID）

事务在首次修改数据时会被分配一个 XID，例如在 INSERT、UPDATE、DELETE 和 SELET FOR SHARE/UPDATE 中。
- XID 分配与 XidGen LWLock 序列化。
- XID 分配通常非常快，但有时可能会遇到问题。它通过 SLRU 缓存每 32K 个事务分配并清零一个新的提交日志（clog）页。该 clog 页分配可能会为页替换刷新脏页。
- 这会导致响应时间的不可预测的峰值。
只读事务不分配 XID。它们不受分配新 XID 的 LWLock 争用。
XID 存储在元组头中，并作为 xmin 和 xmax 系统列可见（`SELECT xmin, xmax, * FROM mytable`）。
- xmin 是创建元组的事务的 XID（INSERT、UPDATE、COPY）。
- xmax 是执行以下操作的事务的 XID：
  - 删除元组（DELETE、UPDATE）。
  - 锁定元组（例如 SELECT FOR SHARE/UPDATE）
特殊 XID 值
- 0：无效 XID
- 1：引导 XID。在 `initdb` 期间由引导处理使用。
- 2：冻结 XID：最近版本的 PostgreSQL 只对序列元组使用它，不对表使用它。

MVCC：多版本并发控制

MVCC 的主要优势是“读取永远不会阻塞写入，写入永远不会阻塞读取”。也就是说，同一行的 UPDATE/DELETE 和 SELECT 不会互相阻塞。
- 同一行的写入会互相阻塞。
- 在传统的基于锁的并发控制中，同一行的读取和写入会冲突。
总体工作原理
- 插入和更新一行会创建该行的新版本。更新会保留旧的行版本供其他正在运行的事务使用。（多版本）
- 创建事务的 XID 设置为新行版本的 xmin 字段。
- 新行版本在提交之前仅对创建它的事务可见。
- 创建事务提交后，所有新的后续事务都将能够看到新行版本。其他现有事务将继续看到旧的行版本。旧的行版本现在是一个“死元组”。
- 删除一行不会删除行版本。它将删除事务的 XID 设置为行版本的 xmax 字段。
- 删除的行版本在提交之前仅对删除它的事务不可见。
- 删除事务提交后，所有新的后续事务都将无法看到行版本。其他现有事务将继续看到行版本。行版本现在是一个“死元组”。
- 最后，当没有剩余的事务能够看到死元组时，vacuum 会将其删除。
元组可见性工作原理
- 每个事务使用它自己的快照、提交日志（clog）以及目标元组头中的 xmin 和/或 xmax 来确定它是否可以看到给定的行版本。
- 什么是快照
  - 特定时间点运行的事务的快照。
  - 您可以运行 “`SELECT pg_current_snapshot();`” 来查看当前事务的快照。
  - 快照的文本表示形式为 xmin:xmax:xip_list。例如，10:20:10,14,15。
  - xmin：仍然处于活动状态的最低事务 ID。所有小于 xmin 的事务 ID 要么已提交并可见，要么已回滚并已死。
  - xmax：已完成的最高事务 ID 加 1。所有大于或等于 xmax 的事务 ID 在快照时尚未完成，因此不可见。
  - xip_list：快照时正在进行的事务。事务 ID 满足 xmin <= X < xmax 且不在此列表中的事务在快照时已经完成，因此根据其提交状态是可见的还是已死的。
  - 在 READ COMMITTED 事务中，在每个 SQL 语句开始时都会获取快照。
  - 在 REPEATABLE READ 或 SERIALIZABLE 事务中，在第一个 SQL 语句开始时获取快照，并在整个事务中使用。
- 什么是提交日志（clog）
  - 一个表示事务状态的位数组。
  - 使用两个位来指示事务的结果：正在进行、已提交、已中止、已提交。
  - 存储在 $PGDATA/pg_xact/ 中的一组文件中。
  - 缓存在 128 个 8 KB 页的内存缓冲区中。
- 当快照显示目标事务已完成时，会参考 clog。
- 根据快照和 clog，已提交事务的更改可见，已中止或正在运行的事务的更改不可见。
- 实际的元组可见性要复杂得多…

提示位

提示位是元组头中的 infomask 字段中的位，有助于确定元组可见性。
它们用于性能优化。对数据正确性不是必需的。
它们表示 xmin 或 xmax 指示的事务是已提交还是已中止。有四个标志位
1. `HEAP_XMIN_COMMITTED`：xmin 事务已提交
2. `HEAP_XMIN_INVALID`：xmin 事务已中止
3. `HEAP_XMAX_COMMITTED`：xmax 事务已提交
4. `HEAP_XMAX_INVALID`：xmax 事务已中止
如何使用提示位
1. 事务检查提示位以查看 xmin 和/或 xmax 事务是已提交还是已中止。
2. 如果提示位已设置，则已完成。
3. 否则，检查提交日志（$PGDATA/pg_xact/）以及可能检查子事务层次结构（$PGDATA/pg_subtrans/）以确定事务结果。这是一个昂贵的操作。
4. 设置提示位。它们稍后将持久保存到磁盘。
设置提示位会写入数据页，并且如果启用页校验和，还会写入 WAL。

锁

即使请求的模式与持有的锁兼容，锁请求也可能等待。

问：您认为事务 3 会继续执行查询吗？
1. 事务 1：一个针对 mytable 正在运行的长时间运行的 `SELECT`。
2. 事务 2：运行 “`ALTER TABLE mytable ADD COLUMN new_col int;`”。由于 ALTER TABLE 的 Access Exclusive 锁请求与事务 1 的 `SELECT` 持有的 Access Share 锁冲突，因此被阻塞。
3. 事务 3：针对 mytable 运行一个简短的 `SELECT` 查询。
答：事务 3 会等到事务 2 完成，因为事务 2 更早出现并且正在等待。
后面的请求者会尊重等待队列中更早的等待者，不会超过他们。否则，更早的请求者可能会无端地等待很长时间。
因此，即使是预计会快速执行的 DDL，也要在以下时间执行：
- 非高峰时段，或
- 使用锁超时。例如，在 DDL 之前运行 “`SET lock_timeout = '5s';`”。如果超时，则重试 DDL。
轻量级锁并非如此。在极端情况下，对 LWLock 的 Exclusive 模式请求可能会由于后面一个接一个的 Share 模式请求者而等待数十秒。

已准备好的事务潜伏着持有锁

已准备好的事务会继续持有锁，但它不会出现在 pg_stat_activity 中，因为它没有关联的会话。
pg_locks 会将已准备好的事务显示为一个带有 NULL pid 的条目。检查 pg_prepared_xacts。

数据完整性验证

数据校验和

目的和用法
- 每个关系的数据页，包括所有分支，在其页头中都有一个 16 位校验和。
- 旨在检测 I/O 系统（例如，卷管理器、文件系统、磁盘驱动器、存储固件、存储设备等）造成的损坏。早期检测可以防止损坏传播。
- 并非旨在检测内存错误。
- 在整个集群级别启用，要么使用 `initdb` 的 -k/--data-checksums，要么在数据库服务器关闭时使用 `pg_checksums`。默认情况下处于禁用状态，因为其性能开销。
- 运行 “`SHOW data_checksums`” 以了解是否启用了数据校验和。它返回 on 或 off。
工作原理
- 校验和是根据页内容计算的，并在即将将数据页写入磁盘时设置。
- 在从磁盘读取页之后，通过比较页头中设置的值和新计算的值来验证校验和。
- 如果验证失败，将发出 WARNING 和 ERROR 消息，导致查询失败。
- 如果在执行校验和验证之前，页头未能通过基本完整性检查，则查询将失败并显示相同的 ERROR 消息，而不会显示指示校验和失败的 WARNING。

WAL CRC

WAL 在每个 WAL 记录头中使用 32 位 CRC。
CRC 在将 WAL 记录放入 WAL 缓冲区时设置，并在读取 WAL 记录时验证。

用于检测、绕过或修复数据损坏的实用程序（有些可能很危险！）

附加模块
- amcheck：检测堆（表、序列、物化视图）和 B 树索引的逻辑损坏。
- pg_surgery：`heap_force_kill()` 和 `heap_force_freeze()` 分别强制删除和冻结堆元组。
- pg_visibility_map：`pg_check_frozen()` 和 `pg_check_visible()` 检测可见性映射损坏。
配置参数
- ignore_checksum_failure
- zero_damaged_pages
- ignore_system_indexes

备份和恢复

备份和恢复方法

文件系统级备份（二进制格式）
使用 pg_dump/pg_dumpall 的 SQL 转储（文本格式）
连续归档（二进制格式）

备份和恢复方法的特点

SQL 转储和连续归档可以在线执行。文件系统级备份需要关闭数据库服务器。
SQL 转储可以选择性地备份和还原单个表。其他方法不能只备份或还原某些单个表或表空间。
SQL 转储通常会更小，因为 SQL 脚本只需要包含索引创建命令，而不是索引数据。
SQL 转储可以加载到更新的主要版本数据库中。
SQL 转储可以将数据库传输到不同的机器架构，例如从 32 位服务器到 64 位服务器。
连续归档可以执行 PITR。数据库集群可以恢复到最新状态或某个时间点。
由 pg_dump 创建的转储是一致的；每个数据库的转储是在 pg_dump 启动时对数据库的快照。pg_dumpall 依次对每个数据库调用 pg_dump，因此不能保证整个数据库集群的一致性。
pg_dump 在单个事务中转储数据库中的所有数据，发出许多 SELECT 命令。这个长时间运行的事务可能会
- 阻止其他需要强锁定模式的操作，例如 ALTER TABLE、TRUNCATE、CLUSTER、REINDEX。
- 导致表和索引膨胀，因为 vacuum 无法删除死元组。
连续归档的 pg_backup_start() 和 pg_basebackup 在开始时执行检查点。用户可以在“快速”和“分散”之间选择检查点速度。
归档恢复以及崩溃恢复都会清空未日志化的关系的内容。SQL 转储会输出未日志化表的內容。
pg_dumpall 的 --no-role-passwords 选项使用 pg_roles 而不是 pg_authid 来转储数据库角色。这允许在 DBaaS 等受限环境中使用 pg_dumpall，在这些环境中，用户不允许读取 pg_authid 以保护密码。恢复的角色将具有 NULL 密码。

流式复制

架构

拓扑结构
- 仅复制整个数据库集群。部分复制不可行。
- 一台主服务器复制到一台或多台备用服务器。
- 每个备用服务器都从一个主服务器复制。
- 备用服务器可以级联更改到其他备用服务器。
- 主服务器不知道备用服务器的位置。备用服务器连接到由 primary_conninfo 参数指定的主服务器。
  - 例如：primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass'
主服务器和备用服务器版本
- 不同的主要版本不兼容。
- 不同的次要版本将兼容，因为磁盘格式相同，但不提供正式支持。建议将主服务器和备用服务器保持在相同的次要版本。
- 在次要版本升级期间，最好先更新备用服务器。
流程和数据流
- 在服务器启动时，备用服务器首先从归档中读取并应用 WAL，然后从 $PGDATA/pg_wal/ 读取，然后启动一个 walreceiver，它连接到主服务器并从那里流式传输 WAL。如果复制连接终止，它会每隔 5 秒重复此循环，这可以通过 wal_retrieve_retry_interval 配置。
- 主服务器在接受来自 walreceiver 的连接请求时会生成 walsender。
- walsender 读取 WAL 并将其发送到 walreceiver。
- walreceiver 将流式传输的 WAL 写入并刷新到 $PGDATA/pg_wal/，并通知启动进程。
- 单个启动进程读取并应用 WAL。
- walreceiver 定期向 walsender 通知复制进度 - 它已写入、刷新和应用了多少 WAL。
- 级联备用服务器也运行 walsender 和 walreceiver。
复制用户
- 复制用户需要 REPLICATION 角色属性。
- REPLICATION 允许用户读取所有用于复制的数据，但不能用于 SELECT 查询。

一般管理

备用服务器是只读的。包括角色在内的任何对象都无法仅在备用服务器上创建。
max_wal_senders 应该略高于备用服务器的数量，以便备用服务器可以在临时意外断开连接后接受连接，而断开的 walsender 仍然存在。
可以在备用服务器上进行备份。
不需要 archive_timeout 来减少数据丢失窗口。
级联复制减轻了主服务器的负载。
主服务器上的 WAL
- 没有任何措施，主服务器不关心备用服务器，并会删除/回收备用服务器仍然需要的旧 WAL 文件。
- 如果备用服务器请求已被删除的 WAL，主服务器会发出类似 "ERROR: requested WAL segment 000000020000000300000041 has already been removed" 的消息。
- 要让主服务器保留 WAL 文件，可以使用复制槽（推荐）或设置 keep_wal_size（旧方法）。
- max_slot_wal_keep_size 对复制槽保留的 WAL 量进行限制。
同步复制
- 如果同步备用服务器不可用，事务会在提交期间挂起。
- 要恢复挂起的事务，请删除 synchronous_standby_names 设置并重新加载配置。这会使复制变为异步。

复制延迟的原因

硬件配置：服务器、存储和网络
主服务器上的工作负载很重：主服务器上生成的 WAL 量很大，以至于单个启动进程无法跟上。
- 设置 wal_compression = on 以减少 WAL 的数量。
从缓慢的归档中检索 WAL：备用服务器无法从主服务器获取 WAL，因此必须从 WAL 归档中获取。
恢复冲突：某些操作的重播可能会被备用服务器上运行的查询阻塞。
- 这与使用热备用时相关。
- 减少 max_standby_archive_delay 和 max_standby_streaming_delay 以取消冲突的查询并尽早恢复 WAL 重播。

热备用

在服务器处于归档恢复或备用模式时运行只读查询的能力。
要确定服务器是否处于热备用状态，请使用 PostgreSQL 14 及更高版本中的 "SHOW in_hot_standby"，或使用 "SELECT pg_is_in_recovery()"。
恢复冲突
- WAL 重播与备用服务器上的查询之间的冲突。
- 延迟 WAL 重播或取消查询。
- 导致恢复冲突的主服务器上的操作包括
  - 需要独占锁的操作：DDL、LOCK、vacuum（包括 autovacuum）对文件的截断
    - 独占锁请求会被 WAL 日志记录并由备用服务器重播。
  - 删除备用服务器上的查询在其中放置临时文件的表空间
  - 删除备用服务器上客户端已连接到的数据库
  - 清理备用服务器事务仍然可以根据其快照看到的死元组
  - 清理备用服务器事务具有缓冲区锁定（例如，光标位于页面上）的页面的 vacuum
- 发生恢复冲突时会发生什么
  - WAL 应用最多等待 max_standby_archive_delay 和 max_standby_streaming_delay 指定的时间段（删除 DATABASE 和 ALTER DATABASE SET TABLESPACE 的重播除外）。
  - 然后，在重播 DROP DATABASE 的情况下，会终止冲突的会话，或者在其他情况下，会取消冲突的查询。
  - 如果空闲会话持有锁，该会话也会被终止。
- 监视恢复冲突
  - 备用服务器上的 pg_stat_database_conflicts 显示由于每种类型的恢复冲突而取消的查询数量。
  - "log_recovery_conflict_waits = on" 会记录 WAL 应用等待时间超过 deadlock_timeout 并且等待已完成的消息。
    - LOG: recovery still waiting after 1.023 ms: recovery conflict on snapshot
    - DETAIL: Conflicting processes: 1234, 1235
    - LOG: recovery finished waiting after 3.234 ms: recovery conflict on snapshot
- 最小化因恢复冲突而取消的查询数量
  - 避免需要独占锁的操作。例如：ALTER TABLE、VACUUM FULL、CLUSTER、REINDEX、TRUNCATE
  - 通过在主服务器上设置 vacuum_truncate 存储参数来禁用 vacuum 对文件的截断。
    - 例如：ALTER TABLE some_table SET (vacuum_truncate = off);
  - 在备用服务器上设置 hot_standby_feedback = on。
    - 将最旧的 XID 发送到主服务器，反映在 pg_stat_replication.backend_xmin 中，vacuum 在决定删除死元组时会考虑这一点。
    - 可能会导致表膨胀，因为死元组删除被延迟。
    - 无法阻止所有冲突。
  - 调整备用服务器上的 max_standby_streaming_delay/max_standby_archive_delay。
  - 调整主服务器上的 vacuum_defer_cleanup_age。
- 理想情况下，应该拥有独立的备用服务器，一些用于高可用性，另一些用于容忍陈旧数据的只读工作负载。

监视复制延迟

pg_stat_replication
- 不仅在主服务器上可用，而且在级联备用服务器上也可用。
- pg_current_wal_lsn 与视图的 sent_lsn 字段之间的较大差异可能表明主服务器负载过重。
- 备用服务器上 sent_lsn 与 pg_last_wal_receive_lsn 之间的差异可能表明网络延迟，或者备用服务器负载过重。
pg_stat_wal_receiver
- pg_last_wal_replay_lsn() 与视图的 flushed_lsn 之间的较大差异表明，WAL 的接收速度快于重播速度。
- 例如：SELECT pg_wal_lsn_diff(pg_last_wal_replay_lsn(), flushed_lsn) FROM pg_stat_wal_receiver;
pg_stat_wal
- 检查为繁重的写入工作负载生成的 WAL 的数量。
存储写入延迟、IOPS 和吞吐量以检查繁重的写入活动。
pg_stat_database_conflicts

逻辑复制

架构

使用发布和订阅模型
- 发布是一种要复制其更改的表的集合。
- 订阅表示与发布者及其订阅的发布的连接。
- 一个发布者发布一个或多个发布。
- 一个订阅者拥有一个或多个订阅。
- 一个发布可以有多个订阅者。
- 一个订阅可以订阅多个发布。
- 发布可以选择将它们产生的更改限制为 INSERT、UPDATE、DELETE 和 TRUNCATE 的任何组合。
- 发布可以限制要复制的行和列。
流程和数据流
- 涉及的进程：发布者上的 walsender，订阅者上的订阅工作者（应用工作者、tablesync 工作者）。
- 即使使用了一些与 walreceiver 相关的参数，walreceiver 也不会出现。
1. 在订阅者上的服务器启动时，除非 max_logical_replication_workers 为 0，否则会启动逻辑复制启动器。
2. 逻辑复制启动器为每个启用的订阅启动一个应用工作者。
3. 应用工作者连接到发布者。
4. 应用工作者为尚未完成初始同步的表启动 tablesync 工作者。这些 tablesync 工作者都连接到发布者。
5. 发布者为来自订阅工作者的每个连接请求生成一个 walsender。
6. tablesync 工作者的 walsender 会将表的初始副本发送到 tablesync 工作者。（初始数据同步/复制）
7. walsender 读取 WAL，将更改解码为逻辑复制协议格式，并将它们存储在逻辑解码工作内存中，并可能存储在文件中。当事务提交时，walsender 会将其解码后的更改发送到订阅工作者。
8. 订阅工作者会应用收到的更改。

一般管理

主要限制
- 发布只能包含表。
- DDL 不会被复制。
  - 先在订阅者上添加表列，然后再在发布者上添加。删除表列时，请反转顺序。
- 序列数据不会被复制。
复制标识
- 已发布的表必须具有复制标识才能复制 UPDATE 和 DELETE 操作。
- 用作标识订阅者上要更新或删除行的键。
- 如果已发布的表没有复制标识，UPDATE 和 DELETE 操作将在发布者上失败。INSERT 操作会成功。
- 可以是主键（默认）、唯一索引或整行。
- 可以通过 ALTER TABLE REPLICA IDENTITY 配置。
- 复制标识列的旧值会被 WAL 日志记录。
调整性能
- max_sync_workers_per_subscription
  - 多个 tablesync 工作者（每个表一个）将根据 max_sync_workers_per_subscription 配置并行运行。
  - 当订阅中存在许多表时，这可能有助于加快初始表同步速度。
- logical_decoding_work_mem
  - 检查 pg_stat_replication_slots 以查看溢出到磁盘的事务。如果 spill_txns、spill_count 和 spill_bytes 较高，请考虑增加此参数的值。

复制冲突

在订阅者上应用传入的更改可能会由于约束违反或权限不足而失败。这就是冲突。
解决冲突
1. 如果订阅尚未开始，请运行 "ALTER SUBSCRIPTION name DISABLE;" 禁用它。可以将订阅配置为在应用工作者检测到任何错误时自动禁用。运行 "ALTER SUBSCRIPTION ... WITH (disable_on_error = on);"
2. 在服务器日志中查找复制源名称和冲突事务的结束 LSN。
3. 执行以下任一操作
  - 通过运行 "ALTER SUBSCRIPTION ... SKIP (冲突事务的结束 LSN)" 或 "SELECT pg_replication_origin_advance(rep_origin_name, 冲突事务结束的下一个 LSN)" 从发布者跳过事务
  - 修复订阅者上的表数据。
4. 通过运行 "ALTER SUBSCRIPTION name ENABLE;" 启用订阅。

监控

发布者
- pg_stat_replication_slots
  - 每个逻辑复制槽位一行。已解码数据的总事务数和数量，以及溢出到磁盘的事务数和已解码数据量。
- 用于流复制的其他系统和统计信息视图。
订阅者
- pg_stat_subscription_stats
  - 每个订阅一行。初始表同步期间和应用更改期间的错误数量。
- pg_stat_subscription
  - 每个订阅工作者（应用工作者、表同步工作者）一行。正在复制的表、发送/报告的最后一个 WAL 位置等。
- pg_subscription_rel
  - 每个已订阅表一行。表的状态，用于了解初始同步是否正在进行。

参考

PostgreSQL 文档

连接

WAL

事务

锁

数据完整性验证

备份和恢复

流式复制

逻辑复制

安全

验证

更改密码时加密密码

CREATE/ROLE ... PASSWORD 'some_password' 将发送和记录指定的密码原样。因此，指定未加密的密码很危险。
这些语句接受加密的密码（使用 MD5 或 SCRAM 哈希）。
psql 的 \password 很方便
- psql 运行 "SHOW password_encryption" 以确定密码哈希方案（MD5 或 SCRAM），对提供的密码进行哈希处理，然后发出 ALTER 命令。
- 哈希后的密码仍然可能出现在服务器日志中。临时将 log_min_error_statement 设置为 'PANIC' 可以防止这种情况。

数据库内验证配置文件非常有限

PostgreSQL 仅提供通过 CREATE/ROLE VALID UNTIL 'some_timestamp' 来过期密码的功能。
不提供以下功能
- 强制密码复杂度
- 当在特定时间段内失败的登录尝试次数超过阈值时，锁定用户帐户
- 限制在特定时间段内重复使用相同的密码

实现密码复杂度：使用以下任一方法

PostgreSQL 的可信语言扩展 (pg_tle)
- 用户创建使用 SQL、PL/pgSQL、JavaScript 等语言检查密码的扩展。
- 这可用于托管服务。
外部身份服务，例如 LDAP 或 Kerberos
证书验证
- 使用 SSL 客户端证书执行验证。不需要密码。

跟踪失败的登录尝试：执行以下任一操作

在服务器日志中搜索包含“密码验证失败”或 SQLSTATE 28P01（无效密码）的消息
- 使用 SQLSTATE 比使用消息文本更好，因为消息可能会根据服务器版本和 lc_message 设置而有所不同。（将 %e 添加到 log_line_prefix 以发出 SQLSTATE。）
PostgreSQL 的可信语言扩展 (pg_tle)
- 用户使用客户端验证钩子，并创建记录和检查登录失败的扩展。

授权

角色权限默认继承

在 SQL 标准和其他 DBMS 中，需要使用 SET ROLE 来获取另一个角色的权限。
在 PostgreSQL 中，角色会自动继承其所属的其他角色的权限。这可能令人惊讶。
要近似于 SQL 标准，请对用户使用 NOINHERIT，对角色使用 NOINHERIT。

预定义角色

提供一些角色，以向非超级用户授予部分管理权限。
它们可以通过 GRANT 授予。
代表性角色是
- pg_monitor：可以读取各种有用的配置设置、统计信息和其他系统信息。
- pg_signal_backend：可以向其他后端发送信号以取消查询或终止会话。
- pg_read_server_files、pg_write_server_files 和 pg_execute_server_program：以数据库运行的用户的身份访问数据库服务器上的文件并运行程序。例如，这些操作允许将数据从服务器上的文件复制到文件或从文件复制到文件，或者运行其他程序（例如 gzip 和 curl）。

默认权限

ALTER DEFAULT PRIVILEGES 可以设置将来创建的数据库对象的默认权限。
- 例如，ALTER DEFAULT PRIVILEGES IN SCHEMA app_schema GRANT INSERT, UPDATE, DELETE, SELECT ON TABLES TO app_user;
目标数据库对象是模式、表、视图、序列、函数和类型。
不会更改现有数据库对象的权限。

参考

PostgreSQL 文档

[PostgreSQL 用户帐户https://postgresql.ac.cn/docs/current/postgres-user.html]
[数据库角色https://postgresql.ac.cn/docs/current/user-manag.html]
[客户端验证https://postgresql.ac.cn/docs/current/client-authentication.html]
[权限https://postgresql.ac.cn/docs/current/ddl-priv.html]
[行级安全策略https://postgresql.ac.cn/docs/current/ddl-rowsecurity.html]
[加密选项https://postgresql.ac.cn/docs/current/encryption-options.html]
[使用 SSL 的安全 TCP/IP 连接https://postgresql.ac.cn/docs/current/ssl-tcp.html]
[使用 GSSAPI 加密的安全 TCP/IP 连接https://postgresql.ac.cn/docs/current/gssapi-enc.html]

验证

授权

隐藏数据

可管理性

记忆

大型结果集会导致客户端内存不足

运行 SELECT 时，psql 会检索整个结果集并将所有行存储在客户端内存中。
使用 "psql -v FETCH_COUNT=100 ..." 等 FETCH_COUNT 变量，psql 会使用游标并发出 DECLARE、FETCH 和 CLOSE 命令以逐段检索结果集。
客户端驱动程序具有类似的功能，例如 psqlODBC 的 UseDeclareFetch 和 PgJDBC 的 defaultRowFetchSize 连接参数。

服务器端内存不足 (OOM) 问题的常见原因

大量连接
- 即使是空闲连接也可能会继续占用大量内存。PostgreSQL 在会话期间将数据库对象元数据保存在内存中。这样做是为了提高性能。您可以通过 CacheMemoryContext 膨胀来注意到这一点。
- 如果使用连接池，随着时间的推移，许多连接可能会消耗大量的内存。这是因为连接是从池中随机选择的，用于访问某些关系，然后释放回池，这会导致许多会话累积许多关系的元数据。
work_mem 的高值
- 建议不要在实例级别 (postgresql.conf) 或数据库级别 (ALTER DATABASE) 设置 work_mem 的高值。许多会话可能同时分配该内存量。更糟糕的是，每个 SQL 语句都可以在并行运行这种排序和/或哈希操作，每个操作都可以分配与 work_mem 一样多的内存。
- 对于基于哈希的操作，最多将分配 work_mem * hash_mem_multiplier 字节的工作内存。
max_locks_per_transaction 较低
- 每个可锁定对象（例如，表、索引、序列、XID，但不包括行）在被锁定时会在锁表中分配一个条目。该条目表示可锁定对象、授予者、等待者以及授予/请求的锁定模式。
- 锁表在共享内存中分配。它的大小在服务器启动时固定。
- max_locks_per_transaction 的默认值为 64。这意味着每个事务预计会锁定 64 个或更少的对象。
- 锁表中的条目数量为 (max_connections + max_prepared_transactions + alpha) * max_locks_per_transaction。
- 如果可用，一个事务可以使用超过 max_locks_per_transaction 个条目。
- 如果许多并发事务中的每一个都可能访问更多对象，例如，接触数百或数千个分区，请增加 max_locks_per_transaction。

无法检索大型 bytea 值

例如，在成功插入 550 MB 的 bytea 列值后，获取它失败并出现类似“无效内存分配请求大小 1277232195”的错误消息。
为什么？
- 当 PostgreSQL 服务器将查询结果发送到客户端时，它会将数据转换为文本格式或以二进制格式返回。
- psql 和客户端驱动程序指示服务器使用文本格式。
- PostgreSQL 在将 bytea 数据转换为文本格式时使用十六进制或转义格式。默认格式为十六进制。十六进制和转义格式分别使用 2 和 4 个字节来表示文本格式中的每个原始字节。
  - 例如：SELECT 'abc'::bytea; 返回 \x616263
- PostgreSQL 服务器会分配一个连续的内存区域来将每个列值转换为文本格式。此分配大小限制为 1 GB - 1。该限制与 TOAST 中可变长度数据类型的处理有关。
- 由于此限制，PostgreSQL 无法以文本格式返回超过 500 MB 的 bytea 数据。

存储

存储空间不足的常见原因

表膨胀，因为 vacuum 无法删除死元组：死元组仍然存在的原因在单独的文档中描述。
WAL 累积：WAL 卷持续增长的原因在单独的文档中描述。
服务器日志文件
- 由于 pgAudit、auto_explain 和其他日志参数（如 log_statement 和 log_min_duration_statement）导致的过度日志记录
- 日志轮转和清除配置不正确：log_rotation_age、log_rotation_size、log_truncate_on_rotation
创建临时文件
- work_mem 很小，或者查询计划很糟糕。
- 保持可保留游标打开。
  - 例如：DECLARE CURSOR cur WITH HOLD FOR SELECT * FROM mytable; COMMIT;
  - 在提交过程中，可保留游标的结果集存储在大小为 work_mem 的工作内存区域中，超过 work_mem 的内容将溢出到临时文件中。
- 使用以下方法检查临时文件的使用情况
  - pg_stat_database 的 temp_files 和 temp_bytes
  - log_temp_files = on，当文件被删除时，它会记录文件路径和大小
  - 由 EXPLAIN ANALYZE 或 auto_explain 获取的查询计划

存储配额

PostgreSQL 无法限制存储使用情况，除了临时文件。
temp_file_limit 可以限制每个会话在任何时刻使用的临时文件的总大小。超过此限制的事务将被中止。
如果您想限制数据库、表或 WAL ($PGDATA/pg_wal/) 的大小，请将其放在文件系统上的表空间中，该文件系统的大小有限。
- 数据库/表的表空间可以通过 CREATE/ALTER DATABASE/TABLE ... TABLESPACE 明确指定，或者通过 default_tablespace 参数隐式指定。
- temp_tablespaces 可用于指定为临时表/索引以及排序/哈希操作创建临时文件的存储位置。
- WAL 目录可以通过 initdb 的 --waldir 选项指定。此外，在创建数据库集群后，可以将其移出数据目录并使用符号链接链接。

日志记录和调试

致命错误：数据库正在启动

在旧的主版本中，此消息可以在服务器启动期间以 1 秒的间隔输出。
这看起来很令人惊讶，但它不是实际问题。
"为什么？ pg_ctl start" 在后台启动 postmaster，并尝试以 1 秒的间隔连接到数据库。如果连接成功，pg_ctl 会返回成功。否则，如果服务器仍在执行恢复并且无法接受连接，则会报告上述消息。
在新版本中，您将不再看到此消息。 pg_ctl 不会尝试连接。相反，postmaster 在可以接受连接时会在 postmaster.pid 中写入“ready”，pg_ctl 会检查它。

通过限制目标来避免过度日志记录

不仅是日志记录，而且许多参数可以针对每个用户、数据库或它们的组合进行配置。例如
- ALTER USER oltp_user SET log_min_duration_statement = '3s';
- ALTER DATABASE analytics_db SET log_min_duration_statement = '60s';
- ALTER USER batch_user IN DATABASE oltp_db SET log_min_duration_statement = '30s';

可以为会话启用调试日志记录，而不会弄乱服务器日志

在全局范围内将 log_min_messages 设置为 DEBUG1 - DEBUG5 可能不可接受，因为这会导致输出大量日志。
您只能在客户端获取特定操作的调试消息，如下所示
- export PGOPTIONS="-c client_min_messages=DEBUG5"

psql -d postgres -c "select 1"

找出 psql 的反斜杠命令的作用

使用 psql 的 -E/--echo-hidden 选项。它会显示在后台发出的查询。

删除重复行

以下查询将删除重复的行，保留 ctid 最小的行，并显示已删除行的内容。
ctid 是一个系统列，表示行版本在其表中的物理位置：（块号、项目 ID）。 ctid 可能会由于 UPDATE 和 VACUUM FULL 而发生变化，因此在操作期间以 Share 或更强的模式锁定表可能更安全。

WITH x AS (SELECT some_table dup, min(ctid)
    FROM        some_table
    GROUP BY 1
    HAVING count(*) > 1
)
DELETE FROM    some_table
USING     x
WHERE     (some_table) = (dup)
    AND some_table.ctid <> x.min
RETURNING some_table.*;

真空

真空的目的

回收或重用更新或删除的行所占用的磁盘空间。
更新 PostgreSQL 查询规划器使用的数据库统计信息。
更新可见性映射，这将加快仅索引扫描的速度。
防止由于事务 ID 回绕或多事务 ID 回绕而丢失非常旧的数据。

真空类型

并发（延迟或常规）真空
- 在目标关系上获取 Share Update Exclusive 锁。不阻止 SELECT 和 DML 命令。
- 保留原始数据文件并对其进行修改。 TID 不会改变。
- 只有当文件末尾存在一定数量的连续空块时，数据文件才会缩小。文件中间的未使用空间将保留以供重用。
- 在 pg_stat_progress_vacuum 视图中报告其进度。
完整真空
- 在目标关系上获取 Access Exclusive 锁。阻止 SELECT 和 DML 命令。
- 将活动元组从旧数据文件复制到新数据文件，并删除旧数据文件。重新构建索引。 TID 会改变。
- 数据文件将被完全压缩，并且最小。
- 可能会使用两倍的磁盘空间：一个用于现有关系，另一个用于新的关系。
- 始终积极地冻结元组。
- 实际处理过程与 CLUSTER 相同。
- 在 pg_stat_progress_cluster 视图中报告其进度。
自动真空从不运行完整真空。

真空的主要步骤

启动事务。
- 当有多个目标关系时，真空会为每个关系启动并提交一个事务，以便尽快释放锁。
为堆获取 Share Update Exclusive 锁并打开它。非回绕预防真空如果关系无法获取锁，将放弃对关系的真空操作，并发出以下消息。
- LOG: 跳过 vacuum "rel_name" --- 锁不可用
为索引获取 Row Exclusive 锁并打开它们。
分配工作内存以累积死元组的 TID。
重复以下步骤，直到处理完整个堆
- 扫描堆：将死元组 TID 累积到工作内存中，直到其填满或到达堆的末尾。将死元组的项目 ID 保留下来。此外，如果需要，会修剪和碎片整理每个页面，并且可能会冻结活动元组。
- 真空索引：删除包含死元组 TID 的索引条目。
- 真空堆：回收死元组的项目 ID。这是在这里完成的，而不是在扫描堆时完成的，因为在删除指向它的索引条目之前，无法释放项目 ID。
- 在上述处理过程中更新 FSM 和 VM。
清理索引。
- 更新每个索引的 pg_class 中的 relpages 和 reltuples 的统计信息。
- 关闭索引，但保留其锁，直到事务结束。
截断堆，以便将关系末尾的空页面返回给操作系统。
- 如果堆的末尾至少有 1,000 个块和 (relation_size / 16) 个连续空块，则数据文件将被截断。
- 在堆上获取 Access Exclusive 锁。如果另一个事务持有冲突锁，则最多等待 5 秒。如果无法获得锁，则放弃截断。
- 向后扫描堆以验证末尾页面是否仍然为空。定期检查是否有其他事务正在等待冲突锁。如果有人正在等待，则释放 Access Exclusive 锁并放弃截断。
更新关系统计信息。
- 更新 pg_class 的 relpages、reltuples、relallvisible、relhasindex、relhasrules、relhastriggers、relfrozenxid 和 relminmxid。
关闭关系。
提交事务。
真空关系的 TOAST 表。
对每个关系重复上述处理。
更新数据库统计信息。
- 更新 pg_database.datfrozenxid 为 pg_class.relfrozenxid 值的最小值，并在 pg_xact/ 中截断提交日志。
- 更新 pg_database.datminmxid 为 pg_class.relminmxid 值的最小值，并在 pg_multixact/ 中截断多事务数据。

自动真空设计为非侵入式

自动真空在完成一定工作量后会休息（睡眠）。因此，它不会持续消耗资源。
- "一定的工作量" 和睡眠时间可以通过 autovacuum_vacuum_cost_limit 和 autovacuum_vacuum_cost_delay 分别配置。 autovacuum_vacuum_cost_delay 的默认值为 2 毫秒。
如果由于某些冲突锁而无法获取关系锁，自动真空将跳过该关系。回绕预防自动真空不会这样做。
如果并发事务在等待冲突关系锁时已经等待了 deadlock_timeout 秒，并发现该锁被自动真空持有，则并发事务会取消非积极的自动真空。可以看到这些消息
- 错误：取消自动真空任务
- DETAIL: 自动清理表 "mytable"
如果 VM 显示数据页仅包含对所有事务可见的元组（VM 中设置了全可见位），则清理会跳过读取数据页。积极的清理会读取此类页面，以冻结元组。
如果 VM 显示数据页仅包含已冻结的元组（VM 中设置了全冻结位），则清理会跳过读取数据页。
当自动清理无法获得数据页面的独占 LWLock 时，它会在数据页面上执行减少的工作。用于回绕的自动清理不会这样做。
如果另一个事务持有或正在等待目标关系的锁，则清理会放弃截断关系。

不会对关系运行自动清理

检查以下内容以查看是否如此。
- pg_stat_all_tables 的 last_autovacuum 和 autovacuum_count 列
- 将 log_autovacuum_min_duration 设置为 0 后，服务器日志
常见原因
- 关系必须有资格进行自动清理。
  - 主要更新/删除的关系：已更新/删除的元组 >= autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * pg_class.reltuples
  - 主要插入的关系：已插入的元组 >= autovacuum_vacuum_insert_threshold + autovacuum_vacuum_insert_scale_factor * pg_class.reltuples
- 自动清理工作器忙于其他许多和/或大型关系。
- 某些事务连续长时间请求或持有冲突的关系锁。非回绕预防清理会放弃此类关系。
- 自动清理无法清理临时表。需要手动运行清理。这可能导致 XID 回绕和数据库关闭。
- 由于崩溃或归档恢复（包括故障转移）而丢失了存储在 pg_stat/ 中的统计信息。这些统计信息在恢复期间始终被重置。自动清理依赖于这些统计信息（可以通过 pg_stat_all_tables 查看）来确定是否需要清理。

为什么清理不会删除死元组

自动清理速度慢
长时间运行的事务
带有 hot_standby_feedback = on 的物理备用
未使用的复制槽
孤立的已准备事务

降低 XID 回绕的风险

减少 XID 使用。
- 每个子事务都会分配自己的 XID。子事务由 SAVEPOINT 和 PL/pgSQL 的异常块（BEGIN ... EXCEPTION）启动。
- 某些客户端驱动程序提供语句级回滚。它用 SAVEPOINT 和 RELEASE SAVEPOINT 包含每个 SQL 语句。
使自动清理平稳运行（见上文）。
降低 autovacuum_vacuum_insert_scale_factor（PostgreSQL 13+）或 autovacuum_freeze_max_age，以便自动清理更频繁地处理表。
安排定期 VACUUM FREEZE 运行。

加速自动清理的配置

对于大型表，降低 autovacuum_vacuum_threshold、autovacuum_vacuum_scale_factor、autovacuum_vacuum_insert_threshold、autovacuum_vacuum_insert_scale_factor。
减少 autovacuum_naptime
- 如果写工作负载很重且主机拥有许多 CPU 内核，即使 1 秒也很实用。
增加 autovacuum_max_workers
- 当存在许多关系时有效。每个关系仅由一个自动清理工作器处理。
- 同时增加 autovacuum_vacuum_cost_limit。否则，每个自动清理工作器会更频繁地休眠，因为成本限制在所有活动的自动清理工作器之间共享。
增加 maintenance_work_mem/autovacuum_work_mem
- 工作内存存储死元组 TID 的数组。 TID 是（块号，项号），为 6 字节。
- 设置较大的值会减少索引扫描的数量。
- 无论参数值多大，最大分配大小为 1 GB - 1。
- 并不总是分配指定的大小。实际大小足以容纳所有可能的 TID，因此对于小型表而言，它将很小。
- 对于没有索引的表，仅分配不到 2 KB。清理仅累积一个表块的 TID，因为它不需要扫描索引。
增加 vacuum_buffer_usage_limit
- 清理默认情况下使用 256 KB 的环形缓冲区来缓存数据页面，以便它不会逐出应用程序可能使用的页面。
- 清理也从缓存页面中受益：堆页面被读取两次，索引页面可能被读取多次。
- 将此设置为 0 允许清理无限制地使用共享缓冲区。
减少 autovacuum_vacuum_cost_delay，增加 autovacuum_vacuum_cost_limit
- 将 autovacuum_vacuum_cost_delay 设置为 0，这会使自动清理像手动清理一样运行。
将大型表进行分区，以便多个自动清理工作器可以同时处理其分区。
删除不必要的索引。
- 自动清理一次处理一个索引。（手动清理可以使用其 PARALLEL 选项并行处理它们。）

升级

版本的特点

主版本
- 包含新功能和不兼容性。
- 每年发布一次。
- 敏感的错误修复仅包含在最新的主版本中。 “敏感”包括可能导致不兼容、不利影响（如不稳定和安全）或需要大量代码更改（得不偿失）的修复。
- 升级可以跳过中间的主版本。例如，版本 11 可以升级到 16，无需经过 12 到 15。
- 始终需要仔细的计划和测试，以处理不兼容的更改。
次版本
- 仅包含经常遇到的错误、安全问题和数据损坏问题，以降低与升级相关的风险。
- 始终建议运行最新的次版本。社区认为不升级比升级风险更大。
- 至少每三个月发布一次，在二、五、八、十一月的第二个星期四发布。可能会发布其他次版本来解决紧急问题。
- 升级可以跳过中间的次版本。
- 通常不需要转储和还原；您可以停止数据库服务器，安装更新的二进制文件，然后重新启动服务器。
- 某些次版本可能需要额外的步骤，以弥补已修复错误的不良影响，例如重建受影响的索引。请参阅发行说明中的“迁移到版本<主版本>.<次版本>”部分。

主要升级方法

pg_dumpall/pg_dump 和 psql/pg_restore：简单，停机时间长
pg_upgrade：相对简单，停机时间短
逻辑复制：设置和操作复杂，停机时间极短

pg_upgrade 的概述

将数据库集群升级到更高版本，无需转储/还原用户数据。
不是就地升级：将数据从旧的数据库集群迁移到用 initdb 新创建的新的数据库集群。
基本思想是，由于关系数据存储格式很少更改，只有系统目录的布局会更改，因此 pg_upgrade 只转储和还原数据库模式，并将关系数据文件按原样使用。
支持从 9.2 及更高版本升级。
降级不可行。
不会迁移 pg_statistic 中的数据库统计信息。用户需要在 pg_upgrade 完成后在每个数据库中运行 ANALYZE。

pg_upgrade 的主要步骤

为日志和中间文件创建输出目录。
检查目标集群的主版本是否更新，以及旧集群和新集群是否通过比较 pg_control 中的信息进行二进制兼容。
获取旧集群的数据库和关系（表、索引、TOAST 表、物化视图）列表。
获取包含 C 语言函数的库名称列表。
执行各种检查以查找升级的阻碍因素，例如无法连接到数据库以及存在已准备事务。
通过运行pg_dumpall --globals-only创建全局对象的转储。
通过运行pg_dump --schema-only创建每个数据库的转储。当指定 --jobs 时，这会通过为每个数据库生成一个进程或线程来并行化。
通过运行LOAD检查以前提取的可加载库是否存在于新集群中，这些库包含 C 语言函数。
将旧集群的 pg_xact/ 中的提交日志文件和 pg_multixact/ 中的 MultiXact 文件复制到新集群。
为新集群设置下一个 XID 和 MultiXact ID 以接管旧集群。
通过运行psql在新集群中还原全局对象。
通过运行pg_restore在新集群中还原数据库模式。当指定 --jobs 时，这会通过为每个数据库生成一个进程或线程来并行化。
获取新集群的数据库和关系（表、索引、TOAST 表、物化视图）列表。
从旧集群链接或复制用户关系文件到新集群。当指定 --jobs 时，这会通过为每个表空间生成一个进程或线程来并行化。
为新集群设置下一个 OID。
创建一个脚本以删除旧集群（delete_old_cluster.sh）。此脚本将删除数据目录和表空间版本目录。
报告应更新的扩展，并创建 update_extensions.sql。此脚本包含 ALTER EXTENSION ... UPDATE 命令列表。

用于排查 pg_upgrade 问题的日志文件

存储在 $NEWPGDATA/pg_upgrade_output.d/<时间戳>/ 中
pg_upgrade 成功完成时将被删除。
文件
- pg_upgrade_server.log：postgres 服务器日志。指定为 pg_ctl 的 -l。
- pg_upgrade_dump_<DB-OID>.log：pg_dump 和 pg_restore 的日志。
- pg_upgrade_utility.log：pg_upgrade 运行的各种命令的日志，例如 psql、pg_resetwal。这包括用于转储和还原全局对象的 pg_dumpall/psql。
- pg_upgrade_internal.log：其他 pg_upgrade 日志。
- loadable_libraries.txt：存在于旧集群中但未在新集群中找到的 C 语言函数库列表。

参考

PostgreSQL 文档

记忆

存储

国际化和本地化

日志记录

真空

分区

升级

其他技巧

应用程序开发

数据类型

数字

对于精确计算和/或具有许多位数的数字，选择 numeric。
对于较小的存储空间和更快的计算，选择整数类型（smallint、int、bigint）和浮点类型（real、double precision、float）。
decimal 类型是 numeric 的别名。psql 的 \d 和 pg_dump 输出 decimal 列为 numeric 而不是 decimal。

时间戳

timestamp without time zone 忽略 TimeZone 参数。该值按原样存储和返回。
timestamp with time zone 遵循输入值中的显式时区，否则遵循 TimeZone 参数。输入值将转换为 UTC，输出值将根据生效的时区从存储的值转换。

二进制

存储二进制数据的可用方法
- bytea 数据类型
- 大型对象：使用类似文件系统的 open/close/read/write 接口，数据存储在 pg_largeobject 中，用户表列包含指向 pg_largeobject 中行的 OID 值。
- 外部文件：应用程序在文件系统或对象存储中管理数据，并在表字符列中存储文件路径。
如何选择
- 需要事务（ACID）属性？-> bytea、大型对象
- 处理 1 GB 或更大的列值？-> 大型对象、外部文件
- 需要随机和/或分段访问？-> 大型对象、外部文件
- 希望在 100 MB 或更大的列值上获得最佳性能？-> 外部文件

使用大型对象的技巧

不要使用大型对象。它们可能会有问题。使用 bytea 列或外部文件存储，如 OS 文件系统和对象存储。
删除大量 LOB
- 尝试在一个事务中删除许多大型对象，例如 "SELECT lo_unlink(lo_oid) FROM mytable;"，可能会出现以下错误消息
  - ERROR: out of shared memory
  - HINT: You might need to increase max_locks_per_transaction.
- 原因：删除大型对象时，会使用 Access Exclusive 模式锁定它。因此，在锁定表中需要与删除的 LOB 数量一样多的条目。
- 解决方案：执行以下一项或两项操作
  - 增加 max_locks_per_transaction。必须重新启动数据库服务器。
  - 分批删除 LOB，例如，每个事务删除 100 个 LOB。
处理孤立的 LOB
- 孤立的 LOB 是一个大型对象，其 OID 不出现在数据库的任何 oid 或 lo 数据列中。
- 如果应用程序在删除关联的表行时未能通过调用 lo_unlink() 删除它，就会出现这样的孤立 LOB。
  - 解决方案：执行以下两项操作
  - 使用 vacuumlo 删除孤立的 LOB。
  - 使用扩展并在 LOB 列上设置触发器。当包含 LOB OID 的表行更新或删除时，它会自动调用 lo_unlink()。

序列

没有无间隙序列

序列在以下情况下会产生间隙
- 事务回滚：因为 nextval() 和 setval() 调用永远不会回滚，所以分配的序列值不会被回收。
- 缓存的值未被使用：如果为序列启用了缓存，nextval() 会预先分配指定数量的值并将它们缓存到会话的本地内存中。后续的 nextval() 调用会从缓存中获取值，直到缓存为空，然后再次预先分配一些值。因此，如果会话结束时未使用所有缓存的值，这些值将成为间隙。
- 服务器崩溃：即使使用 NO CACHE 序列，在以下步骤中也会看到间隙：nextval() -> 崩溃恢复 -> nextval()。为了提高性能，PostgreSQL 不会 WAL 日志记录从序列中获取每个值的步骤。nextval() 会 WAL 日志记录一个比当前值提前 32 个数字的值，接下来的 32 次调用 nextval() 不会 WAL 日志记录任何内容。结果，一些数字似乎被跳过了。

参考

PostgreSQL 文档

数据类型

大型对象

清理 PostgreSQL 中的大量 BLOB

分页

序列

PostgreSQL 中序列的间隙

可扩展性和性能

许多连接

想处理许多并发客户端？那么请执行以下操作

在每个应用程序服务器以及中央服务器上设置连接池。
将 max_connections 和实际连接数限制为 CPU 内核数量的几倍，最多为几百。
超过此限制，性能往往会下降，主要原因是
- 客户端后端的高内存使用，可能导致交换。
- CPU 上下文切换
- CPU 缓存行争用
- 锁，特别是自旋锁：如果一个进程持有自旋锁，而其他进程来到同一受保护的部分，这些迟到的进程将等待自旋锁，并继续消耗 CPU。
- 处理 PostgreSQL 内部数据结构：某些数据结构及其处理取决于连接数；创建快照在这里很突出
即使是空闲连接也不无辜。它们会导致高资源使用率。

检测问题

为 ORM（对象关系映射器）增加 track_activity_query_size

某些视图（如 pg_stat_activity 和 pg_stat_statements）显示查询字符串。
因为这些查询字符串存储在固定大小的共享内存中，所以每个这样的查询字符串的长度是固定的，即 track_activity_query_size。较长的查询在此限制处被截断。
Hibernate 或其他一些 ORM 会生成非常长的查询。将 track_activity_query_size 设置为 32 KB 或更高可能会有用。

利用 plprofiler 来诊断 PL/pgSQL 函数和过程的瓶颈

这是一个扩展，它会创建一个 HTML 报告，显示函数和过程每个步骤的运行时间、从那里调用的例程的总运行时间以及每个例程的执行时间。

日志记录

服务器日志记录可能会阻塞，但不会显示等待

日志记录收集器（记录器）是将日志写入服务器日志文件的唯一进程。
每个后端进程都会将其标准错误写入日志，该标准错误通过 Unix 管道连接到记录器的读取端点。记录器从管道读取消息并将其写入文件。
如果管道已满，后端在写入管道时可能会被阻塞。当记录器因日志记录量过大而落后时，就会发生这种情况，例如，当使用 pgAudit、auto_explain 和 log_min_duration_statement 的某种组合并且许多并发会话运行简短查询时。
管道上的阻塞不被视为等待事件（可能是错误），因此后端似乎正在消耗 CPU。

导入和导出

ALTER TABLE SET UNLOGGED/LOGGED 很重

这会将整个表重写到新的数据文件中，并 WAL 日志记录这些写入操作。
因此，不能将其用于高效的数据加载 - 将表切换到 UNLOGGED，将数据加载到表中，然后将其设置为 LOGGED。

使用 COPY 加载数据后，查询或第一次 vacuum 很慢

这是因为这些命令必须为它们想要查看的行设置提示位。设置提示位会修改共享缓冲区，并在启用数据校验和时 WAL 日志记录这些更改。这可能会产生大量的写入。
COPY (FREEZE) 可以解决这个问题。FREEZE 选项会冻结加载的行并设置它们的提示位。
表必须在当前子事务中创建或截断。这样做是为了防止其他事务在 COPY 事务提交之前看到已冻结的行。

外部数据包装器 (FDW)

通过 postgres_fdw 加速查询

手动对外部表运行 ANALYZE
- autovacuum 不会对外部表执行 ANALYZE。因此，本地统计信息可能会过时，导致查询计划不佳。
为长时间运行的查询启用 use_remote_estimate
- 即 ALTER FOREIGN SERVER/TABLE ... OPTIONS (use_remote_estimate 'true');
- 这将使 postgres_fdw 发出 EXPLAIN 以在远程服务器上执行成本估算。
- 由于 EXPLAIN 的往返，查询规划时间会变长。因此，这对于简短查询可能不值得。可以使用不同的外部服务器/表，为 OLTP、批处理和分析工作负载设置不同的参数。
增加 fetch_size
- 即，ALTER FOREIGN SERVER/TABLE ... OPTIONS (fetch_size '1000');
- postgres_fdw 使用游标从外部表中获取行。fetch_size 决定一次获取的行数。默认值为 100。
- 如果网络延迟很高，通过增加此设置减少往返次数可能会有所帮助。请注意，较高的值需要更多内存来存储获取的行。
增加 batch_size
- 即，ALTER FOREIGN SERVER/TABLE ... OPTIONS (batch_size '1000');
- 默认情况下，在多行插入（INSERT ... SELECT, INSERT ... VALUES (row1), (row2),..., COPY FROM）期间，postgres_fdw 一次将一行插入外部表。
- 提高此设置将显着提高吞吐量，尤其是在网络延迟很高的情况下。
在 extensions 参数中列出在本地和远程服务器上具有兼容行为的扩展名
- 即，ALTER FOREIGN SERVER ... OPTIONS (extensions 'extension1,extension2');
- 这些扩展中的不可变函数和运算符被认为在本地和远程服务器上产生相同的结果。因此，它们的执行将被发送到远程服务器。
- 这在这些函数和运算符在 WHERE 子句中使用时特别有用。这些过滤器将在远程服务器上执行，因此传输的行更少。

全文搜索

在插入许多新文档后，全文搜索查询变得慢得多

将数据插入到启用了快速更新的 GIN 索引时，新的索引条目不会被放入索引主结构中。相反，它们被放置在索引的待处理列表中，其大小由 gin_pending_list_limit 设置。稍后，当待处理列表区域已满时，这些待处理列表条目将被移到主索引结构中。
这样做是为了提高性能，因为插入一个文档会涉及到对主索引的许多插入操作，这取决于文档中词语的数量。
全文搜索查询在主索引结构之前扫描待处理列表。因此，如果待处理列表包含许多待处理条目，则速度会很慢。
Vacuum（包括 autovacuum）也将待处理列表条目移到主索引中。因此，在 Vacuum 之后，全文搜索查询将更快。
建议调整 autovacuum，使其在插入或更新文档后相当频繁地运行。
可以使用此查询查看待处理列表页和元组的数量（pgstatginindex 位于 pgstattuple 扩展中）
- SELECT * FROM pgstatginindex('some_gin_index');

实用程序

快速随机抽取表行

传统方法很慢，因为它会扫描并排序整个表。
- SELECT * FROM mytable ORDER BY random() LIMIT 1;
使用 TABLESAMPLE 子句可以非常快地返回行，几乎与表大小无关。
- TABLESAMPLE 获取表的样本部分。提供了一些内置的抽样方法。
- 此外，可以通过添加扩展来自定义抽样方法。例如，tsm_system_rows 检索指定数量的随机行
  - CREATE EXTENSION tsm_system_rows;
  - SELECT * FROM mytable TABLESAMPLE SYSTEM_ROWS(1);
- SYSTEM_ROWS 在表的 data 文件中随机选择一个块，然后在其中按顺序获取行。如果需要更多行，将选择额外的块。

记忆

使用大页

设置 huge_pages = on。
- 这将显着减少内存使用量，因为页表变小。
- 此外，由于减少了 CPU 的 TLB 缓存未命中，因此可以预期性能会提高。
考虑到减少内存使用量和提高性能作为稳定操作的一部分，应首选 huge_pages 的“on”而不是“try”。
- 当 huge_pages 设置为“on”时，如果操作系统无法分配足够的大页，PostgreSQL 将拒绝启动，并发出以下消息
  - FATAL: could not map anonymous shared memory: Cannot allocate memory
  - HINT: 此错误通常表示 PostgreSQL 请求的共享内存段超过了可用内存、交换空间或大页。要减少请求大小（当前为 1234567890 字节），请减少 PostgreSQL 的共享内存使用量，例如通过减少 shared_buffers 或 max_connections。
- 在这种情况下，请重新启动操作系统或执行故障转移。

关于 shared buffers 的提示

避免客户端后端写入磁盘。
- 如果服务器进程想要一个新页时没有空闲的共享缓冲区，它必须驱逐一个已使用的缓冲区。如果驱逐的页是脏页，服务器进程需要将该页写入磁盘。这会增加响应时间。
- 可以通过检查pg_stat_bgwriter 中的 buffers_backend 是否很高来检测这种情况。如果 buffers_backend_fsync 也很高，则情况更糟。
- 为了缓解这种情况
  - 创建更多空闲缓冲区：增加 shared_buffers。
  - 创建更多干净缓冲区：增加 bgwriter_lru_multiplier，以便后台写入器更积极地写入脏缓冲区。如果 pg_stat_bgwriter 的 maxwritten_clean 经常上升，请尝试增加 bgwriter_lru_maxpages。
较大的 shared buffers 可能会适得其反。
- 在具有高性能存储的宿主机上，shared buffers 的优势会减弱。
- 这是因为 PostgreSQL 使用操作系统的文件系统缓存：数据在文件系统缓存和共享缓冲区中都被缓存（双重缓冲）。
- 因此，从 RAM 的 25% 开始分配给 shared buffers，然后根据需要一直增加到 40% 左右，只要你能看到一些改进。
- 但是，一些基准测试表明 64 GB 或更多可能会造成损害。
利用 pg_prewarm 在故障转移后快速恢复性能。
- 在数据库服务器重启或故障转移后，共享缓冲区的内容为空，或者可能与故障转移之前的内容截然不同。因此，应用程序响应时间会变差，直到共享缓冲区预热。
- 在 shared_preload_libraries 中添加 pg_prewarm，并将 pg_prewarm.autoprewarm 设置为 on。
- 这将启动 autoprewarm 工作进程，该工作进程会定期将共享缓冲区中缓存的关系和块号列表保存到文件中。在服务器启动时，pg_prewarm 工作进程会读取该文件以重新填充共享缓冲区。
"SELECT * FROM some_table;" 不一定缓存整个表。
- 你可能想这样做来进行性能测试或应用程序预热，但它不起作用。此外，它根本不缓存索引。
- 如果关系的大小超过共享缓冲区的四分之一，则它的顺序扫描仅使用 256 KB 的共享缓冲区。
- 这样做的原因是，仅通过此类扫描触及的页不太可能很快再次被需要，因此 PostgreSQL 试图防止此类大型顺序扫描将许多有用的页驱逐出共享缓冲区。
- 同样，批量写入（例如 COPY FROM 和 CREATE TABLE AS SELECT）仅使用 16 MB 的共享缓冲区。
- 要缓存整个关系，请运行SELECT pg_prewarm('relation_name')。这对于索引同样适用。

关于本地内存的提示

设置足够的 work_mem 需要反复尝试。
- 不幸的是，没有简单的方法来估计 work_mem 设置以避免磁盘溢出。
- log_temp_files 显示的临时文件是不够的。必须包含用于在内存中缓冲临时数据的额外开销。
- 估计 work_mem 的一种方法是将排序或哈希的计划行的宽度和数量相乘，这些信息可以在查询计划中找到。为开销添加一些额外的内存，例如，将其再乘以 1.1 左右。
- 如果使用并行查询，则将结果除以（使用的并行工作进程数量 + 1）。“+1” 是用于并行领导进程的。
- 运行 EXPLAIN ANALYZE 以查看是否使用了外部文件。尝试增加 work_mem，直到不再使用外部文件。
effective_cache_size 不分配任何内存。
- 它仅用于估计索引扫描的成本。规划器假设有足够的内存可用，用于缓存查询数据。
- 较高的值会使索引扫描更有可能被使用，而较低的值会使顺序扫描更有可能被使用。

网络

在运行大量短 SQL 命令时要注意网络延迟

你的批处理应用程序在连续发出许多小型 SQL 语句时，在迁移到不同的环境后速度变慢了很多倍吗？
这可能是因为网络延迟较高。检查网络通信是否缓慢。
- 测量简单 SQL 的往返时间，例如：
  - \timing on
  - SELECT 1;
- 检查 ClientRead 和 ClientWrite 等待事件是否在增加。

游标

DECLARE CURSOR 速度很快。它会创建一个查询计划，但不会计算结果集。FETCH 会启动计算。
游标查询的计划方式不同于非游标查询。你可以看到同一个 SELECT 语句的不同查询计划。
- 非游标查询针对总运行时间进行了优化。优化器假设客户端将使用整个结果集。
  - 更可能选择顺序扫描和排序，因为索引扫描被认为成本高昂。
- 游标查询针对启动和初始数据检索的运行时间进行了优化。优化器假设客户端将仅获取结果集的一小部分。
  - 优化器会进行索引扫描，以加快前 10% 数据的创建速度。
  - 可以使用 cursor_tuple_fraction 参数配置“10%”。

锁

利用快速路径锁以提高性能

如果许多并发短事务每个事务都触及了许多关系，则用于保护锁表的 lwlocks 会成为争用瓶颈。这种争用表现为 LWLock:LockManager 等待事件。
虽然锁表被分成 16 个分区，并且这些分区由不同的 lwlocks 覆盖，但数百个并发事务会导致对这些 lwlocks 的等待。
快速路径锁来解救
- 弱锁（Access Share、Row Share 和 Row Exclusive 模式）使用快速路径锁机制进行获取。它不使用锁表。相反，这些锁在共享内存中的每个后端区域中进行记录。
- SELECT 和 DML 会获取这些弱锁，因此它们不会受到锁管理器 lwlock 争用的影响。
但是，如果以下任一情况为真，则无法使用快速路径锁
- 事务已经具有 16 个快速路径关系锁。每个后端记录区域限制为 16 个条目。访问具有许多分区和索引的表，或联接许多表的查询将失败。
- 某些事务尝试获取强锁（Share、ShareRowExclusive、Exclusive 和 AccessExclusiveLock 模式）。
  - 对同一关系的现有快速路径锁将被转移到锁表。
  - 如果有人拥有或请求强锁，则后续事务在获取对不同关系的锁时可能无法使用快速路径锁。这是因为强锁的存在是使用一个包含 1024 个整数计数器的数组来管理的，这实际上是对锁空间的 1024 路分区。如果请求的弱锁需要与现有强锁在同一分区中进行管理，则它无法使用快速路径。
快速路径锁在pg_locks 中显示为 fastpath 列为真。

HOT

利用 HOT（堆内元组）

HOT 加快 UPDATE 操作。
如果不使用 HOT，问题是什么？
- 索引将更大，因为每个行版本在每个索引中都有一个索引条目。使用这些索引的索引扫描也会更慢。
- WAL 卷将更大，更新将更慢，因为任何列的更新都会将新条目插入所有索引中。
为了使 HOT 正常工作，必须满足以下两个条件
- 包含更新行的块有足够的空闲空间来容纳新的行版本。
- 更新没有修改任何索引列。
那么，我该怎么办呢？
- 设置表的 fillfactor 为新行版本腾出空间。
  - 例如，CREATE TABLE mytable ... WITH (fillfactor = 90);, ALTER TABLE mytable SET (fillfactor = 90);
  - 较低的 fillfactor 会使表变大，从而导致共享缓冲区丢失和更长的顺序扫描。
  - 也许你应该从 fillfactor = 90 开始，如果 HOT 不太好用，再降低这个设置。
- 删除不必要的索引。
我怎么知道 HOT 是否在工作？
- 查看 pg_stat_all_tables 确保 n_tup_hot_upd 相对于 n_tup_upd 较高。

表布局

为了获得最佳的存储效率和性能，请从最大的固定长度类型（例如，bigint，timestamp）到最小的固定长度类型（例如，smallint，bool），然后是可变长度类型（例如，numeric，text，bytea）声明表列。

存储效率来自数据对齐要求。
- 例如，bigint 对齐到 8 字节边界，而 bool 对齐到 1 字节边界。
- 在以下示例中，前者返回 48，后者返回 39。
  - SELECT pg_column_size(ROW('true'::bool, '1'::bigint, '1'::smallint, '1'::int));
  - SELECT pg_column_size(ROW('1'::bigint, '1'::int, '1'::smallint, 'true'::bool));
- 可以使用以下命令查看对齐要求：SELECT typalign, typname FROM pg_type ORDER BY 1, 2;
更好的性能来自上述较小的数据大小和直接列访问。
- 如果固定长度列放在行的前面，PostgreSQL 可以计算和缓存行中固定长度列的位置。因此，可以使用其偏移量直接访问任何行的请求固定长度列数据。
- 一旦出现可变长度列，后续列的位置需要为每一行计算，方法是将所有列的实际长度相加。因此，访问行末尾的列将很慢。

在 FROM 子句中使用返回复合类型的函数，而不是 SELECT 列列表

假设 sample_func() 的返回类型是复合类型 (a int, b int, c int)。
不好：SELECT (sample_func()).*;
好：SELECT * FROM sample_func();
在不好的情况下，"(sample_func()).*" 被扩展为 "(sample_func()).a", "(sample_func()).b", "(sample_func()).c"。因此，该函数被调用了三次。

TOAST（超大属性存储技术）

这是一种存储最大 1 GB - 1 的大值的机制。
元组不能跨越多个页面。那么，如何存储大于页面大小（通常为 8 KB）的列值呢？
可 TOAST 的数据类型的较大列值会被压缩和/或分解成块。每个块作为关联的 TOAST 表中的一行存储。块大小的选择是为了使四个块行适合一个页面。对于 8 KB 页面大小，大约为 2,000 字节。
可 TOAST 的数据类型是具有可变长度（varlena）表示的数据类型。也就是说，一个 1 或 4 字节的 varlena 头部后跟列值。char(n) 看起来像固定长度，但它具有 varlena 格式。
TOAST 表
- 每个表有 0 或 1 个 TOAST 表和 TOAST 索引。
- 如果需要，TOAST 表及其索引将在 CREATE/ALTER TABLE 中创建。
- TOAST 表为 pg_toast.pg_toast_<main_table_OID>。
- TOAST 索引为 pg_toast.pg_toast_<main_table_OID>_index。
- TOAST 表的 OID 存储在表的 pg_class.reltoastrelid 中。
- TOAST 索引的 OID 存储在 TOAST 表的 pg_class.reltoastidxid 中。
- 每个 TOAST 表都有以下列
  - chunk_id OID：标识特定 TOAST 值的 OID
  - chunk_seq int：值内块的序列号
  - chunk_data bytea：块的实际数据
  - 主键 (chunk_id, chunk_seq)
TOAST 的工作原理
- 它只在要存储在表中的行值宽度超过 2 KB 时触发（当页面大小为 8 KB 时）。
- 压缩和/或将列值移动到 TOAST 表中，直到行值短于 2 KB（当页面大小为 8 KB 时）或不再有增益。此 2 KB 阈值可以使用 CREATE/ALTER TABLE 中的存储参数 toast_tuple_target 为每个表调整。
- 将 TOAST 值的 chunk_id 存储在主表的列中。这称为 TOAST 指针。
值存储策略 - 应该是压缩还是移动到 TOAST 表 - 可以从四个选项中选择，使用 ALTER TABLE ALTER COLUMN column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
压缩方法可以在 pglz 和 lz4 之间选择。它可以通过在 CREATE/ALTER TABLE 中使用 COMPRESSION 列选项来为每个列设置，或者使用默认的 default_toast_compression 参数设置。
插入 TOAST 值可能会变得出乎意料地慢。
- 这往往出现在目标表已经存在数百万个 TOAST 值时，尤其是在连续插入大量行之后。
- 为什么？
  - 每个 TOAST 值都由一个 OID 标识。
  - OID 是一个无符号的 4 字节值，它是从集群范围的计数器生成的，该计数器每 40 亿个值循环一次。因此，单个表不能拥有超过 2^32（40 亿）个 TOAST 值。
  - 在插入 TOAST 值时，PostgreSQL 为其生成一个新的 OID，检查目标表中现有的 TOAST 值是否已使用相同的 OID。如果使用过，PostgreSQL 将生成下一个 OID 并再次执行检查。重复此操作，直到找到一个空闲的 OID。
  - 如果目标表中使用的是连续的 OID，则此重试需要很长时间。
解决方法是将表进行分区。每个分区都有自己的 TOAST 表。因此，每个分区中出现重复 OID 的可能性会降低。

事务

已杀死的（无效）索引元组可以带来神秘的查询加速

如果您遇到对相同查询的相同执行计划的执行时间变化，这可能是由于已杀死的索引元组造成的。
每当索引扫描获取一个堆元组，却发现它已失效时，就会将索引元组标记为已杀死（失效）。然后，未来的索引扫描将忽略它。这将避免其索引键比较以及其堆元组获取。

子事务可能会有害

子事务是事务的一部分，可以回滚，而不会回滚主（顶层）事务。
子事务由 SAVEPOINT 命令显式启动，或者当您进入包含 EXCEPTION 子句的 PL/pgSQL 块时隐式启动。
一些客户端驱动程序提供了一个选项，为每个 SQL 语句启动和结束子事务，例如 PgJDBC 的连接参数 "autosave"。注意它们的默认值。
每个子事务在执行需要 XID 的操作（例如，修改数据或锁定行）时分配自己的 XID。
元组头的 xmin 和 xmax 字段记录更新它的子事务的 XID。为了检查元组可见性，看到 xmin/xmax 的事务需要知道主事务（而不是子事务）是否已结束。
如何知道子事务的主事务
- 当子事务分配其 XID 时，它会将直接父 XID 记录在 $PGDATA/pg_subtrans/ 中。
- pg_subtrans 的结构是一个 XID 数组。例如，XID 100 的父 XID 存储在数组的第 101 个元素中。该数组被分成 8 KB 的页面。
- pg_subtrans 数据被缓存在 32 页的内存区域中。该区域由 SLRU（简单最近最少使用）缓冲区管理。因此，缓存可以包含 32 页 * 8 KB / 4 = 65,536 个事务。
- 因此，要获取主事务的 XID，需要遍历与子事务嵌套深度一样多的条目。
pg_subtrans 是否始终用于元组可见性检查？
- 不。快照不仅存储主事务的 XID，还存储子事务的 XID。如果检查器的快照包含所有子事务，它就可以完成工作，而无需查询 pg_subtrans。
- 但是，情况并非总是如此。每个后端在其共享内存中的 ProcArray 条目中最多可以有 64 个子事务 XID。如果主事务有超过 64 个子事务，则其 ProcArray 条目将被标记为溢出。
- 在创建快照时，会扫描所有正在运行事务的 ProcArray 条目，以收集主事务和子事务的 XID。如果任何条目被标记为溢出，则快照将被标记为子溢出。
- 子溢出快照不包含确定可见性所需的所有数据，因此必须使用 pg_subtrans 将元组的 xmin/xmax 追溯到其顶级事务 XID。
那么，问题是什么？
- pg_subtrans 的读取器与写入器竞争 lwlock 来保护 SLRU 缓冲区，写入器会注册其父 XID。读取器和写入器分别获取共享模式锁和排他模式锁。
- pg_subtrans 缓存并不大。在许多并发子事务下，会产生磁盘 I/O。
如何知道这种情况发生的可能性？
- 等待事件 LWLock:SubtransBuffer、LWLock:SubtransSLRU、IO:SLRURead 和 IO:SLRUWrite 不断增加。
- pg_stat_slru 显示 Subtrans 行中 blks_read 和 blks_hit 增加。（PostgreSQL 13+）
- pg_stat_get_backend_subxact(backend_id) 返回 subxact_count 和 subxact_overflow。（PostgreSQL 16+）

MultiXact 在幕后可能会损害性能

什么是 MultiXact？
- 一种机制，用于记录元组上多个锁定的 XID。（多事务）
- 元组头中的 xmax 字段记录锁定元组的 XID。
- 那么，当多个事务获取同一元组上的锁时会发生什么？
  - 例如，第一个具有 XID 100 的事务运行 SELECT ... FOR SHARE。xmax 变为 100。
  - 接下来，第二个具有 XID 101 的事务在同一元组上运行相同的 SELECT ... FOR SHARE。然后，分配一个新的 MultiXact ID，例如 1，并将其设置为 xmax 字段。
  - 从 MultiXact ID 1 到实际锁定器 XID（100、101）的映射添加到 $PGDATA/pg_multixact/ 中。
外键约束被实现为一个约束触发器，它执行 "SELECT ... FOR KEY SHARE"。因此，MultiXact 可能会在您不知情的情况下使用。
问题可能是什么？
- 与 pg_subtrans 一样，pg_multixact 也通过 SLRU 缓存。因此，它可能会遭受 lwlock 争用和磁盘 I/O 的困扰。
- 当一个 XID 被添加为现有 MultiXact ID 的新成员时，会分配一个新的 MultiXact ID，并将现有成员 XID 复制到一个新的位置。在上面的示例中，当 XID 102 加入具有成员 (100, 101) 的 MultiXact ID 1 时，会新分配 MultiXact 2，(100, 101) 被复制到那里，然后添加 102。如果许多事务同时锁定同一行，则此复制操作会变得更加繁重。
如何知道这种情况发生的可能性？
- 等待事件 LWLock:MultiXact*、IO:SLRURead 和 IO:SLRUWrite 不断增加。
- pg_stat_slru 显示 MultiXactOffset 和 MultiXactMember 行中 blks_read 和 blks_hit 增加。（PostgreSQL 13+）
- pg_get_multixact_members('<MultiXact ID>') 返回成员 XID 集及其锁定模式。

WAL 和检查点

检查点概述

一个处理过程，通过刷新未写入（=脏）的缓存数据来同步内存和存储中的数据。
何时执行？
- 自上次检查点以来，checkpoint_timeout 指定的时间已过去。
- 已积累一定量的 WAL，该量基于 max_wal_size。
- 在基础备份开始时（pg_basebackup、pg_backup_start()）。
- 关闭数据库实例。
- 完成任何形式的恢复。
- 其他必要的杂项计时，例如 CREATE DATABASE，以便数据文件可以在不经过共享缓冲区的情况下复制/移动。
由 checkpoint_timeout 引起的检查点称为计划检查点，而其他检查点称为请求检查点。
在完成检查点时，旧的 WAL 段文件会被删除或循环使用，作为新的 WAL 段文件以备将来重复使用，这基于 min_wal_size。
- 这里，“旧”意味着“不再需要用于崩溃恢复，因为这些 WAL 段中的所有更改都已持久保存到数据文件中”。
- 但是，旧的 WAL 段文件会一直保留，直到它们被归档且不再被 wal_keep_size 或任何复制槽位需要。
检查点是侵入性的，因为
- 数据和 WAL 的存储 I/O 竞争。
- 缓冲区内容锁 lwlock 竞争：当检查点进程正在刷新一个共享缓冲区时，它持有缓冲区内容锁处于共享模式，而一个修改相同缓冲区的事务需要一个独占锁，因此需要等待 lwlock 释放。
- 由于全页写入导致的 WAL 量增加。
什么是全页写入？
- 在检查点之后对每个数据页的第一次修改期间，会将整个页面内容记录到 WAL 中，而不仅仅是更改部分。这是为了在恢复期间恢复撕裂页所必需的。
- 如果 PostgreSQL 在写入页面时主机崩溃，则可能会导致撕裂页。因为 I/O 的原子单位通常小于 PostgreSQL 页面大小（通常为 8 KB）（例如，512 字节磁盘扇区），因此可能出现页面的一部分是新的，而另一部分是旧的。

减少检查点的影响

监控检查点的频率
- 在 pg_stat_bgwriter 中，checkpoints_timed 和 checkpoints_req 分别显示已安排的检查点数和请求的检查点数。
  - 大多数检查点应该被安排，而不是被请求。安排的检查点可以使负载在系统正常运行期间均匀分布。频繁的请求检查点可能会导致性能波动。
- 如果两次连续检查点之间的时间间隔短于 checkpoint_warning，并且较新的检查点是由 WAL 累积请求的，则服务器日志会显示以下消息。
  - LOG: 检查点发生得太频繁（间隔 8 秒）
  - HINT: 考虑增加配置参数“max_wal_size”。
通过增加 max_wal_size 和/或 checkpoint_timeout 来降低检查点的频率。
- 注意，这可能会增加崩溃恢复所需的时间。
将 wal_compression 设置为 on。这会减少全页写入的 WAL。
增加 min_wal_size。这会减少事务创建新 WAL 段文件的需要。

索引

索引的缺点

索引会占用磁盘空间。
更大的磁盘空间会增加物理备份的大小和持续时间。
索引会减慢 INSERT/DELETE/COPY 语句的速度，因为它们总是需要修改所有索引。
索引会阻止 HOT 更新。HOT 仅适用于对非索引列的修改。

您可能没有注意到的索引的好处

B 树索引可以加速 max() 和 min() 聚合函数。它们可以只读取索引末尾的索引条目。
表达式上的索引也会收集表达式计算值的统计信息。
- 例如：CREATE INDEX myindex1 ON mytable ((col1 + col2 * 3));
- 您可以查看索引表达式的统计信息。例如，在上面的情况下，统计信息会出现在 pg_stats 中，tablename=myindex1 和 attname=expr。
- 可以为索引表达式设置统计信息目标。例如：ALTER INDEX index_name ALTER COLUMN expr SET STATISTICS 1000;
外键上的索引会加速约束处理。
- 例如：CREATE TABLE orders (..., product_id int REFERENCES products ON CASCADE DELETE);
- 您可以使用 EXPLAIN ANALYZE 和 auto_explain 查看约束级联处理所花费的时间。外键约束在内部使用触发器实现。
  - 例如：EXPLAIN ANALYZE DELETE FROM products WHERE product_id = 2;
  - ... Trigger for constraint orders_product_id_fkey: time=0.322 calls=1

使索引仅扫描工作

使用 EXPLAIN ANALYZE 查看索引仅扫描需要读取堆的次数。例如，它会显示类似“Heap Fetches: 0”的内容。0 是最好的结果。
使 autovacuum 更积极或运行 VACUUM 来更新可见性映射。这将减少堆获取。

查询规划

ANALYZE 的陷阱

Autovacuum 不会对临时表或外部表运行 ANALYZE。手动对它们运行 ANALYZE。
即使表内容没有改变，ANALYZE 之后查询计划也会改变。
- ANALYZE 会对表内容进行随机抽样（300 x default_statistics_target 行）。因此，收集到的统计信息可能会因读取的行而异。
- 为了避免或减少这种查询计划差异，请执行以下任一操作：
  - 使用第三方软件（如 pg_hint_plan）修复查询计划。
  - 提高 ANALYZE 收集的统计信息量，即 ALTER TABLE ... ALTER COLUMN ... SET STATISTICS。使用的行越多，统计信息波动就越小。但是，这会使 ANALYZE 和查询规划变慢，因为会写入或读取更多统计信息。
  - 将表的存储参数 autovacuum_analyze_threshold 和 autovacuum_analyze_scale_factor 设置为较大的值，这样 autovacuum 实际上就不会对它运行 ANALYZE。然后，如果需要，进行手动 ANALYZE。

使用返回集函数可能会导致糟糕的查询计划

这在使用该函数在 WHERE 子句中筛选行或进行联接时很可能发生。
这是因为计划程序没有关于选择性的合理准确信息。因此，其成本估算将不准确。
CREATE/ALTER FUNCTION 可以设置固定成本和它返回的行数。由 SUPPORT 子句给出的计划程序支持函数（需要用 C 编写）可以动态更改成本和行数。
- CREATE FUNCTION ... RETURNS {SETOF ... | TABLE(...)} COST execution_cost ROWS result_rows SUPPORT support_function

自定义计划和通用计划

PREPARE 执行解析、分析和重写以生成准备好的语句。
- 例如：PREPARE stmt(int) AS SELECT * FROM mytable WHERE col = $1;
EXECUTE 生成查询计划并执行它。
- 例如：EXECUTE stmt(123);
考虑特定参数值的查询计划是最好的。这种计划被称为自定义计划。另一方面，不考虑参数值的查询计划被称为通用计划。
您可以通过占位符的存在来区分自定义计划和通用计划。例如，
- 自定义计划：Filter: (col = 123)
- 通用计划：Filter: (col = $1)
但规划成本很高。如果通用计划足够好，PostgreSQL 会使用它来避免创建自定义计划。
- PostgreSQL 对准备好的语句的前五次执行使用自定义计划。
- 在第六次执行时，会生成一个通用计划，并将其成本与前五次执行的平均成本进行比较。
- 如果通用计划的成本更低，则继续采用它。不会考虑自定义计划。
- 否则，会创建一个新的自定义计划并使用它。在随后的执行中，通用计划的成本会与所有过去执行的自定义计划的平均成本进行比较，选择成本更低的计划。
您可以通过将 plan_cache_mode 分别设置为 force_generic_plan 或 force_custom_plan 来强制使用通用计划或自定义计划。如果通用计划的成本估算被低估，则可能需要强制使用自定义计划。

参考

PostgreSQL 文档

许多连接

检测问题

记忆

存储

网络

表布局

SQL 技巧

事务

HOT

WAL 和检查点

锁

索引

查询规划

联接

日志记录

日志记录 - 隐藏的减速带

并行查询

PostgreSQL 中的并行处理

导入和导出

外部数据包装器 (FDW)

触发器

全文搜索

PostgreSQL 全文搜索与 VACUUM 有何关系

实用程序

操作速查表

简介

架构

客户端-服务器架构

逻辑数据库结构

数据库对象层次结构

对象标识符 (OID)

物理数据库结构

实例

读取和写入数据库数据

查询处理

参考

可靠性和可用性

连接

WAL

事务

锁

数据完整性验证

备份和恢复

流式复制

逻辑复制

参考

安全

验证

授权

参考

可管理性

记忆

存储

日志记录和调试

真空

升级

参考

应用程序开发

数据类型

序列

参考

可扩展性和性能

许多连接

检测问题

日志记录

导入和导出

外部数据包装器 (FDW)

全文搜索

实用程序

记忆

网络

游标

锁

HOT

表布局

事务

WAL 和检查点

索引

查询规划

参考

导航菜单

搜索