记一次PostgreSQL交叉表crosstab行转列导致的OOM

博客园 - MSSQL123

timescaledb 扩展 PostgreSQL 18+ 版本下源码编译安装 ubuntu24下 ext4和xfs类型磁盘以及PostgreSQL实例性能对比测试 PostgreSQL 18 新特性 skip scan，一个鸡肋的功能 PostgreSQL 17 流复制中开启逻辑复制槽同步，以及逻辑槽的故障转移 TiDB 扩容与缩容 PostgreSQL 高可用集群 patroni 自动故障转移测试 Ubuntu 20 环境下 patroni 自动化安装，一分钟快速搭建 patroni 集群 Ubuntu 20 环境下 pg_auto_failover 自动化安装，一分钟快速搭建pg_auto_failover集群 PostgreSQL的消息队列扩展pgmq 磁盘IO延迟和队列深度的关系 TiDB 最小拓扑架构集群安装 PostgreSQL 逻辑复制中的同步和异步模式以及其表现 prometheus 的 altermanager Silences静默告警，优雅地抑制告警 “世界上百分之99的博士和他们的论文都是垃圾”，原本以为这个观点偏激 pg_auto_failover 在多种场景下自动故障转移的验证 pgbouncer连接池设置与压力测试的最大连接数测试 pg_auto_failover 自动故障转移参数博文阅读密码验证 - 博客园 pg_auto_failover 高可用中，PostgreSQL实例配置文件的加载步骤 pg_auto_failover集群monitor节点的高可用 prometheus监控Linux Server node_exporter代理安装和配置 prometheus监控windows window_exporter代理安装和配置 Prometheus 和 Grafana 监控 PostgreSQL 记一次MySQL binlog日志导致磁盘空间占满的问题 SQLServer 2019 标准版在虚拟机上无法充分利用CPU的问题诊断 Windows Failover Cluster集群中的EventId 1196错误日志 pg_auto_failover 环境变量导致的show命令错误 SqlServer 事务复制（transaction replication）的复制位点信息 SqlServer 事务复制的两个参数immediate_sync，allow_anonymous MySQL，SqlServer，PostgreSQL中，如何实现锁定一张表

MSSQL123 · 2026-05-27 · via 博客园 - MSSQL123

0，环境

Ubuntu 20，PostgreSQL 17.6

1，场景

源数据格式

id|business_id|key|val      |created_at                      |
--+-----------+---+---------+--------------------------------+
 1|          1|aaa|100.00000|2026-03-28 18:20:50.553354 +0800|
 2|          1|bbb|200.00000|2026-03-28 18:20:50.553354 +0800|
 3|          1|ccc|300.00000|2026-03-28 18:20:50.553354 +0800|

目标数据格式

business_id|aaa      |bbb      |ccc      |
-----------+---------+---------+---------+
          1|100.00000|200.00000|300.00000|

源表是典型的行标，每一个逻辑id对应多个类似于key-value键值对的数据行，目标是把多行key-value的转换为列显示，这里简化一下逻辑，大概如下

CREATE TABLE my_test (
    id int generated always as identity primary key,
    business_id int,
    key varchar(100),
    val decimal(18,5),
    created_at timestamptz
);

-- 生成1000万条数据，每个 business_id 有3条记录（aaa, bbb, ccc）
-- 总 business_id 数量 = 10,000,000 / 3 = 3,333,333 个
INSERT INTO my_test (business_id, key, val, created_at)
SELECT 
    seq as business_id,
    key,
    val,
    NOW() - interval '60 days' + (seq * interval '0.0005184 second') as created_at
FROM 
    generate_series(1, 3333333) AS seq
    CROSS JOIN LATERAL (
        VALUES 
            ('aaa', 100.00),
            ('bbb', 200.00),
            ('ccc', 300.00)
    ) AS k(key, val)
ORDER BY business_id, key;


--示例数据如下
id|business_id|key|val      |created_at                      |
--+-----------+---+---------+--------------------------------+
 1|          1|aaa|100.00000|2026-03-28 18:20:50.553354 +0800|
 2|          1|bbb|200.00000|2026-03-28 18:20:50.553354 +0800|
 3|          1|ccc|300.00000|2026-03-28 18:20:50.553354 +0800|
 4|          2|aaa|100.00000|2026-03-28 18:20:50.553872 +0800|
 5|          2|bbb|200.00000|2026-03-28 18:20:50.553872 +0800|
 6|          2|ccc|300.00000|2026-03-28 18:20:50.553872 +0800|
 7|          3|aaa|100.00000|2026-03-28 18:20:50.554390 +0800|
 8|          3|bbb|200.00000|2026-03-28 18:20:50.554390 +0800|
 9|          3|ccc|300.00000|2026-03-28 18:20:50.554390 +0800|
10|          4|aaa|100.00000|2026-03-28 18:20:50.554908 +0800|

2，crosstab行转列遭遇OOM

通过crosstab可以完美实现行列转换

CREATE EXTENSION IF NOT EXISTS tablefunc;

create index idx_business_id on my_test(business_id);

--利用crosstab行转列，完全没有问题
SELECT *
FROM crosstab
	(
	    'SELECT business_id, key, val 
	     FROM my_test  where business_id = 888888
	     ORDER BY business_id, key',
	    'VALUES (''aaa''), (''bbb''), (''ccc'')'
	) AS ct(business_id INT, aaa DECIMAL(18,5), bbb DECIMAL(18,5), ccc DECIMAL(18,5))

 
--行转列的结果如下，完全没有问题，因为这样完全可以用到索引
business_id|aaa      |bbb      |ccc      |
-----------+---------+---------+---------+
     888888|100.00000|200.00000|300.00000|

