PG Schema、FDW 与 VACUUM - 面试题

#

★★★

1. PostgreSQL 的角色（Role）与用户（User）的差异，CREATE ROLE vs CREATE USER？

问题

请解释 PostgreSQL 中角色（Role）与用户（User）的区别，以及 CREATE ROLE 与 CREATE USER 两个命令的差异？

考察点

角色与用户的统一模型
CREATE ROLE 与 CREATE USER 的语法差异
LOGIN 属性与角色管理

答案

PostgreSQL 中角色（Role）与用户（User）本质上是同一个概念，二者都存储在 pg_roles 中。区别仅在于 CREATE USER 等价于 CREATE ROLE ... LOGIN，即创建用户时默认赋予 LOGIN 属性（允许登录），而 CREATE ROLE 默认不具备 LOGIN 属性。用户就是具有 LOGIN 属性的角色，角色可以包含其他角色（通过 GRANT 或 INHERIT 实现角色继承），现代 PostgreSQL 中"用户"和"角色"都是角色，只是按是否允许登录来区分。

解析

这种统一模型简化了权限管理——可以把角色当作一组权限的集合，赋给具体用户，实现权限的集中管理。实际中创建只用于登录的账号用 CREATE USER，创建用于权限分组的账号用 CREATE ROLE。

示例代码

CREATE ROLE app_role;                    -- 不可登录，仅作权限分组
CREATE USER app_user WITH PASSWORD 'x'; -- 等价于 CREATE ROLE ... LOGIN
GRANT app_role TO app_user;              -- 用户继承角色权限

#

★★★

2. PostgreSQL 的权限系统，GRANT、REVOKE、ACL？

问题

请描述 PostgreSQL 的权限系统，包括 GRANT、REVOKE 命令以及 ACL（访问控制列表）是如何工作的？

考察点

GRANT/REVOKE 的基本语法
ACL 的存储与表示
对象权限的分类

答案

PostgreSQL 权限系统基于 ACL（访问控制列表）。每个对象（表、库、模式等）在系统目录中都有一个 ACL 数组字段，记录对该对象有权限的角色及权限位。GRANT 授予权限，REVOKE 撤销权限。常见的对象权限包括 SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER、CREATE、CONNECT、EXECUTE、USAGE 等。GRANT 可以指定 WITH GRANT OPTION 使被授权者能再转授权限。所有者（owner）默认拥有对象的全部权限，超级用户拥有所有权限。

解析

ACL 是 PostgreSQL 权限控制的核心数据结构，理解它才能理解 GRANT/REVOKE 的底层机制。查询 pg_class、pg_namespace 等目录的 relacl 字段可以查看或修改 ACL。

示例代码

GRANT SELECT, INSERT ON orders TO app_user;
GRANT ALL ON schema public TO app_role;
REVOKE UPDATE ON orders FROM app_user;

#

★★★

3. PostgreSQL 高级 SQL 特性，递归 CTE、窗口函数、UPSERT、RETURNING？

问题

请介绍 PostgreSQL 的高级 SQL 特性，包括递归 CTE、窗口函数、UPSERT 和 RETURNING 子句？

考察点

递归 CTE 的语法与用途
窗口函数与 OVER 子句
UPSERT（INSERT ... ON CONFLICT）与 RETURNING

答案

PostgreSQL 支持丰富的 SQL 特性。递归 CTE 通过 WITH RECURSIVE 实现自引用查询（如树形结构）。窗口函数配合 OVER 子句在不合并行的前提下进行分组计算（如 ROW_NUMBER、RANK、LAG、SUM OVER）。UPSERT 通过 INSERT ... ON CONFLICT DO UPDATE 实现"存在则更新、不存在则插入"。RETURNING 子句可在 INSERT/UPDATE/DELETE 后返回受影响行的数据，避免二次查询。

解析

