PostgreSQL 面试

PostgreSQL 和 MySQL 有什么区别

PostgreSQL 更强调 SQL 标准、复杂查询、扩展能力和丰富数据类型，例如 JSONB、数组、GIS、窗口函数等；MySQL 在 Web 业务里生态广、使用经验多。两者 MVCC 实现不同，PostgreSQL 更新通常生成新 tuple 并依赖 VACUUM 清理旧版本，MySQL InnoDB 通过 undo log 版本链支持快照读。PostgreSQL 使用 WAL，MySQL InnoDB 使用 redo log，Server 层还有 binlog。

原理：总览、架构与存储、MVCC、VACUUM 与 WAL 原理。

PostgreSQL、MySQL、Oracle、SQL Server 怎么横向比较

横向比较不能只说“PostgreSQL 功能多、MySQL 流行”。应该按存储、索引、事务日志、MVCC、优化器和运维重点比较。MySQL InnoDB 的主键索引是聚簇 B+Tree，二级索引保存主键，undo 支持 MVCC，redo 做恢复，binlog 做复制；PostgreSQL 表通常是 heap，B-tree 索引通过 TID 找 heap tuple，旧版本保存在表里，依赖 VACUUM 清理，WAL 做恢复和复制；Oracle 用 Undo、Redo、SCN 做一致性读和恢复；SQL Server 强调聚集索引、非聚集索引、Transaction Log、锁和行版本。PostgreSQL 的优势是复杂 SQL、JSONB、GIS、扩展索引和分析能力，但也要关注 VACUUM、表膨胀、WAL 和执行计划估算。

追问：

1. PostgreSQL 的 Index Only Scan 为什么还可能访问 heap？
2. PostgreSQL 的 WAL 和 MySQL redo/binlog 有什么区别？
3. VACUUM 不及时会发生什么？
4. B-tree、GIN、GiST、BRIN 分别适合什么场景？

原理：主流关系库核心差异与选型、关系型数据库底层原理、MVCC、VACUUM 与 WAL 原理。

同一条 SQL 在不同数据库为什么性能不同

因为 SQL 文本只是逻辑请求，真正性能取决于表组织、索引叶子层、统计信息、执行计划、可见性判断和回表方式。PostgreSQL 表通常是 heap，B-tree 索引命中后通过 TID 找 heap tuple，并按 xmin/xmax 和快照判断可见性；Index Only Scan 也要依赖 Visibility Map。MySQL InnoDB 二级索引可能回聚簇索引，Oracle 通过 ROWID 回表，SQL Server 可能 Key Lookup。所以“都走索引”不代表一样快，要看扫描范围、heap fetch、Buffers、排序和估算行数偏差。

追问：

1. Index Only Scan 为什么还可能访问 heap？
2. VACUUM 和 Visibility Map 对覆盖查询有什么影响？
3. EXPLAIN ANALYZE 的 estimated rows 和 actual rows 差距大说明什么？

原理：同一条列表查询在四种数据库里的差异、为什么走了索引还是慢、索引与优化器。

PostgreSQL 怎么从零学到生产可用

PostgreSQL 要按 SQL、表设计、索引、执行计划、事务 MVCC、VACUUM、WAL、备份恢复和线上排查这条线学习。先掌握建表、约束和查询，再理解 B-tree、GIN、BRIN 等索引适合什么条件，然后用 EXPLAIN ANALYZE 看真实执行计划。深入部分要理解 PostgreSQL 更新通常生成新 tuple，旧版本通过 xmin/xmax 和快照控制可见性，后续由 VACUUM 清理；WAL 用于崩溃恢复、复制和时间点恢复。生产排查要结合 pg_stat_activity、pg_locks、pg_stat_user_tables、执行计划和 Buffers 判断问题发生在哪一层。

原理：从零到生产级掌握、核心全过程原理。

PostgreSQL 一条 SQL 怎么执行

PostgreSQL 客户端连接通常由一个后端进程处理。SQL 进入后会经过 Parser 解析、Rewriter 重写、Planner 根据统计信息选择执行计划、Executor 执行计划。执行时可能走顺序扫描、Index Scan、Bitmap Scan、Join、Sort 等节点。读取 heap tuple 时还要根据 MVCC 快照判断版本是否可见。排查慢 SQL 时要看 EXPLAIN ANALYZE 的真实耗时、真实行数、估算行数偏差和 Buffers。

原理：核心全过程原理、索引与优化器、性能排查。

PostgreSQL 为什么需要 VACUUM

PostgreSQL 的 MVCC 更新和删除不会立刻物理移除旧 tuple，而是留下旧版本。等没有事务需要这些旧版本后，VACUUM 负责清理 dead tuple，避免表和索引膨胀。如果长事务一直存在，VACUUM 不能清理它可能仍然需要看到的旧版本，表会越来越膨胀，扫描页数增加，查询变慢。

