Postgres

PostgreSQL 在读已提交级别执行普通表读取时，会使用为当前语句建立的快照。即使另一个事务在读取尚未结束时提交，表扫描仍沿用原来的可见性边界；下一条语句则可能看到新提交。因此，同一个事务可以得到各自一致、却在两条语句之间不同的答案，并不表示任何一条语句看到了别人未提交的更新中间状态。先明确观察边界，才能判断这种变化是不是业务允许的结果。 指明真正使用的边界 读已提交是 PostgreSQL 的默认隔离级别，但角色、数据库或会话可以修改配置默认值。因此，只看到不带限定的 BEGIN，不能证明应用到底用了哪一档。下面的实验明确选择读已提交。可重复读则复用事务第一条非事务控制语句建立的快照；仅仅开始事务，并不等于已经取得那份实际读取快照。 快照约束的是其他事务修改的可见性。读取事务自己此前执行的写入，仍然对后面的语句可见。因此，一致快照并不是把所有来源的值都无条件冻结，也不保证应用稍后根据旧结果行动时，相关数据仍然是最新的。读取时看到一致状态，与写入提交时仍满足决策前提，是不同要求。 坏掉的样子 两次读已提交查询可以看到不同的已提交数据集合。例如先统计数量、等待一段时间，再取对应记录，如果其他事务在中间修改数据，数量与列表可能对不上。若业务要求这两项观察来自同一视图，应组合为合适的一条查询，或者为跨多条语句的读取选择适当的事务级快照。不能用“它们放在同一个事务里”替代隔离级别的具体保证。 也不要把普通读取规则推广到所有写入或加锁读取。读已提交下，更新可能先等待另一个写者，随后针对更新后的行重新判断条件；SELECT FOR UPDATE 也有明确的加锁行为。此前稳定的读取，并没有预订那一行，更没有自动建立跨行业务不变量。隔离级别、原子更新和显式协调分别回答不同的并发问题，需要根据实际操作组合使用。 普通表扫描的边界，也不能直接推广到 VOLATILE SQL 或过程语言函数内部发起的查询。这类函数可以为内部查询取得新快照，STABLE 和 IMMUTABLE 函数则使用调用查询的快照。本例的建议锁门闩没有再次读取业务表，因此不能据此推断，任意通过函数组装的报表都共享同一条可见性边界。 快速验证 在可丢弃的 PostgreSQL 18 数据库中打开甲、乙两个新的 psql -X -v ON_ERROR_STOP=1 会话，使用同一角色，显式事务之外保留正常自动提交。模式名称和建议锁键仅供本实验使用。建议锁只是控制执行先后的门闩，并不锁住正在被修改的业务表记录。 在乙中创建初始值，并持有会话级门闩。 CREATE SCHEMA statement_snapshot_demo; CREATE TABLE statement_snapshot_demo.item (id integer PRIMARY KEY,value integer); INSERT INTO statement_snapshot_demo.item VALUES (1,100); SELECT pg_advisory_lock(8610,1); 在甲中启动读已提交语句。它建立语句快照后会等待门闩；保持它运行，再操作乙。 SET application_name='xbcatm_snapshot_reader'; SET statement_timeout='60s'; BEGIN ISOLATION LEVEL READ COMMITTED; WITH gate AS MATERIALIZED (SELECT pg_advisory_xact_lock(8610,1)) SELECT value FROM statement_snapshot_demo.item CROSS JOIN gate WHERE id=1; 在乙中观察甲。只有结果显示 Lock 与 advisory 后才继续，证明语句确实已经开始执行，而不是根据估计的等待时间猜测。 SELECT wait_event_type,wait_event FROM pg_stat_activity WHERE application_name='xbcatm_snapshot_reader' AND state='active'; 仍在乙中通过自动提交写入新值，然后释放门闩，再返回甲收集读取结果。 UPDATE statement_snapshot_demo.item SET value=200 WHERE id=1; SELECT pg_advisory_unlock(8610,1); 在甲中发起新的读取并结束事务，最后在乙中移除夹具。 SELECT value FROM statement_snapshot_demo.item WHERE id=1; COMMIT; DROP SCHEMA statement_snapshot_demo CASCADE; 原文的 PostgreSQL 18.6 观察未在本轮重跑，其中甲确实停在建议锁等待上。乙在释放门闩之前已经把存储值改为二百，解锁函数返回真；但甲那条已经开始运行的语句仍返回一百。甲在同一个显式事务里执行的下一条语句，才返回二百。这个门闩让执行顺序可以观察，不依赖操作人员复制命令的速度，也不把单纯等待几秒当作并发证据。 ...