这些特性让很多原本需要应用层或存储过程处理的需求可以一条 SQL 完成，显著提升开发效率与表达力，是 PostgreSQL 面试与工程中的高频考点。

示例代码

-- 递归 CTE：组织树
WITH RECURSIVE tree AS (
  SELECT id, name, parent_id FROM org WHERE id = 1
  UNION ALL
  SELECT o.id, o.name, o.parent_id FROM org o JOIN tree t ON o.parent_id = t.id
) SELECT * FROM tree;

-- UPSERT + RETURNING
INSERT INTO t(id, v) VALUES (1, 'x')
ON CONFLICT (id) DO UPDATE SET v = EXCLUDED.v
RETURNING id, v;

#

★★★

4. PostgreSQL 的表继承（INHERITS）与分区（PARTITION BY）？

问题

请比较 PostgreSQL 的表继承（INHERITS）与声明式分区（PARTITION BY）的实现机制和适用场景？

考察点

表继承的机制与约束
声明式分区的类型
继承与分区的异同

答案

表继承通过 CREATE TABLE child INHERITS (parent) 创建子表，子表拥有父表的列并可新增列，查询父表可自动包含子表数据。声明式分区是 PostgreSQL 10+ 提供的原生分区功能，通过 PARTITION BY RANGE/LIST/HASH 创建，物理上将数据分布到不同分区表，支持分区裁剪优化。相比表继承，声明式分区对约束、唯一性、索引、外键支持更完善，是官方推荐的方式。

解析

表继承是早期特性，语义灵活但约束薄弱（如唯一约束不跨子表、外键支持有限）。声明式分区用更受限但更规范的模型换取了更强的约束保证和优化能力，现代项目应优先使用声明式分区。

示例代码

CREATE TABLE orders (
  id bigint, created_at date NOT NULL, amount numeric
) PARTITION BY RANGE (created_at);
CREATE TABLE orders_2026 PARTITION OF orders
  FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

#

★★★

5. 递归 CTE 的典型应用，组织树、BOM 物料展开与图遍历，如何通过 WITH RECURSIVE 控制递归深度并避免死循环？

问题

请说明递归 CTE 在组织树、BOM 物料展开与图遍历中的典型应用，以及如何控制递归深度和避免死循环？

考察点

递归 CTE 的终止机制
控制递归深度的技巧
避免死循环的方法

答案

递归 CTE 由非递归项（初始集）和递归项（UNION ALL 引用自身）组成，每次迭代把结果加入工作表，直到递归项不再产生新行。典型应用包括组织架构树、BOM 物料多级展开、图谱路径遍历。控制递归深度可通过在递归项中增加深度计数列并用 WHERE 限制深度；避免死循环的关键是防止数据环（如父子关系成环）导致无限递归，可增加路径记录列来检测已访问节点，或用 depth 上限强制终止。

解析

死循环是递归 CTE 的主要风险，尤其当数据存在环（如 A 的父是 B，B 的父又是 A）时。通过 depth 计数和路径去重（如记录已访问 id）是工程上常用的安全手段。

示例代码

WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 1 AS depth, ARRAY[id] AS path
  FROM org WHERE id = 1
  UNION ALL
  SELECT o.id, o.name, o.parent_id, t.depth + 1, t.path || o.id
  FROM org o JOIN tree t ON o.parent_id = t.id
  WHERE t.depth < 10 AND NOT o.id = ANY(t.path)  -- 深度限制 + 防环
) SELECT * FROM tree;

#

★★★

6. VACUUM 的工作机制，清理死元组、回收空间？

问题

请解释 VACUUM 的工作机制，包括如何清理死元组和回收空间？

考察点

MVCC 死元组产生的原因
VACUUM 的清理过程
空间回收与 reuse

答案

