Ubuntu 22.04上PostgreSQL 14集群大数据查询性能优化全攻略

在Ubuntu 22.04 LTS上跑PostgreSQL 14集群，用来支撑大数据量查询，最折磨人的坑就两个：查询越来越慢，和节点时不时不稳定。这两个问题如果我接手一个集群，通常不会先急着改参数，而是先做一套相对完整的评估——从系统层、数据库层、集群层、执行计划层，逐层往下找原因。这篇文章我就把亲手验证过的优化流程整理出来：哪些参数值得调、调到什么区间，哪些架构改动性价比最高，以及一堆文档里不会写的坑。

整篇文章面向已经在跑PostgreSQL、但遇到大数据查询性能问题的DBA或后端工程师。你不需要懂多深的内核原理，只要有一台Ubuntu 22.04 LTS机器、能sudo，就可以照着操作。但要提前说明，优化这件事不是“抄参数”就能一劳永逸的，它的本质是“先测、再调、再测”的循环，我会把每个环节的测试方法一起写清楚。

1. 优化前先摸清家底：基线评估与系统层准备

1.1 先回答一个问题：瓶颈到底在哪

我见过太多人一上来就调 shared_buffers，这是典型的头疼医头。大数据查询慢，可能是磁盘IO扛不住，可能是内存不够导致频繁落盘，可能是CPU核数不足导致并行度上不去，也可能是查询计划本身选错了。不先定位瓶颈，参数调得再花哨也没有意义。

我的建议是花半小时做一轮“手脚并用”式的健康检查，按顺序来：

• 内存：free -g看总量、剩余量和swap占用。如果swap已经有使用量，说明内存吃紧，这时候调大 shared_buffers 前得先解决内存水位问题。
• CPU：mpstat -P ALL 1观察每个核心的使用率。如果有单核打满而其他核空闲，说明并行或者查询计划可能有问题。
• 磁盘：iostat -x 1看 %util、await、w_await。SSD的%util接近100%说明IO已经饱和，这时候调什么内存参数都没用，重点得放在减少逻辑IO或提升IO带宽上。
• 网络：集群跨节点场景下，再看一下网卡流量和重传率，sar -n DEV 1就能看。

操作系统层面还有两个容易被忽略的因素：透明大页（THP）和NUMA。PostgreSQL在大内存机器上受THP影响明显，透明大页的分配和释放可能引发间歇性卡顿，线上稳妥的做法是关闭：

echo never | tee /sys/kernel/mm/transparent_hugepage/enabled

为了重启后依然生效，可以写入/etc/default/grub的 GRUB_CMDLINE_LINUX_DEFAULT 参数，也可以用 systemd service 在开机时执行，二选一即可。

1.2 文件系统与挂载参数

如果数据目录在SSD或云盘上，文件系统建议选 xfs。挂载时建议加noatime，减少每次读取文件时更新atime带来的写IO。查看当前挂载参数：

mount | grep /data

如果没看到 noatime，可以在确认没有写入业务的情况下重新挂载：

sudo mount -o remount,noatime /data

需要知道，这种方式不是持久化的，如果想长期生效要改/etc/fstab里的对应行。生产环境操作前一定要确认没有活动事务，否则可能造成连接中断。

1.3 pgbench 建立性能基线

优化这件事，千万别凭感觉说“快了还是慢了”。用数据说话。用 pgbench 建一个模拟库：

pgbench -i -s 100 mydb

-s 100表示100倍默认数据量，大约1GB。想模拟大数据量就继续放大，比如-s 2000。然后跑一个读写混合测试：

pgbench -c 32 -j 8 -T 120 -P 5 -M prepared mydb

这里-c 32是并发连接数，-j 8是线程数，-M prepared使用预备语句，更贴近真实业务的长连接模式。跑完记录 tps 和平均延迟，后面所有优化做完后，用同样的命令再跑一次做对比。

如果是真实业务SQL，pgbench造数代替不了。建议用EXPLAIN (ANALYZE， BUFFERS， TIMING)采样几条核心慢SQL，把执行计划存成基线文档。后面每次调整配置后，重新执行同一条SQL并对比执行计划，判断改动是否有效。

注意：pgbench反映的是吞吐能力，不能完全代表你的业务查询延迟。生产优化必须结合真实慢SQL样本做验证。

1.4 内核参数校准

在/etc/sysctl.conf里添加以下建议值：

vm.swappiness=10 vm.dirty_ratio=15 vm.dirty_background_ratio=5 fs.aio-max-nr=1048576