PostgreSQL 的 UNLOGGED 表保留正常 SQL 事务行为，但放弃其内容的崩溃恢复能力。它适合能够重复生成的暂存数据，不适合把唯一副本托付给一次提交。判断边界是服务器是否经历了非正常关闭后的恢复，不能简单地把“重启”两个字等同于数据丢失，也不能因为一次正常重启后行还在，就认为它具备持久性保证。 哪些内容跳过 WAL 非日志表的数据变化及其索引变化不写入预写日志。系统目录的变化仍可能产生 WAL，同一个事务还可能修改普通表，所以不能把整个工作负载描述为“完全没有 WAL”。表的数据页仍然占据存储空间，仍要经过缓冲区管理和后续写回；UNLOGGED 并不是内存表，也不是不需要磁盘的缓存。写入收益来自减少特定日志工作，实际改善幅度需要测量，不能承诺固定倍数。 服务器正常运行时，事务、快照、约束和回滚仍然生效。语句被拒绝或应用断开连接，并不意味着此前提交的全部行都会消失。但是后端进程异常退出可能导致服务器重启其他进程并进入恢复，因此也不能声称“应用事件绝不影响非日志表”。排查时应查看 PostgreSQL 日志和恢复过程，而不是仅凭事故最初发生在哪一层就推断数据状态。 重启与复制的边界 干净关闭后，非日志表内容会保留到下次启动。pg_ctl 的 fast 模式会回滚正在执行的事务并正常关闭，immediate 模式则直接中止进程，不完成干净关闭。后者需要崩溃恢复，恢复期间非日志关系会被重置：表定义仍在，但此前提交的行不再存在。主机维护若完成了干净关闭，并不会仅因为“重启”就清空这类表；反过来，不能把正常关闭的测试当成抗崩溃证明。 物理备库不会收到非日志表的数据内容。备库仍处于恢复模式时，读取这类关系会报错，而不是成功返回零行。提升为主库后可以读取它，但没有已经复制过来的行可以恢复。因此故障切换流程必须先重建或重新填充，再允许依赖这些数据的消费者进入服务。主备拥有同一个表定义，只说明目录信息存在，并不代表表中业务数据已经复制。 普通日志表不能用外键引用非日志表。PostgreSQL 会拒绝这样的建表语句，因为持久化的子表引用不能安全依赖可能在恢复中消失的父表行。下面的实验直接检查这一限制，不会把它描述成“外键还在、父表行没了、数据库默默允许不一致”。设计暂存区时，要把关系约束放到符合持久性边界的位置，而不是事后修补孤立记录。 坏掉的样子 一个常见事故是导入程序把文件装入非日志暂存表后就确认成功，并删除唯一的原始文件。随后服务器崩溃，暂存行消失，而输入已无处重取，这就变成不可恢复的数据丢失。原始输入应该至少保留到持久化目标提交成功，重建流程也必须知道需要哪一个输入版本。去重记录和导入检查点要属于持久化契约，不能和暂存内容一起丢掉。 另一个问题出现在报表服务改读备库，或刚完成提升就立即对外服务。只检测行数为零会漏掉恢复模式下的读取错误；无限重试也无法生成不存在的数据。应明确标记“正在重建”，在重建完成且结果验证通过前控制请求准入。演练要测量重建耗时、上游是否可用、记录数及内容是否符合预期；只建回一张空表不算恢复，能运行的脚本也不等于已经证明恢复时间满足要求。 快速验证 把 Bash 代码保存为 unlogged.sh。它要求以非 root 用户执行 PostgreSQL 18 的命令行工具。下面的容器命令提供工具，不发布网络端口，也不挂载宿主机数据；两个实验数据库都位于容器内的新临时目录。 docker run --rm --network none --user postgres --entrypoint bash -i \ postgres:18-alpine < unlogged.sh #!/usr/bin/env bash set -euo pipefail pg_lab=$(mktemp -d /tmp/unlogged-lab.XXXXXX) cleanup() { for cluster in standby primary; do if [ -f "$pg_lab/$cluster/postmaster.pid" ]; then pg_ctl -D "$pg_lab/$cluster" -m immediate -w stop >/dev/null fi done rm -rf -- "$pg_lab" } trap cleanup EXIT primary() { psql -X -h "$pg_lab" -p 55432 -d postgres -v ON_ERROR_STOP=1 "$@"; } standby() { psql -X -h "$pg_lab" -p 55433 -d postgres -v ON_ERROR_STOP=1 "$@"; } start_primary() { pg_ctl -D "$pg_lab/primary" -l "$pg_lab/primary.log" \ -o "-k $pg_lab -p 55432 -c listen_addresses=''" -w start >/dev/null } initdb -D "$pg_lab/primary" --no-locale --encoding=UTF8 --auth=trust >/dev/null start_primary primary <<'SQL' CREATE TABLE durable (id integer PRIMARY KEY); CREATE UNLOGGED TABLE scratch (id integer PRIMARY KEY); INSERT INTO durable VALUES (1); INSERT INTO scratch VALUES (1); BEGIN; INSERT INTO scratch VALUES (2); ROLLBACK; DO $$ BEGIN EXECUTE 'CREATE TABLE durable_ref (id integer REFERENCES scratch(id))'; RAISE EXCEPTION 'a logged table unexpectedly referenced an unlogged table'; EXCEPTION WHEN invalid_table_definition THEN RAISE NOTICE 'logged-to-unlogged foreign key rejected'; END $$; SQL counts='SELECT (SELECT count(*) FROM durable), (SELECT count(*) FROM scratch)' test "$(primary -Atc "$counts")" = '1|1' pg_ctl -D "$pg_lab/primary" -m fast -w stop >/dev/null start_primary test "$(primary -Atc "$counts")" = '1|1' printf 'clean restart: 1|1\n' pg_basebackup -h "$pg_lab" -p 55432 -D "$pg_lab/standby" -R -X stream -c fast pg_ctl -D "$pg_lab/standby" -l "$pg_lab/standby.log" \ -o "-k $pg_lab -p 55433 -c listen_addresses=''" -w start >/dev/null test "$(standby -Atc 'SELECT pg_is_in_recovery()')" = t test "$(standby -Atc 'SELECT count(*) FROM durable')" = 1 if standby -Atc 'SELECT count(*) FROM scratch' 2>"$pg_lab/read-error"; then echo 'unlogged read unexpectedly succeeded on standby' >&2 exit 1 fi grep -F 'cannot access temporary or unlogged relations during recovery' "$pg_lab/read-error" pg_ctl -D "$pg_lab/standby" -w promote >/dev/null test "$(standby -Atc "$counts")" = '1|0' printf 'promoted standby: 1|0\n' pg_ctl -D "$pg_lab/primary" -m immediate -w stop >/dev/null start_primary test "$(primary -Atc "$counts")" = '1|0' printf 'crash recovery: 1|0\n' primary -Atc 'SHOW server_version' echo 'PASS: rollback, foreign key restriction, clean restart, standby, promotion, crash' 主库与备库使用同一个临时目录中的不同 Unix 套接字端口，退出时的 trap 只关闭这两个实验集群并移除其文件。实验故意对临时主库执行 immediate 关闭，不能把这一步改成已有数据库目录。PostgreSQL 18.6 上，回滚和正常重启后的计数都是 1|1；仍处于恢复模式的备库拒绝读取 scratch。备库提升和主库崩溃恢复后分别得到 1|0，表明持久化行还在而暂存内容不在。错误文本断言使用集群的 C locale，避免本地化消息造成误判。 ...

