记一次PostgreSQL交叉表crosstab行转列导致的OOM
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 "****"
浙公网安备 33010602011771号