--- title: Let's Build a Postgres Extension for Estimating Memory... description: A hands-on walkthrough builds a Postgres extension called querymem that estimates a query's worst-case work_mem consumption by hooking into the parser and... canonical: https://daily.dev/posts/let-s-build-a-postgres-extension-for-estimating-memory-usage--qa2usetek twitter:card: summary_large_image twitter:site: @dailydotdev og:type: website og:site_name: daily.dev og:title: Let's Build a Postgres Extension for Estimating Memory Usage! | daily.dev og:description: A hands-on walkthrough builds a Postgres extension called querymem that estimates a query's worst-case work_mem consumption by hooking into the parser and... og:url: https://daily.dev/posts/let-s-build-a-postgres-extension-for-estimating-memory-usage--qa2usetek og:image: https://api.daily.dev/og/posts/Qa2UseTek.png og:image:alt: Let's Build a Postgres Extension for Estimating Memory Usage! og:image:width: 1200 og:image:height: 630 og:locale: en --- > ## Documentation Index > Fetch the complete documentation index at: https://daily.dev/llms.txt > Use this file to discover all available pages before exploring further. # Let's Build a Postgres Extension for Estimating Memory Usage! **[Planet PostgreSQL](https://daily.dev/sources/planet-postgresql)** · 20 min read · 0 upvotes · 0 comments ## Summary A hands-on walkthrough builds a Postgres extension called querymem that estimates a query's worst-case work_mem consumption by hooking into the parser and planner. The guide covers setting up a build environment, writing a PGXS-based extension, calling raw_parser, transformTopLevelStmt, and planner directly, recursively walking the resulting plan tree (including subplans) to accumulate work_mem multipliers for hash nodes, and finally wiring the estimator into ExecutorStart_hook with custom GUCs to log or block queries exceeding memory thresholds. Real pgbench queries demonstrate the estimates climbing from 4MB for a simple scan to 57MB for a materialized CTE with joins. Full source is available on GitHub. ## Full article daily.dev links to this article rather than hosting it. Read it at the original source: ## Questions this post answers ### How can I estimate how much work_mem a Postgres query will actually use before running it? Walk the query's execution plan tree and accumulate a multiplier: add 1.0 for every ordinary plan node and hash_mem_multiplier (default 2.0) for every hash node (T_Hash, T_HashJoin, T_HashJoinState, T_HashPath, T_HashState), including nodes in subplans like materialized CTEs, then multiply the total by work_mem to get a worst-case estimate in kilobytes. _Developers tuning work_mem or debugging runaway query memory can find deeper Postgres internals write-ups like this on daily.dev._ ### How do I call the Postgres query planner directly from inside a C extension? Parse the query text with raw_parser() to get a RawStmt, convert it to a Query using make_parsestate() and transformTopLevelStmt(), then pass that Query into planner(query, query_arg, 0, NULL) to receive the same PlannedStmt the executor would actually run, complete with a planTree and a list of subplans for CTEs and subqueries. _Anyone building custom Postgres tooling around the planner can track deep-dive guides like this via daily.dev._ ### Why would a Postgres query use far more memory than a single work_mem allocation? Each memory-hungry node in a query's execution plan (sorts, hashes, joins) gets its own separate work_mem allocation rather than sharing one pool, and hash nodes cost work_mem multiplied by hash_mem_multiplier (default 2.0) instead of a single unit. A query with a materialized CTE, joins, and a sort can therefore claim ten or more times work_mem at once, e.g. 57MB from a seemingly simple statement against a 4MB default. _Engineers chasing unexpected memory spikes from Postgres queries can follow explanations like this through daily.dev._ ## Similar posts on daily.dev - [Can your Postgres survive a bad query?](https://daily.dev/posts/can-your-postgres-survive-a-bad-query--sdf4kdxbn) · ClickHouse · 7 upvotes · 1 comments - [All Your GUCs in a Row: maintenance\_work\_mem](https://daily.dev/posts/all-your-gucs-in-a-row-maintenance-work-mem-zcr07vjsd) · Planet PostgreSQL · 0 upvotes · 0 comments - [Don't give Postgres too much memory](https://daily.dev/posts/don-t-give-postgres-too-much-memory-xluq2yiti) · Lobsters · 1 upvotes · 0 comments - [Part 1: What It Is and What It Isn't](https://daily.dev/posts/part-1-what-it-is-and-what-it-isn-t-wtgi7rhxh) · pganalyze · 0 upvotes · 0 comments - [Introducing Postgres Query Insights in ClickHouse Cloud](https://daily.dev/posts/introducing-postgres-query-insights-in-clickhouse-cloud-yy9pc9sbl) · ClickHouse · 5 upvotes · 0 comments --- Tags: [#c](https://daily.dev/tags/c), [#postgresql](https://daily.dev/tags/postgresql) [View this post on daily.dev](https://daily.dev/posts/let-s-build-a-postgres-extension-for-estimating-memory-usage--qa2usetek) ```json {"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://daily.dev/#organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180},"sameAs":["https://twitter.com/dailydotdev","https://github.com/dailydotdev","https://www.linkedin.com/company/daily-dev-ltd"]},{"@type":"WebSite","@id":"https://daily.dev/#website","url":"https://daily.dev","name":"daily.dev","publisher":{"@id":"https://daily.dev/#organization"},"potentialAction":{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https://daily.dev/search?q={search_term_string}"},"query-input":"required name=search_term_string"}}]} {"@context":"https://schema.org","@type":"TechArticle","headline":"Let's Build a Postgres Extension for Estimating Memory Usage!","url":"https://daily.dev/posts/let-s-build-a-postgres-extension-for-estimating-memory-usage--qa2usetek","mainEntityOfPage":{"@type":"WebPage","@id":"https://daily.dev/posts/let-s-build-a-postgres-extension-for-estimating-memory-usage--qa2usetek"},"datePublished":"2026-08-14T19:31:22.523Z","dateModified":"2026-09-13T20:21:23.537Z","description":"A hands-on walkthrough builds a Postgres extension called querymem that estimates a query's worst-case work_mem consumption by hooking into the parser and...","image":"https://postgr.es/static/images/pgEdge-favicon.webp","thumbnailUrl":"https://postgr.es/static/images/pgEdge-favicon.webp","isAccessibleForFree":true,"articleSection":"Planet PostgreSQL","inLanguage":"en","publisher":{"@type":"Organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180}},"author":{"@type":"Organization","name":"Planet PostgreSQL","logo":"https://media.daily.dev/image/upload/logos/placeholder.jpg","url":"https://daily.dev/sources/planet-postgresql"},"commentCount":0,"discussionUrl":"https://daily.dev/posts/let-s-build-a-postgres-extension-for-estimating-memory-usage--qa2usetek","interactionStatistic":[{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":0},{"@type":"InteractionCounter","interactionType":{"@type":"CommentAction"},"userInteractionCount":0}],"keywords":"c,postgresql","timeRequired":"PT20M"} {"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https://daily.dev"},{"@type":"ListItem","position":2,"name":"Planet PostgreSQL","item":"https://daily.dev/sources/planet-postgresql"},{"@type":"ListItem","position":3,"name":"Let's Build a Postgres Extension for Estimating Memory Usage!"}]} {"@context":"https://schema.org","@type":"FAQPage","@id":"https://daily.dev/posts/let-s-build-a-postgres-extension-for-estimating-memory-usage--qa2usetek#faq","mainEntity":[{"@type":"Question","name":"How can I estimate how much work_mem a Postgres query will actually use before running it?","acceptedAnswer":{"@type":"Answer","text":"Walk the query's execution plan tree and accumulate a multiplier: add 1.0 for every ordinary plan node and hash_mem_multiplier (default 2.0) for every hash node (T_Hash, T_HashJoin, T_HashJoinState, T_HashPath, T_HashState), including nodes in subplans like materialized CTEs, then multiply the total by work_mem to get a worst-case estimate in kilobytes. Developers tuning work_mem or debugging runaway query memory can find deeper Postgres internals write-ups like this on daily.dev."}},{"@type":"Question","name":"How do I call the Postgres query planner directly from inside a C extension?","acceptedAnswer":{"@type":"Answer","text":"Parse the query text with raw_parser() to get a RawStmt, convert it to a Query using make_parsestate() and transformTopLevelStmt(), then pass that Query into planner(query, query_arg, 0, NULL) to receive the same PlannedStmt the executor would actually run, complete with a planTree and a list of subplans for CTEs and subqueries. Anyone building custom Postgres tooling around the planner can track deep-dive guides like this via daily.dev."}},{"@type":"Question","name":"Why would a Postgres query use far more memory than a single work_mem allocation?","acceptedAnswer":{"@type":"Answer","text":"Each memory-hungry node in a query's execution plan (sorts, hashes, joins) gets its own separate work_mem allocation rather than sharing one pool, and hash nodes cost work_mem multiplied by hash_mem_multiplier (default 2.0) instead of a single unit. A query with a materialized CTE, joins, and a sort can therefore claim ten or more times work_mem at once, e.g. 57MB from a seemingly simple statement against a 4MB default. Engineers chasing unexpected memory spikes from Postgres queries can follow explanations like this through daily.dev."}}]} ```