PostgreSQL 的 MVCC 通过多版本元组实现，UPDATE/DELETE 产生死元组（dead tuple）。VACUUM 扫描数据页，识别被所有事务可见性判定为过期的死元组并标记其空间可复用（把死元组指针指向空闲空间列表），同时更新可见性映射（visibility map）和统计信息。传统 VACUUM 只是把空间标记为可复用，并不把文件还给操作系统；VACUUM FULL 才真正重写表、物理压缩空间带回磁盘。

解析

理解 VACUUM 不能把空间"还给操作系统"这一点很重要——普通 VACUUM 后表的文件大小可能不变，但空间可供后续插入复用，因此表膨胀不一定导致磁盘增长。VACUUM FULL 会重写并加 ACCESS EXCLUSIVE 锁，需谨慎使用。

#

★★★

7. 膨胀（Table Bloat）的检测，pgstattuple、pg_stat_user_tables？

问题

请说明如何检测 PostgreSQL 表膨胀（Table Bloat），包括 pgstattuple 扩展和 pg_stat_user_tables 视图的使用？

考察点

膨胀的定义与危害
pgstattuple 的检测方法
pg_stat_user_tables 的统计指标

答案

表膨胀指实际占用空间远超应有效数据所需空间。检测方法：一是 pgstattuple 扩展，调用 pgstattuple('table')（或 pgstattuple_approx）得到 dead_tuple_count、dead_tuple_percent 等精确指标；二是系统视图 pg_stat_user_tables，其中有 n_dead_tup、n_tup_ins/upd/del、last_vacuum、last_autovacuum 等字段，可粗略判断死元组积累情况。通常 dead_tuple_percent 或 n_dead_tup 长期偏高说明 VACUUM 跟不上即可判断膨胀。

解析

pgstattuple 需要逐页扫描，精确但开销大；pg_stat_user_tables 是累积统计，可快速粗查。二者结合：先用系统视图快速筛查，再对可疑表用 pgstattuple 精确确认。

示例代码

CREATE EXTENSION pgstattuple;
SELECT * FROM pgstattuple('orders');
SELECT relname, n_dead_tup, n_live_tup, last_autovacuum
FROM pg_stat_user_tables WHERE relname = 'orders';

#

★★★

8. 长事务/未提交事务为何会拖住 oldest xmin、使 VACUUM 无法回收其后产生的死元组？如何通过 pg_stat_activity 的 xact_start 与 backend_xmin 定位元凶？

问题

请解释长事务如何拖住 oldest xmin 并阻止 VACUUM 回收死元组，以及如何通过 pg_stat_activity 定位元凶？

考察点

xmin 与 VACUUM 回收的关系
长事务对回收的阻碍
pg_stat_activity 的定位方法

答案

VACUUM 只能回收"所有当前事务都不可见"的死元组，判定依据是事务快照的 oldest xmin —— 即数据库中最老的活跃事务 ID。若存在一个长事务（长时间未提交），其 xmin 会一直很旧，VACUUM 只能回收该 xmin 之前产生的死元组，其后产生的死元组即便已无人引用也因"可能被该老事务看到"而无法回收，导致膨胀累积。通过 pg_stat_activity 查看 backend_xmin 和 xact_start（或 backend_start），backend_xmin 最小且 xact_start 最早的行即是元凶。

解析

这是 VACUUM 与 MVCC 结合的核心原理。长事务同时会阻塞 vacuum 的 feedback（若开启 hot_standby_feedback）并拖住复制延迟。定位时按 backend_xmin 升序排序，找出最老的活跃事务。

示例代码

SELECT pid, state, xact_start, backend_xmin, backend_xid, query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY backend_xmin ASC;

#

★★★

9. pg_repack 在线重建表的原理（建影子表 + 触发器/日志捕获增量 → 短暂加锁做最终同步与 RENAME 交换）是什么？相比 VACUUM FULL 的长时间 ACCESS EXCLUSIVE 锁有何优势？

问题

请解释 pg_repack 在线重建表的原理，以及它相比 VACUUM FULL 的优势？

考察点

