r/PostgreSQL • u/chaptor • 2d ago
Help Me! Book recommendations for PostgreSQL deep dive
I use PostgreSQL at work. It's critical for our operations, and yet no one in our team is an expert. My knowledge is cobbled together from general SQL knowledge (university and 10+ years work) and lots of articles, stack overflow, and trial & error (and more recently some AI Q&A) for PostgreSQL in particular. I've been using it for many years now and we have made many objective improvements over that time.
Still, I'm working on guesswork and magic most of the time. I have a rough intuition of how it does things, but have never had it spelled out in full. I've looked through this sub-reddit for book recommendations but most of what I can see are self-promotes, which are difficult to gauge for quality/purpose; or "how to use PostgreSQL" books. I could probably learn a decent amount from the latter by skimming past the parts I know (though I unfortunately get de-motivated quickly by a book when I have to do this). But ideally I'd like something that digs into how PostgreSQL's internals actually work. How does it marshall the computer's resources to do what it needs to do? And then at the mid-level, how does its engine optimise queries, and how does it maintain itself? I understand this is always changing and could go very deep but some fundamentals would really help me have a stronger intuition about it.
Does anyone have any book recommendations in this vein? Or maybe the more appropriate book is more generally about relational database engines in general?
15
u/Big-Branch-3643 2d ago
CMU DB has a lot of videos that also cover PostGreSQL https://www.youtube.com/@CMUDatabaseGroup
Also see the list of books listed by PostGreSQL website https://www.postgresql.org/docs/books/
8
u/Zestyclose-Turn-3576 2d ago
I think I would start with a very thorough review of the documentation, and from there look for publicly available articles on very precise topics of interest, because the book publishing life-cycle is longer than the PostgreSQL major release lifecycle.
2
u/HorseyMovesLikeL 2d ago
This is the right one. Reading the source documentation should always be the starting point and the postgres documentation has quite an in depth section on internals, from query parsing, to physical storage and everything in between.
1
u/CrayonUpMyNose 8h ago
Agreed, the postgres docs are very well written and a joy to work with on a regular basis.
2
u/andatki 1d ago edited 1d ago
I also recommend one of the many Postgres conferences around the world which can be as short as one day. In my experience there are often sessions on fundamentals and there's also opportunities to meet practitioners and committers/contributors to PostgreSQL itself and that could set you on a path of learning what's most interesting to you. There's a wealth of content on YouTube as well but an in-person full event might give you a good sampling and provide opportunities to build professional connections.
2
u/Ecksters 1d ago edited 1d ago
If you're interested in optimizing Postgres from an application-developer standpoint, The Art of PostgreSQL is the best paid resource I've found, in addition to Use the Index, Luke! (as a free resource) for understanding more broad concepts across all SQL DBs.
It sounds like you're wanting to dig into internals, in which case other books may be better, but I suspect you may find a lot to learn from those resources.
1
u/scotterockaroo 18h ago
Books are good, https://www.interdb.jp/pg is the cliff notes to the source code that everyone should have bookmarked.
1
u/AutoModerator 2d 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.
0
u/donk8r 2d ago
For getting from guesswork to intuition on a database you already run, auto_explain beats a book. Turn it on and your own production queries print their plans into the log, and every node name it emits becomes something you've got a reason to go read about.
log_analyze makes those plans far more useful and costs real overhead on every statement, so sample it rather than leaving it on. pg_stat_statements first, so you know which queries deserve the noise.
6
u/chaptor 1d ago edited 1d ago
I know this would be unpleasant to hear if not true, but this reads like AI. It therefore makes me suspicious that it’s a hallucination or at least not a personal experience backed piece of advice. If you are a human using AI to layout your thoughts, I recommend skipping the AI and just writing them out, even if you find writing difficult — ai writing << mediocre/bad human writing. If you are a human just trying to help and that’s just how you write, I sincerely apologise 🥰. If you are a bot, please kindly die 🙏
5
u/Ecksters 1d ago
It's the same advice I'd give, digging into EXPLAINs is definitely what pushed me to understand DB internals more, and it lets you do it within the context of your actual application, so what you're learning is concrete and you can test changes against something real.
You can use sites like https://explain.dalibo.com/ to turn it into a nice visual, or some GUI tools, like PgAdmin, have Explain visualizers built-in.
13
u/sceadu 1d ago
https://postgrespro.com/community/books/internals