5 ms·

The Design of Postgres (1986) [pdf]

218 points by craigkerstiens 4y ago

mistrial9 4y ago
now playing at UC BERKELEY DATA SYSTEMS AND FOUNDATIONS https://hydro.run/ https://hydro.run/ https://epic.berkeley.edu/ https://epic.berkeley.edu/ https://sky.cs.berkeley.edu/ https://sky.cs.berkeley.edu/ https://slice.eecs.berkeley.edu/ https://slice.eecs.berkeley.edu/
- deleted 4y ago
  [deleted]
groestl 4y ago
I found typos, I wonder if they published it already :)
- deepsun 4y ago
  Send them a patch :)
amw-zero 4y ago
Postgres is good
- chrislan815 4y ago
  word
- koolba 4y ago
  Postgres is good because Postgres is great.
janfb 4y ago
It took me a long time to understand why we write academic articles like we do. But reading classic articles like this one really puts it into perspective for me. It is a clean and simple way to preserve knowledge over decades. Something that no blog or wiki page could guarantee.
- offmycloud 4y ago
  I think that the most annoying omission from the standard academic paper format is the year of publication.
  - DiggyJohnson 4y ago
    What fields do you see this in? I read a lot of social science-y academic articles for research for a nonfiction book I’m writing, and I would be very frustrated if a date wasn’t included at the front of the article. Though I’m not sure if I’ve been lulled into a fall sense of security by finding the date on the cover sheet of the PDF (prepended by the article’s publisher/distributer, I imagine). In no way am I disagreeing with you, more sharing the fact that I’d be surprised and frustrated to come across this omission. Author and publishing date are arguably more important than the title, in my opinion - which now that I think about it is probably why these are the metadata we use to index citations.
    - anyfoo 4y ago
      Computer Science/Math. Here's an almost completely random example, I literally searched for "graph coloring paper" just because that was a subject I could think of top of my head, and found this as first hit: https://www.cs.cmu.edu/~avrim/Papers/coloring_worstcase.pdf https://www.cs.cmu.edu/~avrim/Papers/coloring_worstcase.pdf Yep, no date.
      - maxov 4y ago
        In case you do not know, I find Google Scholar to be pretty useful for finding dates and other information about the paper - e.g. in this case https://scholar.google.com/scholar?hl=en&as_sdt=0%2C14&q=New+Approximation+Algorithms+for+Graph+Coloring&btnG= https://scholar.google.com/scholar?hl=en&as_sdt=0%2C14&q=New... I work with papers in CS/math and have been able to find dates and metadata like DOI pretty quick. There are complications when there are really multiple versions of a paper, like one in a conference that is only an extended abstract and one in a journal, but you would need to figure out which version you want to read in that situation anyway. I agree it's annoying, but coupled with reference manager software like Zotero it's hardly an issue for me.
papito 4y ago
Michael Stonebraker is a prodigy in this field. He did something very rare - he took a concept deep within the academia and made it a blockbuster product in the real world. The man writes small databases for fun over a weekend, I mean, come on. His interview on Software Engineering Radio is still one of the best episodes I have listened to. Decades of wisdom, right there: https://www.se-radio.net/2013/12/episode-199-michael-stonebraker/ https://www.se-radio.net/2013/12/episode-199-michael-stonebr...
- apavlo 4y ago
  > The man writes small databases for fun over a weekend, I mean, come on. I am a student of Mike. He is a great man, but he would not call himself a programmer.
  - DiggyJohnson 4y ago
    What would he call himself then?
    - riceart 4y ago
      Mike, usually.
- arminiusreturns 4y ago
  This is the reason I am so fascinated with both voltdb and scidb... having read the articles he and his teams were pushing (more for volt and their commercial side) I saw that he was in a different league than others and the insights and comments he made about nosql and variants resonated deeply as a sysadmin type who has to suffer the slings and arrows of so many db systems.