GIN 索引从复合值中提取可搜索的键，并记录这些键对应哪些行。全文文档、数组和 JSONB 都可以使用这种访问方法，但每个索引表达式仍需要匹配的操作符类。GIN 提供的是一组支持特定谓词的搜索能力，不是一建就能自动加速所有字段、类型和查询条件的万能索引。评估它之前，必须先明确应用到底怎样查找数据。 索引的是搜索表示 全文搜索中，to_tsvector 把正文转换成规范化词项，tsquery 描述要匹配的条件。建在这个向量上的 GIN 可以先缩小候选行集合，避免每次请求都解析全部文档。但是常见词仍可能对应很大的候选集合，后续读取行、复查条件、计算排名和排序都可能成为主要开销。索引查得快，并不代表整个搜索页面一定快，更不意味着返回所有匹配项没有成本。全文 GIN 保存词项而不保存权重标签，因此带权重限制的搜索需要复核行，不能只从索引候选就判断完全匹配。 文本配置本身属于查询语义。English 配置可能把 “outage” 词干化成不同于 simple 配置的词项，所以建立在 to_tsvector('english', body) 上的表达式索引，需要查询使用匹配的表达式。省略配置会让转换依赖会话设置，也不会匹配这个显式 English 索引。两参数形式还保证索引中的转换不随默认配置漂移，否则相同正文可能在不同时间产生不同词项。 其他 GIN 用法使用不同操作符。JSONB 的默认操作符类与 path 操作符类支持的条件并不完全相同，数组包含索引也不是全文索引。应根据真实谓词选择列、表达式和操作符类，而不是看到一种数据类型支持 GIN 就直接添加。如果应用查询不兼容，索引仍消耗存储和写入资源，却无法带来读收益；这种情况比没有索引更容易给人错误信心。 待处理条目仍可搜索 一篇文档可能贡献许多键，所以维护倒排结构的写入工作量较大。启用 fastupdate 时，新条目可以先进入 pending list，再合并到主索引结构。查询会同时搜索待处理列表和主结构，因此它调整的是维护工作的时机及额外查询成本，不是用延迟可见性换写入速度。当前快照中可见的新行，在列表清理之前也必须能够被查到。 VACUUM、自动 ANALYZE、显式清理函数或列表超过阈值，都可能触发合并。列表过大会拖慢搜索，前台写入碰上清理周期也可能出现延迟尖峰。增大阈值只是改变什么时候付出成本，并没有消除维护工作。调整前应结合导入速度、查询延迟、自动维护行为和维护窗口评估，不能把某个配置值当成对所有业务都适用的答案。 坏掉的样子 常见回归是应用重构后改了文本配置或表达式，查询仍返回正确结果，却失去了原先预期的访问路径。检查 EXPLAIN 中实际使用的条件和索引，先确认语义一致，再讨论规划器选择。小表采用顺序扫描本身并不能证明配置错误；但 simple 表达式确实不能拿 English 表达式索引当成相同语义使用，强行关闭顺序扫描也不会修正这种不匹配。 ...

