7 ms·

Anyone has an idea on what makes postgresql both such a robust and always evolving db ? i’m amazed at the pace at which they’re adding deep features ( such as j

by bsaul 8y ago

Anyone has an idea on what makes postgresql both such a robust and always evolving db ? i’m amazed at the pace at which they’re adding deep features ( such as jit) without breaking anything release after release, in an open source environment. Usually this kind of things are the consequences of a good architecture but i wonder if anyone has an more in-depth explanation on what in the architecture makes it that good.

iagooar 8y ago
I guess it's simple: they have world-class engineers behind it. Of the smartest people in tech, that truly understand their craft. That, and a broad, open community.
- Coding_Cat 8y ago
  And probably (because of that) a very robust codebase-base. Adding a JIT to a 'textual query'->'results' pipeline is a lot easier if you took the effort to silo off each individual step than it is when you consider 'text'->'result' to be one fixed step. Even if the initial partioning is a lot of work. (pure speculation, I haven't seen their code yet, but I am curious now).
- abledon 8y ago
  You gotta be insanely good and enjoy your craft to enjoy writing low level DB code for open source lol
  - simias 8y ago
    DBs might not be glamorous but I'm sure there are many very interesting problems to solve when developing them. Of course on such a large, old project there's got to be a lot of tedious maintenance work as well, but that's true for all software in my experience. It's often a conversation I have with people looking to get into software development. Often they'll aim for video games or something like that but I often warn them that it might not be nearly as cool as they imagine. You're more likely to end up scripting crappy menu systems than being the next Carmack. On the other hand some of the most interesting pieces of software I've written were for very unsexy industrial applications. And I actually have good working conditions unlike people working in the videogame industry apparently.
    - pizza234 8y ago
      Often they'll aim for video games or something like that but I often warn them that it might not be nearly as cool as they imagine. You're more likely to end up scripting crappy menu systems than being the next Carmack. Interestingly, Carmack himself said if he wouldn't write videogames, he would work on databases. I can't find the exact quote, but here's a similar reference: I enjoy lots of different areas of it [programming]... I'm taking a great deal of enjoyment writing device drivers for Linux. I could also be having a good time writing a database manager or something because there are always interesting problems.
      - Twisell 8y ago
        And I recently had mindblowing moment when I realized that Civilization VI is heavily SQL based. I didn’t have time to dive into it but the Graphical UI is basically an interface to visualize and update a database. Seems afterwards quite logical and clever considering the turn based gameplay.
        abledon 8y ago
        Now just design a civ style game with sql command-line interface but fancy autocomplete bright warcarft3 colors and a fun storyline . Have a generation of gamers learn sql
  - jeltz 8y ago
    It varies a lot. Many parts of the code base are very accessible and easy to modify for almost anyone, while some parts, especially the query planner, contain some of the most advanced code I have worked with.
  - pgaddict 8y ago
    I'm not sure I understand the "open source" part - it's not as if we do it for free. I'd say for most of the senior PostgreSQL engineers / contributors it's part of their paid jobs. We either work for companies providing various PostgreSQL services, or for companies using PostgreSQL. We might do the same type of work on a proprietary database, but that would lack the community/cooperation aspect of the project. Of course, enjoying the craft is somewhat natural in both cases. And yes, it's a bit scary how smart some of the contributors are.
zrail 8y ago
The PostgreSQL ethos, from the very beginning, has been "make it correct, then make it fast". I don't know a lot about the team structure but I do know they don't have much in the way of formal process. It's just a team of competent professionals making the world a better place.
- mason55 8y ago
  Also the initial release of features is usually very minimal, almost to the point of not being usable in the real world except for a very targeted use case. Then things sloowwwwly expand to cover more and more things as people have time to test and review. And if your patch adds a good feature but in a way that is bad architecturally then it will be rejected or fully rewritten with no care about what that means from a timing perspective. It means that it takes awhile for things like parallel query to really become valuable but when they do you know they work and work well.
  - sroussey 8y ago
    The partition by hash is a good example in this release. I wondered by it wasn’t done when ranges were done, but patience is a virtue.
  - ecnahc515 8y ago
    Yep, it's honestly the epitome of release early and release often applied to something mission critical like a DB. By the time it's in a release, even the minimal version of a feature is pretty good quality, but just not necessarily the full-feature yet, so people can still test it and rely on it like it's a full stable feature missing minor features.
devilmoon 8y ago
Not my personal experience, but one of my professors is trying to get his patch approved into Postgres for an upcoming release; since his changes are very deep and touch the kernel (he wants to add support for temporal data) he told me the whole process will take years and the whole community will examine these changes before anything is added. I think that pretty much explains it: Cutting edge features are proposed way earlier than commercial DBs start thinking about implementing them, and the community hones them over years before they are released to the mainstream public.
- bunderbunder 8y ago
  This mirrors my experience working at a company that achieved similar results on some of its internal products. The very biggest difference between working there and working anywhere else, is that developers spent only a tiny fraction of time actually writing code. The biggest chunk of it was spent on peer review. Every change required sign-off from everyone on the immediate team, and, if it was being made to a component that other teams interacted with, they'd also have members reviewing the changes. And, as a newcomer, I found the reviews to be brutal. Over time, though, I came to recognize that what originally felt like others getting all up in my business about pedantic little variable naming issues was actually everyone having my back about legitimate maintainability concerns. Next biggest chunk was spent on planning - understanding requirements, talking over changes with anyone who might be impacted by them, etc. The second biggest difference was just how fast we could move. I think a lot of that came from a certain "wei wu wei" that was attributable to the deep understanding of the systems we were working on that developed out of all that time with our hands off our keyboards. Allow me the vice of inserting a mangled Hitchhiker's quote here: "If human beings don’t keep exercising their [fingers], he thought, their [hands] probably seize up. After a few months’ consideration and observation he abandoned this theory in favor of a new one. If they don’t keep on exercising their [fingers], he thought, their brains start working."
- jeffdavis 8y ago
  What is the professor's name?
- tomnipotent 8y ago
pgaddict 8y ago
Good question. I'm contributing to postgres for quite a few years and I still don't have a clear answer to that. It certainly is not a single discrete reason, but IMHO a combination of various factors: 1) careful step-by-step engineering (vision, reviews, bug-fixing, ...) 2) open and civil community 3) not founded/owned by particular company (no one has the upper hand, companies serve customers with different needs, compete but also cooperate, ...) 4) code / documentation quality I could probably go on, but those four are probably the most important ones. (At least for me.)
- hi41 8y ago
  Congratulations to the postgres team. How do you create such a high quality product when you have to understand the code written by so many people? I take a long time to understand what someone else has written.
  - pgaddict 8y ago
    It's not like every contributor (or even committer) understands all parts of the code base - I certainly don't. People usually start by writing external code (e.g. by writing extensions in C), learn the code style and basic constructs (memory context management, data types, etc.) and then gradually expand their knowledge by working on stuff closer to core and/or reviewing stuff that interests them. At least that's how it worked for me and a couple of people around me. I don't think it's particularly specific to Postgres. Regarding the high quality, we don't do anything magical - we simply apply well-known tools in a careful and methodical way. 1) careful reviews - Not just the code, but appropriate tests, comments and docs, and most importantly overall design/direction. It's not uncommon to reject the patch on the basis that there's a better approach, and get it reworked from scratch. Evan patches from the most senior contributors generally go through it too (with the exception of bugfixes, ...). 2) not accepting technical debt - No quick hacks that would make our life harder down the road. No features that are arbitrarily incompatible with other features. No compiler warnings. 3) incremental development - Split large patches, even if it means it's not really useful initially. 4) testing - We have a range of tests, that are executed on a variety of machines/platforms/compilers ... (https://buildfarm.postgresql.org/ https://buildfarm.postgresql.org/) 5) adopt useful technologies - static analysis, test coverage, valgrind, fuzzing (sqlsmith found quite a few bugs), ... 6) attention to bug reports / fixing them promptly (instead of just stashing them somewhere forever) I'm sure there are many additional things contributing to the quality. In any case, there's no secret sauce, except for applying those principles consistently.