pg_repack 的在线重建流程
影子表与增量捕获机制
与 VACUUM FULL 的锁对比

答案

pg_repack 实现了在线重建表以消除膨胀。原理是：先创建一张影子表（新表），把原表数据复制过去；同时建立触发器（或利用日志）捕获复制期间产生的增量变更写入日志表；复制完成后，在短暂的独占锁窗口内把日志回放应用到影子表，最后通过 RENAME 交换原表与影子表，并重建索引与触发器。整个过程大部分时间无需 ACCESS EXCLUSIVE 锁，只有最终同步阶段短暂加锁。相比 VACUUM FULL 需要长时间持有 ACCESS EXCLUSIVE 锁阻塞所有读写，pg_repack 可以几乎无阻塞地在线完成，适合生产环境。

解析

关键优势是"在线"——VACUUM FULL 重写表时锁表导致应用不可用，而 pg_repack 把大部分工作放到后台，仅在毫秒级窗口锁表。注意 pg_repack 需要预留与表等大的磁盘空间，且需要主键或无主键时用某些特殊模式。

#

★★★

10. 高写入表如何调优 autovacuum（降低 autovacuum_vacuum_scale_factor、提高 autovacuum_vacuum_cost_limit、增加 worker 数）以跟上死元组产生速度？

问题

请说明对高写入表如何调优 autovacuum 参数，使其清理速度跟上死元组产生速度？

考察点

autovacuum 触发阈值
cost limit 与 worker 数
表级针对性调优

答案

高写入表死元组产生速度快，默认 autovacuum 可能跟不上。调优手段：降低 autovacuum_vacuum_scale_factor（默认 0.2，表示死元组占比超过 20% 才触发）或调低 autovacuum_vacuum_threshold（默认 50），使更频繁触发；提高 autovacuum_vacuum_cost_limit（默认 -1 继承全局默认 200）并调低 autovacuum_vacuum_cost_delay，让单次 VACUUM 能承受更高的 IO 成本、清理更积极；增加 autovacuum_max_workers 让更多 worker 并行。这些参数既可全局设置，也可在单表上通过 ALTER TABLE 的存储参数（如 autovacuum_vacuum_scale_factor）针对性覆盖。

解析

平衡点是既及时清理防膨胀，又不让 VACUUM 消耗过多 IO 影响正常业务。对特定热点表用表级存储参数覆盖是精准做法，避免全局参数影响所有表。

示例代码

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05,
                        autovacuum_vacuum_cost_limit = 1000);
-- 全局
ALTER SYSTEM SET autovacuum_max_workers = 5;

#

★★

11. Schema 的搜索路径（search_path）与多租户？

问题

请解释 Schema 的搜索路径（search_path）机制以及其如何用于多租户架构？

考察点

search_path 的作用
Schema 隔离与多租户
安全风险

答案

search_path 是 PostgreSQL 解析未限定表名时查找的顺序，默认 "user", public。通过 SET search_path 可切换当前会话使用的模式集合，实现同一数据库内按 Schema 隔离数据。多租户场景可让每个租户拥有独立 Schema，通过在连接时设置 search_path 指向对应租户 Schema，实现租户间数据隔离与定制。注意要将 postgres 等非租户 Schema 排除在 search_path 之外以避免跨租户访问，并警惕 search_path 注入带来的安全风险。

解析

Schema 隔离相比独立数据库成本更低、迁移友好，是常见的多租户方案。但需注意权限控制与 search_path 的严谨配置，避免租户间越权。

示例代码

CREATE SCHEMA tenant_a;
SET search_path = tenant_a, public;
SELECT * FROM orders;  -- 解析为 tenant_a.orders

#

★★

12. 常用扩展，postgis、pg_trgm、uuid-ossp、pg_stat_statements、pg_cron？

问题

请介绍 PostgreSQL 常用扩展及其用途，包括 postgis、pg_trgm、uuid-ossp、pg_stat_statements、pg_cron？

