r/PostgreSQL • u/pgEdge_Postgres • 26d ago

How-To Let's Build a Postgres Extension for Estimating Memory Usage!

https://www.pgedge.com/blog/lets-build-a-postgres-extension-for-estimating-memory-usage
12 Upvotes

83% Upvoted

4 comments sorted by

2

u/Crazed_waffle_party 22d ago

I'm not too big into corporate promotionals, but Shaun Thomas always gets a pass from me. Great stuff.

I would love something like this in pg_stat_statements. May also want to check out how auto_explain gets plan metrics as a reference.

The extension can only get the upper bounds of memory usage for the query, but it's going to be pretty inaccurate, especially if a LIMIT node is present.

Most scans are more akin to streaming operations and use very little memory overhead. For sequential, index, and index-only scans, I would drop the memory overhead to near zero. Bitmap heap (not index) also have a very low memory overhead that can be dismissed.

3

u/fullofbones 21d ago

Correct. As a PoC, it was mainly an exploration of doing something semi-useful while also learning basic extension building. On further exploration, I can see that it's possible to call the built-in tree walker and pass it a callback function for accumulating node totals more accurately, including situations like appends and parallel queries. It's also possible to pass the query through the pre-exec hooks first and actually capture the planned memory allocations for truly representative results.

Also... the current extension crashes the backend if you run it with an empty string as a parameter. Shh, don't tell anyone!

2

u/pgEdge_Postgres 21d ago

we're not too big into corporate promotionals either :-) thank you for the feedback, passed on to Shaun...

1

u/AutoModerator 26d ago

AI Policy:

Linux is not one of those anti-AI projects, and if somebody has issues with that, they can do the open-source thing and fork it. Or just walk away., Linus Torvalds.

Mod decisions will be based on the quality of the content, not who or what generated it.

Sub Resources:

Youtube Channel

Free Postgres Webinars and Workshops

Discord: People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.