一条SQL引发的惨案，内存耗尽？

NickYoung

发布于 2026-07-09 16:32:21

又遇到一起值得给大家分享的案例，在PG14版本以下使用声明式分区表时，稍不小心就会将机器内存打爆，怎么回事呢？且看老杨分析。

问题现象

有图有真相，先看视频。

一个简单的delete语句60s将内存干到120GB，执行完成后内存正常释放，看起来并不像泄漏，更像是BUG？

问题分析

对于高内存问题，可以先打印MemoryContextStat，查看内存分布。

PG14及以上版本，可以直接调用函数SELECT pg_log_backend_memory_contexts(pid);

14以下版本，可以使用gdb去打印。 gdb --batch --pid 17185 --ex 'call MemoryContextStats(TopMemoryContext)'

可以看到主要是MessageContext占用了100多G。

TopMemoryContext: 26914664 total in 821 blocks; 5252280 free (34 chunks); 21662384 used
  pgstat TabStatusArray lookup hash table: 4194304 total in 10 blocks; 942232 free (25 chunks); 3252072 used
  TopTransactionContext: 8192 total in 1 blocks; 7744 free (0 chunks); 448 used
  TableSpace cache: 8192 total in 1 blocks; 2096 free (0 chunks); 6096 used
  Operator lookup cache: 24576 total in 2 blocks; 10760 free (3 chunks); 13816 used
  RowDescriptionContext: 8192 total in 1 blocks; 6896 free (0 chunks); 1296 used
  MessageContext: 125898262984 total in 53379 blocks; 4414792 free (10 chunks); 125893848192 used
    partition directory: 8192 total in 1 blocks; 560 free (0 chunks); 7632 used
  Operator class cache: 8192 total in 1 blocks; 560 free (0 chunks); 7632 used
  smgr relation table: 16777216 total in 12 blocks; 6441432 free (44 chunks); 10335784 used
  TransactionAbortContext: 32768 total in 1 blocks; 32512 free (0 chunks); 256 used
  Portal hash: 8192 total in 1 blocks; 560 free (0 chunks); 7632 used
  TopPortalContext: 8192 total in 1 blocks; 7936 free (1 chunks); 256 used
  Relcache by OID: 4194304 total in 10 blocks; 935096 free (24 chunks); 3259208 used
  CacheMemoryContext: 194248872 total in 36 blocks; 4815960 free (4 chunks); 189432912 used
    partition constraint: 2048 total in 2 blocks; 728 free (0 chunks); 1320 used: batch_partition_019999
    partition constraint: 2048 total in 2 blocks; 728 free (0 chunks); 1320 used: batch_partition_019998
    partition constraint: 2048 total in 2 blocks; 728 free (0 chunks); 1320 used: batch_partition_019997
    partition constraint: 2048 total in 2 blocks; 728 free (0 chunks); 1320 used: batch_partition_019996
    partition constraint: 2048 total in 2 blocks; 728 free (0 chunks); 1320 used: batch_partition_019995
    partition constraint: 2048 total in 2 blocks; 728 free (0 chunks); 1320 used: batch_partition_019994
    partition constraint: 2048 total in 2 blocks; 728 free (0 chunks); 1320 used: batch_partition_019993
    partition constraint: 2048 total in 2 blocks; 728 free (0 chunks); 1320 used: batch_partition_019992
    partition constraint: 2048 total in 2 blocks; 728 free (0 chunks); 1320 used: batch_partition_019991
    partition constraint: 2048 total in 2 blocks; 728 free (0 chunks); 1320 used: batch_partition_019990
    partition constraint: 2048 total in 2 blocks; 728 free (0 chunks); 1320 used: batch_partition_019989
    partition constraint: 2048 total in 2 blocks; 728 free (0 chunks); 1320 used: batch_partition_019988
    partition constraint: 2048 total in 2 blocks; 728 free (0 chunks); 1320 used: batch_partition_019987
    partition constraint: 2048 total in 2 blocks; 728 free (0 chunks); 1320 used: batch_partition_019986
...

MessageContext主要是用来存放statement及parser tree，query tree和plan tree等数据。

MessageContext --- this context holds the current command message from the
frontend, as well as any derived storage that need only live as long as
the current message (for example, in simple-Query mode the parse and plan
trees can live here).  This context will be reset, and any children
deleted, at the top of each cycle of the outer loop of PostgresMain.  This
is kept separate from per-transaction and per-portal contexts because a
query string might need to live either a longer or shorter time than any
single transaction or portal

sql语句非常简单：delete from batch_partitioned where created_at < '2025-11-13'::timestamp; 那可能就是plan的环节占用内存高了。

监控进程内存的专业工具有很多，这里由于是短时间申请了大量内存，肯定有大量内存函数调用，要搞清楚调用链路使用perf分析就可以了。