原理：事务与锁、MVCC、VACUUM 与 WAL 原理。

PostgreSQL 的 WAL 解决什么问题

WAL 是预写日志，解决的是可靠性和恢复问题。PostgreSQL 修改数据页时，不要求每次提交都立即把完整数据页刷盘，而是先写描述修改的 WAL。事务提交后，只要 WAL 可靠落盘，即使数据库宕机，也可以重启后重放 WAL 恢复已提交修改。WAL 还用于流复制、时间点恢复和备份恢复。

原理：核心全过程原理、MVCC、VACUUM 与 WAL 原理。

PostgreSQL 有哪些索引

PostgreSQL 默认通用索引是 B-tree，适合等值、范围、排序和唯一约束；GIN 适合 JSONB、数组和全文检索；GiST 适合空间、范围、相似性搜索；BRIN 适合字段值和物理顺序相关的超大表；Hash 适合等值查询。选择索引要看查询条件、数据分布和执行计划。

原理：索引与优化器。

为什么 Index Only Scan 也可能访问表

PostgreSQL 要考虑 MVCC 可见性。即使查询字段都在索引里，如果相关 heap page 没有被 Visibility Map 标记为 all-visible，数据库仍可能访问 heap 检查 tuple 对当前快照是否可见。所以 PostgreSQL 的 Index Only Scan 效果和 VACUUM、Visibility Map、表膨胀都有关系，不是字段在索引里就一定完全不访问表。

原理：核心全过程原理、索引与优化器。

PostgreSQL 的 returning 有什么用

returning 可以让 insert、update、delete 在执行完成后直接返回变更后的数据或被删除的数据。它常用于插入后返回自增主键、更新时间、默认值，或者状态更新后返回影响的业务对象。相比写完再 select 一次，returning 更清晰，也避免并发下二次查询读到非本次变更结果。

追问：

1. insert returning 和再查一次有什么区别？
2. update returning 没返回行说明什么？
3. returning 能不能替代事务？

原理：基础 SQL：Insert 和 Returning、基础 SQL：returning价值。

on conflict 为什么能解决并发重复插入

on conflict 依赖唯一约束或唯一索引，由数据库在写入时检查冲突。两个请求同时插入同一个手机号或资产编号时，数据库唯一约束会保证只有一个成功，另一个进入 do update 或 do nothing 分支。它比应用层先查再插入更可靠，因为先查再插入在并发下会出现两个请求都查不到然后同时插入的竞态。

追问：

1. excluded 表示什么？
2. 没有唯一约束能不能用 on conflict？
3. 为什么唯一性必须放数据库兜底？

原理：基础 SQL：Upsert、Upsert为什么依赖唯一约束。

PostgreSQL 的 jsonb 适合什么场景

jsonb 适合保存半结构化和扩展属性，例如资产扩展字段、设备采集原始属性、第三方回调报文。它支持 @>、->、->> 等查询，并可配合 GIN 索引优化包含查询。但核心字段如订单号、用户 ID、金额、状态、创建时间不应该都塞进 jsonb，因为约束、Join、统计信息、权限审计和执行计划都会变复杂。

追问：

1. JSONB 和普通列怎么取舍？
2. GIN 索引为什么适合 JSONB？
3. JSONB 滥用会带来什么问题？

原理：基础 SQL：JSONB 查询、JSONB适用边界、索引与优化器。

timestamp 和 timestamptz 区别

timestamp 是不带时区语义的日期时间值，timestamptz 表示一个具体时间点，展示时会根据会话时区转换。跨时区系统、订单支付时间、采集日志时间更推荐 timestamptz；只表达本地日程或营业时间这类墙上时间时可以考虑 timestamp。关键是应用、数据库、接口要统一时间语义。

追问：

1. timestamptz 是不是把时区字符串存进去？
2. Java 应用里应该用什么时间类型映射？
3. 为什么日志和支付时间更推荐时间点语义？

原理：基础 SQL：timestamp 和 timestamptz。

PostgreSQL 为什么要控制连接数

PostgreSQL 常见是多进程模型，一个客户端连接通常对应一个 backend process。连接数过多会带来进程、内存、上下文切换和锁管理成本。Java 服务如果每个实例连接池都开几百个连接，多个实例叠加后可能压垮数据库。生产要控制应用连接池大小，高并发短连接场景可以使用 PgBouncer。

追问：

1. PostgreSQL 多进程模型和 MySQL 线程模型有什么差异？
2. 为什么连接池不是越大越好？
3. pg_stat_activity 能看什么？

原理：架构与存储：进程模型。

PostgreSQL 的 tuple 是什么

