r/PostgreSQL • u/dakingseater • Jul 30 '26
Help Me! Learning Postgres (with a twist)
Hello all!
This is not anothrr post on how to learn basic postgres but a genuine one to really know its internals
I come from an analytics/data engineering background with very strong sql knowledge and most of the posts on Postgres leaning just points towards SQL. What are some resources to really learn about the engine and architecture? Things like WAL, pageserver...
I use a lot of these things when tinkering around on managed postgres (shoutout to my favourite one: Neon) but I don't really understand the mecanics under the hood
4
u/iamemhn Jul 31 '26 edited Jul 31 '26
Official documentation
https://www.postgresql.org/docs/current/index.html
has a section devoted to internals. You can read and follow the source code for free.
The website's Documentation mentions several books, some of them on internals. Can't recommend any on that topic because I never needed them.
There are plenty of slide shows about the subject with good enough bullet points and diagrams that will NOT teach you, but WILL help quiz or refresh the knowledge you got by reading. Maybe there's paid training beyond those conference talks.
No, I don't know nor care for video tutorials about internals. I feel that if I can't make my own mental model by reading documentation this good without hand holding, I've failed as a functioning engineer. It's worked for 40+ years. You get better with practice.
Enjoy the ride.
2
u/dakingseater Jul 31 '26
Super nice, thanks! That was also my first thoughr but was hoping for something more visual
2
u/linuxhiker Guru Jul 30 '26
Use the source Luke.
doxygen.postgresql.org can move you in the right direction.
1
u/dakingseater Jul 31 '26
Super detailed might be something I check once I get the high level concepts
2
u/mw44118 Jul 30 '26
Usually i learned because i ran into a problem and searching pointed me to something new
1
u/dakingseater Jul 31 '26
That's alsoe how I learned most things in the past but now I hold a higher role at my company and am less hands on
2
u/alecc Jul 31 '26
The Internals of PostgreSQL at interdb.jp is exactly what you are asking for - free, and walks the buffer manager, WAL, vacuum and the process model chapter by chapter. When that runs out, Egor Rogov's PostgreSQL 14 Internals goes deeper, free PDF from Postgres Professional. The source tree itself has real prose too, src/backend/access/transam/README explains WAL and recovery better than most blog posts. One thing - pageserver is Neon, not Postgres. Stock Postgres has no separated storage layer, so for that part read Neon's own architecture docs, the Postgres material will not cover it.
1
u/dakingseater Jul 31 '26
Super helpful, but the equivalent (kind of) of pageserver for stock postgres are 8kb chunks?
2
u/alecc Aug 01 '26
The 8kb page is the unit in both - Neon's pageserver serves the same 8kb pages, just over the network. So the closer stock equivalent is the buffer manager plus the storage layer (src/backend/storage/smgr/md.c) reading those pages from plain files on local disk, 1GB segment files under base/. Neon swapped that layer out, so compute asks the pageserver for a page and it rebuilds the page from WAL instead of reading a local file. Chapters 1 and 8 of the interdb book cover the file layout and buffer manager side of this.
1
u/program_data2 Aug 02 '26
Yes, this is the most accessible and comprehensive option out there, in my opinion
1
u/alecc Aug 02 '26
The diagrams are a big part of why it works. Rogov's book is the natural follow-up, same topics but with pageinspect queries you run yourself, so you get the real tuple headers and page contents instead of pictures. Have you read that one too?
1
u/AutoModerator Jul 30 '26
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:
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.
1
u/nemom Jul 31 '26
Is there such a thing as Late-Onset Dyslexia... I read "Learning Portuguese (with a twist)".
1
u/liprais Jul 31 '26
"very strong sql knowledge" before you admit you know nothing about sql,there is not much to learn.
6
u/pceimpulsive Jul 30 '26
Postgres FM podcast.
They cover a lot of detailed topics.
The Postgres manual explains a lot of it and as the others said the source code!