davidw 4y ago
So... some of these DB objects are in turn more DB commands, so that if you fetch the thing, it fetches the underlying things? I don't know much about DB design/implementation but that seems kind of wild.
- gavinray 4y ago
  It gets hairier than this. For example, in Postgres, the place where all the metadata is stored that the DB uses in internal business logic is not regular C structs, but as rows in special tables (relations). Like say you want to look up the physical file location of a table, using its ID (something Postgres functions have to do internally at various points). It does this by reading from a relation. It's sort of like a compiler that compiles itself. It can't always have been that way, so I have to wonder at what point the codebase swapped over to doing it this way.
  - derefr 4y ago
    > It can't always have been that way Sure it could have. 1. Start with a library version of just the storage engine; 2. write some CLI tools that use that library to direct-manipulate table heap files, batch import/export between them and serialization formats, etc — y'know, like these ones for BDB (which was the SQLite of its day, and contemporary to Postgres at Berkeley) — https://docs.oracle.com/cd/E17076_05/html/api_reference/C/utilities.html https://docs.oracle.com/cd/E17076_05/html/api_reference/C/ut... ; 3. define the core system-schema tables for the DBMS, and the required static data to go in them; 4. write a script that uses the CLI tools to bootstrap a "database" (directory of loose heap files) using that schema — call it initdb(1); 5. and now start writing the rest of the DBMS, which can now take "there is an initialized cluster with a populated system schema" as an axiomatic assumption, opening it and reading important state and configuration from said schema files from almost the first line of code. (I have no special knowledge of PG's earliest versions, so this might be a just-so story; but today, initdb(1) is a separate tool with separate code that doesn't come from some library that also gets compiled into the PG postmaster. And that's not the sort of design you would move to, if you had started out with a design where the postmaster could bootstrap an empty cluster directory into existence on startup.)
davidkuennen 4y ago
Wow, Postgres is older than me. I never knew. Love Postgres and use is for basically all of my projects.
- jeltz 4y ago
  The open source project is much younger, from 1995. I know some people who used the first open source version, Postgres95, back then.
lucasfcosta 4y ago
At this point, Postgres is probably the piece of software whose presence is almost unavoidable in most projects. It’s fascinating to see the way it’s stood the test of time.
captrb 4y ago
I'd forgotten the bit in here about the storage hierarchy, especially optical disks and immutable storage. I'm finding the modern "big data" stack with blob stores, immutable storage, and neutral formats (Parquet, Avro) to be useful, especially when it can be seamlessly read from a DBMS instance with its own optimized storage (Redshift, Vertica, and others can do this). However, I miss Postgresql's types and planner. From afar, it seems like Postgresql would be a perfect fit into this tiered ecosystem. Having a "hot set" in Postgresql and the "cold set" in a blob store for cost optimization in particular... as long as PG could use parallel query to maximize throughput over the higher-latency connection.

now playing at UC BERKELEY DATA SYSTEMS AND FOUNDATIONS https://hydro.run/ https://hydro.run/ https://epic.berkeley.edu/ https://epic.berkeley.edu/ https://sky.cs.berkeley.edu/ https://sky.cs.berkeley.edu/ https://slice.eecs.berkeley.edu/ https://slice.eecs.berkeley.edu/

deleted 4y ago
[deleted]

groestl 4y ago

I found typos, I wonder if they published it already :)

deepsun 4y ago
Send them a patch :)

amw-zero 4y ago

Postgres is good

chrislan815 4y ago
word
koolba 4y ago
Postgres is good because Postgres is great.

janfb 4y ago

It took me a long time to understand why we write academic articles like we do. But reading classic articles like this one really puts it into perspective for me. It is a clean and simple way to preserve knowledge over decades. Something that no blog or wiki page could guarantee.

offmycloud 4y ago
I think that the most annoying omission from the standard academic paper format is the year of publication.
- DiggyJohnson 4y ago
  What fields do you see this in? I read a lot of social science-y academic articles for research for a nonfiction book I’m writing, and I would be very frustrated if a date wasn’t included at the front of the article. Though I’m not sure if I’ve been lulled into a fall sense of security by finding the date on the cover sheet of the PDF (prepended by the article’s publisher/distributer, I imagine). In no way am I disagreeing with you, more sharing the fact that I’d be surprised and frustrated to come across this omission. Author and publishing date are arguably more important than the title, in my opinion - which now that I think about it is probably why these are the metadata we use to index citations.
  - anyfoo 4y ago
    Computer Science/Math. Here's an almost completely random example, I literally searched for "graph coloring paper" just because that was a subject I could think of top of my head, and found this as first hit: https://www.cs.cmu.edu/~avrim/Papers/coloring_worstcase.pdf https://www.cs.cmu.edu/~avrim/Papers/coloring_worstcase.pdf Yep, no date.
    - maxov 4y ago
      In case you do not know, I find Google Scholar to be pretty useful for finding dates and other information about the paper - e.g. in this case https://scholar.google.com/scholar?hl=en&as_sdt=0%2C14&q=New+Approximation+Algorithms+for+Graph+Coloring&btnG= https://scholar.google.com/scholar?hl=en&as_sdt=0%2C14&q=New... I work with papers in CS/math and have been able to find dates and metadata like DOI pretty quick. There are complications when there are really multiple versions of a paper, like one in a conference that is only an extended abstract and one in a journal, but you would need to figure out which version you want to read in that situation anyway. I agree it's annoying, but coupled with reference manager software like Zotero it's hardly an issue for me.