qaq 8y ago
I think there are many factors on tech. side it's pretty modular and has very detailed comments in the source code, on the ecosystem side there is no single entity behind PG so it created an ecosystem where multiple entities can contribute and build products and services around PostgreSQL.
vikrantrathore 8y ago
I think the simple answer is philosophy of Kaizen(改善)- continuous improvement. I started working with postgresql when it was just postgres95. Recently found that my earliest question on postgresql-users was 13th October 1998, 20 years ago (https://postgrespro.com/list/id/3.0.2.32.19981013214318.006939a4@chemquick.com https://postgrespro.com/list/id/3.0.2.32.19981013214318.0069...). I still use PostgreSQL in my startup for almost all critical production workload. This in itself a testament to its improvements. I remember I starting with c libraries of msql and postgres95 and continued to work with them as they evolved into postgreSQL and a new kid on the block mysql. I liked postgreSQL due to its adherence to SQL. Since I like working with relational algebra. So even though MySQL was more popular in later years, I still used PostgreSQL more often, except for building qmail servers with mySQL backend.
- Koshkin 8y ago
  > Kaizen This is the last call of a hungry manager crowing on top of the body of an already dead software project.
  - zephharben 8y ago
    This might be valid if we were discussing an internal, proprietary software project. We're talking about an open source project used by millions of people that has gotten CONTINUOUSLY better over the course of decades - to the point where it is now - arguably - the best of its kind. Given this track record, I think postgres devs are entitled to use whatever management-speak euphemism they like. In this case, the idiom's original meaning/intent seems apt.
jeffdavis 8y ago
My opinion (as a community member and contributor): PostgreSQL is willing to accept delays in some features -- even if highly desired -- to enable high overall feature development and quality. Also, more developers are willing to put time into refactoring and simplifying what they need before starting on their project. In other words the groundwork is laid first and then the new feature. Another thing that might help is that there is no bug tracker. Bugs are fixed and the fixes backported after a report comes in. Bugs don't end up stuck in a backlog.
atomic77 8y ago
On that note, I stumbled on an LWN article a couple of years ago that made me reflect on this miraculous balance of new features and stability: https://lwn.net/Articles/645020/ https://lwn.net/Articles/645020/ Anyone more involved in the pgsql community able to comment on how this balance is being struck? It doesnt' seem like the release schedule has changed much, though the version numbers seem to be incrementing faster (no v10.1, 10.2, etc?)

by bsaul 8y ago

iagooar 8y ago

I guess it's simple: they have world-class engineers behind it. Of the smartest people in tech, that truly understand their craft. That, and a broad, open community.

Coding_Cat 8y ago
And probably (because of that) a very robust codebase-base. Adding a JIT to a 'textual query'->'results' pipeline is a lot easier if you took the effort to silo off each individual step than it is when you consider 'text'->'result' to be one fixed step. Even if the initial partioning is a lot of work. (pure speculation, I haven't seen their code yet, but I am curious now).
abledon 8y ago
You gotta be insanely good and enjoy your craft to enjoy writing low level DB code for open source lol
- simias 8y ago
  DBs might not be glamorous but I'm sure there are many very interesting problems to solve when developing them. Of course on such a large, old project there's got to be a lot of tedious maintenance work as well, but that's true for all software in my experience. It's often a conversation I have with people looking to get into software development. Often they'll aim for video games or something like that but I often warn them that it might not be nearly as cool as they imagine. You're more likely to end up scripting crappy menu systems than being the next Carmack. On the other hand some of the most interesting pieces of software I've written were for very unsexy industrial applications. And I actually have good working conditions unlike people working in the videogame industry apparently.
  - pizza234 8y ago
    Often they'll aim for video games or something like that but I often warn them that it might not be nearly as cool as they imagine. You're more likely to end up scripting crappy menu systems than being the next Carmack. Interestingly, Carmack himself said if he wouldn't write videogames, he would work on databases. I can't find the exact quote, but here's a similar reference: I enjoy lots of different areas of it [programming]... I'm taking a great deal of enjoyment writing device drivers for Linux. I could also be having a good time writing a database manager or something because there are always interesting problems.
    - Twisell 8y ago
      And I recently had mindblowing moment when I realized that Civilization VI is heavily SQL based. I didn’t have time to dive into it but the Graphical UI is basically an interface to visualize and update a database. Seems afterwards quite logical and clever considering the turn based gameplay.
      - abledon 8y ago
        Now just design a civ style game with sql command-line interface but fancy autocomplete bright warcarft3 colors and a fun storyline . Have a generation of gamers learn sql
- jeltz 8y ago
  It varies a lot. Many parts of the code base are very accessible and easy to modify for almost anyone, while some parts, especially the query planner, contain some of the most advanced code I have worked with.
- pgaddict 8y ago
  I'm not sure I understand the "open source" part - it's not as if we do it for free. I'd say for most of the senior PostgreSQL engineers / contributors it's part of their paid jobs. We either work for companies providing various PostgreSQL services, or for companies using PostgreSQL. We might do the same type of work on a proprietary database, but that would lack the community/cooperation aspect of the project. Of course, enjoying the craft is somewhat natural in both cases. And yes, it's a bit scary how smart some of the contributors are.

zrail 8y ago

The PostgreSQL ethos, from the very beginning, has been "make it correct, then make it fast". I don't know a lot about the team structure but I do know they don't have much in the way of formal process. It's just a team of competent professionals making the world a better place.

mason55 8y ago
Also the initial release of features is usually very minimal, almost to the point of not being usable in the real world except for a very targeted use case. Then things sloowwwwly expand to cover more and more things as people have time to test and review. And if your patch adds a good feature but in a way that is bad architecturally then it will be rejected or fully rewritten with no care about what that means from a timing perspective. It means that it takes awhile for things like parallel query to really become valuable but when they do you know they work and work well.
- sroussey 8y ago
  The partition by hash is a good example in this release. I wondered by it wasn’t done when ranges were done, but patience is a virtue.
- ecnahc515 8y ago
  Yep, it's honestly the epitome of release early and release often applied to something mission critical like a DB. By the time it's in a release, even the minimal version of a feature is pretty good quality, but just not necessarily the full-feature yet, so people can still test it and rely on it like it's a full stable feature missing minor features.

devilmoon 8y ago

Not my personal experience, but one of my professors is trying to get his patch approved into Postgres for an upcoming release; since his changes are very deep and touch the kernel (he wants to add support for temporal data) he told me the whole process will take years and the whole community will examine these changes before anything is added. I think that pretty much explains it: Cutting edge features are proposed way earlier than commercial DBs start thinking about implementing them, and the community hones them over years before they are released to the mainstream public.

bunderbunder 8y ago
This mirrors my experience working at a company that achieved similar results on some of its internal products. The very biggest difference between working there and working anywhere else, is that developers spent only a tiny fraction of time actually writing code. The biggest chunk of it was spent on peer review. Every change required sign-off from everyone on the immediate team, and, if it was being made to a component that other teams interacted with, they'd also have members reviewing the changes. And, as a newcomer, I found the reviews to be brutal. Over time, though, I came to recognize that what originally felt like others getting all up in my business about pedantic little variable naming issues was actually everyone having my back about legitimate maintainability concerns. Next biggest chunk was spent on planning - understanding requirements, talking over changes with anyone who might be impacted by them, etc. The second biggest difference was just how fast we could move. I think a lot of that came from a certain "wei wu wei" that was attributable to the deep understanding of the systems we were working on that developed out of all that time with our hands off our keyboards. Allow me the vice of inserting a mangled Hitchhiker's quote here: "If human beings don’t keep exercising their [fingers], he thought, their [hands] probably seize up. After a few months’ consideration and observation he abandoned this theory in favor of a new one. If they don’t keep on exercising their [fingers], he thought, their brains start working."
jeffdavis 8y ago
What is the professor's name?
tomnipotent 8y ago

pgaddict 8y ago

Good question. I'm contributing to postgres for quite a few years and I still don't have a clear answer to that. It certainly is not a single discrete reason, but IMHO a combination of various factors: 1) careful step-by-step engineering (vision, reviews, bug-fixing, ...) 2) open and civil community 3) not founded/owned by particular company (no one has the upper hand, companies serve customers with different needs, compete but also cooperate, ...) 4) code / documentation quality I could probably go on, but those four are probably the most important ones. (At least for me.)