考察点

各扩展的用途
扩展的安装方式
需放入 shared_preload_libraries 预加载的扩展（pg_stat_statements、pg_cron）

答案

PostgreSQL 扩展（Extension）通过 CREATE EXTENSION 安装。postgis 提供地理空间数据类型与空间索引（GiST）；pg_trgm 提供三元组相似度计算，支持模糊搜索（ILIKE、% 通配符）与相似度排序；uuid-ossp 生成 UUID（v1/v4）；pg_stat_statements 统计 SQL 执行情况（运行次数、总耗时、平均耗时），用于性能分析；pg_cron 提供数据库内定时调度，可调度 VACUUM、分区清理等。安装时部分扩展需编译或放入 shared_preload_libraries（如 pg_stat_statements、pg_cron）。

解析

扩展机制是 PostgreSQL 生态强大的体现。pg_stat_statements 和 pg_cron 需要预加载到共享库，pg_stat_statements 还需 contrib 安装。理解各扩展的定位有助于选型。

示例代码

CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pg_cron;

#

★★

13. TOAST（The Oversized-Attribute Storage Technique），超长字段的存储机制？

问题

请解释 TOAST 机制，即 PostgreSQL 如何处理超长字段的存储？

考察点

TOAST 的触发条件
压缩与行外存储
TOAST 表与索引影响

答案

TOAST（The Oversized-Attribute Storage Technique）用于处理超出单个数据页大小的字段。当一行过大（默认约 2KB 阈值）时，大字段会被压缩并可能移入独立的 TOAST 表（列式存储），主表只保留指针。每个有可变长字段的表隐含一个 TOAST 表（pg_toast）。字段存储策略包括 PLAIN（不可压缩不外出）、EXTENDED（先压缩后外出）、EXTERNAL（不压缩直接外出）、MAIN（优先压缩，尽量留主表）。TOAST 字段通常不能建普通索引，但可建表达式索引或 GIN 索引。

解析

TOAST 让单行可以容纳远超单页大小的数据（如大文本、JSON），且查询不涉及 TOAST 字段时不会读取 TOAST 数据，性能良好。理解存储策略对优化大字段读写有指导意义。

示例代码

ALTER TABLE doc ALTER COLUMN content SET STORAGE EXTERNAL;  -- 不压缩直接外出
SELECT relname, reltoastrelid FROM pg_class WHERE relname='doc';

#

★★

14. 外部数据封装器（FDW, Foreign Data Wrapper），跨库访问？

问题

请解释外部数据封装器（FDW）机制及其在跨库访问中的应用？

考察点

FDW 的概念与实现
跨库/跨源访问
postgres_fdw 的使用

答案

FDW（Foreign Data Wrapper）允许 PostgreSQL 把外部数据源（其他 PostgreSQL 库、MySQL、Oracle、文件等）当作本地表访问。通过 CREATE EXTENSION postgres_fdw、CREATE SERVER、CREATE USER MAPPING、CREATE FOREIGN TABLE 建立映射，之后即可用 SQL 查询外部表。普通 FDW 查询时把过滤条件下推（pushdown）到远端；postgres_fdw 还支持 INSERT/UPDATE/DELETE 与事务。FDW 是跨库、跨源数据整合和联邦查询的常用方案。

解析

FDW 是 PostgreSQL 开放数据访问能力的核心机制。相比 ETL 复制数据，FDW 提供实时访问，但网络往返与性能是考量点。理解下推优化有助于写出高效跨库查询。

示例代码

CREATE EXTENSION postgres_fdw;
CREATE SERVER remote_srv FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'db2', dbname 'app');
CREATE USER MAPPING FOR CURRENT_USER SERVER remote_srv
  OPTIONS (user 'u', password 'p');
CREATE FOREIGN TABLE ft_orders (id int, amount numeric)
  SERVER remote_srv OPTIONS (schema_name 'public', table_name 'orders');
