三国志2017fan客户端
1.49 GB · 2025-11-15
最近在深入研究PostgreSQL的内部机制，发现这玩意儿的进程架构真的挺有意思的。今天就和大家聊聊PostgreSQL实例是怎么运作的，从按下启动按钮到处理查询的整个过程。
我一开始接触PostgreSQL的时候，总以为它就是一个单体程序在跑。后来用ps命令一看，好家伙，一堆postgres进程，当时就懵了。研究了一阵子才搞明白，PostgreSQL其实更像一个工厂系统。
graph TB
subgraph clients["客户端"]
C1[应用程序 1]
C2[应用程序 2]
C3[应用程序 N]
end
subgraph connection["连接管理"]
PM[Postmaster<br/>主进程]
end
subgraph backend["查询执行"]
BE1[Backend 1]
BE2[Backend 2]
BE3[Backend N]
end
subgraph memory["共享内存"]
SB[Shared Buffers<br/>数据缓存]
WB[WAL Buffers<br/>日志缓冲]
CL[CLOG<br/>事务状态]
end
subgraph bgworkers["后台维护"]
BG[Background Writer]
CK[Checkpointer]
WW[WAL Writer]
AV[AutoVacuum]
end
subgraph storage["存储"]
DF[(数据文件)]
WF[(WAL日志)]
end
C1 --> PM
C2 --> PM
C3 --> PM
PM --> BE1
PM --> BE2
PM --> BE3
BE1 --> SB
BE2 --> SB
BE3 --> SB
BE1 --> WB
BG --> SB
CK --> SB
WW --> WB
BG --> DF
CK --> DF
WW --> WF
AV --> DF
style PM fill:#ff6b6b,stroke:#333,stroke-width:3px
style SB fill:#4ecdc4,stroke:#333,stroke-width:2px
style WB fill:#ffe66d,stroke:#333,stroke-width:2px
这里面有几个关键角色：
Postmaster是老大，负责总调度。它就像厂长一样，监听5432端口等客户来连，每来一个连接就fork出一个Backend进程去服务。我当时好奇为什么不用线程，后来才知道这样做的好处是进程间隔离得好，一个进程崩了不影响其他的。
Backend进程是真正干活的。一个客户端连接对应一个Backend，这个设计挺有意思的。每个Backend有自己独立的内存空间，负责解析SQL、优化查询、执行操作。不过这也意味着如果你的max_connections设置成500，理论上就可能有500个Backend进程，吃内存还是挺猛的。
后台进程这帮家伙是维护团队，各司其职：
共享内存就像工厂的仓库，所有进程都能访问。主要是Shared Buffers存数据页，WAL Buffers存日志，还有一些锁表、事务状态之类的东西。
我们来看看执行pg_ctl start之后发生了什么。
sequenceDiagram
actor Admin as 管理员
participant OS as 操作系统
participant PM as Postmaster
participant Conf as 配置文件
participant SM as 共享内存
participant WAL as WAL日志
participant BG as 后台进程
Note over Admin,BG: 阶段1: 启动命令
Admin->>OS: pg_ctl start -D /data
OS->>PM: 启动Postmaster进程
activate PM
Note over Admin,BG: 阶段2: 读取配置
PM->>Conf: 读取 postgresql.conf
Conf-->>PM: 返回配置参数
PM->>Conf: 读取 pg_hba.conf
Conf-->>PM: 返回认证规则
Note over Admin,BG: 阶段3: 分配共享内存
PM->>SM: 分配 Shared Buffers
activate SM
PM->>SM: 分配 WAL Buffers
PM->>SM: 初始化 Lock Tables
PM->>SM: 初始化 CLOG
Note right of SM: 总共约4.2GB<br/>共享内存
Note over Admin,BG: 阶段4: 崩溃恢复检查
PM->>WAL: 检查 pg_control 文件
WAL-->>PM: 返回数据库状态
alt 数据库异常关闭
Note right of PM: 需要恢复!
PM->>WAL: 读取WAL日志
PM->>SM: 重放WAL记录
Note right of SM: 恢复到一致状态
else 数据库正常关闭
Note right of PM: 无需恢复
end
Note over Admin,BG: 阶段5: 启动后台进程
PM->>BG: fork() Background Writer
activate BG
Note right of BG: 每200ms扫描
PM->>BG: fork() Checkpointer
Note right of BG: 每10min检查点
PM->>BG: fork() WAL Writer
Note right of BG: 每200ms刷盘
PM->>BG: fork() AutoVacuum Launcher
Note right of BG: 每1min检查
PM->>BG: fork() Stats Collector
Note right of BG: 收集统计
Note over Admin,BG: 阶段6: 准备就绪
PM->>OS: 监听端口 5432
Note over PM: 开始接受连接
PM-->>Admin: 启动完成
deactivate SM
deactivate BG
deactivate PM
这个过程其实有几个点值得注意：
启动的时候会读两个重要配置文件，postgresql.conf是主配置，pg_hba.conf管认证规则。我之前改了postgresql.conf忘了reload，查了半天为什么不生效，后来才发现有些参数必须重启才行。
共享内存的分配比较关键。Postmaster会一次性分配好所有共享内存，这就是为什么shared_buffers改了必须重启的原因。我当时在一台16GB的机器上把shared_buffers设成了12GB，结果启动都启动不起来，操作系统直接拒绝分配这么大的共享内存。后来查资料才知道，一般建议设置为物理内存的25-40%就够了。
如果上次是异常关闭，启动时会做崩溃恢复。Postmaster会检查pg_control文件，发现状态不对就会去读WAL日志重放。这个机制挺靠谱的，我经历过几次服务器断电，重启后PostgreSQL都能自动恢复过来。
启动完后台进程的顺序也是有讲究的。Background Writer和Checkpointer是写数据的，WAL Writer是写日志的，AutoVacuum是清理垃圾的。这些进程都是从Postmaster fork出来的，它们会一直在后台运行，直到数据库关闭。
验证启动是否成功，可以看看进程列表：
ps -ef | grep postgres
# 你会看到类似这样的输出
postgres 1234 1 Ss 09:00 postgres
postgres 1235 1234 Ss 09:00 postgres: checkpointer
postgres 1236 1234 Ss 09:00 postgres: background writer
postgres 1237 1234 Ss 09:00 postgres: walwriter
postgres 1238 1234 Ss 09:00 postgres: autovacuum launcher
第一个进程就是Postmaster，后面的都是它的子进程。
内存配置是PostgreSQL性能的关键，我当时踩了不少坑。先看看内存的整体结构：
graph TD
subgraph shared["共享内存区（所有进程共享）"]
SB["Shared Buffers<br/>数据页缓存<br/>推荐: RAM的25-40%<br/>示例: 4GB (16GB服务器)"]
WB["WAL Buffers<br/>事务日志缓冲<br/>推荐: 16MB<br/>或 shared_buffers的1/32"]
CL["CLOG<br/>事务提交日志<br/>记录事务提交状态"]
LS["Lock Space<br/>锁管理表<br/>管理各种锁"]
PA["Proc Array<br/>进程数组<br/>活动进程信息"]
end
subgraph local1["Backend 1 本地内存"]
WM1["work_mem<br/>查询操作<br/>排序/哈希/聚合<br/>每个操作独立!"]
MM1["maintenance_work_mem<br/>维护操作<br/>VACUUM/CREATE INDEX"]
TB1["temp_buffers<br/>临时表缓冲"]
end
subgraph local2["Backend 2 本地内存"]
WM2["work_mem"]
MM2["maintenance_work_mem"]
TB2["temp_buffers"]
end
subgraph os["操作系统层"]
OC["OS Page Cache<br/>文件系统缓存<br/>effective_cache_size<br/>推荐: RAM的50-75%"]
end
SB --> OC
WB --> OC
WM1 -.使用.-> SB
WM2 -.使用.-> SB
style SB fill:#4ecdc4,stroke:#333,stroke-width:3px
style WB fill:#ffe66d,stroke:#333,stroke-width:3px
style WM1 fill:#f38181,stroke:#333,stroke-width:2px
style WM2 fill:#f38181,stroke:#333,stroke-width:2px
style OC fill:#a8e6cf,stroke:#333,stroke-width:2px
Shared Buffers不是越大越好
我最开始以为这个越大性能越好，就把32GB服务器的shared_buffers设成了24GB。结果发现checkpoint的时候IO压力巨大，而且启动时间变得超长。后来研究才明白，PostgreSQL的设计是依赖操作系统缓存的，shared_buffers设置太大反而会和OS cache抢内存。
一般的建议是物理内存的25-40%，比如16GB的机器设4GB就挺合适：
# 16GB服务器的配置
shared_buffers = 4GB # 25% of 16GB
effective_cache_size = 12GB # 75% of 16GB，这个只是告诉优化器的，不实际分配
work_mem是个大坑
这个参数我被坑惨了。work_mem是每个查询操作使用的内存，注意是每个操作！一个复杂查询可能有多个排序、哈希操作，每个都会用work_mem。
假设你设置了work_mem = 256MB，max_connections = 500，某个查询有5个操作同时跑，那就是256MB × 5 = 1.28GB，如果有100个这样的并发查询，理论上就需要128GB内存！服务器直接OOM。
我的建议是保守设置，比如16MB：
work_mem = 16MB # 保守设置
# 计算公式参考：
# (RAM - shared_buffers) / (max_connections × 预期并发操作数)
# = (16GB - 4GB) / (200 × 3) ≈ 20MB
如果某些查询确实需要更大的work_mem，可以会话级别临时调整：
SET work_mem = '256MB'; -- 仅对当前会话生效
SELECT * FROM huge_table ORDER BY ...;
effective_cache_size容易被忽略
这个参数不分配内存，但它告诉查询优化器操作系统大概有多少缓存可用。设置合理的话，优化器会做出更好的查询计划。一般设置为物理内存的50-75%。
监控缓存命中率是个好办法：
SELECT
sum(heap_blks_read) as disk_reads,
sum(heap_blks_hit) as buffer_hits,
round(
sum(heap_blks_hit)::numeric /
nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0) * 100,
2
) as hit_ratio_percent
FROM pg_statio_user_tables;
一般来说，hit_ratio_percent应该在99%以上。如果低于90%，说明缓存太小了，考虑增加shared_buffers。
现在我们来看看一个SQL查询的完整生命周期。假设有个客户端要查询数据：
sequenceDiagram
participant C as 客户端
participant PM as Postmaster
participant BE as Backend
participant SM as Shared Memory
participant D as 磁盘
C->>PM: 1. 连接请求
PM->>BE: 2. fork Backend进程
BE-->>C: 3. 连接成功
C->>BE: 4. 发送SQL查询
Note over BE: 解析阶段
BE->>BE: 5. 词法+语法分析
BE->>BE: 6. 语义检查
Note over BE: 规划阶段
BE->>SM: 7. 获取统计信息
SM-->>BE: 表大小/索引信息
BE->>BE: 8. 生成执行计划
Note over BE: 执行阶段
BE->>SM: 9. 查找数据页
alt 缓存命中
SM-->>BE: 10a. 返回数据
Note right of SM: Buffer Hit
else 缓存未命中
SM->>D: 10b. 读取文件
D-->>SM: 11b. 返回数据
SM-->>BE: 12b. 返回数据
Note right of SM: Buffer Miss
end
BE->>BE: 13. 过滤/聚合
BE-->>C: 14. 返回结果
这个过程其实挺复杂的：
解析阶段会把SQL文本转成内部结构，检查语法是否正确，表和字段是否存在。这一步比较快。
规划阶段是重点。优化器会从统计信息里获取表的行数、数据分布等，然后评估不同的执行方案。比如是全表扫描还是用索引，是用嵌套循环还是哈希连接。这个过程相当于给查询找一条最优路径。
我们可以用EXPLAIN看看优化器在想什么：
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users WHERE age > 18;
-- 输出类似这样：
-- Seq Scan on users
-- (cost=0.00..15.50 rows=100 width=40)
-- (actual time=0.012..0.156 rows=95 loops=1)
-- Filter: (age > 18)
-- Rows Removed by Filter: 5
-- Buffers: shared hit=5
-- Planning Time: 0.123 ms
-- Execution Time: 0.234 ms
这里面有几个关键信息：
执行阶段Backend就开始真正读数据了。它会先去Shared Buffers里找，找到了就直接用（Buffer Hit），找不到就得去磁盘读（Buffer Miss）。读进来的数据会放到Shared Buffers里，下次其他查询可能就能直接用了。
INSERT、UPDATE、DELETE这些写操作涉及的东西更多。PostgreSQL用的是WAL（Write-Ahead Logging）机制，简单说就是先写日志，再改数据。
sequenceDiagram
participant C as 客户端
participant BE as Backend
participant WB as WAL Buffers
participant SB as Shared Buffers
participant WF as WAL Files
participant DF as Data Files
C->>BE: BEGIN
BE->>BE: 分配事务ID
C->>BE: INSERT INTO users ...
Note over BE,WF: 先写日志 (WAL)
BE->>WB: 写WAL记录
Note over BE,DF: 再改数据
BE->>SB: 修改数据页
BE->>SB: 标记为脏页
BE-->>C: OK
C->>BE: COMMIT
Note over BE,WF: 提交时强制刷盘
BE->>WB: 写COMMIT记录
WB->>WF: fsync刷到磁盘
Note right of WF: 持久化保证
BE-->>C: COMMIT成功
Note over SB,DF: 后台异步写入
BG->>SB: 扫描脏页
BG->>DF: 异步写入磁盘
这个设计挺巧妙的。WAL日志是顺序写，速度快。数据文件是随机写，速度慢，但可以异步慢慢写。就算数据还没完全写到数据文件，只要WAL写成功了，崩溃后也能恢复。
PostgreSQL用的多版本并发控制（MVCC）挺有意思。UPDATE操作不是原地修改，而是创建新版本，旧版本保留着。每行数据有个xmin（创建它的事务ID）和xmax（删除它的事务ID）。
不同事务根据自己的快照看到不同的数据版本，这样读不阻塞写，写也不阻塞读。但代价是会产生很多死元组（dead tuples），需要VACUUM来清理。
我们经历过一次表膨胀的问题。有张表频繁UPDATE，结果发现查询越来越慢，用下面的SQL一看，死元组占了30%：
SELECT
tablename,
n_live_tup AS live_rows,
n_dead_tup AS dead_rows,
round(
n_dead_tup * 100.0 /
NULLIF(n_live_tup + n_dead_tup, 0),
2
) AS bloat_percent
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;
后来手动VACUUM了一下，查询速度才恢复正常。
WAL的配置也有几个要注意的：
# WAL基础配置
wal_level = replica # minimal/replica/logical
wal_buffers = 16MB # WAL缓冲区大小
wal_writer_delay = 200ms # WAL Writer间隔
# WAL文件管理
min_wal_size = 1GB # 保留的WAL最小大小
max_wal_size = 2GB # 触发checkpoint的WAL大小
# 同步提交配置
synchronous_commit = on # 持久性级别
# on: 等待WAL写入并fsync（最安全）
# local: 写入但不等待fsync（较快）
# off: 不等待写入（最快，可能丢数据）
synchronous_commit这个参数很关键。设成on最安全但最慢，off最快但崩溃可能丢最近几个事务的数据。我们在生产环境都是用on，但在一些允许丢数据的场景（比如日志收集）会用off来提升性能。
AutoVacuum是PostgreSQL的清洁工，负责清理MVCC产生的死元组。刚开始我没太重视这个，后来吃了大亏。
graph TD
Start[AutoVacuum Launcher<br/>每1分钟醒来] --> Check[检查所有表统计]
Check --> Calc{dead_tuples > <br/>threshold + scale * live}
Calc -->|是| Launch[启动Worker进程]
Calc -->|否| Sleep[继续休眠 1分钟]
Launch --> Worker[Worker扫描表]
Worker --> Clean[标记死元组空间]
Clean --> FSM[更新Free Space Map]
FSM --> VM[更新Visibility Map]
VM --> Stats[更新统计信息]
Stats --> Done[Worker完成]
Done --> Sleep
Sleep --> Check
style Start fill:#4facfe,stroke:#333,stroke-width:2px
style Worker fill:#95e1d3,stroke:#333,stroke-width:2px
style Done fill:#ffe66d,stroke:#333
AutoVacuum默认每分钟检查一次，看哪些表需要清理。触发条件是：
dead_tuples > 50 + 0.2 * live_tuples
比如10000行的表，需要积累50 + 2000 = 2050个死元组才触发。对于频繁更新的表，这个阈值可能太高了。
我们有张订单表，每秒几百个UPDATE，结果AutoVacuum总是跟不上，表越来越大。后来给这张表单独调了参数：
ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor = 0.02, -- 从0.2降到0.02
autovacuum_vacuum_threshold = 100
);
这样10000行的表只需要100 + 200 = 300个死元组就触发，膨胀问题基本解决了。
监控AutoVacuum执行情况也很重要：
SELECT
schemaname,
tablename,
last_autovacuum,
last_autoanalyze,
n_live_tup,
n_dead_tup,
round(
n_dead_tup * 100.0 /
NULLIF(n_live_tup, 0),
2
) AS dead_percent
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;
如果发现某些表的last_autovacuum是很久以前，或者dead_percent很高，就得关注了。
还有一点，长事务会阻止VACUUM清理。我们遇到过有个分析查询跑了几个小时，期间所有的VACUUM都无法清理这个查询可见的旧版本，导致表膨胀。所以尽量避免长时间的事务。
生产环境跑PostgreSQL，监控是必不可少的。
SELECT
count(*) AS current,
current_setting('max_connections')::int AS max,
round(
count(*)::numeric /
current_setting('max_connections')::numeric * 100,
1
) AS usage_percent
FROM pg_stat_activity;
如果usage_percent经常超过80%，要么增大max_connections，要么考虑用连接池（比如PgBouncer）。我们现在都用PgBouncer，几千个应用连接对应几十个数据库连接，省资源。
SELECT
pid,
now() - query_start AS duration,
state,
substring(query, 1, 50) AS query
FROM pg_stat_activity
WHERE state != 'idle'
AND query_start < now() - interval '5 seconds'
ORDER BY duration DESC;
这个查询能找出运行超过5秒的SQL。我们在生产环境设置了log_min_duration_statement = 1000，自动记录超过1秒的查询。
SELECT
pid,
wait_event_type,
wait_event,
query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';
如果发现有查询长时间等锁，可能是有长事务或者死锁。找到阻塞的进程后，必要时可以用pg_terminate_backend杀掉。
-- 查看最大的表
SELECT
schemaname,
tablename,
pg_size_pretty(
pg_total_relation_size(
schemaname||'.'||tablename
)
) AS total_size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(
schemaname||'.'||tablename
) DESC
LIMIT 10;
-- 查看WAL占用
SELECT
pg_size_pretty(sum(size))
FROM pg_ls_waldir();
我们有次WAL目录把磁盘撑爆了，原因是archive_command配置错误，WAL文件归档失败堆积。后来修复了归档命令，老的WAL才被清理掉。
症状是应用报错"FATAL: sorry, too many clients already"。查了一下发现有几百个idle连接不释放，原来是应用的连接池配置有问题，连接泄露了。
临时解决办法是杀掉空闲太久的连接：
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
AND now() - state_change > interval '2 hours';
长期解决还是要修应用代码，确保连接正确释放。
有次线上查询突然慢了10倍，排查发现统计信息过期了，优化器选错了执行计划。手动ANALYZE后恢复正常：
ANALYZE table_name;
后来把autovacuum_analyze_scale_factor调小了，确保统计信息及时更新。
前面提到过，MVCC会产生死元组。如果AutoVacuum跟不上，表会越来越大，查询越来越慢。除了调整AutoVacuum参数，严重的时候可能需要VACUUM FULL：
VACUUM FULL table_name;
但要注意VACUUM FULL会锁表重写，生产环境要慎用。我们一般会在低峰期做，或者用pg_repack这种在线重建的工具。
用了几年PostgreSQL，总结一些经验：
启用AutoVacuum并定期检查执行情况，这个真的很重要。定期ANALYZE保持统计信息准确，特别是数据变化大的表。
使用连接池管理连接，别让应用直连数据库。我们用PgBouncer，效果挺好。
监控缓存命中率，保持在99%以上。如果太低说明shared_buffers不够或者查询模式有问题。
避免长事务，它会阻止VACUUM清理，导致表膨胀。我们现在规定分析类查询必须在只读副本上跑。
检查表膨胀率，及时处理超过20%的表。设置慢查询日志，log_min_duration_statement = 1000能帮你找到性能瓶颈。
在测试环境验证所有配置变更，有些参数改错了可能导致性能下降甚至启动失败。
建立完善的备份和恢复策略，定期演练恢复流程。我们用的是pg_basebackup做基础备份，加上WAL归档做增量。
Checkpoint是PostgreSQL的一个重要机制，它负责把内存中的脏页全部刷到磁盘，创建一个一致性检查点。崩溃恢复的时候，只需要从最近的checkpoint开始重放WAL就行了。
graph LR
Start[Checkpoint触发] --> Scan[扫描Shared Buffers]
Scan --> Find[找出所有脏页]
Find --> Sort[按文件/页号排序]
Sort --> Write[顺序写入磁盘]
Write --> Sync[fsync强制刷盘]
Sync --> Update[更新pg_control]
Update --> Done[Checkpoint完成]
style Start fill:#ff6b6b,stroke:#333,stroke-width:2px
style Write fill:#4ecdc4,stroke:#333,stroke-width:2px
style Done fill:#95e1d3,stroke:#333
Checkpoint的触发条件有几个：
Checkpoint配置要小心：
checkpoint_timeout = 10min # 检查点最大间隔
checkpoint_completion_target = 0.9 # 在90%时间内完成
# 这个配置的意思是：
# 如果10分钟触发checkpoint，那会在9分钟内慢慢把脏页写完
# 这样可以平滑IO压力，不会一下子涌入大量写请求
我们之前checkpoint_completion_target设成0.1，结果checkpoint时IO瞬间打满，业务查询都变慢了。改成0.9后，IO压力平滑了很多。
可以在日志里看checkpoint的执行情况：
log_checkpoints = on
日志会输出类似这样的信息：
LOG: checkpoint starting: time
LOG: checkpoint complete: wrote 1953 buffers (11.9%);
0 WAL file(s) added, 0 removed, 1 recycled;
write=8.505 s, sync=0.024 s, total=8.556 s;
sync files=7, longest=0.013 s, average=0.003 s
如果看到checkpoint频繁或者时间很长，可能需要调整参数。
几个后台进程其实是配合工作的，理解它们的关系能更好地调优。
sequenceDiagram
participant BE as Backend进程
participant SB as Shared Buffers
participant BW as Background Writer
participant CK as Checkpointer
participant WW as WAL Writer
participant WF as WAL Files
participant DF as Data Files
Note over BE,DF: 正常写入流程
BE->>SB: 修改数据页（标记脏页）
BE->>WF: 写WAL日志
Note over BW: 每200ms醒来
BW->>SB: 扫描脏页
BW->>DF: 异步写入部分脏页
Note right of BW: 减轻checkpoint压力
Note over WW: 每200ms醒来
WW->>WF: 刷WAL缓冲
Note over CK: 每10分钟或WAL满
CK->>SB: 扫描所有脏页
CK->>DF: 全部强制写入
CK->>DF: fsync确保落盘
Note right of CK: 创建一致性检查点
Background Writer是个好帮手，它会提前把一些脏页写掉，这样checkpoint到来的时候压力就小多了。但它不会太激进，有个成本控制：
bgwriter_delay = 200ms # 扫描间隔
bgwriter_lru_maxpages = 100 # 每次最多写100页
bgwriter_lru_multiplier = 2.0 # 成本倍数
我们根据服务器的IO能力调整了这些参数。SSD的话可以把bgwriter_lru_maxpages调大一些，让它写得更积极。
PostgreSQL的进程间通信主要靠共享内存和信号量。
所有进程都能访问共享内存，读写数据页、WAL缓冲、锁表等。但为了避免冲突，访问共享资源时需要用轻量锁（LWLock）或自旋锁（SpinLock）保护。
信号量用于进程间的同步，比如一个Backend等待另一个Backend释放锁。
查看当前进程信息：
SELECT
pid,
backend_type,
backend_start,
state,
wait_event_type,
wait_event
FROM pg_stat_activity
ORDER BY backend_start;
backend_type会显示进程类型：
如果看到很多进程的wait_event_type是Lock，说明锁竞争比较严重，可能需要优化业务逻辑。
遇到问题的时候，有几个调试方法挺好用。
查看进程在干什么
SELECT
pid,
usename,
application_name,
client_addr,
backend_start,
state,
state_change,
query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;
能看到每个活跃进程正在执行的SQL，执行了多久。
查看锁等待关系
SELECT
blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query
FROM pg_stat_activity AS blocked
JOIN pg_stat_activity AS blocking
ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;
这个能找出谁阻塞了谁，对排查锁等待很有用。
查看表和索引的使用情况
-- 表扫描统计
SELECT
schemaname,
tablename,
seq_scan, -- 顺序扫描次数
seq_tup_read, -- 顺序扫描读取的行数
idx_scan, -- 索引扫描次数
idx_tup_fetch -- 索引扫描读取的行数
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;
如果某个表的seq_scan很高但没有idx_scan，可能需要加索引。
查看索引大小和使用情况
SELECT
schemaname,
tablename,
indexname,
idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan;
idx_scan为0的索引可能是无用索引，占着空间还拖慢写入速度，可以考虑删掉。
分析慢查询
-- 启用慢查询日志
ALTER SYSTEM SET log_min_duration_statement = 1000; -- 记录超过1秒的查询
SELECT pg_reload_conf();
-- 或者临时设置
SET log_min_duration_statement = 1000;
慢查询会记录到日志文件，可以用pgBadger这种工具分析。
PostgreSQL的流复制也是基于WAL的。主库把WAL日志流式传输给备库，备库不断重放WAL来保持同步。
graph LR
Primary[主库] -->|WAL Stream| Standby1[备库1]
Primary -->|WAL Stream| Standby2[备库2]
Primary -.Archive.-> Archive[WAL归档]
Archive -.Restore.-> Standby1
Archive -.Restore.-> Standby2
style Primary fill:#ff6b6b,stroke:#333,stroke-width:3px
style Standby1 fill:#4ecdc4,stroke:#333,stroke-width:2px
style Standby2 fill:#4ecdc4,stroke:#333,stroke-width:2px
主库需要配置：
wal_level = replica # 启用复制级别WAL
max_wal_senders = 10 # 最大WAL发送进程数
wal_keep_size = 1GB # 保留的WAL大小
备库通过一个叫WAL Receiver的进程接收WAL，然后由Startup进程重放。备库可以设置成hot standby模式，允许只读查询：
hot_standby = on # 允许备库只读
我们用备库做查询分流，把报表、分析这些查询都扔到备库上，减轻主库压力。不过要注意，备库重放WAL的时候可能和只读查询冲突，需要调整：
max_standby_streaming_delay = 30s # 备库查询最多延迟重放30秒
如果备库查询时间太长，可能导致主备延迟增大。
最后说说性能优化，这块其实没有银弹，要具体问题具体分析。
索引优化
索引是最直接有效的优化手段。但索引不是越多越好，每个索引都会拖慢写入速度。我们的原则是：
查询优化
用EXPLAIN ANALYZE看执行计划，找出瓶颈：
有时候改写SQL比加索引效果更好。比如用EXISTS替代IN，用JOIN替代子查询等。
分区表
数据量特别大的表可以考虑分区。我们有张日志表，按月分区，查询只扫描相关分区，速度快很多：
CREATE TABLE logs (
id bigint,
created_at timestamp,
message text
) PARTITION BY RANGE (created_at);
CREATE TABLE logs_2024_01 PARTITION OF logs
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE logs_2024_02 PARTITION OF logs
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
分区表的维护比较麻烦，需要定期创建新分区、删除旧分区。我们写了个定时任务自动管理。
连接池
前面提过，用PgBouncer这种连接池能大大减少数据库的连接开销。PgBouncer支持三种池化模式：
我们用的是transaction模式，兼顾了连接复用和事务隔离。
读写分离
把只读查询分流到备库，可以减轻主库压力。但要注意主备延迟，如果业务对数据一致性要求高，可能不适合读写分离。
我们的做法是，实时性要求高的查询走主库，报表、分析这些可以容忍几秒延迟的走备库。
PostgreSQL的进程架构和内存管理其实还有很多细节，这篇文章只是覆盖了主要的部分。真正要用好PostgreSQL，还需要在实践中不断摸索和积累经验。
有些东西我也还在研究，比如JIT编译、并行查询、逻辑复制等。这些特性在特定场景下能带来显著的性能提升，但也有各自的适用条件和限制。
建议多看官方文档，多做测试，多关注社区的讨论。遇到问题的时候，查日志、看执行计划、分析统计信息，大部分问题都能找到线索。
如果这篇文章对你理解PostgreSQL有帮助，那就太好了。有问题或者想交流的，欢迎留言。
1.49 GB · 2025-11-15
1.87 GB · 2025-11-15
1.87 GB · 2025-11-15
