Do you like building Postgres extensions? Of course you do! My "Let's Build a Postgres Extension" presentation garnered rave reviews at Postgres Conference 2026 in San Jose and PG Data 2026 in Chicago. But what if you didn't go? Sure the slides are available on both sites, but that's not quite the same, is it?

Now that the dust has settled and my long series on Postgres 19 has finally reached its natural conclusion, let's get back to our regularly scheduled shenanigans. It's time to explore the exciting and daunting world of building an extension that does something fun: predicting query memory consumption, and potentially logging or blocking them based on results. Consider this a natural progression of my Introduction to Developing Postgres Extensions article.

If you've ever wondered what the "right" setting for work_mem is, you're not alone. Each query node (join, sort, group) gets its own allocation, so large queries can command much more RAM than you might expect. Despite this, there's been no tool to give a rough estimate of how many allocations of work_mem a query might require. In the spirit of that missing tool, we'll be building something... semi-capable as a proof-of-concept. It's better to have something than nothing, after all. Unfortunately, almost nothing we need to predict lives in the manual. It lives in the source. Grab a machete, we're going into the untamed jungle.

I hope you're ready, because by the time we're done, you'll be a Postgres extension artisan!

Blazing the Trail

Before we can read the map, we need to sketch it out. Building an extension that pokes around inside the planner means we need the Postgres server's own headers, and that means getting the source. You have two honest options here: grab a release tarball straight from the download page, or clone the git mirror to live closer to the metal. We're targeting Postgres 18, so either works fine.

You'll also want the build toolchain. The Postgres wiki has the canonical list of everything, though I personally found that libicu dev libraries are also required.

On a Debian-flavored box, that's a single incantation:

sudo apt install build-essential libreadline-dev zlib1g-dev flex bison \
  libxml2-dev libxslt-dev libssl-dev libxml2-utils xsltproc ccache \
  pkg-config libicu-dev

RHEL folks need to reach for something slightly different:

sudo yum install -y bison-devel readline-devel \
  zlib-devel openssl-devel wget ccache libicu-devel

sudo yum groupinstall -y 'Development Tools'

With that in place, compiling Postgres is the same three-step ritual as usual:

cd postgres
./configure --prefix=/custom
make -j8
make install

Let's be honest here, wrangling a source Postgres install alongside whatever your package manager already put on the machine is a great way to lose an afternoon. So I highly recommend cheating. A tiny Dockerfile built on the official postgres:18 image gives us a clean, disposable environment with the server dev headers already present:

FROM postgres:18

RUN apt-get -y update && apt-get -y upgrade && apt-get -y install \
    build-essential clang postgresql-server-dev-18 libreadline-dev \
    zlib1g-dev flex bison libxml2-dev libxslt-dev libssl-dev \
    libxml2-utils xsltproc ccache pkg-config libicu-dev && apt-get clean

COPY . /build
RUN cd /build && make && make install

Then it's easy to launch a test container running the extension:

docker build -t querymem:latest .
docker run -d --name=querymem -e POSTGRES_HOST_AUTH_METHOD=trust querymem:latest

Now for the extension itself. Postgres ships a build system called PGXS that borrows the server's own machinery, so our Makefile is almost embarrassingly short:

MODULE_big = querymem
PGFILEDESC = "Extension to view query memory usage"
EXTENSION = querymem
DATA = querymem--1.0.sql
OBJS = querymem.o
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

Every extension also needs a control file so Postgres knows what it's installing. Our querymem.control just registers the name and version:

comment = 'PG Query Memory Extension'
default_version = '1.0'
module_pathname = '$libdir/querymem'
relocatable = true

Postgres will run a SQL file when someone types CREATE EXTENSION querymem. For us that file is named querymem--1.0.sql and only contains /* SQL CODE TBA */ as an inert stub; we'll fill it in later.

Believe it or not, it's already possible to build the extension with make && make install. That will produce an inert shell that compiles cleanly and installs, but does absolutely nothing else.

