【数据库调优 02】PostgreSQL 在 VPS 上的性能调优：共享内存、连接池与索引

本篇是 数据库调优 系列的第 2 篇，专注「深调篇」——只讲 PostgreSQL 单库：内存配比、廉价盘 IOPS 暗坑、PgBouncer 实战、EXPLAIN/ANALYZE 抓慢查询、索引选型与 autovacuum。如果你还在用 1G 小鸡同时跑 MySQL 和 PG、想先看「双库省内存」总纲，请回看第 1 篇。

很多人在 VPS 上第一次跑 PostgreSQL，都会遇到一个诡异的现象：库刚建好、没几个人访问，内存却悄悄吃满，随后进程被系统的 OOM killer 一刀毙命，数据库直接起不来。问题通常不在 PostgreSQL 本身，而在于它的默认配置是给「独占整台大机器」的数据库服务器准备的——shared_buffers 默认只有 128MB 看着很小，但 work_mem、连接数、autovacuum 这些默认值叠加在一台 2GB 内存的 VPS 上，反而能把整台机器拖垮。本文就按「内存配比 → 磁盘 IOPS → 连接池 → 慢查询 → 索引 → 表膨胀」的顺序，把小内存 VPS 上跑稳 PostgreSQL 的关键参数逐一讲清，所有数字都给可照搬的起点值，你照着改就能避开最常见的坑。

延伸阅读

更多相关攻略推荐：在 GPU VPS 上装 CUDA 跑大模型：驱动、nvidia-s、2026 按小时 GPU 成本横评：H100 / A100 / A4、2026 实测：GPU VPS 上部署 ComfyUI 跑 Stab、【GPU VPS 01】带 GPU 的 VPS 怎么租最划算？H10、2026 游戏服 VPS 怎么选：高主频 CPU、内存与 DDoS 。

一、为什么小内存 VPS 上的 PostgreSQL 总容易跪

先纠正一个常见误解：PostgreSQL 的内存不是像 Java 那样一块大堆。它主要有两块——一块是 shared_buffers，这是所有连接共享的「数据页缓存」，相当于在操作系统文件缓存之上又盖了一层自己的缓存；另一块是 work_mem，它是按「操作」而不是按「连接」分配的，一次复杂查询里如果同时有排序、哈希聚合、哈希连接三个操作，就会吃掉 3 倍的 work_mem。再乘上并发连接数，就是最坏情况下的查询内存预算。这块算错，OOM killer 就会来吃数据库。

还有一点容易被忽略：PostgreSQL 每来一个客户端连接，就在服务端 fork 出一个独立的后端进程，哪怕它正闲着，也要占 5 到 10MB 内存。一个 Web 应用开了 100 个常驻连接，光连接 overhead 就先吃掉 500MB 到 1GB，还没开始跑任何查询。所以在 VPS 上，连接数本身就是一个内存杀手，这正是后面要用 PgBouncer 挡一下的原因。

结论先放在这里：小内存 VPS 调优的核心，是「把内存分给对的地方」+「别让连接数失控」+「让查询走对索引」+「看清磁盘的真实能力」。下面几节分别展开。

二、内存怎么分：shared_buffers 与 work_mem 的配比

shared_buffers 是表数据和索引页的缓存，经验起点是系统总内存的 25%。给太少（低于 15%）会逼着 PostgreSQL 频繁读盘；给太多（超过 40%）反而没好处，因为操作系统自己的页缓存也在帮你缓存热数据，把内存全抢走反而让 OS 没缓存可用。具体落到常见 VPS 规格：2GB 内存设 512MB，4GB 设 1GB，8GB 设 2GB，16GB 设 4GB。改完需要重启 PostgreSQL 才生效。

effective_cache_size 不是真正分配内存，它只是一个「提示」，告诉查询规划器「操作系统和文件系统大概能给你多少缓存可用」，帮它判断走索引扫描还是顺序扫描更划算。建议设到总内存的 50% 到 75%（2GB 给 1GB，4GB 给 2.5GB，8GB 给 5GB，16GB 给 11GB）。低估它会让规划器莫名其妙地选顺序扫描，慢得离谱。

work_mem 控制每个排序、哈希连接、聚合操作能用的内存，不够就往磁盘写临时文件，慢几个数量级。它和连接数强相关，一个粗略公式：(总内存 - shared_buffers - 留给 OS 的) / (max_connections × 2)。常见起点：2GB 设 4MB，4GB 设 8MB，8GB 设 16MB，16GB 设 32MB。注意这是「每个操作」的量，复杂查询会成倍叠加，所以宁小勿大。如果你用 EXPLAIN ANALYZE 看到某个查询在往磁盘 spill，再针对性地把那个会话的 work_mem 调大：用 SET LOCAL work_mem = '64MB'; 只对当前事务生效，比全局调大安全得多。