• swappiness 调到10，意思是尽量别把活跃内存页交换到swap。
• dirty_ratio 和 dirty_background_ratio 控制脏页写回的触发阈值，对大量写入场景影响比较大，可以缓解周期性IO卡顿。
• fs.aio-max-nr 与PostgreSQL的异步IO相关，默认值通常够用，提前调大没坏处。

修改后执行：

sudo sysctl -p

2. PostgreSQL 14 核心配置参数调整

2.1 内存参数：shared_buffers 与 effective_cache_size

PostgreSQL 使用自己的共享缓冲池缓存数据页。对于大数据查询，shared_buffers 太小会导致命中率低，每次查询都去磁盘读页；太大又容易挤占操作系统页缓存，反而增加一层复制开销。

经验法则是：shared_buffers 设置为物理内存的25%。如果是专用数据库服务器，可以到30%~40%。以64GB内存的机器为例，设16GB是均衡值。

shared_buffers = 16GB effective_cache_size = 48GB

effective_cache_size 并不是分配内存，它只是告诉优化器“系统里大概有多少可用缓存”。PostgreSQL会把它作为评估索引扫描成本的重要依据。设得太低，规划器宁愿全表扫描也不用索引；设得过高，规划器又会过度乐观，选错索引。64GB机器给48GB，是常见的初始值。

2.2 work_mem：排序与哈希连接的关键内存

work_mem 控制单个排序、哈希连接、聚合操作最多能使用多少内存。它跟 shared_buffers 不一样，是“每个操作都可能分配”的内存，并发一高，内存消耗会被放大好几倍。

判断 work_mem 是否过小的实用方法，是查临时文件落盘情况：

SELECT datname, temp_files, temp_bytes FROM pg_stat_database ORDER BY temp_bytes DESC;

如果 temp_files 持续增长，说明work_mem不足，排序溢出了磁盘。这时候可以逐步调大，但必须观察内存余量，别一下给到256MB，否则并发一高就可能触发OOM。

work_mem = 32MB

以64GB内存、200个连接的服务器为例，32MB的work_mem在100个并发排序时，最坏情况也就3.2GB，压力不大。如果业务里大量hash join，可以先调到64MB观察。

2.3 检查点与WAL参数

大数据查询往往伴随着大量写入负载，检查点和WAL参数直接影响稳定性：

max_wal_size = 8GB min_wal_size = 2GB checkpoint_timeout = 15min checkpoint_completion_target = 0.9 wal_buffers = 16MB wal_writer_delay = 200ms

checkpoint_completion_target 设为0.9，意思是把检查点刷脏页的动作尽量分散到整个检查点周期，避免集中刷盘造成IO尖峰。如果你的业务可以接受小概率崩溃丢失最近事务，synchronous_commit 可以设为 off，显著降低提交延迟。但对订单、支付这类强一致场景，不建议动这个参数。

2.4 并行查询参数

PostgreSQL 14的并行能力对大数据查询非常关键，但默认配置偏保守，可以适度放开：

max_parallel_workers_per_gather = 4 max_parallel_workers = 8 min_parallel_table_scan_size = 8MB min_parallel_index_scan_size = 1MB parallel_setup_cost = 100 parallel_tuple_cost = 10

这些参数的含义是：超过8MB的表扫描才考虑并行；超过1MB的索引扫描可以考虑并行；单个查询最多用4个并行worker；整个实例最多8个。如果服务器是16核以上，可以继续调高。但要注意，并行worker多了以后CPU和IO争抢会抵消收益，最佳值需要实测，常见经验是不超过CPU核心数的一半。

提醒：并行查询不是所有SQL都适用。大表扫描、批量聚合、排序类SQL收益明显；OLTP类型的小事务频繁更新场景，并行反而可能引入额外调度开销。

2.5 连接数与后台进程

max_connections 默认100，如果应用直接建长连接，很容易出现连接不够的问题。

max_connections = 200

但是每条连接都会消耗内存，改大之前先确认系统余量。200个连接看似不多，每个后端进程可能占用几MB到几十MB内存，整体开销不可忽略。更合理的方式是引入连接池，这个放到集群部分细讲。

3. 集群层面优化：让它不只是“多个节点在那跑”

3.1 先理解集群优化的核心矛盾

集群的价值在于三点：分流压力、高可用、提升大查询的资源上限。前两点好理解，第三点容易被忽略——通过主备多节点，把分析类只读查询分流到从库，主库专注写。大数据查询基本都是只读分析型SQL，这种架构收益最大。

很多团队搭了主从，但应用层把所有流量都打到主库，从库只是像个摆设。架构上的优化往往比调参数更立竿见影，也更考验整体设计能力。

3.2 PgBouncer 连接池：立竿见影的稳定性提升

连接数一多就报“too many clients already”，这是PostgreSQL高并发场景最常见的报错。用 PgBouncer 能极大减少实际数据库连接数。

