PostgreSQL 性能调优

引言

PostgreSQL 作为一款功能强大、极其严格的学院派开源关系数据库，越来越多地被应用于复杂的企业级数据存储和空间地理信息（PostGIS）分析。然而，PostgreSQL 默认安装时的内存等配置是为了在极小资源下能启动，要发挥其性能，必须进行深度调优。

定义：类似于 MySQL 的 innodb_buffer_pool_size，代表 PostgreSQL 缓存数据页面的专用共享内存。
调优建议：在专用的数据库服务器上，一般建议将其设置为服务器总物理内存的 25% 左右。设置过高反而会因为与操作系统自身的缓存（Page Cache）冲突而导致性能下降。

定义：用于控制单个查询中排序操作（ORDER BY、DISTINCT） and 哈希连接（HASH JOIN）所能使用的私有内存。
调优建议：该内存是针对每个查询中的每个排序操作单独分配的。如果是高并发系统，切忌设置过大，否则并发查询数一多很容易触发系统的 OOM Killer。通常设置为 4MB - 16MB 即可。

通过 EXPLAIN ANALYZE 执行 SQL，不仅能获取 PostgreSQL 对查询的最优路径预估，还能直接在后端执行该 SQL 并输出真实的磁盘读取和 CPU 耗时：

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

重点关注 Seq Scan（全表扫描）和 Index Scan（索引扫描）。如果发现大表依然在走全表扫描，说明索引未生效。

PostgreSQL 的多版本并发控制（MVCC）在更新或删除记录时，并不是直接在原位置修改，而是插入一个新版本并标记旧版本为“死元组（Dead Tuples）”。

Dead Tuples 问题：随着数据频繁更新，死元组会占用大量磁盘空间，降低查询效率，这种现象称为“膨胀（Bloat）”。
Autovacuum：PostgreSQL 带有自动 VACUUM 守护进程，会在空闲时回收死元组并更新表统计数据。务必确保 autovacuum 参数处于开启状态，并对频繁更新的巨型表微调其触发因子。

引言

核心内存参数调优

shared_buffers

定义：类似于 MySQL 的 innodb_buffer_pool_size，代表 PostgreSQL 缓存数据页面的专用共享内存。

调优建议：在专用的数据库服务器上，一般建议将其设置为服务器总物理内存的 25% 左右。设置过高反而会因为与操作系统自身的缓存（Page Cache）冲突而导致性能下降。

work_mem

定义：用于控制单个查询中排序操作（ORDER BY、DISTINCT） and 哈希连接（HASH JOIN）所能使用的私有内存。

调优建议：该内存是针对每个查询中的每个排序操作单独分配的。如果是高并发系统，切忌设置过大，否则并发查询数一多很容易触发系统的 OOM Killer。通常设置为 4MB - 16MB 即可。

maintenance_work_mem

定义：用于控制日常维护操作（如 CREATE INDEX、VACUUM、ALTER TABLE）所能使用的最大内存。

调优建议：由于这类操作并不频繁，可以配置一个较大的值（如 512MB - 1GB），能显著加快大表建立索引的速度。

执行计划分析与 VACUUM 机制

使用 EXPLAIN ANALYZE 排查慢查询

通过 EXPLAIN ANALYZE 执行 SQL，不仅能获取 PostgreSQL 对查询的最优路径预估，还能直接在后端执行该 SQL 并输出真实的磁盘读取和 CPU 耗时：

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

重点关注 Seq Scan（全表扫描）和 Index Scan（索引扫描）。如果发现大表依然在走全表扫描，说明索引未生效。

理解 MVCC 与 VACUUM 机制

PostgreSQL 的多版本并发控制（MVCC）在更新或删除记录时，并不是直接在原位置修改，而是插入一个新版本并标记旧版本为“死元组（Dead Tuples）”。

Dead Tuples 问题：随着数据频繁更新，死元组会占用大量磁盘空间，降低查询效率，这种现象称为“膨胀（Bloat）”。

Autovacuum：PostgreSQL 带有自动 VACUUM 守护进程，会在空闲时回收死元组并更新表统计数据。务必确保 autovacuum 参数处于开启状态，并对频繁更新的巨型表微调其触发因子。