PostgreSQL 内存管理

内存管理是 PostgreSQL 数据库性能优化的核心组成部分，合理的内存配置可以显著提高数据库的性能和稳定性。本文档详细介绍了 PostgreSQL 的内存管理机制、核心参数配置和优化策略。

内存架构概述

1. 内存层次结构

PostgreSQL 的内存管理分为多个层次，从操作系统层面到数据库内部层面，形成了一个完整的内存管理体系：

操作系统内存：包括系统缓冲区、页缓存等
PostgreSQL 实例内存：PostgreSQL 数据库实例使用的内存
数据库会话内存：每个数据库连接使用的内存
查询执行内存：每个查询执行过程中使用的内存

2. 内存分配机制

PostgreSQL 采用了以下内存分配机制：

共享内存：所有连接共享的内存区域，用于存储数据缓冲区、WAL 缓冲区等
私有内存：每个连接私有的内存区域，用于存储会话状态、查询执行上下文等
动态内存：在查询执行过程中动态分配的内存，用于存储中间结果、排序缓冲区等

3. 内存管理组件

PostgreSQL 的内存管理主要由以下组件组成：

共享缓冲区：存储数据库页面的缓存，减少磁盘 I/O
WAL 缓冲区：存储 WAL 日志的缓冲区，提高写入性能
工作内存：用于排序、哈希等操作的内存
维护工作内存：用于 VACUUM、CREATE INDEX 等维护操作的内存
临时缓冲区：用于临时表的内存
本地内存：每个连接使用的本地内存

核心内存参数配置

1. 共享内存参数

1.1 shared_buffers

描述：数据库共享缓冲区大小，用于缓存数据页面

推荐值：

对于 1GB-16GB 内存的服务器：内存的 25%
对于 16GB 以上内存的服务器：内存的 25%-40%，但不超过 16GB

配置示例：

sql

ALTER SYSTEM SET shared_buffers = '4GB';
SELECT pg_reload_conf();

1.2 wal_buffers

描述：WAL 日志缓冲区大小

推荐值：

自动调整（默认值）：-1，表示由 PostgreSQL 自动调整，通常为 shared_buffers 的 3% 或 64KB，取较大值
手动设置：对于高写入负载的系统，可以设置为 16MB-64MB

配置示例：

sql

ALTER SYSTEM SET wal_buffers = '16MB';
SELECT pg_reload_conf();

1.3 max_connections

描述：最大客户端连接数

推荐值：

对于 OLTP 系统：根据服务器资源和连接池配置调整，通常 100-500
对于 OLAP 系统：50-200

注意：max_connections 会影响其他内存参数的配置，因为每个连接都会占用一定的内存

配置示例：

sql

ALTER SYSTEM SET max_connections = '200';
-- 需要重启 PostgreSQL 服务才能生效

2. 会话内存参数

2.1 work_mem

描述：每个查询执行操作（如排序、哈希）使用的内存大小

推荐值：

对于 OLTP 系统：4MB-16MB
对于 OLAP 系统：16MB-64MB

注意：一个查询可能会使用多个 work_mem 大小的内存块，因此实际使用的内存可能会超过 work_mem 的设置

配置示例：

sql

ALTER SYSTEM SET work_mem = '8MB';
SELECT pg_reload_conf();

2.2 maintenance_work_mem

描述：用于维护操作（如 VACUUM、CREATE INDEX、ALTER TABLE）的内存大小

推荐值：

一般设置为 1GB-4GB
不超过系统内存的 25%

配置示例：

sql

ALTER SYSTEM SET maintenance_work_mem = '2GB';
SELECT pg_reload_conf();

2.3 temp_buffers

描述：每个会话用于临时表的内存大小

推荐值：

一般设置为 8MB-64MB
对于频繁使用临时表的场景，可以适当增大

配置示例：

sql

ALTER SYSTEM SET temp_buffers = '16MB';
SELECT pg_reload_conf();

3. 其他内存参数

3.1 effective_cache_size

描述：PostgreSQL 估计的可用系统缓存大小，用于查询优化器

推荐值：

一般设置为系统内存的 50%-75%
不包括 shared_buffers 的大小

配置示例：

sql

ALTER SYSTEM SET effective_cache_size = '8GB';
SELECT pg_reload_conf();

3.2 max_stack_depth

