如何修复PostgreSQL中bytea查询时的无效内存分配请求问题

问题描述 #

能够向bytea类型列插入大尺寸数据，但直接通过repeat函数生成相同尺寸的bytea并查询时，触发内存分配错误。

代码示例 #

INSERT INTO bytea_test(data) values (repeat('x', 1024 * 1024 * 1023)::bytea);
-- 执行成功

select repeat('x', 1024 * 1024 * 1023)::bytea;
-- 报错：invalid memory alloc request size 2145386499

运行环境 #

• 部署在K8s容器中
• PostgreSQL 12.5 (Debian 12.5-1.pgdg100+1) on x86_64-pc-linux-gnu，gcc (Debian 8.3.0-6) 8.3.0编译，64位

当前PostgreSQL配置 #

max_connections=100
shared_buffers=6GB
effective_cache_size=4GB
maintenance_work_mem=64MB
checkpoint_completion_target=0.5
wal_buffers=16MB
default_statistics_target=100
random_page_cost=4
effective_io_concurrency=1
work_mem=3GB
min_wal_size=80MB
max_wal_size=6GB
max_worker_processes=8
max_parallel_workers_per_gather=2

问题原因与解决方法 #

1. 核心原因 #

repeat函数生成bytea数据时，内部会申请双倍于最终数据大小的内存用于中间处理。你要生成1023MB的bytea，函数实际会请求约2046MB内存，而PostgreSQL默认的单内存分配上限为2GB（2147483647字节），接近这个阈值导致分配失败。而插入操作是直接写入数据块，不会经过repeat的双倍内存分配逻辑，因此可以成功。

2. 调整单内存分配上限 #

修改max_alloc_size参数提升单个内存分配的最大值：

• 在postgresql.conf中添加或修改：

  max_alloc_size = 4GB
  

• 重启PostgreSQL服务使配置生效。

3. 绕过repeat函数查询 #

直接查询表中已存储的bytea数据，避免重新生成：

SELECT data FROM bytea_test;

这种方式会直接从磁盘读取数据块，不会触发repeat的特殊内存分配逻辑，可正常返回结果。

4. 优化work_mem配置 #

当前work_mem设置为3GB，虽单个查询内存充足，但repeat的内存分配不受work_mem控制。过高的work_mem可能导致并发查询时内存耗尽，建议根据容器总内存调整，比如容器内存为8GB时，work_mem设为1GB左右更合理。

内容的提问来源于stack exchange，提问作者笹原康央