SELECT * FROM ft_orders WHERE id = 1;

#

★★

15. GRANT 的语法与权限模型，对象权限、列级权限与 GRANT OPTION 的传递机制，PUBLIC 与 DEFAULT PRIVILEGES 对新建对象权限的影响？

问题

请解释 GRANT 的语法与权限模型，包括对象权限、列级权限、GRANT OPTION 传递机制，以及 PUBLIC 与 DEFAULT PRIVILEGES 的作用？

考察点

GRANT 的对象与列级权限
GRANT OPTION 的传递
PUBLIC 与 DEFAULT PRIVILEGES

答案

GRANT 可授予对象级权限（表、视图、序列、函数、库、模式、类型等）和列级权限（仅对指定列授予 SELECT/UPDATE 等）。WITH GRANT OPTION 使被授权者可将该权限继续转授他人，形成权限传递链。PUBLIC 是代表所有角色的特殊集合，对 PUBLIC 授权即所有角色获得权限。DEFAULT PRIVILEGES 通过 ALTER DEFAULT PRIVILEGES 设置未来新建对象的默认权限（例如让新建表默认授予某角色 SELECT），防止新对象权限被遗漏。

解析

权限模型的核心是"谁对什么对象有什么权限、能否再转授"。DEFAULT PRIVILEGES 解决了"对象创建后权限不自动继承"的运维痛点，是规范化权限管理的常用手段。

示例代码

GRANT SELECT, UPDATE (name) ON users TO app_role;          -- 列级权限
GRANT SELECT ON users TO app_role WITH GRANT OPTION;
ALTER DEFAULT PRIVILEGES FOR ROLE owner IN SCHEMA public
  GRANT SELECT ON TABLES TO report_role;

#

★★

16. PostgreSQL 扩展的约束冲突？

问题

请解释 PostgreSQL 扩展在安装或使用中可能遇到的约束冲突问题？

考察点

扩展与已有对象冲突
扩展依赖冲突
版本冲突

答案

PostgreSQL 扩展的约束冲突主要指：扩展内部对象（表、函数、类型）与已存在对象同名冲突，导致 CREATE EXTENSION 报错；扩展依赖的底层能力（如某些库、编译选项）缺失；同一扩展不同版本间的升级路径冲突；以及扩展之间对系统目录或共享变量的访问冲突。常见处理是卸载冲突对象、使用 WITH SCHEMA 指定扩展安装到独立 Schema、或升级数据库/扩展版本。

解析

扩展冲突是运维常见问题，理解"扩展对象会注册到系统目录"有助于诊断冲突。把扩展安装到独立 Schema 可减少与业务对象命名冲突。

#

★★

17. REVOKE 的语法与级联行为，REVOKE GRANT OPTION FOR 与 CASCADE 的区别，如何撤销通过角色继承间接获得的权限？

问题

请解释 REVOKE 的语法与级联行为，包括 REVOKE GRANT OPTION FOR 与 CASCADE 的区别，以及如何撤销通过角色继承获得的权限？

考察点

REVOKE 基本语法
GRANT OPTION FOR 与 CASCADE
角色继承的权限撤销

答案

REVOKE 用于撤销权限。REVOKE GRANT OPTION FOR 只撤销被授权者的"转授权"，保留其自身的使用权限；REVOKE ... CASCADE 则在撤销权限时级联撤销由该权限派生的所有权限（包括通过 GRANT OPTION 转授给第三方的权限）。若要撤销通过角色继承（GRANT role TO user）间接获得的权限，不能直接对用户 REVOKE 该角色赋予的权限，而应 REVOKE role FROM user 或撤销角色本身的权限，因为继承权限是"通过角色"得来的。

解析

区分"撤销自身权限"与"撤销转授权"是关键。CASCADE 会波及下游，需谨慎使用。角色继承的权限撤销要回到角色层面处理。