表达式索引把计算结果保存为索引键。查询比较 lower(email) 时，可以搜索预先计算的键，而不用对整张表逐行执行 lower。代价是索引创建和维护阶段需要完成计算与存储。它不会修改原始 email，也不保证任何查询都会选择这个索引，更不意味着表达式文本有一点不同就必然失去匹配。 索引计算后的含义 lower(email) 索引保存的是与行关联的规范化结果，使用相同计算含义的等值条件可以成为索引条件。直接比较原始 email 的语义不同，不会自动获得这条搜索路径。同样，先 trim 再 lower 增加了一项操作，现有索引不能假定去除空白永远不会改变数据。因此是否匹配要看计算本身，而不是只看它们都涉及 email 列。 PostgreSQL 匹配解析后的表达式，并进行某些简化。多加几层括号本身不会破坏匹配，因此查询不需要与索引定义逐字完全相同。反过来，数学上等价的写法也不一定都能被识别为相同。应检查计划中的实际条件和索引表达式定义，不能只做表面字符串比较，也不能承诺规划器能够证明任意表达式等价。 唯一表达式索引还能执行大小写规范化后的唯一性规则。这是在改变允许存储的数据，不只是提速。已有行规范化后发生冲突，会阻止索引创建；未来插入也会面对新约束。单纯 lower 不是邮箱等价性的完整定义，其他文本还可能受 locale 和 collation 影响。应先明确业务规范化规则，再决定把哪一种计算结果作为约束依据。 让被索引的值保持稳定 索引表达式中的函数必须满足 PostgreSQL 的不可变性要求：结果应依赖输入参数，而不能随着当前时间或外部状态自行变化。now() 不能被当成永远稳定的计算值建索引。为了绕过检查而把函数随意标为 IMMUTABLE，并不会让函数真的不可变，反而可能使已存索引键与后续计算结果不一致，产生比慢查询更严重的正确性问题。 表达式在插入及需要维护索引的更新时计算。即使规划器从不选它，存储和维护成本仍然存在。修改自定义函数行为或规范化策略时，也必须考虑已经保存在索引里的旧结果；仅替换函数代码不会重新计算所有键。重建索引和验证已有数据，应当属于这次语义变更的一部分，而不是上线后发现异常才补做。 坏掉的样子 ORM 重构可能把条件改成 lower(trim(email))，索引却仍保存 lower(email)。查询结果仍正确，但执行器可能扫描更多行。另一种问题是排序规则或自定义函数改变后，代码仍依赖旧键。排查时要对照最终生成的 SQL、索引定义、相关函数行为与目标环境，不能仅根据源代码中的一个方法名推断数据库收到什么。 把唯一表达式索引当成无害的性能调整也很危险。已有规范化重复值会让创建失败，未来写入则受到新的唯一约束。应审计并按业务语义处理冲突，不能为了建索引悄悄删除记录。生产创建还需要选择合适的方式并验证完整结束；目录里存在一个索引名称，不足以证明它已经成功构建并可用，失败的并发构建可能留下需要处理的状态。 快速验证 保存为 expression.sql，执行 psql -X -v ON_ERROR_STOP=1 -f expression.sql，连接可丢弃的 PostgreSQL 18 数据库。实验的一万个地址使用保留的 example.test 域名，表和索引都是临时对象，不会接触真实账户。异常块用于检查错误边界，而不只是展示一条成功查询。 ...

