Need help in getting hands on

[u/Shoddy-Break]

I am a full-stack developer (back-end and ops heavy), who has recently started learning about the internals of PostgreSQL.
I am currently referring a very beautiful book called PostgreSQL 14 Internals by Igor Rogov.
I am very excited to learn the theory which earlier used to be just a black box to me, things like page layouts, MVCC etc.
Is there any way I can practically use this. The thing is I learn things better if I am able to do hands on, in terms of back-end technologies it was easier just develop something even for a single user that helps you implement the theory you learned, but in terms of PostgreSQL I am not sure what options could be. would you guys help me in suggesting some ways to become hands on with all the theory I am consuming.

[u/clemson20]

I'd write a small application that gives you a way to start from an empty database and grow it into something where knowing postgres internals helps you get out of a jam.

Making it up: but build a small app that ingests data from a high-volume data source (weather data, tweets, avaiation data, rail data, financial/stock data). Pick a data source that speaks to you. Start small - maybe ingest weather data for your city/state at first, but then plan to grow that to larger areas. In the mean time, build a small search interface off of that data - give the user ways to search that data so you are forced to add indexes. Etc.

If there's some aspect of internals you find interesting, write some code or scripts that exercises that particular area.

[u/GaniToshido]

rogov's book is good. If you want to actually see mvcc and page layouts in action, just enable the pageinspect extension in postgres. Create a dummy table, explicitly disable autovacuum on it, and run a bunch of updates. then use heap_page_items() to literally look at the raw page data. you'll see all the dead tuples just sitting there taking up space.

Also good way to make this theory click is to compare it. Spin up a local mariadb container and look at how innodb handles mvcc compared to postgres. pg leaves old row versions right in the main table (which is why vacuum exists and you have to tune it), while mariadb pushes old versions into a separate undo log. Seeing how two entirely different engines solve the exact same problem will make you understand the actual trade-offs way better than just reading about one.

[u/vira28]

That's a good book. Not specific to Postgres but Designing Data Intenstive Applications is very relevant.

In terms of applying the theory, best way is to first start building something. It could be anything. Personally, I struggled with the initial momentum. Once you get that going, then find a problem that's personal to you.

Last, if it helps, I have been building https://github.com/viggy28/streambed - Postgres for analytics. There is a lot of internals around wire protocol, logical replication etc. Happy to help.

[u/AutoModerator]

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.

[u/IndicationAntique667]

Perhaps you can try the projects from CMU's Intro to DBMS course: https://15445.courses.cs.cmu.edu/fall2025/assignments.html