描述：每个服务器进程的最大堆栈深度

推荐值：

默认值为 2MB，通常不需要修改
对于复杂查询或递归函数，可以适当增大，但不超过操作系统限制

配置示例：

sql

ALTER SYSTEM SET max_stack_depth = '4MB';
SELECT pg_reload_conf();

3.3 shared_memory_type

描述：共享内存类型

推荐值：

对于 Linux 系统：mmap 或 sysv，取决于内核版本
对于 Windows 系统：windows

配置示例：

sql

ALTER SYSTEM SET shared_memory_type = 'mmap';
-- 需要重启 PostgreSQL 服务才能生效

内存优化策略

1. 基于工作负载的内存优化

1.1 OLTP 系统内存优化

shared_buffers：设置为内存的 25%-30%
work_mem：设置为 4MB-16MB
maintenance_work_mem：设置为 1GB-2GB
effective_cache_size：设置为内存的 50%-60%
max_connections：设置为 100-500

1.2 OLAP 系统内存优化

shared_buffers：设置为内存的 30%-40%
work_mem：设置为 16MB-64MB
maintenance_work_mem：设置为 2GB-4GB
effective_cache_size：设置为内存的 60%-75%
max_connections：设置为 50-200

1.3 混合工作负载内存优化

根据实际工作负载调整内存参数
考虑使用资源组（PostgreSQL 10+）来管理不同类型查询的内存使用
监控系统内存使用情况，及时调整参数

2. 内存使用优化技巧

2.1 合理设置 work_mem

work_mem 不是越大越好，过大的 work_mem 可能导致系统内存不足
对于复杂查询，可以临时增大 work_mem

sql

-- 临时增大 work_mem 用于特定查询
SET work_mem = '32MB';
-- 执行复杂查询
SELECT * FROM large_table ORDER BY complex_column;
-- 恢复默认值
RESET work_mem;

2.2 优化维护操作内存使用

对于大型表的 VACUUM 或 CREATE INDEX 操作，可以临时增大 maintenance_work_mem

sql

-- 临时增大 maintenance_work_mem 用于 VACUUM 操作
SET maintenance_work_mem = '4GB';
-- 执行 VACUUM 操作
VACUUM FULL large_table;
-- 恢复默认值
RESET maintenance_work_mem;

2.3 优化临时表内存使用

对于频繁使用临时表的场景，可以适当增大 temp_buffers
考虑将临时表空间存储在高性能存储上

2.4 避免内存泄漏

及时关闭不再使用的数据库连接
避免长时间运行的查询
定期监控数据库进程的内存使用情况

内存监控与管理

1. 内存使用监控

1.1 查看共享内存使用情况

sql

-- 查看共享内存使用情况
SELECT name, setting, unit, short_desc
FROM pg_settings
WHERE category = 'Resource Usage / Memory' AND name IN ('shared_buffers', 'wal_buffers', 'max_connections');

1.2 查看会话内存使用情况

sql

-- 查看当前会话的内存使用情况
SELECT pid, usename, datname, query, 
       pg_total_relation_size(relid) AS relation_size
FROM pg_stat_activity
JOIN pg_stat_user_tables ON pg_stat_activity.query ~ pg_stat_user_tables.relname
WHERE state = 'active';

1.3 查看系统内存使用情况

bash

# 使用 free 命令查看系统内存使用情况
free -h

# 使用 top 命令查看进程内存使用情况
top -p $(pgrep -d ',' postgres)

2. 内存使用统计

2.1 pg_stat_bgwriter

描述：后台写入器统计信息，包括共享缓冲区的使用情况

sql

-- 查看后台写入器统计信息
SELECT * FROM pg_stat_bgwriter;

2.2 pg_stat_progress_vacuum

描述：VACUUM 操作的进度信息，包括内存使用情况

sql

-- 查看 VACUUM 操作的进度信息
SELECT * FROM pg_stat_progress_vacuum;

2.3 pg_stat_progress_create_index

描述：CREATE INDEX 操作的进度信息，包括内存使用情况

sql

-- 查看 CREATE INDEX 操作的进度信息
SELECT * FROM pg_stat_progress_create_index;

3. 内存监控工具

3.1 Prometheus + Grafana

配置示例：