连接策略负责组合两路输入，并不是某段 SQL 永久绑定的属性。只查询三个客户时，通过索引反复定位购买记录可能很合算；查询所有购买记录时，扫描一遍并构建哈希表可能更合适。PostgreSQL 根据预计工作、可用访问路径、顺序和内存预期，在合法候选中选择。表有多大确实重要，但过滤后还剩多少行，比表的名字或它平时被称为“大表”更重要。 反复查找与复用查找表 嵌套循环每取一条外层记录，就利用这条记录执行一次内层路径。内层并不一定扫描整张表，也可以进行索引或位图查找。外层结果很少、内层定位足够精确时，重复执行的成本可能很低。读计划时，应把内层节点的每轮行数与 loops 放在一起看，才能知道实际重复了多少工作，而不是看到循环就直接判断它低效。 哈希连接从一路输入建立临时哈希表，再用另一边的记录探测匹配。这是执行期间的数据结构，与 USING hash 建立的持久索引没有直接关系；不需要先建一个哈希索引才能使用哈希连接。真正的构建侧是计划中 Hash 节点下方的子树。应查看这个位置，不能假设 SQL 里写在左边或右边的表必然负责构建，也不能只比较两张基础表的原始大小。 如果两路输入已经具备适当顺序，或者排序的成本值得支付，合并连接也是候选。因此，嵌套循环和哈希连接并没有穷尽规划器的全部选择。不同过滤条件、输出要求和合法连接条件，都可能改变哪些方案值得考虑。算法名称只是计划的一部分，不能代替对输入与结果规模的分析。 坏掉的样子 外层结果被低估时，原本看似便宜的单次内层查找会被重复过多次数。哈希连接也可能因为超出内存预算而分成多批，产生临时读写，并不保证始终完整留在内存中。应检查批次数和临时缓冲区活动，不能看见 Hash 就推导出“恒定的单次查找成本”或“所有规模下都线性且很快”。 大量重复连接键还可能产生巨大的结果集合，无论使用哪种查找策略都需要产生查询要求的那些行。意外的多对多连接，应通过修正关系或查询语义解决，而不是只把连接节点换个名字。如果两侧每个键都重复很多次，乘出来的输出规模本身就可能是主要瓶颈。 原生 PostgreSQL 不会解释 /*+ HashJoin */ 这样的优化器提示注释。扩展可以提供提示功能，但那属于另外的环境契约。规划器开关可以支持受控对比实验，不过不能因为一次小数据实验，就永久在全局关闭某种连接方式；其他查询的输入和收益可能完全不同。 快速验证 在 PostgreSQL 18 的 psql 中执行下面事务。它创建五百个客户和五万条购买记录，每个客户对应一百条购买记录，并在引用列上建立普通 B-tree 索引。两条查询计算同一种连接关系的金额总和，区别只在于是否限制客户范围。 \set ON_ERROR_STOP on BEGIN; CREATE TEMP TABLE customers (id integer PRIMARY KEY); CREATE TEMP TABLE purchases (id integer PRIMARY KEY,customer_id integer,amount integer); INSERT INTO customers SELECT g FROM generate_series(1,500) AS g; INSERT INTO purchases SELECT g,1+(g-1)%500,10 FROM generate_series(1,50000) AS g; CREATE INDEX purchases_customer ON purchases(customer_id); ANALYZE customers; ANALYZE purchases; EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT sum(p.amount) FROM customers c JOIN purchases p ON p.customer_id=c.id WHERE c.id<=3; EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT sum(p.amount) FROM customers c JOIN purchases p ON p.customer_id=c.id; DO $$ BEGIN IF (SELECT sum(p.amount) FROM customers c JOIN purchases p ON p.customer_id=c.id WHERE c.id<=3) IS DISTINCT FROM 3000::bigint THEN RAISE EXCEPTION 'wrong selective join'; END IF; IF (SELECT sum(p.amount) FROM customers c JOIN purchases p ON p.customer_id=c.id) IS DISTINCT FROM 500000::bigint THEN RAISE EXCEPTION 'wrong broad join'; END IF; END $$; ROLLBACK; PostgreSQL 18.6 实测中，限定客户编号不超过三时采用嵌套循环。内层位图堆扫描每轮返回一百条购买记录，一共执行三轮。连接输出三百行，金额总和为三千。嵌套循环里出现位图扫描是正常组合：扫描节点描述内层记录怎样被取到，连接节点描述怎样反复利用外层记录组合结果，两者不是互斥选项。 ...