安装很简单：

sudo apt install pgbouncer

修改/etc/pgbouncer/pgbouncer.ini：

[databases] mydb = host=127.0.0.1 port=5432 dbname=mydb [pgbouncer] listen_port = 6432 listen_addr = 0.0.0.0 auth_type = md5 pool_mode = transaction max_client_conn = 2000 default_pool_size = 50

pool_mode 建议用 transaction，一个数据库连接可以被多个客户端事务轮流使用，连接复用效率比 session 模式高得多。应用把连接串从5432改成6432即可，不需要重启数据库。

实测心得：PgBouncer 对高并发小事务场景提升最明显，但如果业务全是单条超大查询，一个查询跑十分钟，连接池的并发复用价值就大打折扣。它主要解决“连接数暴涨导致系统崩溃”的稳定性问题。

还有坑要提醒：PgBouncer 在 transaction 模式下，客户端预编译语句（prepared statement）可能失效，因为同一个数据库连接可能被多个会话复用。应用层如果是重度使用 prepared statement 的，要评估兼容性，或者用 session 模式。

3.3 主从复制搭建与读写分离

PostgreSQL 14 做主从流复制很成熟。主库配置：

wal_level = replica max_wal_senders = 10 hot_standby = on

创建复制账号：

CREATE USER replica REPLICATION LOGIN PASSWORD 'your_strong_password';

从库通过基础备份启动复制。13版本之前需要 recovery.conf，14版本后配置写在postgresql.auto.conf里：

primary_conninfo = 'host=主库IP port=5432 user=replica password=your_strong_password'

然后启动从库，用 pg_stat_replication 检查复制状态：

SELECT client_addr, state, sync_state, replay_lag FROM pg_stat_replication;

3.4 自动故障转移：repmgr 或 Patroni

半夜主库宕机，手工切换非常痛苦，强烈建议使用 repmgr 或 Patroni。以 repmgr 为例，流程是：

1. 安装 repmgr
2. 各节点配置 repmgr.conf
3. 主库注册：repmgr primary register
4. 从库克隆：repmgr -h 主库IP -U repmgr -d repmgr standby clone
5. 配置 failover 脚本或配合 keepalived

有个容易踩的坑：自动切换后，应用连接串如果直连旧IP，故障转移就白做了。要么用虚拟IP，要么让应用通过VIP访问数据库，要么让连接池挂在VIP后面，把故障转移逻辑交给连接池。

3.5 负载均衡

如果只读流量很大，可以在多个从库前加一层负载均衡。Haproxy 做 TCP 转发是最简单的，不需要理解PostgreSQL协议。

配置示例：

listen postgres_readonly bind *:5433 mode tcp balance roundrobin server pg-slave1 192.168.1.11:5432 check server pg-slave2 192.168.1.12:5432 check

应用把只读查询指向5433，自动在两个从库间轮询；写操作继续指向主库5432。这套方案比引入 Pgpool-II 轻量很多，运维成本也低。

4. 大数据查询优化：索引、分区与SQL重写

4.1 从慢日志和 pg_stat_statements 定位问题

别猜，用数据定位。先开启慢日志：

log_min_duration_statement = 1000 log_statement = none track_io_timing = on logger = 'stderr'

重启后，超过1秒的SQL都会记录。更推荐使用 pg_stat_statements 插件来做SQL级统计：

CREATE EXTENSION pg_stat_statements;

然后查Top SQL：

SELECT query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;

注意 total_exec_time 单位是毫秒。这个查询能把整个实例里最耗时的SQL一次性拉出来，非常方便。

4.2 索引优化：选择性决定一切

索引不是越多越好。我给出几条经验性结论：

• 等值查询、高选择性列，用B-tree索引。
• 范围查询且物理顺序与时间顺序一致的大表，BRIN索引性价比极高，体积只有B-tree的几十分之一。
• 数组、全文搜索、JSON字段，用GIN。
• 固定WHERE条件的场景，部分索引能大幅缩小索引体积。

举例，一张10亿行的订单流水表，常见查询：

SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01';

对 created_at 建B-tree索引会很大，维护成本高；但BRIN索引只需要在每个数据页块范围内记录最小和最大时间，体积几十MB，查询也能快速裁剪到对应块：

CREATE INDEX idx_orders_created_at_brin ON orders USING brin(created_at);

复合索引的顺序也常被忽视。假如查询条件经常是WHERE status='PAID' AND created_at>=...，索引建(status, created_at)还是(created_at, status)？

原则是：等值条件列放前面，范围条件列放后面。B-tree索引能先通过等值条件把范围大幅缩小，再在剩余范围内用第二个列做扫描。所以优先：