maintenance_work_mem 管的是 VACUUM、建索引、pg_restore 这类维护操作，给大方一点能显著缩短维护窗口。常见起点：2GB 给 128MB，4GB 给 256MB，8GB 给 512MB，16GB 给 1GB。wal_buffers 一般保持默认的 -1（自动）就好，手动调基本没收益。

为了直观，下面把常见内存档位的可抄起点整理成一张表（PostgreSQL 16/17 通用，新机器可上 18）。注意 work_mem 是「每操作」量，所以下表给的是保守起点：

把这些写成一个独立配置文件最稳妥，升级 PostgreSQL 包时不会被覆盖。例如在 Debian/Ubuntu 上放到 /etc/postgresql/17/main/conf.d/10-tuning.conf，内容大致是：

# 10-tuning.conf
shared_buffers = 1GB          # 4GB VPS 起点
effective_cache_size = 2.5GB
work_mem = 8MB
maintenance_work_mem = 256MB
max_connections = 100
checkpoint_timeout = 15min
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9
wal_compression = on
random_page_cost = 1.1        # 廉价盘/SSD 必调，见第三节
effective_io_concurrency = 200

改完重启：sudo systemctl restart postgresql@17-main。验证一下有没有真正生效：

sudo -u postgres psql -c "SELECT name, setting, unit \
  FROM pg_settings \
  WHERE name IN ('shared_buffers','work_mem','maintenance_work_mem','effective_cache_size');"

最后提醒一句：磁盘的随机读写能力会反过来影响规划器怎么选执行计划，下面第三节专门讲这个最容易踩的暗坑。

三、廉价盘的 IOPS 陷阱：云盘 burst 与随机读写

这是小内存 VPS 上最容易被忽略、也最致命的坑。很多标价极低的机器，磁盘是突发型 SSD 或干脆是共享 HDD，平时跑分好看，一旦你的 burst 额度（通常是几百 MB/s 用几分钟）耗尽，随机读写 IOPS 能掉一个数量级，数据库直接卡成 PPT。PostgreSQL 默认 random_page_cost = 4.0 是按机械盘假设调的，在 SSD/NVMe 上会高估随机读代价，导致规划器宁可选 Seq Scan 也不选索引。所以上了 SSD/NVMe 一定要把 random_page_cost 降到 1.1、effective_io_concurrency 提到 200（这两个参数我们已经写进了上面的 10-tuning.conf），让规划器放心用索引。

动手改之前，先用 fio 量一下盘的真实随机性能，心里才有底：

fio --name=randread --ioengine=libaio --rw=randread --bs=4k --numjobs=4 --size=1G --runtime=60 --time_based

经验数字：廉价突发 SSD 的 4K 随机读可能在 1 万–2 万 IOPS 区间，但持续写一阵会被限速；纯 NVMe 独享盘能到 3 万–5 万 IOPS；而 HDD 或共享盘可能只有几百到一两千。买机器前如果能找到商家给的 IOPS 上限或 burst 策略（AWS t 系列、阿里云共享型、部分超售严重的低价盘都是重灾区），务必心里有数。Hetzner 的 AX 系列、Contabo 的 VPS L 系列给的是相对实在的 NVMe，跑小库舒服很多；真要极致廉价，RackNerd 这类年付机器也能跑，但先把 IOPS 测明白。

另一个省 IO 的办法：把临时文件目录指向更快的盘或内存盘（tmpfs）。排序、哈希溢出时会写临时文件，这类操作频繁的话立竿见影。再配合上一节的 checkpoint_completion_target = 0.9 把检查点写压力摊开，就能避免 checkpoint 一来就暴雨式刷盘把查询卡住。

四、PgBouncer：用连接池挡住打满

前面说过，每个 PostgreSQL 连接都是 fork 出来的进程，吃内存。当你的应用有 10 个副本、每个副本开 20 个连接，就是 200 个服务端连接绝大部分时间在发呆，却把内存吃光，再一遇流量高峰，连接抖动本身就能把数据库搞挂，报错 FATAL: sorry, too many clients already。PgBouncer 就是干这个的标准解：它在应用和 PostgreSQL 之间放一个轻量代理，对外扛住上千个廉价客户端连接，对内只用一小撮固定的真实连接去复用。

PgBouncer 有三种池模式，最关键的是选对模式：

• session（会话模式）：一个客户端连上来，就独占一条服务端连接直到它断开。几乎没池化收益，但最安全，因为会话级特性都能用。
• transaction（事务模式）：一条服务端连接只在「一个事务」期间属于某个客户端，事务一提交就回池。这是大多数 Web 应用的甜点区，复用率最高。
• statement（语句模式）：每条语句执行完就回池，最激进，但多语句事务会直接报错，很少用。