示例代码

REVOKE SELECT ON orders FROM app_role;               -- 撤销自身权限
REVOKE GRANT OPTION FOR SELECT ON orders FROM app_role;  -- 仅撤销转授
REVOKE SELECT ON orders FROM app_role CASCADE;       -- 级联撤销
REVOKE readonly_role FROM app_user;                  -- 撤销角色继承

#

★★

18. pg_cron 在 PostgreSQL 中的应用，调度 VACUUM、分区清理与物化视图刷新的最佳实践，与系统 cron 相比的持久化与失败可见性优势？

问题

请说明 pg_cron 在 PostgreSQL 中的应用，包括调度 VACUUM、分区清理和物化视图刷新的最佳实践，及其相比系统 cron 的优势？

考察点

pg_cron 的配置与调度
典型运维任务的调度
与系统 cron 的对比

答案

pg_cron 通过 CREATE EXTENSION pg_cron 安装，需在 shared_preload_libraries 中加载并重启。用 cron.schedule 创建定时任务，可调度 VACUUM、分区 DETACH/DROP、物化视图 REFRESH 等数据库内任务。相比系统 cron，pg_cron 的优势在于：任务定义持久化在数据库（cron.job 表），跨实例一致；执行结果与失败状态可查询（cron.job_run_details），失败可见、可审计；与数据库权限体系集成，可指定执行用户；且因在数据库进程内执行，无需额外配置 psql 连接。

解析

数据库内任务用 pg_cron，避免系统 cron 调用 psql 时的连接、认证、权限管理痛点，且失败可见性更好。适合把 VACUUM、分区维护、物化视图刷新搬进数据库。

示例代码

CREATE EXTENSION pg_cron;
SELECT cron.schedule('vacuum-vacuum', '0 2 * * *',
  $$VACUUM$$);
SELECT cron.schedule('refresh-mv', '*/30 * * * *',
  $$REFRESH MATERIALIZED VIEW CONCURRENTLY mv_report$$);
SELECT * FROM cron.job_run_details WHERE status <> 'succeeded';

#

★★

19. pg_trgm 在模糊搜索中的应用，三元组相似度与 GIN/GiST 索引如何支撑 ILIKE 和相似度排序，误匹配与索引膨胀如何控制？

问题

请说明 pg_trgm 在模糊搜索中的应用，包括三元组相似度、GIN/GiST 索引如何支撑 ILIKE 和相似度排序，以及误匹配与索引膨胀的控制？

考察点

三元组（trigram）相似度
pg_trgm 的索引类型
误匹配与膨胀控制

答案

pg_trgm 通过把字符串切分为相邻三元字符组（trigram）来度量相似度，提供 similarity() 函数（0~1）和 % 操作符。GIN 索引支持 ILIKE '%x%' 通配符模糊匹配和 similarity 排序，GiST 索引则支持相似度距离排序（ORDER BY x <-> 'term'）。误匹配控制：可使用相似度阈值（如 similarity > 0.3）或 pg_trgm.similarity_threshold 参数过滤低质量结果。索引膨胀控制：GIN 索引本身有 pending 列表与 fastupdate，可定期 VACUUM、调整 gin_pending_list_limit，或对高频词禁用。

解析

pg_trgm 是把模糊搜索从全表扫描变成索引查询的关键。GIN 适合等值/包含匹配，GiST 适合 KNN 距离排序。合理设置阈值避免低质量误匹配，并注意索引膨胀的维护。

示例代码

CREATE EXTENSION pg_trgm;
CREATE INDEX trgm_idx ON users USING gin (name gin_trgm_ops);
SELECT * FROM users WHERE name ILIKE '%张%';
SELECT * FROM users ORDER BY name <-> '张三' LIMIT 10;

#

★★

20. uuid-ossp 的应用，uuid_generate_v4 与基于时间戳+MAC 的 v1 差异，作为主键对 B-tree 索引碎片的影响，与 pgcrypto 的 gen_random_uuid 如何选型？