tuple 可以理解为 PostgreSQL 表中某一行的某个版本。普通表通常是 heap 存储，数据页中保存 tuple。更新一行时，PostgreSQL 往往不是原地覆盖，而是生成新 tuple，旧 tuple 通过 xmin/xmax 和事务快照判断是否仍对某些事务可见。等没有事务需要旧版本后，VACUUM 再清理。

追问：

1. tuple 和逻辑行有什么区别？
2. ctid 是什么，能不能作为业务主键？
3. 更新为什么会产生 dead tuple？

原理：架构与存储：表页和Tuple、xmin/xmax和可见性。

xmin、xmax 是什么

xmin 表示创建某个 tuple 的事务 ID，xmax 表示删除或更新该 tuple 的事务 ID。PostgreSQL 查询时会结合当前事务快照、xmin、xmax 判断这个 tuple 版本是否对当前查询可见。它们是 PostgreSQL MVCC 可见性判断的重要基础。

追问：

1. 为什么查询要判断 tuple 可见性？
2. xmax 不为空是否代表所有事务都看不到？
3. 长事务为什么影响旧版本清理？

原理：架构与存储：xmin/xmax、MVCC、VACUUM 与 WAL 原理。

HOT 更新是什么

HOT 是 Heap-Only Tuple。如果更新没有修改索引列，并且 heap 页中还有空间，PostgreSQL 可以在 heap 页内生成新 tuple，并让索引仍指向 HOT 链入口，避免为所有索引写入新索引项。HOT 能减少索引膨胀和更新成本。若更新了索引列、页内空间不足或 fillfactor 太满，HOT 机会会减少。

追问：

1. HOT 为什么能减少索引膨胀？
2. fillfactor 和 HOT 有什么关系？
3. 频繁更新状态字段时要不要把状态建索引？

原理：架构与存储：HOT更新。

Autovacuum 做什么

Autovacuum 是 PostgreSQL 自动清理和统计维护机制，负责清理 dead tuple、更新统计信息、冻结老事务 ID，防止事务 ID 回卷。它不是可有可无的后台任务。Autovacuum 跟不上或被长事务阻挡时，表和索引会膨胀，执行计划可能因为统计信息过旧而变差。

追问：

1. Autovacuum 和 VACUUM 有什么关系？
2. 为什么长事务会阻挡 VACUUM？
3. Autovacuum 只清理空间吗？

原理：架构与存储：Autovacuum、MVCC、VACUUM 与 WAL 原理。

PostgreSQL 表膨胀怎么排查

表膨胀通常来自大量 update/delete 产生 dead tuple，而 VACUUM 没有及时清理或被长事务阻挡。排查时先看 pg_stat_user_tables 中 n_dead_tup、last_autovacuum、last_analyze，再看 pg_stat_activity 是否有长事务。表现是表和索引越来越大、扫描页数增加、查询逐渐变慢。

追问：

1. n_dead_tup 高说明什么？
2. 为什么长事务会让 dead tuple 清不掉？
3. 表膨胀和索引膨胀有什么区别？

原理：架构与存储：商业场景、常用排查 SQL。

PostgreSQL Checkpoint 是什么

Checkpoint 会把一部分脏页写入磁盘，并记录恢复起点，减少崩溃恢复时需要重放的 WAL 范围。Checkpoint 不是事务提交本身，提交主要依赖 WAL 持久化。Checkpoint 太频繁可能带来写 IO 峰值，太少会增加恢复时间和 WAL 保留压力。

追问：

1. Checkpoint 和 commit 有什么区别？
2. WAL 为什么能让数据页稍后刷盘？
3. Checkpoint 太频繁有什么风险？

原理：架构与存储：Checkpoint。

EXPLAIN ANALYZE BUFFERS 看什么

EXPLAIN ANALYZE 会真实执行 SQL，并展示实际耗时、实际行数、循环次数；BUFFERS 会展示共享缓冲区命中和读取情况。排查时要对比估算行数和实际行数，如果差距大，说明统计信息或数据分布可能有问题；还要看 Buffers 判断读页成本，不能只看总耗时。

追问：

1. EXPLAIN 和 EXPLAIN ANALYZE 区别是什么？
2. 估算行数和实际行数差很多说明什么？
3. Buffers 为什么比只看耗时更稳定？

原理：架构与存储：Planner、性能排查。

PostgreSQL 慢 SQL 怎么排查