使用 node_exporter 监控系统内存使用情况
使用 postgres_exporter 监控 PostgreSQL 内存使用情况
在 Grafana 中创建内存监控仪表盘

3.2 pg_top

描述：PostgreSQL 专用的 top 工具，用于监控 PostgreSQL 进程的资源使用情况

使用示例：

bash

pg_top -h localhost -U postgres

3.3 pgbadger

描述：PostgreSQL 日志分析工具，可以生成内存使用情况的报告

使用示例：

bash

pgbadger -o memory_report.html postgresql.log

版本差异注意事项

版本	差异说明
PostgreSQL 9.x	内存管理功能相对简单，缺少一些高级特性
PostgreSQL 10+	引入了资源组功能，可以更好地管理内存资源
PostgreSQL 12+	改进了内存管理算法，提高了内存使用效率
PostgreSQL 13+	增强了内存统计功能，提供了更多的内存使用指标
PostgreSQL 14+	改进了共享内存管理，支持更大的共享内存
PostgreSQL 15+	增强了内存监控功能，提供了更多的内存使用视图
PostgreSQL 16+	改进了内存分配算法，提高了内存分配效率

内存管理最佳实践

1. 合理规划内存分配

提前规划：在数据库设计阶段就考虑内存规划
按功能分配：根据不同的功能分配不同的内存
考虑未来扩展：预留足够的内存用于未来的业务增长

2. 监控内存使用情况

实时监控：实时监控系统和数据库的内存使用情况
定期分析：定期分析内存使用趋势，预测未来内存需求
设置告警：当内存使用率超过阈值时，及时发出告警

3. 优化查询内存使用

优化查询：减少查询的内存使用
使用合适的索引：减少排序和哈希操作的内存使用
避免复杂查询：尽量避免使用过于复杂的查询
使用物化视图：对于频繁执行的复杂查询，考虑使用物化视图

4. 定期维护

定期 VACUUM：清理无效数据，减少内存使用
定期 ANALYZE：更新统计信息，帮助查询优化器选择更好的执行计划
定期重启：对于长时间运行的数据库实例，定期重启可以释放碎片化的内存

5. 考虑高可用性

配置合理的内存参数：确保数据库在故障情况下能够正常运行
考虑使用集群：使用 PostgreSQL 集群可以提高系统的可用性和内存使用效率
配置自动故障转移：确保在主库故障时，从库能够及时接管

内存管理案例分析

案例一：OLTP 系统内存优化

业务需求

高并发 OLTP 系统，峰值 QPS 达 5000
数据库服务器配置：16GB 内存，8 核 CPU
当前数据库响应时间较长，需要优化内存配置

问题分析

shared_buffers 配置过小（仅 1GB）
work_mem 配置过大（64MB）
系统内存使用率过高（95%）
频繁出现内存不足警告

优化方案

调整 shared_buffers：

sql

ALTER SYSTEM SET shared_buffers = '4GB';

调整 work_mem：
sql
```
ALTER SYSTEM SET work_mem = '8MB';
```

调整 maintenance_work_mem：

sql

ALTER SYSTEM SET maintenance_work_mem = '1GB';

调整 effective_cache_size：

sql

ALTER SYSTEM SET effective_cache_size = '8GB';

重启 PostgreSQL 服务：
bash
```
systemctl restart postgresql-15
```

优化效果

数据库响应时间从 500ms 降至 150ms
系统内存使用率从 95% 降至 70%
不再出现内存不足警告
查询性能提升明显

案例二：OLAP 系统内存优化

业务需求

大型 OLAP 系统，处理大量数据分析查询
数据库服务器配置：64GB 内存，16 核 CPU
当前复杂查询执行时间过长，需要优化内存配置

问题分析

work_mem 配置过小（仅 8MB）
maintenance_work_mem 配置过小（仅 512MB）
复杂查询需要大量排序和哈希操作
系统内存使用率较低（仅 40%）

优化方案

调整 shared_buffers：

sql

ALTER SYSTEM SET shared_buffers = '16GB';

调整 work_mem：
sql
```
ALTER SYSTEM SET work_mem = '32MB';
```

调整 maintenance_work_mem：

sql

ALTER SYSTEM SET maintenance_work_mem = '4GB';

调整 effective_cache_size：

sql

ALTER SYSTEM SET effective_cache_size = '40GB';