问题

请说明 uuid-ossp 扩展的应用，比较 uuid_generate_v4 与 v1 的差异及其对 B-tree 索引的影响，并说明与 pgcrypto 的 gen_random_uuid 如何选型？

考察点

UUID v1 vs v4
随机 UUID 对索引的影响
生成方案的选型

答案

uuid-ossp 提供 uuid_generate_v4()（随机 UUID）和 uuid_generate_v1()（基于时间戳+节点 MAC 的 UUID）。v1 具备时间顺序性，但会泄露 MAC 与时间信息；v4 完全随机，无泄露但无顺序性。作为主键时，随机 UUID（v4）的新增值无规律，会随机插入 B-tree 索引的各个位置，导致索引页分裂频繁、碎片化、缓存命中率下降；而 v1 的值随时间是递增的，插入多为尾部追加，索引更紧凑。pgcrypto 的 gen_random_uuid() 与 uuid_generate_v4() 等价（PG13+ 内置 gen_random_uuid 无需扩展）。选型上：若需顺序性且可接受信息泄露用 v1，一般场景推荐 v4/随机，若追求写入性能可考虑 ULID 或序列。

解析

主键选择是索引性能的关键。随机 UUID 主键在高并发插入下会因随机 B-tree 写入造成页分裂与膨胀，是大表 insert 性能的常见瓶颈。理解 v1/v4 差异有助于针对性选型。

#

★★

21. PostgreSQL 17/18 的增量视图维护与 merge 增强对工程实践的影响？

问题

请说明 PostgreSQL 17/18 的增量视图维护与 MERGE 增强对工程实践的影响？

考察点

增量物化视图维护
MERGE 语句增强
对工程实践的影响

答案

PostgreSQL 17 尚未引入增量物化视图维护（incremental materialized view maintenance 仍在社区开发中，未进入正式版本），物化视图刷新仍以全量重建或 REFRESH MATERIALIZED VIEW CONCURRENTLY 并发刷新为主，大数据量基表的实时/准实时报表仍需在应用侧做增量同步。MERGE 语句在 17 中增强了灵活性（新增 RETURNING、WHEN NOT MATCHED BY SOURCE、可修改视图等），使"有则更新无则插入"的业务逻辑更简洁、更原子。这些增强让工程实践中数据合并更可控、ETL 更声明式，减少自建同步的复杂度。

解析

增量物化视图维护目前尚未进入 PostgreSQL 正式版本（仍在开发中），工程上仍以 CONCURRENTLY 并发刷新或应用侧增量同步为主。MERGE 增强（17 起支持 RETURNING 等）提升数据合并的原子性与可读性，是工程上值得迁移利用的新特性。

#

★

22. pgvector 的 HNSW 索引与向量检索性能调优要点？

问题

请说明 pgvector 的 HNSW 索引与向量检索的性能调优要点？

考察点

HNSW 索引原理
调优参数
检索性能考量

答案

pgvector 提供向量类型与相似度检索，支持 HNSW（Hierarchical Navigable Small World）索引，通过构建多层图结构实现近似最近邻检索，避免了暴力扫描。HNSW 调优参数：m（每层最大连接数，默认 16，增大提高召回率但增加索引大小与构建时间）、ef_construction（构建时探索范围，默认 64，越大召回越好但构建更慢）、ef_search（查询时探索范围，默认 40，越大召回率越高但延迟越高）。调优要点：根据业务召回率与延迟要求平衡 m/ef_search；向量维度与数据量决定是否需要量化；合理设置 max_connections 与工作线程；与普通列混合查询时利用索引组合。

解析

HNSW 是在"召回率-延迟-索引大小"三者间权衡。理解 ef_search 对查询延迟的影响，pgvector 还支持 IVFFlat 索引（更省内存但召回略低）。调优需结合数据分布与硬件。