8 ms·

The Internals of PostgreSQL

194 points by stevenmh 2y ago

HackerThemAll 2y ago
This is an old, but great and regularly updated, source of information about this great RDBMS. A lot of knowledge is accumulated there.
brasetvik 2y ago
Also recommend this free book: https://postgrespro.com/community/books/internals https://postgrespro.com/community/books/internals
codepathfinder 2y ago
Thanks for sharing. I have been digging deeper around database internals and looks amazing.
- topherjaynes 2y ago
  Check out the CMU history of the DB class, Andy Pavlo is an amazing teacher and you really get a feel for DBs https://www.youtube.com/watch?v=LWS8LEQAUVc&ab_channel=CMUDatabaseGroup https://www.youtube.com/watch?v=LWS8LEQAUVc&ab_channel=CMUDa... same class from 5 years ago is great because he's like well I can't get in my hotel room so I'll just record from the street https://www.youtube.com/watch?v=SdW5RKUboKc&t=797s&ab_channel=CMUDatabaseGroup https://www.youtube.com/watch?v=SdW5RKUboKc&t=797s&ab_channe.... Tons of good videos to go through on their youtube channel.
  - codepathfinder 2y ago
    Thanks a ton!
rrr_oh_man 2y ago
> Transaction logs are an essential part of databases because they ensure that no data is lost even when a system failure occurs. They are a history log of all changes and actions in a database system. This ensures that no data is lost due to failures, such as a power failure or a server crash. The log contains sufficient information about each transaction that has already been executed, so the database server can recover the database cluster by replaying the changes and actions in the transaction log in the event of a server crash. The writing is horrendous
- tasuki 2y ago
  The author is Japanese, cut them some slack.
  - xnickb 2y ago
    Or better yet, offer help
    - rrr_oh_man 2y ago
      In what way? First thought: If a sports team is not playing well, do you mail in coaching advice? I elaborated a bit here, though: https://news.ycombinator.com/item?id=43254469 https://news.ycombinator.com/item?id=43254469
  - rrr_oh_man 2y ago
    Thanks for pointing this out. I probably should have, being a non-native speaker myself. Though, the core of my issue with this is not the language itself, but how the paragraphs are structured. Imho you might cut 50% of words and not lose any information. See here: https://news.ycombinator.com/item?id=43254469 https://news.ycombinator.com/item?id=43254469
- paulddraper 2y ago
  What makes the writing horrendous?
  - shxx 2y ago
    If rrr_oh_man's profile is real (https://bit.ly/48d9o9P https://bit.ly/48d9o9P), his resume appears inconsistent and unprofessional. The emptier a person is, the more likely they are to criticize others.
    - deleted 2y ago
      [deleted]
    - rrr_oh_man 2y ago
nimish 2y ago
The worst part of Postgres (and linux) is the awful mailing list driven development process. I have patches to Postgres that I'd rather just keep porting up every version than to try to figure out how to make Outlook work with a 1980s process. The internals themselves are quite nicely engineered as you'd expect
- felideon 2y ago
  > how to make Outlook work Well that’s your first problem. Unless you’re using corporate email?
  - nimish 2y ago
    Correct. It's corporate email. Imagine the amount of contributions that are gated behind stupid email policies because some fossil mailing list client can't handle modern email in the year of our lord 2025
    - pritambaral 2y ago
      > Outlook > modern email That can't be true. Now, I haven't used Outlook myself, but I've received enough garbage from that godforsaken relic of a time when MS wanted to EEE email to know that that can't be true. FWIW, I've posted patches to the PG mailing list just fine with GMail. The web app, that is.
      - nimish 2y ago
        OK. Every major corporation uses Outlook and there is no way I can change that so by not accepting its foibles you are artificially stopping interested contributors who would do it on company time and hardware It's a very FOSS pathology.
        majewsky 2y ago
        If by "pathology" you mean "we optimize for the comfort of the people already actively engaged in the project instead of hypothetical future contributors", I don't think this argument is as strong as you think it is. If anything, this kind of excessive legal and process red tape is much more common in enterprise-driven vs. community-driven open source projects.
voctor 2y ago
Not related at all but the elephant illustration remind me of the Grand Elephant https://www.lesmachines-nantes.fr/en/discover/the-grand-elephant/ https://www.lesmachines-nantes.fr/en/discover/the-grand-elep...
deleted 2y ago
[deleted]
fforflo 2y ago
Apart from the usual advice to "start working on a patch to learn the internals," which is not as easy to do because it's a mature product and most of the low-hanging fruits have been reaped, I'd also suggest checking the extensions shipped with the main source tree under $top/contrib. Each one is self-contained and usually works with a subset of the internals. Some good ones: pageinspect, pg_buffercache, pg_prewarm, pg_statstatements, pg_walinspect
tracker1 2y ago
OT: Ug the text contrast is really hard to read here.