And this is where our free gravy train goes off the rails. The Extending SQL and C-Language Functions chapters of the manual are genuinely great, and they got us this far. But they only really cover the basics. The moment we want to reach into the parser or the planner, the user-facing docs stop there.

The real developer documentation lies elsewhere:

  1. The src/**/README files that the hackers wrote for each other.

  2. The (sometimes voluminous) comments atop the headers in src/include.

  3. The /contrib directory, which is a working cookbook of extensions written by the core devs themselves.

We're not lost. We just have to follow the signposts they left along the way. And on that note, where do we go? We need a plan for the extension itself.

Death By a Thousand Nodes

The documentation for work_mem describes it as the maximum memory a query operation may use before spilling to temporary disk files. That memory is granted per operation, not per query, so each node in the execution plan that needs working space gets its own allotment. A sort gets its own. A hash gets its own. A second sort three levels down the tree gets yet another. So a statement with a dozen memory-hungry nodes can, in the worst case, claim a dozen times work_mem all at once.

It all boils down to this: the worst-case memory for a statement is work_mem times the number of hungry nodes in its plan.

The same documentation also notes that the memory limit for a hash table is computed by multiplying work_mem by hash_mem_multiplier, which defaults to 2.0 in modern Postgres. So a hash node doesn't cost us one unit of work_mem, it costs some multiple.

Our prediction, then, is an accumulation. Walk the plan. Add 1.0 for every ordinary node. Add hash_mem_multiplier for every hash node. Multiply the accumulated total by work_mem, and out pops a worst-case memory estimate. That accumulation of multipliers is the entire mathematical heart of querymem.

Now we have a shopping list for supplies. We need to:

  1. parse a query.

  2. transform the raw query parse into an execution plan.

  3. walk plan nodes and accumulate the correct multiplier.

Time to go find those pieces.

Cutting Through the Babble

Our first stop is query parsing, where Postgres transforms the raw lexical structure of a query string into something the planner will understand.

Let's start with a stub function so we have somewhere to hang our discoveries. The C side declares the module magic and a single function that takes text and, for now, lies about the answer by claiming the memory estimate is always zero:

PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(get_query_mem);

Datum
get_query_mem(PG_FUNCTION_ARGS)
{
    char *query_arg = text_to_cstring(PG_GETARG_TEXT_PP(0));

    PG_RETURN_INT32(DatumGetInt32(0));
}

The SQL wrapper in our installer file exposes it to users:

CREATE FUNCTION get_query_mem(text) RETURNS INT
    AS 'MODULE_PATHNAME', 'get_query_mem'
    LANGUAGE C VOLATILE;

So how do we turn that query_arg string into a parse tree? This is where the scavenger hunt really begins. Let's follow the signposts:

  1. src/backend/parser/README says to start at parser.c.

  2. The very first thing in parser.c is a function called raw_parser, which sounds promising.

  3. The raw_parser function returns a List of parse trees.

  4. Each item in the list is a RawStmt node.

  5. RawStmt nodes are defined in include/nodes/parsenodes.h.

I'll admit that the entire time I was working on this extension, I felt like I was singing The Skeleton Dance. The leg bone's connected to the knee bone...

Ahem. Anyway, what exactly do we do with a list of nodes? Back to the READMEs. Sing with me!

  1. src/backend/nodes/README explains how nodes work.

  2. include/nodes/pg_list.h defines a pile of helper macros.

  3. We only care about the first statement in the parse tree.

  4. The linitial macro unwraps the first List item.

There's also a wonderful debugging function called pprint that pretty-prints any node structure to the server log, revealing the actual truth about what's inside the box. Following all of those leads, our function now looks like this:

Datum get_query_mem(PG_FUNCTION_ARGS)
{
    char *query_arg = text_to_cstring(PG_GETARG_TEXT_PP(0));
    List *parse_tree = raw_parser(query_arg, RAW_PARSE_DEFAULT);
    RawStmt *parsed = (RawStmt *)linitial(parse_tree);

    pprint(parsed);

    PG_RETURN_INT32(DatumGetInt32(0));
} // get_query_mem

