[HN Gopher] The Internals of PostgreSQL
___________________________________________________________________
The Internals of PostgreSQL
Author : kklisura
Score : 202 points
Date : 2022-01-26 15:08 UTC (7 hours ago)
(HTM) web link (www.interdb.jp)
(TXT) w3m dump (www.interdb.jp)
| kklisura wrote:
| > ...postgres server process listens to one network port, the
| default port is 5432
|
| > Whenever receiving a connection request from a client, it
| starts a backend process. (And then, the started backend process
| handles all queries issued by the connected client.)
|
| > To achieve this [server] starts ("forks") a new process for
| each connection. From that point on, the client and the new
| server process communicate without intervention by the original
| postgres process. Thus, the master server process is always
| running, waiting for client connections, whereas client and
| associated server processes come and go. [1]
|
| So, Postgres is using process-per-connection model. Can some
| explain why this is? And why not something like thread-per-
| connection?
|
| [1] https://www.postgresql.org/docs/9.6/tutorial-arch.html
| outworlder wrote:
| > So, Postgres is using process-per-connection model. Can some
| explain why this is? And why not something like thread-per-
| connection?
|
| Thread vs process under Linux is kind of a potato potato thing.
| They are essentially the same thing - based on the flags
| provided when you create the process, you can indicate if you
| want things like shared memory or not. But they are ultimately
| the same construct, with similar overhead. Plus, fork() is
| incredibly useful.
|
| It makes sense, for such an important piece of software, to
| minimize shared memory.
| Ostrogodsky wrote:
| The FAQ section is refreshingly and hilariously honest:
|
| https://www.interdb.jp/pg/faq.html
| dang wrote:
| Past related threads:
|
| _The internals of PostgreSQL_ -
| https://news.ycombinator.com/item?id=13488315 - Jan 2017 (53
| comments)
|
| _The Internals of PostgreSQL_ -
| https://news.ycombinator.com/item?id=12142364 - July 2016 (1
| comment)
| trulyme wrote:
| The license is interesting:
|
| > If you work at Amazon, you cannot use and refer to this
| document because of the copyright violation issues.
|
| I wonder which one of the Amazon's missteps triggered the OP's
| ire. :)
| nexuist wrote:
| The real question is does this apply to the warehouse workers
| as well?
| [deleted]
| hbgl wrote:
| It shows that OP clearly has no idea about how copyright law
| works. Everybody can use the work under fair use and you cannot
| restrict people from posting links to your site based on
| copyright. It seems like the author has a god complex. "I
| create therefore I shall command."
| grantjpowell wrote:
| I constantly recommend this at work. The specific content isn't
| super helpful to Saas day to day development, but for me it built
| an intuition about postgres that has been invaluable. I think
| once I understood the "heart and soul" of postgres, the heap and
| the mvcc, many other properties about the database just "clicked"
| in my head.
| rattray wrote:
| Do you remember which chapters (or other resources) talk about
| heap & mvcc specifically?
| luhn wrote:
| The heap is described in 1.3 [1] and MVCC is described in
| Chapter 5 [2]. I'd also recommend the Postgres docs [3], they
| aren't quite as accessible as this but are detailed and
| overall well-written.
|
| [1] https://www.interdb.jp/pg/pgsql01.html#_1.3.
|
| [2] https://www.interdb.jp/pg/pgsql05.html
|
| [3] https://www.postgresql.org/docs/14/internals.html
| jjice wrote:
| Part 3.1.1 is about the parser, which uses lex and yacc. I
| actually used it as a source to convince a professor to let us
| use lex and yacc to build our lexer and parser for a SQL engine
| for a DB implementation class I took. His expectations were a bit
| unrealistic for what a bunch of seniors would be able to
| accomplish in three weeks in C (other similar level of difficulty
| classes and job searching weigh a real burden), and he initially
| said no to me asking if we could use lex and yacc. Postgres's
| source made a convincing enough argument. Gave me a reason to
| learn lex and yacc, as well as making a much easier to modify
| parser. I wrote the parser for my group, and we were the only
| group that finished the parser in time, and at all.
|
| Just an anecdote with some fond memories that were made possible
| by Postgres and its internals. Postgres has a place in my heart
| for that, and being a damn fine DB of course!
| eatonphil wrote:
| What did he want you to do initially? Handwritten?
| jjice wrote:
| Handwritten. It worked for the first portion of the parser
| (DDL), but it was a real pain to debug and build while
| keeping track of mallocing and freeing along the way.
|
| We got an A on that first parser that was hand written as
| well, but once we got to DML, I felt like we could really use
| tools given our time frame.
| stingraycharles wrote:
| It sounds a bit silly to implement a parser for a DB
| internals course, though. You'll end up teaching lexing and
| parsing (something which the professor wasn't too familiar
| with himself it seems?), rather than db internals.
|
| Implementing your own WAL + storage API for example, is
| more something along the lines I would expect.
| jjice wrote:
| Absolutely. That was one of my complaints with the class.
| I have an interest in compilers and the process, but I
| took this course to learn about storage and efficient
| lookups.
| stickfigure wrote:
| Agreed. At my university, in my era (almost 30 years
| ago), the compilers sequence was two full quarters and
| generally considered to be the hardest classes in the CSc
| major. I really loved it but it would have been a huge
| distraction mixed in with the DB classes.
|
| Those compilers classes turned out to be pretty useful. I
| ended up having to build custom parsers several times in
| my career... including an SQL parser.
| dtech wrote:
| to be pair those compilers course should cover much more
| than parsing. Iirc correctly it was like 10% of the
| course at my time.
| jjice wrote:
| I actually took my university's compiler course at the
| same time as this DB course, and that was my favorite
| class out of all four years, hands down. Honestly, the
| parsing portion of my DB class was helped out by the
| knowledge I got from the compiler class earlier in the
| semester, so at least it worked out!
| john567 wrote:
| I think it's important to write your own parser, at some point.
| Though, for the purpose of learning you need someone to guide
| you.
|
| If you're learning this stuff you need a simple challenge to
| work through the end.
|
| With regards to lex and yacc, I absolut detest these tools.
| They are horrible but they do work. And if you just want a
| functioning parser they'll do. My main criticism of these tools
| are the horrible errors you might end up with and the lack of
| sensible extension points.
|
| If it is your first foray into parsers, I do think a simple
| grammar and handwritten lexer/parser is a good first step.
| Iterate some on that, then use tools to help with the verbose
| stuff.
|
| Though to be honest, I enjoy writing parsers by hand. So, I'm a
| bit biased.
| jjice wrote:
| So we did build the DDL handwritten and did well on that
| portion of the product, but given the constraint of me being
| the only person to write the parser for the DML, along with
| having to be done and error free in three weeks where I was
| also occupied, all while being written in C, I felt yacc
| really helped a lot there.
|
| The big thing for this particular project was all the
| pitfalls of C with having to track memory allocations and
| passing arrays of pointers around and mentally keeping track.
| Yacc streamlined this a lot. If this was a production project
| and not a class I took along side four other, hand writing
| would be a serious option I'd consider (mostly for error
| reporting purposes like you mentioned), but I might even lean
| towards getting an MVP done in a parser generator and
| eventually converting over to handwritten if the need arose.
| outworlder wrote:
| > and he initially said no to me asking if we could use lex and
| yacc
|
| What?
|
| On my compilers class, I was explicitly asked to use lex and
| yacc. Sure, for the very first assignment on that class we were
| asked to write the lexer by hand. But when it came to actually
| do the interesting parts, lex and yacc it was.
| dboreham wrote:
| This seems to have been subject to waxing and waning fashion.
| I had a similar experience recently -- someone sent me a PR
| with a hand-written parser. I commented "do people write
| parsers by hand any more?". After some push back, I searched
| online and found a ton of articles "why you should write your
| own parser and not use a parser generator". Still beats me as
| to why though.
___________________________________________________________________
(page generated 2022-01-26 23:00 UTC)