194 points by stevenmh 2y ago

This is an old, but great and regularly updated, source of information about this great RDBMS. A lot of knowledge is accumulated there.

brasetvik 2y ago

Also recommend this free book: https://postgrespro.com/community/books/internals https://postgrespro.com/community/books/internals

codepathfinder 2y ago

Thanks for sharing. I have been digging deeper around database internals and looks amazing.

topherjaynes 2y ago
Check out the CMU history of the DB class, Andy Pavlo is an amazing teacher and you really get a feel for DBs https://www.youtube.com/watch?v=LWS8LEQAUVc&ab_channel=CMUDatabaseGroup https://www.youtube.com/watch?v=LWS8LEQAUVc&ab_channel=CMUDa... same class from 5 years ago is great because he's like well I can't get in my hotel room so I'll just record from the street https://www.youtube.com/watch?v=SdW5RKUboKc&t=797s&ab_channel=CMUDatabaseGroup https://www.youtube.com/watch?v=SdW5RKUboKc&t=797s&ab_channe.... Tons of good videos to go through on their youtube channel.
- codepathfinder 2y ago
  Thanks a ton!

rrr_oh_man 2y ago

> Transaction logs are an essential part of databases because they ensure that no data is lost even when a system failure occurs. They are a history log of all changes and actions in a database system. This ensures that no data is lost due to failures, such as a power failure or a server crash. The log contains sufficient information about each transaction that has already been executed, so the database server can recover the database cluster by replaying the changes and actions in the transaction log in the event of a server crash. The writing is horrendous

tasuki 2y ago
The author is Japanese, cut them some slack.
- xnickb 2y ago
  Or better yet, offer help
  - rrr_oh_man 2y ago
    In what way? First thought: If a sports team is not playing well, do you mail in coaching advice? I elaborated a bit here, though: https://news.ycombinator.com/item?id=43254469 https://news.ycombinator.com/item?id=43254469
- rrr_oh_man 2y ago
  Thanks for pointing this out. I probably should have, being a non-native speaker myself. Though, the core of my issue with this is not the language itself, but how the paragraphs are structured. Imho you might cut 50% of words and not lose any information. See here: https://news.ycombinator.com/item?id=43254469 https://news.ycombinator.com/item?id=43254469
paulddraper 2y ago
What makes the writing horrendous?
- shxx 2y ago
  If rrr_oh_man's profile is real (https://bit.ly/48d9o9P https://bit.ly/48d9o9P), his resume appears inconsistent and unprofessional. The emptier a person is, the more likely they are to criticize others.
  - deleted 2y ago
    [deleted]
  - rrr_oh_man 2y ago

nimish 2y ago

The worst part of Postgres (and linux) is the awful mailing list driven development process. I have patches to Postgres that I'd rather just keep porting up every version than to try to figure out how to make Outlook work with a 1980s process. The internals themselves are quite nicely engineered as you'd expect

felideon 2y ago
> how to make Outlook work Well that’s your first problem. Unless you’re using corporate email?
- nimish 2y ago
  Correct. It's corporate email. Imagine the amount of contributions that are gated behind stupid email policies because some fossil mailing list client can't handle modern email in the year of our lord 2025
  - pritambaral 2y ago
    > Outlook > modern email That can't be true. Now, I haven't used Outlook myself, but I've received enough garbage from that godforsaken relic of a time when MS wanted to EEE email to know that that can't be true. FWIW, I've posted patches to the PG mailing list just fine with GMail. The web app, that is.
    - nimish 2y ago
      OK. Every major corporation uses Outlook and there is no way I can change that so by not accepting its foibles you are artificially stopping interested contributors who would do it on company time and hardware It's a very FOSS pathology.
      - majewsky 2y ago
        If by "pathology" you mean "we optimize for the comfort of the people already actively engaged in the project instead of hypothetical future contributors", I don't think this argument is as strong as you think it is. If anything, this kind of excessive legal and process red tape is much more common in enterprise-driven vs. community-driven open source projects.

voctor 2y ago

Not related at all but the elephant illustration remind me of the Grand Elephant https://www.lesmachines-nantes.fr/en/discover/the-grand-elephant/ https://www.lesmachines-nantes.fr/en/discover/the-grand-elep...

deleted 2y ago

[deleted]

fforflo 2y ago

Apart from the usual advice to "start working on a patch to learn the internals," which is not as easy to do because it's a mature product and most of the low-hanging fruits have been reaped, I'd also suggest checking the extensions shipped with the main source tree under $top/contrib. Each one is self-contained and usually works with a subset of the internals. Some good ones: pageinspect, pg_buffercache, pg_prewarm, pg_statstatements, pg_walinspect

tracker1 2y ago

OT: Ug the text contrast is really hard to read here.