执行sql，同时用perf抓取。对了，二进制最好包含符号表，抓取的信息能更完整。

perf record  -e page-faults  -p 17185  -g -- sleep 60
[ perf record: Woken up 144 times to write data ]
[ perf record: Captured and wrote 35.845 MB perf.data (187909 samples) ]

然后分析perf.data

从调用链路看，证明了我们的推测，确实问题出在执行计划阶段。比较可疑的是inheritance_planner函数，调用占比99.40%，一直往下调用palloc申请内存。

perf report -n --stdio
# To display the perf.data header info, please use --header/--header-only options.
#
#
# Total Lost Samples: 0
#
# Samples: 187K of event 'page-faults:u'
# Event count (approx.): 30002822
#
# Children      Self       Samples  Command   Shared Object     Symbol                                 
# ........  ........  ............  ........  ................  .......................................
#
    99.68%     0.00%             0  postgres  libc-2.17.so      [.] __libc_start_main
            |
            ---__libc_start_main
               startup_hacks
               PostmasterMain
               ServerLoop
               BackendStartup
               ExitPostmaster
               PostgresMain
               exec_simple_query
               |          
                --99.44%--pg_plan_queries
                          pg_plan_query
                          planner
                          standard_planner
                          |          
                           --99.40%--subquery_planner
                                     inheritance_planner
                                     |          
                                     |--93.85%--adjust_appendrel_attrs
                                     |          |          
                                     |           --93.84%--query_tree_mutator
                                     |                     |          
                                     |                     |--65.02%--__memcpy_ssse3
                                     |                     |          
                                     |                      --28.80%--range_table_mutator
                                     |                                |          
                                     |                                |--23.78%--lappend
                                     |                                |          |          
                                     |                                |           --23.77%--new_tail_cell
                                     |                                |                     |          
                                     |                                |                      --18.86%--palloc
                                     |                                |                                AllocSetAlloc
                                     |                                |          
                                     |                                 --5.03%--palloc
                                     |                                           AllocSetAlloc
                                     |          
                                      --5.54%--grouping_planner
                                                |          
                                                 --5.52%--query_planner
                                                           |          
                                                            --5.18%--setup_simple_rel_arrays
                                                                      |          
                                                                       --5.18%--__GI_memset
                                        
...

那么接下来就从inheritance_planner函数入手分析。从代码来看这个函数主要是针对分区表的delete和update操作，给每个被扫描的子表生成子计划。调用adjust_appendrel_attrs函数复制Query结构体并将parent RTE指向child RTE，然后处理子查询RTE。这个过程中会为每个子表分配一个Query结构体。

/*
 * inheritance_planner
 *   Generate Paths in the case where the result relation is an
 *   inheritance set.
 *
 * We have to handle this case differently from cases where a source relation
 * is an inheritance set. Source inheritance is expanded at the bottom of the
 * plan tree (see allpaths.c), but target inheritance has to be expanded at
 * the top.  The reason is that for UPDATE, each target relation needs a
 * different targetlist matching its own column set.  Fortunately,
 * the UPDATE/DELETE target can never be the nullable side of an outer join,
 * so it's OK to generate the plan this way.
 *
 * Returns nothing; the useful output is in the Paths we attach to
 * the (UPPERREL_FINAL, NULL) upperrel stored in *root.
 *
 * Note that we have not done set_cheapest() on the final rel; it's convenient
 * to leave this to the caller.
 */
 static void
