PostgreSQL BYTEA存储机制、实际大小查询及查询失败问题咨询

问题背景 #

执行以下SQL操作：

create table some_table (col1 bytea);
insert into some_table (col1) values (repeat('x', 1024 * 1024 * 1023)::bytea);
SELECT pg_size_pretty(pg_total_relation_size('some_table'));

结果显示表总大小为12 MB，但预期应为约1GB；执行select * from some_table;时出现错误：

SQL Error [XX000]: Error: invalid memory alloc request size 2145386499.

1. 表大小显示12MB的原因 #

PostgreSQL使用**TOAST（The Oversized-Attribute Storage Technique）**技术存储超过单页（默认8KB）的大字段：

• 大字段不会直接存在主表中，而是被移到关联的TOAST表中存储。
• TOAST默认会对数据进行压缩后存储，你插入的是高度重复的'x'字节序列，压缩率极高，原本1023MB的数据被压缩到极小的体积，加上主表的元数据和TOAST表的少量开销，总磁盘占用仅12MB左右。
• pg_total_relation_size()返回的是主表+关联TOAST表的总磁盘占用，所以显示的是压缩后的实际存储大小。

2. 查询数据实际大小的方法 #

分两种场景：

压缩后的磁盘占用大小 #

pg_total_relation_size('some_table')已经包含了主表和TOAST表的总磁盘占用，用pg_size_pretty()格式化后即可直观查看。如果想单独查看TOAST表的大小：

SELECT pg_size_pretty(pg_total_relation_size('pg_toast.pg_toast_' || (select reltoastrelid from pg_class where relname='some_table')));

原始未压缩的数据大小 #

使用octet_length()函数可以获取bytea字段的原始字节数：

SELECT octet_length(col1) FROM some_table;

这个查询会返回1072693248（即102410241023），对应约1GB的原始数据大小。

3. 查询报错原因与提前预判方法 #

报错原因 #

执行select *时，PostgreSQL默认会将bytea数据转换为十六进制字符串返回给客户端：每个原始字节需要2个十六进制字符表示，1GB的原始数据会生成约2GB的字符串，这超过了PostgreSQL单个内存分配的最大限制（默认max_alloc_size为2GB左右），因此触发内存分配错误。

提前预判方法 #

• 计算所需内存：先通过octet_length(col1)获取原始数据大小，字符串表示的内存需求为原始字节数 * 2 + 额外开销，将这个值与数据库的max_alloc_size对比：

  -- 查看max_alloc_size
  SHOW max_alloc_size;
  -- 计算字符串表示所需大小（字节）
  SELECT octet_length(col1) * 2 FROM some_table;
  

• 避免文本格式查询：如果需要获取大bytea数据，不要用文本格式的select *，改用二进制导出方式，比如：

  COPY some_table TO '/path/to/save/file' BINARY;
  

  或者在客户端使用二进制协议接收数据，避免字符串转换带来的内存开销。

内容的提问来源于stack exchange，提问作者Serji0