PostgreSQL 慢 SQL 排查要先拿到真实 SQL、真实参数和调用频率，再用 EXPLAIN (ANALYZE, BUFFERS) 看真实执行计划。重点看是否 Seq Scan 扫描过多、estimated rows 和 actual rows 是否差距大、Buffers 里 shared read 是否很多、Sort 是否 external merge Disk、Nested Loop 内层 loops 是否过大。如果计划没问题，再查 pg_stat_activity 和 pg_locks 是否锁等待，查 pg_stat_user_tables 的 n_dead_tup、last_autovacuum 判断是否 VACUUM 跟不上和表膨胀。最后再看连接数、IO、CPU 和应用连接池。

追问：

1. 为什么一定要真实参数？
2. cost 是不是毫秒？
3. Rows Removed by Filter 很大说明什么？
4. 排序落盘怎么看？

原理：性能排查：第一步拿真实 SQL、性能排查：EXPLAIN ANALYZE。

PostgreSQL 统计信息为什么会导致计划选错

PostgreSQL 优化器根据统计信息估算过滤后行数和成本，再决定走 Seq Scan、Index Scan、Join 顺序和 Join 算法。统计信息来自 ANALYZE，如果数据倾斜、统计信息过期、多列强相关或者刚导入大量数据，估算 rows 和 actual rows 可能差很多，优化器就可能选错计划。处理方式包括 ANALYZE、提高统计目标、创建扩展统计、重写 SQL 或调整索引。

追问：

1. estimated rows 和 actual rows 差很多说明什么？
2. 什么是多列相关？
3. create statistics 解决什么问题？

原理：性能排查：统计信息为什么会让计划选错、索引与优化器。

PostgreSQL 深分页为什么慢

limit offset 深分页慢，是因为 offset 不是直接跳过前 N 行，数据库通常要先按条件和排序找到前 offset + limit 行，再丢弃 offset 行。offset 越大，扫描和排序浪费越多。核心业务列表更推荐游标翻页，例如用 created_at + id 作为稳定排序锚点，并建立 tenant_id、created_at、id 组合索引。

追问：

1. 为什么只用 created_at 翻页可能重复或漏数据？
2. 游标翻页适不适合跳到任意页？
3. 深分页和索引有什么关系？

原理：性能排查：资产列表深分页很慢。

PostgreSQL for update skip locked 适合什么场景

for update skip locked 适合多执行器并发抢任务，例如采集任务、异步补偿任务、批处理任务。普通 for update 遇到已锁行会等待，skip locked 会跳过被其他事务锁住的行，继续领取后面的任务，减少执行器之间的锁等待。它不能替代幂等和状态机，任务更新仍要用状态、版本号、重试次数等字段保证可恢复。

追问：

1. skip locked 会不会导致任务永远被跳过？
2. 抢任务表要建什么索引？
3. 为什么领取任务和更新状态要在一个事务里？

原理：性能排查：医疗采集任务抢占卡住。

PostgreSQL 备份和高可用怎么设计

PostgreSQL 备份和高可用要按 RPO/RTO 设计。pg_dump 是逻辑备份，适合迁移、单表恢复和中小库导出；pg_basebackup 是物理基础备份，适合整实例恢复和搭建从库。PITR 需要基础备份加持续归档 WAL，恢复时回放 WAL 到目标时间点。流复制通过主库发送 WAL、从库接收并重放 WAL 实现读扩展和故障切换。高可用不能替代备份，因为误删和误更新会同步到从库。

追问：

1. pg_dump 和 pg_basebackup 区别是什么？
2. 为什么 PITR 必须依赖 WAL？
3. 从库为什么不能当备份？
4. RPO 和 RTO 分别是什么意思？

原理：备份与高可用、RPO和RTO怎么倒推方案。

PostgreSQL 流复制为什么会有延迟

流复制不是直接复制表，而是主库产生 WAL 后，由 WAL sender 发给从库，WAL receiver 接收，从库写入并重放 WAL。发送、网络传输、从库写盘、从库重放任一步慢都会产生延迟。大事务、DDL、从库磁盘慢、跨机房网络抖动、从库长查询冲突都可能导致 replay_lag 增大。

追问：

1. sent_lsn、write_lsn、flush_lsn、replay_lsn 分别表示什么？
2. 同步复制一定不丢数据吗？
3. 从库读到旧数据怎么解决？

原理：备份与高可用：流复制、同步复制和异步复制。

复制槽有什么风险

复制槽用于让主库保留某个从库或逻辑复制消费者还没消费的 WAL，避免消费者短暂断开后缺 WAL。但如果复制槽长期 inactive 或消费者一直不消费，主库会持续保留 WAL，可能撑爆磁盘，导致主库故障。所以复制槽必须监控 active 状态、restart_lsn、confirmed_flush_lsn 和 WAL 保留量。

追问：

1. 为什么复制槽会导致 WAL 堆积？
2. 逻辑复制和物理复制都能用复制槽吗？
3. 发现 inactive slot 怎么处理？

原理：备份与高可用：复制槽是什么。