Skip to main content
Documents
Toggle Dark/Light/Auto mode Toggle Dark/Light/Auto mode Toggle Dark/Light/Auto mode Back to homepage
  1. Documents
  2. /
  3. Database
  4. /
  5. PostgreSQL
  6. /
  7. PostgreSQL FAQ

PostgreSQL FAQ

内容：基础、SQL、事务、锁、索引、性能、运维、架构、备份恢复。

一、基础概念

1. PostgreSQL 和 MySQL 的核心区别？

答案：

  • MVCC 实现不同：PostgreSQL 用多版本存储（行版本），无锁读；MySQL InnoDB 用 undo log。
  • 数据类型更丰富：hstore、jsonb、array、range、enum、uuid。
  • 事务隔离更强：PostgreSQL 严格遵循 MVCC，不需要 gap lock。
  • 执行计划更智能：强大的优化器（hash join、merge join、bitmap scan）。
  • 扩展性强：支持自定义类型、函数、索引方法（GiST、GIN 等）。
  • 一致性强于性能：PostgreSQL 更关注 ACID。

2. PostgreSQL 的 MVCC 是如何实现的？

答案：

  • 每行都有两个隐藏字段：xmin（创建事务）、xmax（删除事务）
  • 不用 undo log，行版本直接存在表中
  • VACUUM 清理由过期版本

优势：

  • 无锁读（不阻塞 SELECT）
  • 简化 undo（相比 MySQL）

3. VACUUM 和 VACUUM FULL 的区别？

命令 作用 是否锁表 是否释放磁盘
VACUUM 清理 dead tuple 不锁（轻度） 不释放
VACUUM FULL 重写整个表 锁表 释放空间

二、事务与锁

4. PostgreSQL 的事务隔离级别？

  • Read Uncommitted（内部视为 READ COMMITTED）
  • Read Committed（默认）
  • Repeatable Read
  • Serializable（最强）

5. PostgreSQL 如何实现 Repeatable Read？

repeatable read 获取 一致性快照（snapshot）：

  • 当前事务只能看到开始时的已提交数据
  • 不会看到别人的 UPDATE（防不可重复读）
  • 也不会看到别人的 INSERT（防幻读）

PostgreSQL 不靠 gap lock 来阻止幻读，而是通过 MVCC 快照实现的。

6. 如何防止 UPDATE/DELETE 被并发修改？

使用行锁：

SELECT * FROM table
WHERE id = 1
FOR UPDATE;

阻止其他事务 UPDATE/DELETE。

三、索引与性能

7. PostgreSQL 支持哪些索引类型？使用场景？

索引类型 用法
B-Tree =、<、>、排序（最常用）
GIN jsonb、全文检索、数组、全文搜索
GiST GIS、范围、相似度搜索
BRIN 大宽表、顺序增长列（更轻）
Hash 等值查询（已不推荐）

8. 为什么 PostgreSQL 不推荐 SELECT *？

  • 会读不必要的列，增加 IO
  • 阻碍索引-only scan
  • 新增字段会破坏应用代码兼容性

9. PostgreSQL 索引失效的常见原因？

  • 函数操作字段，如：

    WHERE lower(name) = 'a'

    -> 需要 function index（表达式索引）

  • 不能用在 leading column 上：

    index(a, b)
WHERE b = 1  -- 索引不走

  • 类型隐式转换：

    WHERE id = '1'::text  -- 不走索引

10. EXPLAIN 常见关键字含义？

关键字 含义
Seq Scan 顺序扫描，通常意味着没有索引
Index Scan 使用索引查找
Bitmap Heap Scan 大批量数据更高效的索引扫描
Hash Join 适用于大表 join，速度快
Nested Loop 适用于小表 join

四、SQL 优化

11. PostgreSQL 如何优化 LIKE ‘%xxx%’？

  • 使用 pg_trgm（三元组索引）

  • 建立 GIN 或 GiST 索引：

    CREATE EXTENSION pg_trgm;
CREATE INDEX idx_title_trgm ON article USING gin (title gin_trgm_ops);

12. 如何优化分页性能？

经典方案：主键游标分页

SELECT * FROM table 
WHERE id > last_id
ORDER BY id
LIMIT 20;

避免 OFFSET 深度扫描。

13. 如何避免慢查询？

  • 检查 missing indexes
  • 使用 ANALYZE 保证统计信息新鲜
  • 避免函数包裹字段
  • 不要在 WHERE 中使用 OR（可改成 UNION ALL）

五、存储机制

14. PostgreSQL TOAST 的作用？

用于存储超大字段（text/json/blob）

机制：

  • 避免表行过大
  • 将大字段分块存到 toast 表
  • 主表行只存指针

15. 什么情况会生成 dead tuple？

  • UPDATE（旧版本不可见）
  • DELETE
  • INSERT 失败（rollback）

需要 VACUUM 清除。

六、备份恢复

16. PostgreSQL 热备份方式？

  • pg_basebackup（物理全备）
  • pgBackRest（最广泛使用）
  • WAL 归档（Point-In-Time Recovery）
  • 逻辑备份 pg_dump

17. 什么是 PITR？

Point-In-Time Recovery

通过：

  • base backup（物理备份）
  • WAL（增量）

恢复到某一秒。

18. 如何在线扩容 PostgreSQL？

  • 使用 Patroni + etcd
  • pgpool-II / HAProxy + Streaming Replication
  • 热备节点提升为主节点（failover）

七、架构设计

19. PostgreSQL 水平扩展方案？

  • Citus（分布式）
  • Greenplum
  • TimescaleDB（时间序列）
  • 自己做 Sharding（不推荐）

20. 两表大 JOIN 怎么最快？

方法：

  1. 正确索引
  2. 调大 work_mem
  3. 强制 Hash Join（如果优化器没选）：
SET enable_mergejoin TO off;
SET enable_nestloop TO off;

八、高级特性

21. 什么是 jsonb？优势是什么？

  • 二进制存储
  • 可索引（GIN）
  • 更快比较与查询
  • 更节省空间

比 json 更适合生产环境。

22. PostgreSQL 支持存储过程吗？

是的，支持 PL/pgSQL，也支持 Python、JS、C 等语言。

23. 如何创建表达式索引？ 

CREATE INDEX idx_lower_name ON users (lower(name));

查询需写一致：

WHERE lower(name) = 'tom';

24. 如何锁住整张表？ 

LOCK TABLE users IN SHARE MODE;

用于数据迁移等少数场景。

九、运维

25. PostgreSQL 如何查看死锁？ 

SELECT * FROM pg_locks;

或在 log 中查找：

deadlock detected

26. 如何发现慢 SQL？

开启：

log_min_duration_statement = 500ms

27. 如何清理膨胀（bloat）？

  • VACUUM
  • VACUUM FULL（重写表）
  • pg_repack（在线无锁重写）

28. 如何统计表行数最快？ 

SELECT reltuples::bigint
FROM pg_class
WHERE relname='table';

比 count(*) 快 1000 倍（approx）。

29. 如何查看表膨胀？

使用扩展：

CREATE EXTENSION pgstattuple;
SELECT * FROM pgstattuple('table');

30. PostgreSQL 如何锁定某个行范围？

PostgreSQL 没有 gap lock，只能用：

SELECT ... FOR SHARE;    
SELECT ... FOR UPDATE;

或使用 SERIALIZABLE。