Why a Simple DELETE on a 20k‑Partition Table Exhausted 120 GB Memory in 60 seconds

Problem

In PostgreSQL <14, a DELETE without the partition key on a declaratively partitioned table can consume >100 GB RAM. Example query

delete from batch_partitioned where created_at < '2025-11-13'::timestamp;

runs ~60 s, peaks at ~120 GB, then releases memory.

Memory inspection

On PG14+ call SELECT pg_log_backend_memory_contexts(pid);. On older versions use GDB:

gdb --batch --pid 17185 --ex 'call MemoryContextStats(TopMemoryContext)'

Output shows MessageContext using >100 GB.

MessageContext

Stores the current command message, parser tree, query tree and plan tree; its size grows with planning work performed for each partition.

Perf profiling

Record page‑fault events while the DELETE runs:

perf record -e page-faults -p 17185 -g -- sleep 60

Report reveals inheritance_planner accounts for 99.40 % of planning time and repeatedly calls palloc to allocate memory.

Inheritance planner analysis

The function generates a separate sub‑plan for each child partition, copying the query structure and allocating a new Query node per child.

static void inheritance_planner(PlannerInfo *root) {
    /* ... */
    adjust_appendrel_attrs(...);
    /* ... */
}

During DELETE / UPDATE on an inherited (partitioned) table, this creates a sub‑plan per partition, leading to O(N²) planning cost.

Scale of the problem

Test table batch_partitioned has 20 000 partitions; each partition structure occupies ~4.5 MB, total ~90 GB. Because the DELETE lacks a partition predicate, PostgreSQL scans all partitions, triggering the O(N²) algorithm and exhausting memory.

Community discussion

A 2019 mailing‑list thread led by Tom Lane identified the issue in inheritance_planner and indicated a fix would land in the 13+ series.

PG14 fix

Commit “Rework planning and execution of UPDATE and DELETE” removes inheritance_planner and replaces it with a single sub‑plan plus a tableoid junk column, eliminating the O(N²) cost. After the fix, the same DELETE on 20 000 partitions uses ~5 GB RAM.

For inherited UPDATE/DELETE, instead of generating a separate subplan for each target relation,
we now generate a single subplan like a SELECT's plan, then add ModifyTable on top.
A tableoid junk column identifies the target relation, removing the inheritance_planner hack
and eliminating O(N²) planning cost and memory consumption.

Reproduction function

PL/pgSQL function to create a partitioned table with a configurable number of partitions (default 20 000):

CREATE OR REPLACE FUNCTION create_partitions_batch(
    total_partitions INTEGER DEFAULT 20000,
    batch_size INTEGER DEFAULT 100
) RETURNS void AS $$
DECLARE
    i INTEGER := 0;
    batch_count INTEGER;
    current_batch INTEGER := 0;
BEGIN
    batch_count := ceil(total_partitions::FLOAT / batch_size);
    IF NOT EXISTS (SELECT 1 FROM pg_tables WHERE tablename = 'batch_partitioned') THEN
        CREATE TABLE batch_partitioned (
            partition_key INTEGER,
            data JSONB,
            created_at TIMESTAMP DEFAULT now()
        ) PARTITION BY RANGE (partition_key);
        CREATE INDEX IF NOT EXISTS idx_batch_partitioned_partition_key ON batch_partitioned (partition_key);
        CREATE INDEX IF NOT EXISTS idx_batch_partitioned_created_at ON batch_partitioned (created_at);
        CREATE INDEX IF NOT EXISTS idx_batch_partitioned_json_data ON batch_partitioned USING GIN (data);
    END IF;
    WHILE current_batch < batch_count LOOP
        FOR i IN 0..(batch_size-1) LOOP
            EXIT WHEN (current_batch * batch_size + i) >= total_partitions;
            EXECUTE format(
                '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
            );
        END LOOP;
        PERFORM pg_sleep(0.1);
        current_batch := current_batch + 1;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

References

Excessive memory usage in multi‑statement queries w/ partitioning – PostgreSQL mailing list.

Memory problems and crash of DB when deleting data from table with thousands of partitions – PostgreSQL mailing list.

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