hi41 8y ago
Congratulations to the postgres team. How do you create such a high quality product when you have to understand the code written by so many people? I take a long time to understand what someone else has written.
- pgaddict 8y ago
  It's not like every contributor (or even committer) understands all parts of the code base - I certainly don't. People usually start by writing external code (e.g. by writing extensions in C), learn the code style and basic constructs (memory context management, data types, etc.) and then gradually expand their knowledge by working on stuff closer to core and/or reviewing stuff that interests them. At least that's how it worked for me and a couple of people around me. I don't think it's particularly specific to Postgres. Regarding the high quality, we don't do anything magical - we simply apply well-known tools in a careful and methodical way. 1) careful reviews - Not just the code, but appropriate tests, comments and docs, and most importantly overall design/direction. It's not uncommon to reject the patch on the basis that there's a better approach, and get it reworked from scratch. Evan patches from the most senior contributors generally go through it too (with the exception of bugfixes, ...). 2) not accepting technical debt - No quick hacks that would make our life harder down the road. No features that are arbitrarily incompatible with other features. No compiler warnings. 3) incremental development - Split large patches, even if it means it's not really useful initially. 4) testing - We have a range of tests, that are executed on a variety of machines/platforms/compilers ... (https://buildfarm.postgresql.org/ https://buildfarm.postgresql.org/) 5) adopt useful technologies - static analysis, test coverage, valgrind, fuzzing (sqlsmith found quite a few bugs), ... 6) attention to bug reports / fixing them promptly (instead of just stashing them somewhere forever) I'm sure there are many additional things contributing to the quality. In any case, there's no secret sauce, except for applying those principles consistently.