inheritance_planner(PlannerInfo *root)
{
 /* 省略 */
   /*
   * Generate modified query with this rel as target.  We first apply
   * adjust_appendrel_attrs, which copies the Query and changes
   * references to the parent RTE to refer to the current child RTE,
   * then fool around with subquery RTEs.
   */
  subroot->parse = (Query *)
   adjust_appendrel_attrs(subroot,
           (Node *) parent_parse,
           1, &appinfo);
/* 省略 */

我们的例子中batch_partitioned有20000个分区表，分区键为partition_key，执行的sql没带分区键，所以操作了所有分区表。一个分区表对应的结构体大约占4.5MB，20000个也差不多90GB了。

postgres=# \d+ batch_partitioned
                                     Partitioned table "public.batch_partitioned"
    Column     |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description 
---------------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 partition_key | integer                     |           |          |         | plain    |              | 
 data          | jsonb                       |           |          |         | extended |              | 
 created_at    | timestamp without time zone |           |          | now()   | plain    |              | 
Partition key: RANGE (partition_key)
Indexes:
    "idx_batch_partitioned_created_at" btree (created_at)
    "idx_batch_partitioned_json_data" gin (data)
    "idx_batch_partitioned_partition_key" btree (partition_key)
Partitions: batch_partition_000000 FOR VALUES FROM (0) TO (1),
            batch_partition_000001 FOR VALUES FROM (1) TO (2),
            batch_partition_000002 FOR VALUES FROM (2) TO (3),
            batch_partition_000003 FOR VALUES FROM (3) TO (4),
            batch_partition_000004 FOR VALUES FROM (4) TO (5),
            batch_partition_000005 FOR VALUES FROM (5) TO (6),
            batch_partition_000006 FOR VALUES FROM (6) TO (7),
            batch_partition_000007 FOR VALUES FROM (7) TO (8),
            batch_partition_000008 FOR VALUES FROM (8) TO (9)
            ...

像这种问题，社区肯定有讨论，去搜索了下确实找到了一些信息。

在以下邮件中看到了2019年Tom Lane一众大佬的讨论，认为inheritance_planner的处理是有问题的，预计会在13+版本中调整。

【Excessive memory usage in multi-statement queries w/ partitioning】[1]

【memory problems and crash of db when deleting data from table with thousands of partitions 】[2]

优化方案

查找Release Notes同时进行验证，发现在PG14版本做了优化。

来自Tom Lane的commit:【Rework planning and execution of UPDATE and DELETE】[3]

如commit log所言，去除了令人厌恶的inheritance_planner()。

For inherited UPDATE/DELETE, instead of generating a separate
subplan for each target relation, we now generate a single subplan
that is just exactly like a SELECT's plan, then stick ModifyTable
on top of that.  To let ModifyTable know which target relation a
given incoming row refers to, a tableoid junk column is added to
the row identity information.  This gets rid of the horrid hack
that was inheritance_planner(), eliminating O(N^2) planning cost
and memory consumption in cases where there were many unprunable
target relations.

14版本中delete访问20000个子表消耗内存5GB左右。

感兴趣的朋友可以用这个函数去复现，切勿在重要环境中测试。

CREATE ORREPLACEFUNCTION create_partitions_batch(
    total_partitions INTEGERDEFAULT20000,
    batch_size INTEGERDEFAULT100
) RETURNSVOIDAS $$
DECLARE
    i INTEGER := 0;
    batch_count INTEGER;
    current_batch INTEGER := 0;
BEGIN
    batch_count := ceil(total_partitions::FLOAT / batch_size);
    
    IF NOT EXISTS (SELECT1FROM pg_tables WHERE tablename = 'batch_partitioned') THEN
        CREATETABLE batch_partitioned (
            partition_key INTEGER,
            data JSONB,
            created_at TIMESTAMPDEFAULTnow()
        ) PARTITIONBYRANGE (partition_key);
CREATEINDEXIFNOTEXISTS idx_batch_partitioned_partition_key ON batch_partitioned (partition_key);
       CREATEINDEXIFNOTEXISTS idx_batch_partitioned_created_at ON batch_partitioned (created_at);
CREATEINDEXIFNOTEXISTS idx_batch_partitioned_json_data ON batch_partitioned USING GIN (data);
 RAISE NOTICE '主表 batch_partitioned 创建完成';
    ENDIF;
    
    WHILE current_batch < batch_count LOOP
        RAISE NOTICE '创建批次 %, 进度 %/%', 
            current_batch + 1, 
            (current_batch + 1) * batch_size, 
            total_partitions;
            
        FOR i IN 0..(batch_size - 1) LOOP
            EXIT WHEN (current_batch * batch_size + i) >= total_partitions;
            
            EXECUTEformat(
                'CREATE TABLE IF NOT EXISTS batch_partition_%s PARTITION OF batch_partitioned FOR VALUES FROM (%s) TO (%s)',
                lpad((current_batch * batch_size + i)::TEXT, 6, '0'),
                current_batch * batch_size + i,
                current_batch * batch_size + i + 1
            );
        ENDLOOP;
        
        PERFORM pg_sleep(0.1);        
        current_batch := current_batch + 1;
    ENDLOOP;
    
    RAISE NOTICE '分区表创建完成，总计 % 个分区', total_partitions;
END;
$$ LANGUAGE plpgsql;

小结

本篇简单介绍了高内存问题的分析方法，当遇到内存消耗高问题时，先打印MemoryContext分布，再结合perf等工具进行分析。

by the way，写SQL要规范，做了分区就要带分区条件；大版本升级是相当重要的。

Reference

[1]

【Excessive memory usage in multi-statement queries w/ partitioning】: https://www.postgresql.org/message-id/flat/87ftp6l2qr.fsf%40credativ.de

[2]

【memory problems and crash of db when deleting data from table with thousands of partitions】: https://www.postgresql.org/message-id/flat/CAGvVEFueqXeYL0z2fTQMWYSz7Gc9czSrOvCSNubb9rHHVL-2OA%40mail.gmail.com

[3]

【Rework planning and execution of UPDATE and DELETE】: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=86dc90056

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2025-11-14，如有侵权请联系 cloudcommunity@tencent.com 删除

free