CREATE INDEX idx_orders_status_created_at ON orders(status, created_at);

4.3 分区表：大数据量下的卸载武器

PostgreSQL 14 内置声明式分区，对按时间归档的大表非常合适：

CREATE TABLE orders_part ( id bigint, created_at timestamptz, status text ) PARTITION BY RANGE (created_at); CREATE TABLE orders_2024_01 PARTITION OF orders_part FOR VALUES FROM ('2024-01-01') TO ('2024-02-01'); CREATE TABLE orders_2024_02 PARTITION OF orders_part FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

查询时会自动做分区裁剪，只扫描相关分区。配合 pg_partman 插件可以自动创建新分区、归档旧分区，省去人工定时任务。

必须提醒：分区表的唯一约束必须包含分区键。如果业务需要全局唯一ID，一般得引入序列或UUID，设计阶段就要想清楚，否则建表后很难改。

4.4 查询重写：常见病与处方

几个最容易把查询拖慢的习惯：

• 无脑SELECT *，表里又带大字段，IO被拖死。只取需要的列。
• 在查询条件里对列做函数运算，比如WHERE EXTRACT(YEAR FROM created_at)=2024，索引直接失效。改成范围条件：WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'。
• 用 OR 连接多个范围条件，优化器可能放弃索引。尝试改成 UNION ALL。
• 过度使用 CTE，让优化器难以合并优化。不是所有CTE都不好，但无关链路嵌套尽量少。

这些经验需要结合 EXPLAIN 逐个验证。务必用EXPLAIN (ANALYZE， BUFFERS)查看真实执行时间和缓冲区命中情况，别只看 estimate rows。

还有一个生产中很实用的技巧：大表变更频繁时要保证统计信息不老化。设置：

ALTER TABLE orders SET (autovacuum_enabled = true);

同时维护好 autovacuum 相关参数，让统计信息保持新鲜，避免执行计划因为统计信息过期而走错路径。

5. 监控与故障排查实录

5.1 常用监控视图

• pg_stat_database：整体事务、阻塞、临时文件。
• pg_stat_replication：复制状态和延迟。
• pg_stat_activity：当前连接和运行中的查询。
• pg_stat_statements：SQL级统计。
• pg_buffercache：共享缓冲池命中情况。

查当前锁等待：

SELECT a.datname, a.pid, a.state, a.wait_event_type, a.wait_event, a.query FROM pg_stat_activity a WHERE a.wait_event_type IN ('Lock', 'Extension', 'IO');

查复制延迟：

SELECT client_addr, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes FROM pg_stat_replication;

5.2 常见问题速查表

5.3 我踩过的几个坑

第一个坑：把 work_mem 调成 1GB，结果当天夜班批量任务直接把内存打爆，OOM killer 把 postmaster 干掉了。教训是：work_mem 这类“每个操作都可能分配”的参数，不能只考虑单条SQL，要考虑并发因子。

第二个坑：开启并行查询后，小事务也被拉上并行worker，CPU疯狂切换，TPS不升反降。我给 min_parallel_table_scan_size 和 min_parallel_index_scan_size 设了合理下限，问题才解决。别把并行参数放开就不管了，默认值其实很适合大部分OLTP场景。

第三个坑：透明大页没关。大内存机器上查询间歇性出现几百毫秒卡顿，查SQL、查锁、查慢日志都没有结论，最后发现是THP分配和释放造成的延时。关闭后平稳很多。这个问题往往被忽略，但影响非常大。

第四个坑：只做主从复制，没做VIP。一次主库故障，从库升级为主，应用却还连着旧地址，运维半夜手动切VIP才恢复。所以数据库集群必须把“连接入口”和“实例”解耦，虚拟IP或连接池必须提前设计好。

5.4 一个轻量监控小技巧

如果团队还没有完整的Prometheus + Grafana体系，可以用 crontab 定时采集关键指标。例如每分钟执行：

psql -t -c "SELECT now(), xact_commit, xact_rollback, blks_read, blks_hit FROM pg_stat_database WHERE datname='mydb';" >> /var/log/pg_stats.log

这些数据不需要多花哨，故障排查时能回放半小时前的指标变化，就已经非常有价值了。

我在实际项目里体会最深的一点是：PostgreSQL集群优化最核心的，不是某一两个参数的灵丹妙药，而是建立“评估→调整→验证”的闭环。每次改动只改一个变量，跑同一组测试去对比，才能知道哪个调整真正起了作用。大数据的响应速度和稳定性，不是靠一次调优就一劳永逸的，数据量增长、业务模式变化都会让参数逐渐失配。保持对执行计划和关键指标的敏感度，定期做复查，才是真正的长期维护之道。