对绝大多数网站和 API，选 transaction 模式。它有个代价：会破坏「假设你的会话和服务端连接是同一个」那些特性——比如跨语句要持久化的 SET 值、LISTEN/NOTIFY、advisory lock（pg_advisory_lock）、会话级 prepared statement，以及临时表。如果你的应用用到这些，要么换 session 模式，要么把那部分代码走「不经过池」的直接连接。另一个常见踩坑：数据迁移工具（如 Prisma migrate、Django migrate）通常把整轮迁移包在一个事务里、还要拿 advisory lock、还要跑 CREATE INDEX CONCURRENTLY，这些都得用不经过 PgBouncer 的直接连接去跑，否则会失败。

安装和最小配置（Debian/Ubuntu）：sudo apt install pgbouncer，编辑 /etc/pgbouncer/pgbouncer.ini：

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
default_pool_size = 25
max_client_conn = 200
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 600
server_lifetime = 3600

这里 default_pool_size = 25 是 PgBouncer 替你维持的真实 PostgreSQL 连接数，max_client_conn = 200 是应用侧看到的连接数；reserve_pool_size / reserve_pool_timeout 给突发连接留点缓冲，server_idle_timeout / server_lifetime 让空闲和过老的连接定期回收，避免长跑后连接僵死。一个经验起点公式：default_pool_size ≈ CPU 核数 × 2 + 1，先从保守值开始，盯着 SHOW POOLS; 再调。配完把应用连接串从 5432 改成 6432 指向 PgBouncer 即可，应用侧自己的连接池要相应调小，因为复用已经交给 PgBouncer 了。

关于池大小有个补充：default_pool_size = 25 是通用起点，但小机器（2 核）上往往偏大。经验公式仍是约「vCPU 数 × 2」，所以 2 核机器给 4–6、最多 10 就够了，文档里常见的 25 更适合 8 核以上的机器。上线后怎么看池子健不健康？连管理库查：

psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer
SHOW POOLS;
SHOW STATS;

在 SHOW POOLS 里，cl_waiting 长期大于 0 说明池太小、要调大 default_pool_size；sv_active 一直顶满而客户端还在等，说明后端 PG 真忙，得从查询或机器配置下手。

transaction 模式还有个兼容细节：现代 ORM（Django、Rails、Prisma）基本能兼容；Prisma 还要在连接串加 pgbouncer=true 关闭服务端预备语句。PgBouncer 1.21+ 支持协议级 prepared statement（max_prepared_statements = 100），能安全支撑激进预备语句的 ORM，安装时尽量用 PGDG 源的新版本。

实测下来，同样 50 个并发压测，没接 PgBouncer 时 PostgreSQL 看到 56 个连接，接了之后只剩 16 个，应用却以为自己有 50 个——内存立省几百 MB，p99 延迟也更稳。这就是连接池在小内存 VPS 上「花小钱办大事」的典型收益。

五、用 EXPLAIN / ANALYZE 抓慢查询

调内存和连接是打地基，真正的性能问题往往藏在某条具体的慢 SQL 里。别猜，用 EXPLAIN (ANALYZE, BUFFERS) 让规划器自己说实话。它显示执行计划、预估行数 vs 实际行数、代价估算、以及缓冲区命中 vs 磁盘读。读计划要从下往上读（最内层节点先执行）。

EXPLAIN (ANALYZE, BUFFERS)
SELECT u.name, p.title
FROM posts p
JOIN users u ON u.id = p.user_id
WHERE p.published = true
  AND p.created_at > NOW() - INTERVAL '7 days';

重点看三件事：第一，大表上是不是出现了 Seq Scan（顺序全表扫）。小表顺序扫没问题，大表基本就是慢的根因。第二，计划里的「实际行数」和「预估行数」差了几个数量级？那多半是统计信息过期了，跑一下 ANALYZE 表名; 再测。第三，Buffers 里 read 很多而 hit 很少，说明数据没在内存里，要么缓存不够，要么这条查询本该走索引却没走。看到 Seq Scan 时，确认 WHERE / JOIN / ORDER BY 上的列有没有索引；索引列被函数包住（比如查 LOWER(email) 却只在 email 上建了索引）也会让索引失效。

读计划时还有两个值得盯的点：一是 Rows Removed by Filter（被过滤掉的行数），如果某个节点上这个数字高达几十万，基本就是缺索引的铁证；二是要看 actual time 而不是 cost —— cost 只是规划器的估算，actual time 才是真实耗时，别被 cost 误导。下面是个典型慢查询：从 50 万行订单里按状态和日期筛，先看计划：

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
  AND o.created_at >= NOW() - INTERVAL '30 days'