Compile that, call it, watch the parse tree spill into the log. Progress!

When a Plan Comes Together

But a RawStmt is only syntax. It knows the query said pgbench_accounts, but it has no idea whether that's a real table or a typo, because it never consulted the catalog. And a plan can't come from pure syntax. We need semantic analysis, the step that resolves those names against the catalog and produces a fully analyzed Query node.

Back to the trail signs:

  1. backend/optimizer/README says planner() does it all.

  2. planner() is declared in optimizer/optimizer.h.

  3. planner() takes a Query, not the RawStmt we already have.

  4. We need to convert RawStmt into a Query.

  5. transformTopLevelStmt() in parser/analyze.h does this!

  6. But transformTopLevelStmt() needs a ParseState to pass around.

  7. ParseState is defined in parser/parse_node.h.

  8. We can initialize ParseState with make_parsestate().

Whew! That was a long one. After all of that scavenging, it turns out we only need two new lines of code to transform the raw parsed text into what Postgres would recognize as a query structure:

ParseState *pstate = make_parsestate(NULL);
Query *query = transformTopLevelStmt(pstate, parsed);

Now we're holding a real, analyzed Query. It knows what the tables are and will also catch any parse errors before the rest of the function runs. But a Query only describes what the user wants, not how Postgres intends to get it. One more transformation stands between us and a plan. That optimizer README, by the way, is the closest thing to a real design document the planner has. It's worth reading in full some rainy afternoon if you enjoy feeling simultaneously enlightened and inadequate.

The planner() signature asks for a few things the header requires. Beyond the query itself, it wants the original query string (used for error reporting), a set of cursor options, and a bound parameter list. We have no cursor tricks and no bound parameters, so those are 0 and NULL: 

PlannedStmt *plan = planner(query, query_arg, 0, NULL);

pprint(plan);

Is it a hack to call the planner directly from an extension? Probably. I ran a few LLMs over the code at one point, and nearly all of them flagged the fact this function probably shouldn't be used this way, and without proper memory contexts, is likely a memory leak waiting to happen. Well, that just means we have more ground to cover in a future article!

Otherwise, this is the same entry point the executor uses when it runs a real statement. The plan we get back is, node for node, the plan Postgres would actually execute. We're not simulating anything or reverse-engineering costs from EXPLAIN text. We're asking the optimizer the exact question it answers for every query, and reading its actual answer. One thing to note: passing 0 for cursor options means we don't set CURSOR_OPT_PARALLEL_OK, so planner() hands us a non-parallel plan. Handling parallelism is an advanced use case for later.

In any case, we now have a query plan. What do we do with that?

Tree House

Like most things in the Postgres code, we need to decipher what's actually inside a PlannedStmt. Back to the code!

  1. planner() returns a PlannedStmt struct, which we have.

  2. PlannedStmt is defined in nodes/plannodes.h.

  3. PlannedStmt has a planTree of type Plan.

  4. It also contains a List of subplans.

  5. Each Plan contains a lefttree and righttree Plan node.

Smells an awful lot like a recursive tree traversal algorithm, doesn't it? Carrying that to its natural conclusion, we'll require a minimal context struct to accumulate our running count and multiplier, but otherwise the code is pretty simple:

typedef struct {
  uint16_t node_count;
  double   multiplier;
} WalkContext;

static void
walk_plan(Plan *plan, WalkContext *ctx)
{
    if (plan == NULL)
        return;

    /* TODO: count this node */

    walk_plan(plan->lefttree, ctx);
    walk_plan(plan->righttree, ctx);
}

The if (plan == NULL) return; is the base case that keeps us from walking off the end of a branch. Now, how do we tell a memory-hungry hash node from a cheap sequential scan? I bet you're humming the song already:

  1. Plan nodes contain a NodeTag type field.

  2. All NodeTags are defined in nodes/nodetags.h.

  3. Hash types include: T_HashPath, T_HashJoin, T_Hash, T_HashJoinState, and T_HashState.

  4. Everything else counts normally.