想尝试屏蔽crosstab的复杂语法，将上述sql语句封装成一个视图，然后通过视图查询，类似如：select * from v_my_test where business_id = 888888;
原本想着where条件可以“下推”到crosstab内部，与原始查询一样，也能用到索引，于是对crosstab的查询，去掉where条件之后，将where条件放到外面，如果可行的话就可以用视图了屏幕crosstab的复杂的语法了

--想尝试屏蔽crosstab的复杂语法，将上述sql语句封装成一个视图
create view v_my_test as
select * from
(
	SELECT *
	FROM crosstab
		(
		    'SELECT business_id, key, val 
		     FROM my_test  --where business_id = 888888
		     ORDER BY business_id, key',
		    'VALUES (''aaa''), (''bbb''), (''ccc'')'
		) AS ct(business_id INT, aaa DECIMAL(18,5), bbb DECIMAL(18,5), ccc DECIMAL(18,5))	
)t

--然后通过视图查询
select * from v_my_test where business_id = 888888

--上述通过视图的查询，等价于如下SQL，也即crosstab内部去掉where条件，将where条件放到外面，原本想着where条件可以“下推”到crosstab，也能用到索引
select * from
(
	SELECT *
	FROM crosstab
		(
		    'SELECT business_id, key, val 
		     FROM my_test  --where business_id = 888888
		     ORDER BY business_id, key',
		    'VALUES (''aaa''), (''bbb''), (''ccc'')'
		) AS ct(business_id INT, aaa DECIMAL(18,5), bbb DECIMAL(18,5), ccc DECIMAL(18,5))	
)t
where business_id = 888888

然后就遇到OOM了

2026-05-27 11:13:49.690 CST [437019] LOG:  checkpoint starting: wal
2026-05-27 11:17:10.218 CST [437017] LOG:  server process (PID 1308788) was terminated by signal 9: Killed
2026-05-27 11:17:10.218 CST [437017] DETAIL:  Failed process was running: select * from
select * from
(
	SELECT *
	FROM crosstab
		(
		    'SELECT business_id, key, val 
		     FROM my_test  --where business_id = 888888
		     ORDER BY business_id, key',
		    'VALUES (''aaa''), (''bbb''), (''ccc'')'
		) AS ct(business_id INT, aaa DECIMAL(18,5), bbb DECIMAL(18,5), ccc DECIMAL(18,5))	
)t
where business_id = 888888
	
2026-05-27 11:17:10.219 CST [437017] LOG:  terminating any other active server processes
2026-05-27 11:17:10.247 CST [1311860] FATAL:  the database system is in recovery mode
2026-05-27 11:17:10.293 CST [1311869] FATAL:  the database system is in recovery mode
2026-05-27 11:17:10.409 CST [437017] LOG:  all server processes terminated; reinitializing
2026-05-27 11:17:10.582 CST [1311898] LOG:  database system was interrupted; last known up at 2026-05-27 11:09:27 CST
2026-05-27 11:17:10.600 CST [1311901] FATAL:  the database system is in recovery mode
2026-05-27 11:17:10.645 CST [1311902] FATAL:  the database system is in recovery mode
2026-05-27 11:17:10.688 CST [1311898] LOG:  database system was not properly shut down; automatic recovery in progress
2026-05-27 11:17:10.692 CST [1311903] FATAL:  the database system is in recovery mode
2026-05-27 11:17:10.716 CST [1311898] LOG:  redo starts at 0/658B1820
2026-05-27 11:17:10.782 CST [1311904] FATAL:  the database system is not yet accepting connections
2026-05-27 11:17:10.782 CST [1311904] DETAIL:  Consistent recovery state has not been yet reached.
2026-05-27 11:17:20.716 CST [1311898] LOG:  redo in progress, elapsed time: 10.00 s, current LSN: 0/931FA9C0
2026-05-27 11:17:21.552 CST [1311898] LOG:  invalid record length at 0/98B0E848: expected at least 24, got 0
2026-05-27 11:17:21.555 CST [1311898] LOG:  redo done at 0/98B0E810 system usage: CPU: user: 5.93 s, system: 1.30 s, elapsed: 10.85 s
2026-05-27 11:17:21.591 CST [1311899] LOG:  checkpoint starting: end-of-recovery immediate wait
2026-05-27 11:17:22.591 CST [1311899] LOG:  checkpoint complete: wrote 50482 buffers (80.2%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.941 s, sync=0.044 s, total=1.003 s; sync files=8, longest=0.044 s, average=0.006 s; distance=838004 kB, estimate=838004 kB; lsn=0/98B0E848, redo lsn=0/98B0E848
2026-05-27 11:17:22.619 CST [437017] LOG:  database system is ready to accept connections

3，解决方案

1，废弃视图的想法，直接使用sql语句查询，虽然增加了语法的复杂程度，但至少避免了OOM

2，PostgreSQL的systemctl service文件中，限制单个回话最大使用内存，防止单个查询失控，参考：PostgreSQL 被 OOM 杀库？最佳实践有了！
# 限制单个进程虚拟内存为 2GB（单位：字节，也可直接写 2G）
LimitAS=2147483648

此时如果SQL语句受限制之后，session执行失败，会出现如下错误，而不至于导致整个PostgreSQL实例OOM

SQL Error [53200]: ERROR: out of memory
Detail: Failed on request of size 67108864 in memory context "SPI TupTable".
Where: SQL statement "****"

此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。原文来自 — 版权归原作者所有。

推荐订阅源

博客园 - MSSQL123

0，环境

1，场景

2，crosstab行转列遭遇OOM

3，解决方案