papito 4y ago

Michael Stonebraker is a prodigy in this field. He did something very rare - he took a concept deep within the academia and made it a blockbuster product in the real world. The man writes small databases for fun over a weekend, I mean, come on. His interview on Software Engineering Radio is still one of the best episodes I have listened to. Decades of wisdom, right there: https://www.se-radio.net/2013/12/episode-199-michael-stonebraker/ https://www.se-radio.net/2013/12/episode-199-michael-stonebr...

apavlo 4y ago
> The man writes small databases for fun over a weekend, I mean, come on. I am a student of Mike. He is a great man, but he would not call himself a programmer.
- DiggyJohnson 4y ago
  What would he call himself then?
  - riceart 4y ago
    Mike, usually.
arminiusreturns 4y ago
This is the reason I am so fascinated with both voltdb and scidb... having read the articles he and his teams were pushing (more for volt and their commercial side) I saw that he was in a different league than others and the insights and comments he made about nosql and variants resonated deeply as a sysadmin type who has to suffer the slings and arrows of so many db systems.

davidw 4y ago

So... some of these DB objects are in turn more DB commands, so that if you fetch the thing, it fetches the underlying things? I don't know much about DB design/implementation but that seems kind of wild.

gavinray 4y ago
It gets hairier than this. For example, in Postgres, the place where all the metadata is stored that the DB uses in internal business logic is not regular C structs, but as rows in special tables (relations). Like say you want to look up the physical file location of a table, using its ID (something Postgres functions have to do internally at various points). It does this by reading from a relation. It's sort of like a compiler that compiles itself. It can't always have been that way, so I have to wonder at what point the codebase swapped over to doing it this way.
- derefr 4y ago
  > It can't always have been that way Sure it could have. 1. Start with a library version of just the storage engine; 2. write some CLI tools that use that library to direct-manipulate table heap files, batch import/export between them and serialization formats, etc — y'know, like these ones for BDB (which was the SQLite of its day, and contemporary to Postgres at Berkeley) — https://docs.oracle.com/cd/E17076_05/html/api_reference/C/utilities.html https://docs.oracle.com/cd/E17076_05/html/api_reference/C/ut... ; 3. define the core system-schema tables for the DBMS, and the required static data to go in them; 4. write a script that uses the CLI tools to bootstrap a "database" (directory of loose heap files) using that schema — call it initdb(1); 5. and now start writing the rest of the DBMS, which can now take "there is an initialized cluster with a populated system schema" as an axiomatic assumption, opening it and reading important state and configuration from said schema files from almost the first line of code. (I have no special knowledge of PG's earliest versions, so this might be a just-so story; but today, initdb(1) is a separate tool with separate code that doesn't come from some library that also gets compiled into the PG postmaster. And that's not the sort of design you would move to, if you had started out with a design where the postmaster could bootstrap an empty cluster directory into existence on startup.)

davidkuennen 4y ago

Wow, Postgres is older than me. I never knew. Love Postgres and use is for basically all of my projects.

jeltz 4y ago
The open source project is much younger, from 1995. I know some people who used the first open source version, Postgres95, back then.

lucasfcosta 4y ago

At this point, Postgres is probably the piece of software whose presence is almost unavoidable in most projects. It’s fascinating to see the way it’s stood the test of time.

captrb 4y ago

I'd forgotten the bit in here about the storage hierarchy, especially optical disks and immutable storage. I'm finding the modern "big data" stack with blob stores, immutable storage, and neutral formats (Parquet, Avro) to be useful, especially when it can be seamlessly read from a DBMS instance with its own optimized storage (Redshift, Vertica, and others can do this). However, I miss Postgresql's types and planner. From afar, it seems like Postgresql would be a perfect fit into this tiered ecosystem. Having a "hot set" in Postgresql and the "cold set" in a blob store for cost optimization in particular... as long as PG could use parallel query to maximize throughput over the higher-latency connection.