Putting that together, our "algorithm" is a completely brain-dead switch that just assigns hash_mem_multiplier to any plan node with Hash in the name. Correct? Probably not. Fine for now? Sure. Here's what it looks like:

switch (nodeTag(plan))
{
    case T_Hash:
    case T_HashJoin:
    case T_HashJoinState:
    case T_HashPath:
    case T_HashState:
        ctx->node_count++;
        ctx->multiplier += hash_mem_multiplier;
        break;
    default:
        ctx->node_count++;
        ctx->multiplier += 1.0;
        break;
}

There's one branch a naive left-and-right walk misses: subplans. Materialized CTEs and some subqueries both reside within PlannedStmt rather than the main tree's children. A query that shovels most of its work into a CTE will lie to us about the memory cost if we ignore them. So we walk the main tree first, then loop over every subplan and walk each of those too, before finishing the formula by multiplying the accumulated total against work_mem. Thus, the full plan traversal and total calculation looks like this:

WalkContext ctx = {0, 0.0};
ListCell *lc;

walk_plan(plan->planTree, &ctx);
foreach(lc, plan->subplans)
    walk_plan((Plan *) lfirst(lc), &ctx);

PG_RETURN_INT32(DatumGetInt32(work_mem * ctx.multiplier));

That work_mem variable, incidentally, is a global the server exports for free once we include the right header, and so is hash_mem_multiplier. We don't have to fetch them from anywhere. The executor uses the same two globals we do.

And just like that, querymem has a brain. But a brain is only worth trusting if it survives contact with real plans, so let's load some data and watch the estimate climb as the queries get greedier.

The Very Hungry Caterpillar Query Plan

Nothing beats a realistic table with realistic statistics, and pgbench hands us both for free. We can bootstrap that in the Docker container:

docker exec -it -u postgres querymem pgbench -i -s 10 postgres

Then we can install the extension and turn up logging so we can watch:

CREATE EXTENSION querymem;
SET log_min_messages = NOTICE;

Now we climb a ladder of queries, each one hungrier than the last. First rung, a plain sequential scan with no sorting or hashing anywhere:

SELECT get_query_mem($$ SELECT a.* FROM pgbench_accounts a $$);

 get_query_mem
---------------
          4096

That 4096 is a single work_mem worth of kilobytes, the 4MB default. That's what we expect for the one node that could conceivably need working space. About as cheap as it gets.

Add an ORDER BY and the planner inserts a Sort node, which needs a work_mem allotment of its own:

SELECT get_query_mem($$
    SELECT a.* FROM pgbench_accounts a ORDER BY a.bid;
$$);

 get_query_mem
---------------
          8192

Two nodes, two units, 8MB. Exactly what the theory predicted. Now let's force the planner to reach for a hash:

SELECT get_query_mem($$
    SELECT a.* FROM pgbench_accounts a
      JOIN pgbench_branches b ON (b.bid = a.bid)
     WHERE b.bid > 5 ORDER BY b.bid;
$$);

 get_query_mem
---------------
         28672

Whoa. 28MB? Why so high? Well, let's compare it to the actual query plan Postgres gives us with EXPLAIN: 

Sort
    Sort Key: a.bid
    -> Hash Join
        Hash Cond: (a.bid = b.bid)
        -> Seq Scan on pgbench_accounts a
            -> Hash
            -> Seq Scan on pgbench_branches b
                 Filter: (bid > 5)

Two hashes, two sequential scans, and a sort is five nodes, and each hash counts as two, making our multiplier seven. And what's 4096 * 7? 28672. Not bad for some back-of-the-napkin math!

The top of the ladder is a materialized CTE, which stacks a subplan onto everything else:

