PostgreSQL 17 性能调优实战

以下是 PostgreSQL 17 性能调优的实战方法，涵盖硬件层面、配置参数、数据库设计、查询优化和日常维护等多个方面：

硬件层面

1. 磁盘 I/O 优化

• 使用高速磁盘：选择 SSD（固态硬盘）代替传统的 HDD（机械硬盘）。SSD 具有更低的延迟和更高的随机读写性能，能显著提升 PostgreSQL 的 I/O 性能。例如，在高并发写入场景下，SSD 可以快速处理 WAL（预写式日志）的写入，减少事务提交的等待时间。
• 磁盘阵列配置：采用 RAID（独立磁盘冗余阵列）技术，如 RAID 10 或 RAID 5。RAID 10 提供了高性能和高可靠性，适合对读写性能要求都较高的场景；RAID 5 则在提供一定冗余的同时，具备较好的写入性能。

2. 内存分配

• 增加系统内存：确保系统有足够的内存供 PostgreSQL 使用。一般来说，将 shared_buffers 参数设置为系统内存的 25% - 50% 是一个不错的选择。例如，如果系统有 32GB 内存，可以将 shared_buffers 设置为 8GB - 16GB。
• 避免内存交换：监控系统的内存使用情况，避免出现内存交换（swap）。内存交换会严重影响数据库性能，因为磁盘 I/O 比内存访问慢得多。可以通过调整系统的 swappiness 参数来降低内存交换的可能性。

3. CPU 优化

• 多核 CPU 利用：PostgreSQL 支持并行查询，选择多核 CPU 可以充分利用并行计算的优势。通过调整 max_parallel_workers 和 max_parallel_workers_per_gather 参数，让数据库能够根据系统的 CPU 核心数合理分配并行工作进程。例如，如果系统有 16 个 CPU 核心，可以将 max_parallel_workers 设置为 8，max_parallel_workers_per_gather 设置为 4。

配置参数调整

1. 内存相关参数

• shared_buffers：前面已提及，它是 PostgreSQL 用于缓存数据块的共享内存区域，适当增大该参数可以减少磁盘 I/O。
• work_mem：每个排序操作和哈希表在执行时可以使用的最大内存量。对于经常进行 ORDER BY、GROUP BY 等操作的查询，可以适当增大该参数。例如，将其设置为 64MB - 256MB。
• maintenance_work_mem：在执行 VACUUM、CREATE INDEX 等维护操作时可以使用的最大内存量。适当增加该参数可以加快维护操作的速度，可设置为几百 MB 到几 GB。

2. 并行查询参数

• max_parallel_workers：整个系统可以使用的最大并行工作进程数。根据系统的 CPU 核心数进行设置，一般为 CPU 核心数的一半左右。
• max_parallel_workers_per_gather：每个 Gather 或 Gather Merge 节点可以启动的最大并行工作进程数。可以根据实际查询的并行需求和系统资源进行调整。

3. 日志相关参数

• wal_buffers：WAL 缓冲区的大小。适当增大该参数可以减少 WAL 写入磁盘的次数，提高写入性能。一般可以设置为 shared_buffers 的 1/32 到 1/8。
• synchronous_commit：控制事务提交时是否等待 WAL 写入磁盘。如果对数据的实时一致性要求不是非常高，可以将其设置为 off 或 local，以提高事务提交的性能。

数据库设计优化

1. 表分区

• 对于大型表，可以采用表分区技术。例如，按照时间范围对日志表进行分区，每个分区存储一个月的数据。这样在查询特定时间段的数据时，只需要扫描相应的分区，减少了扫描的数据量，提高了查询性能。

 

-- 创建分区表
CREATE TABLE sales (
    id serial,
    sale_date date,
    amount numeric
) PARTITION BY RANGE (sale_date);

-- 创建分区
CREATE TABLE sales_2024_01 PARTITION OF sales FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

2. 索引优化

• 合理创建索引：根据查询的条件和排序需求创建索引。例如，对于经常用于 WHERE 子句和 ORDER BY 子句的列，可以创建索引。但要避免创建过多的索引，因为索引会增加写入操作的开销。

-- 创建索引
CREATE INDEX idx_sale_date ON sales (sale_date);

• 使用覆盖索引：如果查询只需要索引列中的数据，而不需要访问表中的实际数据行，可以使用覆盖索引。这样可以避免回表操作，提高查询性能。

查询优化

1. 分析查询性能

• 使用 EXPLAIN 和 EXPLAIN ANALYZE 命令分析查询的执行计划。EXPLAIN 会显示查询的执行计划，但不会实际执行查询；EXPLAIN ANALYZE 会实际执行查询，并显示执行计划和每个步骤的实际执行时间。

-- 分析查询执行计划
EXPLAIN ANALYZE SELECT * FROM sales WHERE sale_date BETWEEN '2024-01-01' AND '2024-01-31';

2. 查询重写

• 优化查询语句，避免使用复杂的子查询和嵌套查询。可以将复杂的查询拆分成多个简单的查询，或者使用 JOIN 操作代替子查询。例如，将以下子查询：

SELECT * FROM sales WHERE id IN (SELECT id FROM another_table WHERE condition);

重写为 JOIN 查询：

 

 

SELECT s.* FROM sales s JOIN another_table a ON s.id = a.id WHERE a.condition;

日常维护

1. 定期 VACUUM 和 ANALYZE

• VACUUM：回收被删除或更新行占用的空间，避免表膨胀。可以定期执行 VACUUM 操作，例如每天或每周执行一次。

-- 对 sales 表执行 VACUUM
VACUUM sales;

• ANALYZE：更新表的统计信息，帮助查询优化器生成更优的执行计划。可以在数据发生较大变化后执行 ANALYZE 操作。

-- 对 sales 表执行 ANALYZE
ANALYZE sales;

2. 监控数据库性能

• 使用 PostgreSQL 自带的监控工具（如 pg_stat_activity、pg_stat_statements 等）和第三方监控工具（如 Prometheus + Grafana）监控数据库的性能指标，如查询响应时间、CPU 使用率、内存使用率等。根据监控结果及时调整配置参数和优化查询。