部分索引可以把高频访问的少量行放在更小的结构中。假如 open 任务很少，而请求多数只找 open 任务，在 id 上建立 WHERE status = 'open' 的索引就值得评估。但它是否可用，取决于规划器能为当前查询证明什么，不只是应用在某次执行时碰巧传入了 open。准备语句的计划方式会直接影响这份证据。 证明不需要被排除的行 索引里没有 closed 任务，因此不能独立回答可能需要 closed 行的查询。PostgreSQL 必须在规划时识别查询条件蕴含索引谓词。显式写出的 status = 'open' 提供了这个事实；通用计划中的 status = $1 则没有，因为同一份计划还必须在参数为 closed 时保持正确。规划器不能依靠应用的口头约定丢弃可能需要的数据。 蕴含关系不总是逐字相同。PostgreSQL 能识别一些简单逻辑关系，但不是任意表达式的定理证明器。统一真实需要的谓词，有助于避免重构把原本可用的索引条件藏起来。仅过滤索引键 id 也不能证明状态为 open，即使当前数据或业务习惯让它经常如此。性能优化必须保持查询语义，不能把偶然数据状态当成永久约束。 准备计划改变可用信息 准备语句可以使用根据具体参数规划的 custom plan，也可以使用跨参数复用的 generic plan。状态参数为 open 时，定制计划可能知道所需谓词并考虑部分索引；通用计划面对未知状态，就不能把该索引当成完整答案路径。SQL 文本没有变化，改变的是规划阶段掌握的信息，这也是同一语句不同执行阶段出现不同计划的原因之一。 ...

物化视图保存定义查询的结果，让后续读取直接访问这些已存储的行。它把每次读取时的计算转移到刷新阶段，适合重复执行的报表查询。相应代价是明确的新鲜度边界，以及另一份需要维护的关系数据；它既不保证读取瞬间完成，也不会自动与源表保持同步。选择时应比较完整的读写成本。 已存储结果有自己的生命周期 普通视图保存查询定义，物化视图还保存查询结果。PostgreSQL 可以给物化结果建立索引，并直接针对它规划读取，而不必每次重跑原来的关联与聚合。源表发生普通插入或更新时，这份结果不会自动变化。应用也需要明确查询物化视图；创建它并不会让所有等价的源表查询自动改走这份缓存。 REFRESH MATERIALIZED VIEW 重新执行定义查询，再替换存储内容。标准 PostgreSQL 的刷新不是自动增量维护；即使源表只变了几行，定义查询仍然需要生成新的结果。比较物化与反复直接查询时，要把刷新计算、临时工作、存储和索引维护都计入，而不能只测读缓存的那条快速查询。 存储结果本身仍可能很大，或者缺少适合的索引，因此物化不代表任意读取都便宜。应根据报表过滤条件和返回字段，确定聚合粒度和索引。需要稳定顺序时，还应在读取查询中使用 ORDER BY；刷新不承诺保留定义查询所产生的物理排列，不能把上次看到的顺序当成输出契约。 并发刷新改变的是读者访问方式 普通刷新在替换物化内容时可能阻塞读取。CONCURRENTLY 允许 SELECT 在刷新期间继续访问已有结果，新内容何时可见仍遵循事务与快照规则。它依然需要锁、消耗资源，并可能等待其他操作；同一个物化视图在同一时刻也只能运行一个刷新任务，并不是允许任意多次刷新相互并行。 并发刷新要求视图已经有数据，并至少存在一个适用的唯一索引：索引只能使用列名，而且必须覆盖全部行。部分唯一索引或表达式索引都不满足这个条件。该索引用于刷新时识别结果行，并不意味着源表因此变得不可变，也不意味着源表在相同聚合粒度上具备同样的唯一性。 WITH NO DATA 会把视图变为未填充、不可扫描的状态。它不等于一个可以正常 SELECT 的空结果，也不会保留旧缓存答案。需要先通过普通刷新填充，再请求并发刷新。CONCURRENTLY 与 WITH NO DATA 不能组合成绕过这些前置条件的捷径，部署时应区分“零行有效结果”和“尚未生成结果”。 坏掉的样子 仅有刷新计划，并不能保证陈旧程度有上限。任务可能晚启动、运行很久、失败，或者排队等待另一个刷新；生成结果所用的源快照，也早于刷新工作完成的时刻。应记录成功刷新及报表所需的源数据覆盖范围，不能把配置了某个时间间隔当成数据已经及时更新的证据。失败任务不能像成功发布新结果一样推进页面的新鲜度标记。 部署预期使用并发读取路径时，缺少唯一索引或索引形态不符，会导致 CONCURRENTLY 直接失败。正式依赖前，应实际测试这条路径。PostgreSQL 十八的刷新还要求适用的 MAINTAIN 权限，所以定时任务所用角色必须确实能够刷新该关系。以所有者身份手工执行成功，不能证明任务角色也拥有所需权限。 刷新期间，search_path 会临时变为 pg_catalog、pg_temp。如果定义查询调用了使用未限定动态 SQL 的函数，交互调用成功也不能证明刷新时能找到相同对象。需要检查函数内部的名称解析，而不只看保存下来的视图定义。本例直接引用表，没有验证依赖这类函数的刷新，也没有验证调度器角色的权限。 ...