SELECT get_query_mem($$
    WITH bdata AS MATERIALIZED (
        SELECT * FROM pgbench_branches WHERE bid > 5
    )
    SELECT a.* FROM pgbench_accounts a
      JOIN bdata b ON (b.bid = a.bid)
     WHERE EXISTS (
        SELECT * FROM pgbench_tellers t WHERE t.bid = b.bid
     )
     ORDER BY b.bid;
$$);

 get_query_mem
---------------
         57344

57MB from a query that looks perfectly innocent. That's the entire argument for building this tool in the first place. But a number a human has to remember to check is a number that gets ignored, so let's teach querymem to watch every query on its own and speak up without being asked.

Evolution of an Extension

A function you call on demand is a calculator. To make querymem a living part of the server, we need two things: configuration knobs, and a way to run our estimate automatically on every statement that comes through. Postgres calls its configuration knobs GUCs, and any extension that defines them has to be loaded through shared_preload_libraries, because GUC registration and hook installation have to happen while the server is starting up, before it accepts a single connection. That early-init entry point is a specially named function, _PG_init, which Postgres calls automatically when it loads our library.

The registration functions all live in utils/guc.h, alongside the signal flags and unit definitions, and there are plenty of working examples scattered through /contrib if you'd rather copy than read. We want two integer GUCs, one for a logging threshold and one for a hard block. DefineCustomIntVariable handles both. The disable sentinel is -1, so the minimum bound has to be -1 too, or the machinery would reject the very value meant to switch the feature off.

Given all of that, we need to fill in these sections:

// 1. Define GUC global variables and pre-execution hook

void _PG_init(void)
{
    // 2. Define custom GUCs
    // 3. Reserve our extension's GUC prefix
    // 4. Set pre-execution hook
} // _PG_init

