05.3.1.1 PG 进程模型与实例架构
所属:全栈开发面试题库 / 05 数据库与存储 / 05.3 PostgreSQL / 05.3.1 架构、存储与 MVCC
覆盖范围
- postmaster、backend 与后台辅助进程
- shared_buffers 共享内存与本地 work_mem
- 库 / 表空间 / schema 层级与配置参数分级
- PgBouncer 连接池与连接耗尽治理
- 编码区域、模板库、extension 与实例生命周期
高频题速览
| 编号 | 题目 | 难度 | 频率 |
|---|---|---|---|
| 05.3.1.1-001 | PG 进程模型与 MySQL 区别 | 进阶 | ★★★★★ |
| 05.3.1.1-002 | postmaster 与后台/工作进程 | 进阶 | ★★★★☆ |
| 05.3.1.1-003 | shared_buffers 内存结构 | 进阶 | ★★★★☆ |
| 05.3.1.1-006 | PgBouncer 连接池模式 | 进阶 | ★★★★★ |
| 05.3.1.1-007 | 连接耗尽与 backlog 治理 | 高级 | ★★★★☆ |
进程与内存主线
postmaster 监听连接 → fork backend(每连接一进程)
后台辅助:checkpointer / bgwriter / walwriter / autovacuum launcher / stats
共享内存:shared_buffers / WAL buffers / lock table / proc array
本地内存:work_mem(排序哈希)/ maintenance_work_mem(维护操作)
05.3.1.1-001 PostgreSQL 的进程模型与 MySQL 有什么不同?
频率 ★★★★★ | 难度 进阶
一句话结论
PG 是多进程模型:postmaster 监听,为每个连接 fork 一个 backend 进程;MySQL 是进程内多线程模型。PG 连接是重资源,生产通常配合 PgBouncer 池化;共享内存由所有 backend 共享,排序哈希用本地 work_mem。
概念解释
- 多进程:每连接独立进程,隔离性好但进程开销大。
- 多线程(MySQL):单进程内线程复用,连接开销较小。
- 共享/本地内存:PG 数据缓冲走共享内存,会话私有排序内存独立分配。
原理流程
- 客户端连接到达 postmaster。
- postmaster fork 出 backend 进程处理该会话。
- 会话结束进程退出,资源回收。
代码 / 命令
SELECT pid, usename, state, backend_type
FROM pg_stat_activity;
常见误区
- 误区:PG 连接数多只是内存占用问题。正解:进程调度、上下文切换与本地 work_mem 都会随连接数放大。
- 误区:PG 也能像线程模型那样轻量复用连接。正解:进程复用需靠连接池中间件,而非数据库内部线程复用。
追问
- 问:为什么 PG 不适合大量短连接?答:fork 进程成本高,短连接反复建连开销大,连接池必不可少。
- 问:与 InnoDB 线程模型对照?答:MySQL 一个进程内多线程处理连接,见 05.2.1.1。
05.3.1.1-002 postmaster 与后台/工作进程分别做什么?
频率 ★★★★☆ | 难度 进阶
一句话结论
postmaster 是实例入口,监听连接、fork backend、管理后台进程;后台辅助进程负责检查点、脏页写出、WAL 写入、autovacuum 调度与统计收集;并行查询与逻辑复制另起 worker 进程。
概念解释
- postmaster:实例主进程,负责连接受理与进程生命周期。
- backend:服务单个客户端会话的工作进程。
- background worker:checkpointer、bgwriter、walwriter、autovacuum launcher、stats 等辅助进程。
原理流程
- postmaster 监听端口,新连接 fork backend。
- 辅助进程按各自职责周期运行。
- 专项任务(并行、复制、vacuum)按需启动 worker。
代码 / 命令
SELECT backend_type, count(*) FROM pg_stat_activity GROUP BY backend_type;
常见误区
- 误区:所有查询都由 postmaster 直接处理。正解:postmaster 只受理与管理,实际 SQL 由 backend 执行。
- 误区:后台进程数量固定不变。正解:worker 按负载与配置动态启停。
追问
- 问:checkpointer 与 bgwriter 分工?答:checkpointer 周期刷脏页并定重做起点,bgwriter 提前平滑写出脏页,见 05.3.1.5。
- 问:autovacuum launcher 与 worker 关系?答:launcher 调度,按需 fork worker 执行 vacuum,见 05.3.1.4。
05.3.1.1-003 shared_buffers 内存结构怎么理解?
频率 ★★★★☆ | 难度 进阶
一句话结论
shared_buffers 是 PG 共享内存中的数据页缓存,所有 backend 共享;存放堆表/索引页与相关状态,配合 OS page cache 形成两级缓存;排序哈希等会话私有工作区在本地 work_mem,不在共享缓冲池内。
概念解释
- 数据页缓存:热点表/索引页驻留共享内存。
- 两级缓存:shared_buffers 之上还有操作系统页缓存。
- 本地工作内存:work_mem 按会话/操作分配,超阈值落盘。
原理流程
- 读页先查 shared_buffers,未命中读 OS cache 再读磁盘。
- 脏页由后台进程逐步刷盘。
- 排序/哈希在本地 work_mem 完成,超限写临时文件。
代码 / 命令
SHOW shared_buffers;
SHOW work_mem;
SHOW maintenance_work_mem;
常见误区
- 误区:shared_buffers 设越大越好。正解:过大占用内存且回收成本高,通常占物理内存约四分之一,其余留给 OS cache,实测为准。
- 误区:work_mem 是全局共享池。正解:它按会话/单个排序哈希操作分配,并发高时总量需折算。
追问
- 问:与 MySQL Buffer Pool 对照?答:都是数据页缓存,PG 额外依赖 OS page cache,见 05.2.1.2。
- 问:work_mem 超阈值会怎样?答:排序/哈希落临时磁盘文件,性能下降,见 05.3.2.3。
05.3.1.1-004 配置文件参数如何分级生效?
频率 ★★★☆☆ | 难度 进阶
一句话结论
PG 参数分文件默认值、实例配置文件、数据库/角色/会话级覆盖;部分参数改后 reload 即可生效,部分需重启;会话级 SET 只影响当前会话,优先级高于库级配置。
概念解释
- 配置层级:内置默认 → postgresql.conf → 库/角色级 → 会话级。
- 生效方式:SIGHUP 可 reload;POSTMASTER 类需重启。
- 会话覆盖:
SET或连接参数临时调整。
原理流程
- 启动读配置文件形成实例参数。
- 连接时按库/角色匹配覆盖。
- 会话内 SET 临时修改,断开失效。
代码 / 命令
SHOW config_file;
SELECT name, context FROM pg_settings WHERE name='work_mem';
ALTER DATABASE appdb SET work_mem = '16MB';
SET work_mem = '8MB';
常见误区
- 误区:改完配置文件都要重启。正解:多数参数 reload 生效,context 字段标明生效级别。
- 误区:会话 SET 会永久改变全局配置。正解:只影响当前会话,断开即恢复。
追问
- 问:如何判断参数是否需重启?答:查
pg_settings.context,postmaster类需重启,sighup类 reload。 - 问:reload 命令是什么?答:
SELECT pg_reload_conf();或发送 SIGHUP。
05.3.1.1-005 库、表空间、schema 是什么层级关系?
频率 ★★★☆☆ | 难度 进阶
一句话结论
实例下有多个数据库;数据库内按 schema 组织表/对象;表空间是物理存储位置映射。一个连接只连一个数据库,跨库访问需外部外表或联邦方案(能力以版本扩展为准)。
概念解释
- 数据库(database):独立数据目录与权限边界。
- schema:库内对象命名空间,默认 public。
- 表空间(tablespace):指定对象落盘目录。
原理流程
- 实例初始化多个数据库。
- 连接选定数据库,在其 schema 下建表。
- 表/索引可指定表空间落不同磁盘。
代码 / 命令
\dn -- 列出 schema
SELECT datname FROM pg_database;
SELECT spcname FROM pg_tablespace;
常见误区
- 误区:PG 一个连接可以直接 JOIN 另一个库的表。正解:跨库需 postgres_fdw 等外部封装,非同库直接关联。
- 误区:schema 等于数据库。正解:schema 是库内命名空间,权限与对象隔离粒度不同。
追问
- 问:search_path 起什么作用?答:决定未加 schema 前缀时对象的查找顺序。
- 问:多租户常用什么隔离方式?答:独立 schema 或独立库,按隔离强度与维护成本选择。
05.3.1.1-006 PgBouncer 有哪些连接池模式?
频率 ★★★★★ | 难度 进阶
一句话结论
PgBouncer 分 session / transaction / statement 三种模式:session 绑定会话复用差;transaction 事务结束即归还,大多数应用推荐;statement 每条语句归还,限制多。transaction 模式下会话级状态(临时表、SET、预编译语句)不能跨事务保持。
概念解释
- session 模式:后端连接绑定客户端会话,断开才归还。
- transaction 模式:事务提交/回滚后连接归还复用。
- statement 模式:每条语句结束归还,兼容性最差。
原理流程
- 应用连到 PgBouncer,不直连后端。
- 按池模式把后端连接分配给客户端。
- 事务/语句结束按规则归还并重置会话状态。
代码 / 命令
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
常见误区
- 误区:transaction 模式下 SET 配置能跨请求保持。正解:连接归还后被重置,会话级设置需在事务内重新建立。
- 误区:池化后 advisory lock 可跨事务持有。正解:连接复用会重置状态,跨事务锁需在同一后端连接会话内完成。
追问
- 问:连接池大小怎么定?答:按 CPU 核数与压测并发确定,并非越大越好,见 05.3.2.5。
- 问:临时表在 transaction 模式下有什么坑?答:临时表绑定会话,连接归还后可能不可见,需事务内创建使用。
05.3.1.1-007 连接耗尽与 backlog 如何治理?
频率 ★★★★☆ | 难度 高级
一句话结论
连接数超 max_connections 后新连接被拒;先排查会话泄漏、长事务与连接池配置,再通过 PgBouncer 收敛后端连接、限制连接来源、按负载扩容;backlog 受监听队列与连接速率约束。
概念解释
- max_connections:后端进程连接上限。
- 连接泄漏:应用未释放连接,会话长期挂起。
- 连接池收敛:大量客户端经池化复用少量后端连接。
原理流程
- 查
pg_stat_activity按状态/来源统计连接。 - 识别空闲泄漏、长事务与突发连接。
- 池化收敛、修复应用释放逻辑、调整上限与 backlog。
代码 / 命令
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
SHOW max_connections;
常见误区
- 误区:连接耗尽就调大 max_connections。正解:进程模型下连接越多内存与调度开销越大,应先池化与查泄漏。
- 误区:idle 连接不占资源。正解:空闲进程仍占后端进程与本地内存,长 idle 连接需池化回收。
追问
- 问:idle in transaction 连接有什么危害?答:持快照阻碍 vacuum/freeze,见 05.3.1.4。
- 问:如何限制来源连接?答:按用户/库设置连接数上限与 pg_hba 规则。
05.3.1.1-008 reload 与重启有什么区别?
频率 ★★☆☆☆ | 难度 基础
一句话结论
reload 向实例发送 SIGHUP 重新加载配置,生效范围限于 sighup 级参数,不中断连接;重启需停止再启动实例,postmaster 级参数才生效,期间业务不可用。
概念解释
- SIGHUP 重载:后台进程重读配置,现有连接不中断。
- 重启:终止所有后端进程,重新初始化实例。
- 参数 context:标明改动需要哪种生效方式。
原理流程
- 改配置后查参数 context。
- sighup 类执行 reload 生效。
- postmaster 类安排低峰重启。
代码 / 命令
SELECT pg_reload_conf();
SELECT name, context FROM pg_settings WHERE name='shared_buffers';
常见误区
- 误区:reload 能改内存类核心参数。正解:shared_buffers 等启动期分配的参数需重启。
- 误区:重启会丢数据。正解:正常重启靠 WAL 恢复已提交数据,未提交事务回滚。
追问
- 问:哪些典型参数需重启?答:shared_buffers、max_connections、端口/监听等启动期配置。
- 问:reload 会中断活动事务吗?答:不会,现有会话继续运行,新参数按规则生效。
05.3.1.1-009 日志收集相关参数有哪些?
频率 ★★☆☆☆ | 难度 进阶
一句话结论
日志配置控制日志输出目的地、级别、慢查询阈值与语句参数记录;排障依赖日志定位慢 SQL、错误与连接问题,生产应开启合理级别并落盘集中采集。
概念解释
- 日志目的地:文件、stderr 或外部收集。
- 慢查询日志:超过阈值的语句记录。
- 语句参数:是否记录绑定参数值,便于复现。
原理流程
- 配置日志级别与慢查询阈值。
- 语句执行按规则写日志。
- 集中采集用于排查与基线分析。
代码 / 命令
SHOW log_min_duration_statement;
SHOW log_statement;
常见误区
- 误区:日志级别越高越好。正解:详细日志增加 IO 与存储成本,按排障需要分级。
- 误区:慢日志记录执行后才产生。正解:超阈值语句完成后记录,运行中的长语句需看活动会话。
追问
- 问:慢查询与活动会话怎么配合看?答:日志看历史完成语句,
pg_stat_activity看当前运行语句。 - 问:记录参数有什么隐私风险?答:敏感参数可能落日志,需按合规要求脱敏。
05.3.1.1-010 编码与区域(LC_COLLATE/LC_CTYPE)有什么影响?
频率 ★★☆☆☆ | 难度 进阶
一句话结论
数据库初始化时确定编码与排序/字符分类区域;排序规则影响字符串比较与索引顺序,区域一旦创建通常不可随意变更;跨库迁移需核对编码与区域一致,避免排序与字符处理异常。
概念解释
- 编码(encoding):字符存储编码,如 UTF8。
- LC_COLLATE:字符串排序比较规则。
- LC_CTYPE:字符分类(大小写、字母判断)。
原理流程
- 初始化数据库指定编码与区域。
- 索引按该区域排序构建。
- 变更区域需重建库/对象,不能直接改参数。
代码 / 命令
SELECT datname, pg_encoding_to_char(encoding), datcollate, datctype
FROM pg_database;
常见误区
- 误区:建库后随时改排序规则。正解:区域绑定库/索引,修改需重建相关对象。
- 误区:编码不同也能无缝迁移。正解:编码转换可能丢字符,迁移前需核对字符集。
追问
- 问:ICU 排序规则是什么?答:可按 ICU 定义自定义排序,具体能力随版本以手册为准(待确认)。
- 问:区域不一致会导致什么问题?答:排序结果、索引匹配与大小写处理出现差异。
05.3.1.1-011 模板库 template1 / template0 有什么区别?
频率 ★★☆☆☆ | 难度 基础
一句话结论
新建数据库默认从 template1 复制,其上安装的扩展与自定义设置会带入新库;template0 是干净模板,定制 template1 后需要原始基线时从 template0 创建。
概念解释
- template1:默认模板,可被定制(扩展、默认配置)。
- template0:干净基线,不含定制对象。
- 创建新库:
CREATE DATABASE ... TEMPLATE ...指定模板。
原理流程
- 管理员在 template1 预装常用扩展。
- 新库默认继承这些对象与设置。
- 需要纯净环境时从 template0 建库。
代码 / 命令
CREATE DATABASE appdb TEMPLATE template0;
常见误区
- 误区:template0 也能直接装扩展。正解:template0 保持基线,定制应在 template1 或目标库完成。
- 误区:模板里的对象会随模板更新同步到旧库。正解:复制发生在建库时,旧库不受后续模板变更影响。
追问
- 问:预装扩展有什么好处?答:新库开箱即用,避免逐库 CREATE EXTENSION。
- 问:模板库被占用时能建库吗?答:模板有连接/锁时需先释放,具体报错以手册为准。
05.3.1.1-012 extension 机制是怎么工作的?
频率 ★★★☆☆ | 难度 进阶
一句话结论
扩展把函数、类型、索引方法与表对象打包,CREATE EXTENSION 安装到当前库;扩展文件在共享库目录,对象注册到当前库 schema,跨库需分别安装。
概念解释
- 扩展包:共享库 + SQL 脚本 + 控制文件。
- 库内注册:安装后对象属于当前库与指定 schema。
- 常用扩展:pg_stat_statements、pgstattuple、postgis 等。
原理流程
- 扩展文件安装到服务器目录。
- 目标库执行 CREATE EXTENSION 注册对象。
- 按版本依赖关系升级扩展。
代码 / 命令
CREATE EXTENSION pg_stat_statements;
SELECT extname, extversion FROM pg_extension;
常见误区
- 误区:安装一次扩展全实例可用。正解:对象注册在具体数据库,跨库需重复安装。
- 误区:扩展升级自动完成。正解:需按版本路径执行升级脚本,核对兼容性。
追问
- 问:扩展能跨版本兼容吗?答:随 PG 大版本升级需核对扩展支持矩阵(待确认)。
- 问:扩展安装在哪个 schema?答:默认 public,可指定 schema 集中管理。
05.3.1.1-013 PG 与 MySQL 的实例/库概念如何对照?
频率 ★★★☆☆ | 难度 进阶
一句话结论
PG 实例直接含多个独立数据库,连接固定一个库;MySQL 实例下多库共用同一数据目录与全局权限体系,可在会话内切换库。两者的库隔离粒度、跨库访问与备份单元不同。
概念解释
- PG 库:独立目录与边界,连接不跨库。
- MySQL 库:实例内命名空间,会话可
USE切换。 - 备份单元:PG 实例级基础备份覆盖所有库;MySQL 可单库导出。
原理流程
- 连接 PG 即绑定数据库。
- MySQL 连接可切换默认库。
- 架构设计按各自隔离与跨库能力选择。
代码 / 命令
-- PG
SELECT datname FROM pg_database;
-- MySQL
SHOW DATABASES;
常见误区
- 误区:PG 的 schema 对应 MySQL 的 database。正解:PG database 更接近 MySQL 实例内的库,schema 是库内命名空间。
- 误区:两边跨库 SQL 写法相同。正解:PG 跨库依赖外部表,MySQL 同实例可跨库前缀引用。
追问
- 问:多业务隔离选哪种?答:PG 可独立库隔离,MySQL 实例内多库隔离较弱,结合权限与备份评估。
- 问:实例级参数影响范围?答:PG 实例参数作用于所有库,MySQL 实例参数同样全局生效。
05.3.1.1-014 后台辅助进程各自职责与监控点?
频率 ★★★☆☆ | 难度 进阶
一句话结论
checkpointer 周期定检查点,bgwriter 平滑刷脏页,walwriter 提交 WAL,autovacuum launcher 调度回收,stats 收集统计;监控检查点频率、脏页写出与 vacuum 运行状态判断后台负载。
概念解释
- checkpointer:触发检查点、刷脏页并记录重做点。
- bgwriter:提前后台写出脏页,减少突发刷盘。
- walwriter:把 WAL buffer 刷到 WAL 文件。
- autovacuum launcher:调度 vacuum worker。
原理流程
- 各后台进程按参数周期运行。
- 脏页/WAL/死元组由对应进程处理。
- 监控视图观察运行次数与积压。
代码 / 命令
SELECT * FROM pg_stat_bgwriter;
SELECT count(*) FROM pg_stat_activity WHERE backend_type LIKE 'autovacuum%';
常见误区
- 误区:后台进程闲置说明无负载。正解:需结合脏页、WAL 积压与死元组趋势判断。
- 误区:bgwriter 替代 checkpointer。正解:前者平滑预写,后者负责完整检查点。
追问
- 问:checkpoint 频繁说明什么?答:WAL 容量配置过小或写入量大,见 05.3.1.5。
- 问:autovacuum worker 看不到怎么办?答:无待回收表时 launcher 空闲属正常。
05.3.1.1-015 会话状态与临时表生命周期?
频率 ★★☆☆☆ | 难度 进阶
一句话结论
会话内 SET、临时表、游标、预编译语句与 advisory lock 绑定当前 backend 连接;连接断开或归还池化后按规则重置,跨事务复用需在同一连接会话内重新建立。
概念解释
- 会话状态:配置、临时表、游标、锁等附着连接。
- 生命周期:随会话结束释放,池化归还时重置。
- 跨事务保持:依赖后端连接不被复用切换。
原理流程
- 会话建立后持有私有状态。
- 事务结束状态可保留至会话结束。
- 连接归还池化时执行重置清理。
代码 / 命令
CREATE TEMP TABLE tmp_session(id int);
SET statement_timeout = '5s';
常见误区
- 误区:临时表数据提交后永久存在。正解:临时表绑定会话,断开即删。
- 误区:池化 transaction 模式能保持上一事务的 SET。正解:连接归还重置,需事务内重设。
追问
- 问:advisory lock 跨事务持有的前提?答:持有同一后端连接会话,池化模式需评估。
- 问:预编译语句在池化下注意什么?答:连接复用可能失效,按池模式管理。
05.3.1.1-016 事务块与隐式提交规则?
频率 ★★☆☆☆ | 难度 进阶
一句话结论
显式 BEGIN...COMMIT 为事务块;未包裹的单条语句各自独立事务自动提交;会话内多条语句默认逐条提交,长事务需显式开启并尽快结束,避免持快照阻碍回收。
概念解释
- 隐式提交:每条语句单独成事务。
- 显式事务:BEGIN 开始,COMMIT/ROLLBACK 结束。
- 长事务风险:持快照与锁,延迟 vacuum 与 freeze。
原理流程
- 客户端发送语句,未在事务块内则自动提交。
- BEGIN 后多条语句归入同一事务。
- COMMIT 持久化,ROLLBACK 回滚。
代码 / 命令
BEGIN;
UPDATE account SET balance = balance - 10 WHERE id = 1;
COMMIT;
常见误区
- 误区:会话开着就算长事务。正解:事务状态以 BEGIN 未提交为准,idle 连接未开事务不持快照。
- 误区:自动提交下多条语句是一个事务。正解:每条独立提交,部分失败不回滚其他语句。
追问
- 问:
idle in transaction为什么危险?答:未提交事务持活跃快照,阻碍 vacuum 回收与 freeze,见 05.3.1.4。 - 问:DDL 会隐式提交吗?答:PG 事务内 DDL 随事务提交/回滚,具体行为以手册为准。
05.3.1.1-017 实例启停与数据目录结构?
频率 ★★☆☆☆ | 难度 基础
一句话结论
数据目录(PGDATA)存放配置、基表、WAL 与各库文件;实例由 postmaster 启动监听端口,停止走 fast/smart 等模式;直接拷贝运行中目录不一致,备份需专用工具。
概念解释
- PGDATA:实例根目录,含 base、pg_wal、global、配置文件。
- 启动:postmaster 加载配置、恢复到一致状态后接受连接。
- 停止模式:smart 等会话结束,fast 回滚活动事务后关闭。
原理流程
- 启动读配置与控制文件,WAL 崩溃恢复。
- 后台进程启动,监听连接。
- 关闭按模式等待或中断会话。
代码 / 命令
SHOW data_directory;
SHOW config_file;
常见误区
- 误区:直接复制数据目录就能迁移。正解:运行中拷贝文件不一致,需 pg_basebackup 或停库一致性拷贝。
- 误区:WAL 在 base 目录里。正解:WAL 独立存 pg_wal,备份归档需覆盖。
追问
- 问:全局对象存哪?答:global 目录存共享系统表,各库数据在 base 下对应 OID 目录。
- 问:配置文件可以放在数据目录外吗?答:可以通过参数指定,路径组织按部署规范。
05.3.1.1-018 容器化部署的数据目录与信号注意什么?
频率 ★★☆☆☆ | 难度 进阶
一句话结论
容器化需把 PGDATA 挂载持久卷,避免容器重建丢数据;实例由容器主进程管理,停止/重启信号映射到 postmaster,健康检查区分启动中与可接受连接;配置与 WAL 归档路径落到持久化存储。
概念解释
- 持久卷:数据目录、WAL 归档与备份不能放容器可写层。
- 信号管理:优雅停止需正确转发 SIGTERM 到主进程。
- 健康检查:实例恢复阶段与就绪状态分开判断。
原理流程
- 镜像内初始化数据目录到挂载卷。
- 主进程以 postmaster 前台运行。
- 停止时转发信号,按关闭模式退出。
代码 / 命令
部署要点:PGDATA 持久卷 / WAL 归档外置 / 信号转发 / 就绪探针
常见误区
- 误区:容器重启后数据还在容器层。正解:非挂载可写层随容器删除丢失。
- 误区:进程在启动中就接流量。正解:崩溃恢复未完成前不应接入读写流量。
追问
- 问:WAL 归档在容器里怎么落?答:归档命令写外置存储/对象存储路径,容器内不长期堆积。
- 问:多容器共享数据目录可行吗?答:不可行,单实例独占数据目录,高可用走复制而非共享盘。