ORDER BY o.created_at DESC
LIMIT 100;

如果计划里出现 Seq Scan on orders 且 Rows Removed by Filter 极高，就加一个复合索引，等值列在前、范围列在后：

CREATE INDEX CONCURRENTLY idx_orders_status_created
  ON orders (status, created_at DESC);
ANALYZE orders;

要系统性找慢查询，先开查询日志定位候选：在 postgresql.conf 里设 log_min_duration_statement = 200（记录超过 200ms 的语句），再用 pgBadger 生成报表。更进一步，装 pg_stat_statements 扩展（shared_preload_libraries 加上它），按平均耗时排序能直接锁定最该优化的高频慢 SQL：

CREATE EXTENSION pg_stat_statements;
SELECT query, calls, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

对会改数据的语句做 EXPLAIN ANALYZE，记得包在事务里回滚：BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;，避免副作用。

六、索引怎么选，又如何避免过度索引

索引是双刃剑：它让读变快，但每一条 INSERT/UPDATE/DELETE 都要同步维护所有相关索引，写就变慢，磁盘也涨。所以原则是「只给真在查的列建索引」，而不是见列就建。

索引类型先选对：

• B-tree（默认）：等值（=）、范围（<、>、BETWEEN）、前缀 LIKE（foo%）、排序都用它，覆盖绝大多数场景。
• GIN：全文检索（tsvector）、JSONB 包含（@>）、数组重叠（&&）。用对地方很快，但写入放大明显。
• GiST：几何/地理（PostGIS）、范围类型、最近邻查询。
• BRIN：超大、按时间/顺序天然有序的表（如追加日志的时间戳），体积只有 B-tree 的约千分之一，几乎零写入开销。
• Hash：只支持等值，收益有限，除非你实测证明 B-tree 是瓶颈，否则别用。

复合索引的列顺序很关键：(a, b) 能用于「只查 a」或「a AND b」，但不能用于「只查 b」。把最高选择性的列放前面。覆盖索引（用 INCLUDE 把查询要的列也带进去）能让热查询只扫索引不回表（Index Only Scan），对读多写少的接口提升巨大，例如 CREATE INDEX idx_orders_covering ON orders (user_id, created_at) INCLUDE (status, total);。还有部分索引：CREATE INDEX idx_pending ON orders (created_at) WHERE status = 'pending';，只给真正会查的那一小撮行建索引，体积小、维护快。

过度索引的典型症状是：写入慢、磁盘涨，但 pg_stat_user_indexes 里一堆 idx_scan = 0 的「死索引」白占资源。定期查一下：

SELECT schemaname, relname AS table_name, indexrelname AS index_name, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelid NOT IN (SELECT conindid FROM pg_constraint)
ORDER BY pg_relation_size(indexrelid) DESC;

生产表上建索引务必用 CREATE INDEX CONCURRENTLY，它会边建边放读写，不锁表；普通 CREATE INDEX 在大表上会锁写几分钟，线上就是事故。索引用久了也会膨胀，用 REINDEX INDEX CONCURRENTLY 索引名; 或 pg_repack 在线回收空间。

七、autovacuum 与统计信息：别让表膨胀拖垮你

PostgreSQL 的 MVCC 机制下，UPDATE/DELETE 会产生死元组，需要 autovacuum 后台清理，否则表会持续膨胀、索引失效、查询越来越慢。小内存机器上 autovacuum 默认参数偏保守，表一大就跟不上写入。所以建议保持 autovacuum = on，并把它调激进一点：autovacuum_vacuum_scale_factor = 0.05（死元组到 5% 就 vacuum，默认是 20%）、autovacuum_analyze_scale_factor = 0.02、autovacuum_naptime = 30s，同时设 log_autovacuum_min_duration = 0 方便观察它到底有没有在干活。否则死元组堆积，查询会越跑越慢，磁盘也悄悄涨。

另一个常被忽视的点是统计信息：统计信息过期是 EXPLAIN 估算与实际行数偏差 10 倍以上的主因。除了对大表定期 ANALYZE，对数据极度倾斜的列可以调高 default_statistics_target（默认 100，可到 1000）让规划器更懂分布。这两件事做好了，很多时候「慢查询」不用动 SQL 就自己好了。

延伸阅读：调好参数只是数据库运维的一面，另一面是 自动备份数据库到对象存储 这条保命链路；如果你还在挑机器，VPS 新手入门指南 能帮你按内存和流量选规格，10 刀以下年付横评 可做横向比价（但跑库别只看月付价，磁盘 IOPS 和内存才是命门）。想看具体厂商表现，Contabo 2026 评测 里也聊到了它的大内存机型跑数据库的体感；欧洲节点可看 Hetzner，多机房低延迟回国可看 Vultr。