The first part is easy enough. Just declare our GUC variables and define a "next" executer hook variable (we'll be discussing that later).

int querymem_log_size;
int querymem_max_query_size;
static ExecutorStart_hook_type next_ExecutorStart_hook = NULL;

Then define the log_size GUC to log any query over a certain memory estimate:

DefineCustomIntVariable(
    "querymem.log_size",
    "Log any queries exceeding this amount of estimated memory.",
    "Default: 10x work_mem. Set to -1 to disable.",
    &querymem_log_size,
    work_mem * 10, -1,          // Same limits as work_mem, -1 to disable
    MAX_KILOBYTES,
    PGC_SIGHUP,                 // Only supported by daemon reload
    GUC_UNIT_KB,                // Also allows KB, MB, GB, etc.
    NULL, NULL, NULL            // No hooks necessary
);

And then max_query_size to forcefully abort queries over a certain estimate:

DefineCustomIntVariable(
    "querymem.max_query_size",
    "Prevent execution of queries over this work_mem limit.",
    "Default: 100x work_mem. Set to -1 to disable.",
    &querymem_max_query_size,
    work_mem * 100, work_mem,   // Same limits as work_mem, -1 to disable
    MAX_KILOBYTES,
    PGC_SIGHUP,                 // Only supported by daemon reload
    GUC_UNIT_KB,                // Also allows KB, MB, GB, etc.
    NULL, NULL, NULL            // No hooks necessary
);

Then reserve the extension's prefix so no other extensions can use it:

MarkGUCPrefixReserved("querymem");

And finally replace the current system hook with our hook. This is probably the riskiest portion of the code:

if (ExecutorStart_hook)
    next_ExecutorStart_hook = ExecutorStart_hook;
ExecutorStart_hook = querymem_ExecutorStart;

This is so important because ExecutorStart_hook is a global function pointer Postgres calls at the start of every statement's execution. We save whatever was already there into next_ExecutorStart_hook, then install our own. Notice we save the old one rather than clobbering it. Remember that, because it's the difference between being a good citizen and a saboteur.

We already have the estimation logic, so let's factor it into a helper that takes a PlannedStmt directly, since the executor hands us one:

int32_t
estimate_from_plan(PlannedStmt *plan)
{
    ListCell *lc;
    WalkContext ctx = {0, 0.0};

    walk_plan(plan->planTree, &ctx);
    foreach(lc, plan->subplans)
        walk_plan((Plan *) lfirst(lc), &ctx);

    return work_mem * ctx.multiplier;
}

And here's the hook itself. It grabs an estimate, blocks anything over the hard ceiling, logs anything over the softer threshold, and then, critically, chains to the next hook:

static void
querymem_ExecutorStart(QueryDesc *queryDesc, int eflags)
{
    int32_t estimate = 0;

    if (querymem_log_size > -1 || querymem_max_query_size > -1)
        if (!(eflags & EXEC_FLAG_EXPLAIN_ONLY))
            estimate = estimate_from_plan(queryDesc->plannedstmt);

    if (querymem_max_query_size > -1 && estimate > querymem_max_query_size)
        ereport(ERROR,
            (errcode(ERRCODE_STATEMENT_TOO_COMPLEX),
             errmsg("This query may use too much work_mem (%dk).", estimate),
             errhint("Increase the querymem.max_query_size GUC to continue.")));

    if (querymem_log_size > -1 && estimate > querymem_log_size)
        elog(LOG, "Estimated peak memory usage: %d kB", estimate);

    if (next_ExecutorStart_hook)
        next_ExecutorStart_hook(queryDesc, eflags);
    else
        standard_ExecutorStart(queryDesc, eflags);
}

The ereport(ERROR, ...) does double duty: it aborts the statement outright and logs the offending query text for context, all on its own. The elog(LOG, ...) logs without aborting. We skip EXPLAIN-only runs because there's no reason to block someone from merely inspecting a plan.

Just for added emphasis: Always call the next hook, or the standard one if there is no next. Postgres does not enforce hook chaining. There is no register_executor_hook() that keeps everybody honest, only the convention that each extension saves the previous pointer and calls it. If you install your hook and never chain, you have silently disabled every other extension that wanted to run at executor start, and possibly parts of the server itself. A badly behaving extension can ruin it for everyone, so don't be that extension.

Does it work? The defaults are deliberately too high to trip in a demo, so we lower them and reload:

ALTER SYSTEM SET querymem.log_size = '8MB';
ALTER SYSTEM SET querymem.max_query_size = '20MB';
SELECT pg_reload_conf();

Then we can go back and try to run a couple queries to see the new thresholds in action:

LOG:  Estimated peak memory usage: 12288 kB
STATEMENT:  SELECT a.* FROM pgbench_accounts a ORDER BY a.bid;

If you noticed this number (12MB) is a single allocation higher than what the function returned, you're not seeing things. I think the full execution path wraps the query in an extra node this code doesn't account for. That's a bug to worry about later. For now, we can see that the logging portion of the code worked as expected.

Next comes the hash-join query which the function clocked at 28MB, which is more than enough to hit our 20MB ceiling. Here's what happens when we try executing it:

ERROR:  This query may use too much work_mem (32768k).
HINT:  Increase the querymem.max_query_size GUC to continue.

Sweet, Postgres shuts everything down exactly as we planned!

End of the Line

When the docs run out, the source is the docs.

So the loop the intro opened is closed. Before you run a statement, querymem will report how much working memory it might grab, and it will refuse to let the truly monstrous ones through if requested. The question that keeps surfacing on Discord finally has a tool behind it.

But honestly, the memory calculator is the smaller lesson. The larger one is the source of its parts. Not one function we leaned on, not raw_parser, transformTopLevelStmt, planner, the plan-walking pattern, node tags, or the hook convention, appears in the user manual with a working example. All of it lives in README files stashed across the source, or the source itself. There is no alternative.

This kind of spelunking is definitely somewhat unorthodox. It's slower and more haphazard than reading a polished reference. It's a lot of grepping for a function by parameter types, and a hook that must chain correctly or brick the server is emphatically not a beginner's plaything. There were moments building this where I felt like an absolute idiot. But hey, if I can do it, so can you.

The full source is available at querymem on GitHub. Grab it, then go grep through your own copy of the Postgres tree and see what else is hiding in there.

I promise it's more than you think.