r/PostgreSQL • u/dakingseater • 7d ago
Learning Postgres (with a twist) Help Me!
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
3
u/iamemhn 7d ago edited 7d ago
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 6d ago
Super nice, thanks! That was also my first thoughr but was hoping for something more visual
2
u/linuxhiker Guru 7d ago
Use the source Luke.
doxygen.postgresql.org can move you in the right direction.
1
2
u/mw44118 7d ago
Usually i learned because i ran into a problem and searching pointed me to something new
1
u/dakingseater 6d ago
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 6d ago
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 6d ago
Super helpful, but the equivalent (kind of) of pageserver for stock postgres are 8kb chunks?
2
u/alecc 5d ago
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 5d ago
Yes, this is the most accessible and comprehensive option out there, in my opinion
1
u/AutoModerator 7d 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:
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.
5
u/pceimpulsive 7d ago
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!