连续 WAL 归档把已经完成的日志文件保存在 PostgreSQL 活跃日志目录之外。配合合适的基础备份和不中断的恢复链，这些文件允许恢复过程把有日志保护的数据库变更重放到覆盖范围内的目标。归档与普通提交确认是两条不同的路径：事务可能已经提交，但所在日志段还没有结束，或者归档目的地仍然不可用。 坏掉的样子 PostgreSQL 在执行过程中会修改缓冲区里的数据页；依赖某段日志的数据页持久化之前，相应 WAL 必须先持久化。这不表示数据库从不写数据文件，也不表示每次提交都要等待归档完成。临时表和不记录日志的表内容，也不会仅因开启归档就获得普通 WAL 所提供的恢复保证。把这些边界混在一起，会高估某份备份能恢复的内容。 归档命令的返回码是一条数据完整性契约。返回零意味着文件已经成功保存；其他保留条件也满足后，数据库就可以回收源文件。如果复制失败却返回成功，这条契约便被悄悄打断。非零返回会触发重试，持续失败会使尚未归档的日志留在主库，最终可能填满文件系统。执行检查点既不会修好目的地，也不会解除这份保留义务。 已有文件名必须仔细处理。内容完全相同且已经可靠落盘的重复归档应当成功；同名但字节不同则必须失败，并保留原文件。仅因为目标已经存在就返回成功，会掩盖损坏，或掩盖两个不同集群错误地共用同一个归档命名空间。这里不能用“文件存在”替代“这份内容已经安全保存”。 快速验证 使用非超级操作系统账户运行下面的脚本，并把 PostgreSQL 18 服务端和客户端程序放入 PATH。环境需要支持保留属性复制、临时文件、硬链接以及按文件同步的 Linux 工具。本次在 PostgreSQL 18.6 上执行。脚本创建自己的私有临时集群与 Unix 套接字，关闭网络监听，只写测试数据。本地归档用于展示命令契约，并不是独立故障域里的灾难恢复副本。 #!/bin/sh set -eu umask 077 lab=$(mktemp -d /tmp/xbcatm-archive.XXXXXX) mkdir "$lab/socket" "$lab/archive" primary=$lab/primary cleanup() { if test -f "$primary/postmaster.pid"; then pg_ctl -D "$primary" -m fast -w stop >/dev/null fi } trap cleanup EXIT initdb -D "$primary" --no-locale --encoding=UTF8 --auth-local=trust --auth-host=reject >/dev/null cat > "$lab/archive-copy.sh" <<'ARCHIVE_HELPER' #!/bin/sh set -eu wal_source=$1 wal_name=$2 archive_directory=$3 archive_target=$archive_directory/$wal_name if test -e "$archive_target"; then cmp -s "$wal_source" "$archive_target" sync "$archive_target" "$archive_directory" exit 0 fi archive_temporary=$(mktemp "$archive_directory/.incoming.XXXXXX") trap 'rm -f "$archive_temporary"' EXIT HUP INT TERM cp "$wal_source" "$archive_temporary" sync "$archive_temporary" ln "$archive_temporary" "$archive_target" || cmp -s "$wal_source" "$archive_target" sync "$archive_target" "$archive_directory" ARCHIVE_HELPER chmod 700 "$lab/archive-copy.sh" cat >> "$primary/postgresql.conf" <<CONF listen_addresses = '' unix_socket_directories = '$lab/socket' port = 56432 wal_level = replica archive_mode = on archive_command = '$lab/archive-copy.sh "%p" "%f" "$lab/archive"' CONF pg_ctl -D "$primary" -l "$lab/primary.log" -w -t 15 start >/dev/null export PGHOST=$lab/socket PGPORT=56432 PGDATABASE=postgres PGUSER=$(id -un) export PGCONNECT_TIMEOUT=2 psql -X -v ON_ERROR_STOP=1 -qAt -c 'CREATE TABLE archive_marker (id integer PRIMARY KEY)' pg_basebackup -D "$lab/base" -X stream -c fast -w pg_verifybackup -q "$lab/base" echo base_backup_verified failures_before=$(psql -X -v ON_ERROR_STOP=1 -qAt -c 'SELECT failed_count FROM pg_stat_archiver') psql -X -v ON_ERROR_STOP=1 -qAt <<'SQL' ALTER SYSTEM SET archive_command='/bin/false'; SELECT pg_reload_conf(); SQL attempt=0 while test "$(psql -X -v ON_ERROR_STOP=1 -qAt -c "SELECT current_setting('archive_command')='/bin/false'")" != t; do attempt=$((attempt+1)) test "$attempt" -lt 100 || exit 1 sleep 0.1 done psql -X -v ON_ERROR_STOP=1 -qAt -c 'INSERT INTO archive_marker VALUES (1)' -c 'SELECT pg_switch_wal()' >/dev/null attempt=0 while test "$(psql -X -v ON_ERROR_STOP=1 -qAt -c "SELECT failed_count>$failures_before FROM pg_stat_archiver")" != t; do attempt=$((attempt+1)) test "$attempt" -lt 700 || { echo 'archive failure was not observed' >&2; exit 1; } sleep 0.1 done failed_segment=$(psql -X -v ON_ERROR_STOP=1 -qAt -c 'SELECT last_failed_wal FROM pg_stat_archiver') test ! -f "$lab/archive/$failed_segment" echo archive_failure_observed psql -X -v ON_ERROR_STOP=1 -qAt -c CHECKPOINT test -f "$primary/pg_wal/$failed_segment" echo unarchived_wal_retained_after_checkpoint archived_before=$(psql -X -v ON_ERROR_STOP=1 -qAt -c 'SELECT archived_count FROM pg_stat_archiver') psql -X -v ON_ERROR_STOP=1 -qAt <<'SQL' ALTER SYSTEM RESET archive_command; SELECT pg_reload_conf(); SQL attempt=0 while ! test -f "$lab/archive/$failed_segment" || test "$(psql -X -v ON_ERROR_STOP=1 -qAt -c "SELECT archived_count>$archived_before FROM pg_stat_archiver")" != t; do attempt=$((attempt+1)) test "$attempt" -lt 700 || { echo 'archive repair was not observed' >&2; exit 1; } sleep 0.1 done cmp -s "$primary/pg_wal/$failed_segment" "$lab/archive/$failed_segment" echo archive_repaired "$lab/archive-copy.sh" "$primary/pg_wal/$failed_segment" "$failed_segment" "$lab/archive" echo identical_repeat_accepted printf 'different bytes\n' > "$lab/conflicting-input" if "$lab/archive-copy.sh" "$lab/conflicting-input" "$failed_segment" "$lab/archive"; then echo 'conflicting archive input was accepted' >&2 exit 1 fi cmp -s "$primary/pg_wal/$failed_segment" "$lab/archive/$failed_segment" echo conflicting_repeat_rejected printf 'lab_directory %s\nfailed_segment %s\n' "$lab" "$failed_segment" 首先应出现基础备份已经验证的标记，然后脚本把归档命令临时改成一个确实返回失败的程序。它等待统计中真实出现失败，确认失败文件没有进入归档，再请求检查点，并核对主库仍然保留原始日志。恢复归档命令后，只有进度增加且指定目标文件出现，才打印归档修复标记；随后还比较双方字节，检查复制出来的实际内容。 ...