重启 PostgreSQL 服务：
bash
```
systemctl restart postgresql-15
```

优化效果

复杂查询执行时间从 300s 降至 120s
系统内存使用率从 40% 升至 65%
查询并行度提高，资源利用率提升
数据分析效率明显提高

总结

在实际运维工作中，应根据业务需求和系统特点，合理配置内存参数，监控内存使用情况，优化查询内存使用，定期维护数据库，确保数据库系统的稳定运行和良好性能。

通过合理的内存管理，可以提高数据库的处理能力，减少磁盘 I/O，提高查询响应速度，为业务的持续发展提供有力支持。

PostgreSQL 内存管理 ​

内存架构概述 ​

1. 内存层次结构 ​

2. 内存分配机制 ​

3. 内存管理组件 ​

核心内存参数配置 ​

1. 共享内存参数 ​

1.1 shared_buffers ​

1.2 wal_buffers ​

1.3 max_connections ​

2. 会话内存参数 ​

2.1 work_mem ​

2.2 maintenance_work_mem ​

2.3 temp_buffers ​

3. 其他内存参数 ​

3.1 effective_cache_size ​

3.2 max_stack_depth ​

3.3 shared_memory_type ​

内存优化策略 ​

1. 基于工作负载的内存优化 ​

1.1 OLTP 系统内存优化 ​

1.2 OLAP 系统内存优化 ​

1.3 混合工作负载内存优化 ​

2. 内存使用优化技巧 ​

2.1 合理设置 work_mem ​

2.2 优化维护操作内存使用 ​

2.3 优化临时表内存使用 ​

2.4 避免内存泄漏 ​

内存监控与管理 ​

1. 内存使用监控 ​

1.1 查看共享内存使用情况 ​

1.2 查看会话内存使用情况 ​

1.3 查看系统内存使用情况 ​

2. 内存使用统计 ​

2.1 pg_stat_bgwriter ​

2.2 pg_stat_progress_vacuum ​

2.3 pg_stat_progress_create_index ​

3. 内存监控工具 ​

3.1 Prometheus + Grafana ​

3.2 pg_top ​

3.3 pgbadger ​

版本差异注意事项 ​

内存管理最佳实践 ​

1. 合理规划内存分配 ​

2. 监控内存使用情况 ​

3. 优化查询内存使用 ​

4. 定期维护 ​

5. 考虑高可用性 ​

内存管理案例分析 ​

案例一：OLTP 系统内存优化 ​

业务需求 ​

问题分析 ​

优化方案 ​

优化效果 ​

案例二：OLAP 系统内存优化 ​

业务需求 ​

问题分析 ​

优化方案 ​

优化效果 ​

总结 ​

PostgreSQL 内存管理

内存架构概述

1. 内存层次结构

2. 内存分配机制

3. 内存管理组件

核心内存参数配置

1. 共享内存参数

1.1 shared_buffers

1.2 wal_buffers

1.3 max_connections

2. 会话内存参数

2.1 work_mem

2.2 maintenance_work_mem

2.3 temp_buffers

3. 其他内存参数

3.1 effective_cache_size

3.2 max_stack_depth

3.3 shared_memory_type

内存优化策略

1. 基于工作负载的内存优化

1.1 OLTP 系统内存优化

1.2 OLAP 系统内存优化

1.3 混合工作负载内存优化

2. 内存使用优化技巧

2.1 合理设置 work_mem

2.2 优化维护操作内存使用

2.3 优化临时表内存使用

2.4 避免内存泄漏

内存监控与管理

1. 内存使用监控

1.1 查看共享内存使用情况

1.2 查看会话内存使用情况

1.3 查看系统内存使用情况

2. 内存使用统计

2.1 pg_stat_bgwriter

2.2 pg_stat_progress_vacuum

2.3 pg_stat_progress_create_index

3. 内存监控工具

3.1 Prometheus + Grafana

3.2 pg_top

3.3 pgbadger

版本差异注意事项

内存管理最佳实践

1. 合理规划内存分配

2. 监控内存使用情况

3. 优化查询内存使用

4. 定期维护

5. 考虑高可用性

内存管理案例分析

案例一：OLTP 系统内存优化

业务需求

问题分析

优化方案

优化效果

案例二：OLAP 系统内存优化

业务需求

问题分析

优化方案

优化效果

总结