qaq 8y ago

I think there are many factors on tech. side it's pretty modular and has very detailed comments in the source code, on the ecosystem side there is no single entity behind PG so it created an ecosystem where multiple entities can contribute and build products and services around PostgreSQL.

vikrantrathore 8y ago

I think the simple answer is philosophy of Kaizen(改善)- continuous improvement. I started working with postgresql when it was just postgres95. Recently found that my earliest question on postgresql-users was 13th October 1998, 20 years ago (https://postgrespro.com/list/id/3.0.2.32.19981013214318.006939a4@chemquick.com https://postgrespro.com/list/id/3.0.2.32.19981013214318.0069...). I still use PostgreSQL in my startup for almost all critical production workload. This in itself a testament to its improvements. I remember I starting with c libraries of msql and postgres95 and continued to work with them as they evolved into postgreSQL and a new kid on the block mysql. I liked postgreSQL due to its adherence to SQL. Since I like working with relational algebra. So even though MySQL was more popular in later years, I still used PostgreSQL more often, except for building qmail servers with mySQL backend.

Koshkin 8y ago
> Kaizen This is the last call of a hungry manager crowing on top of the body of an already dead software project.
- zephharben 8y ago
  This might be valid if we were discussing an internal, proprietary software project. We're talking about an open source project used by millions of people that has gotten CONTINUOUSLY better over the course of decades - to the point where it is now - arguably - the best of its kind. Given this track record, I think postgres devs are entitled to use whatever management-speak euphemism they like. In this case, the idiom's original meaning/intent seems apt.

jeffdavis 8y ago

My opinion (as a community member and contributor): PostgreSQL is willing to accept delays in some features -- even if highly desired -- to enable high overall feature development and quality. Also, more developers are willing to put time into refactoring and simplifying what they need before starting on their project. In other words the groundwork is laid first and then the new feature. Another thing that might help is that there is no bug tracker. Bugs are fixed and the fixes backported after a report comes in. Bugs don't end up stuck in a backlog.

atomic77 8y ago

On that note, I stumbled on an LWN article a couple of years ago that made me reflect on this miraculous balance of new features and stability: https://lwn.net/Articles/645020/ https://lwn.net/Articles/645020/ Anyone more involved in the pgsql community able to comment on how this balance is being struck? It doesnt' seem like the release schedule has changed much, though the version numbers seem to be incrementing faster (no v10.1, 10.2, etc?)