Hacker News Reader

Comment by bsaul | Hacker News Reader

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.

View on HN

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.

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."

What is the professor's name?

> Cutting edge features are proposed way earlier than commercial DBs

This is not true. SQL Server & Oracle already have pretty every feature just released in 11 and have had most of them for many years.

Like native JSON support?

That's a "hipster" feature, not something that the "enterprise" customers of these products are asking for.

This is quite a pedantic opinion. I’ve found jsonb pretty useful to handle semi-structured data in production.

I wouldn’t try to use jsonb attribute in a primary key though (even if it’s practically possible as it’s a fully featured base type).

Not sure if you're trying to be funny about the hipster thing.

I've seen the json features used at multiple serious organizations. It turned what would have otherwise been unnecessarily complicated queries into really reasonable queries in our ETL pipeline.

It's a difference of mindset.

Postgres includes many features that increase the number of simple use-cases for the existing data in their database, thus allowing you to replace a Postgres complement (like Mongo) with Postgres for that use-case.

"Enterprise" DBMSes like Oracle and MSSQL, meanwhile, don't focus so much on expanding the use-cases of their offering, as they do on expanding the number of ways a sysadmin can take the existing use-cases and make them scale better, with less manual maintenance. Oracle and Microsoft allow you to replace their ecosystem of tools and extensions with a DBMS-internal feature for the given sysadmin need.

So Postgres has an expanding ecosystem of tooling and extensions, but is slowly eating its complements; while Oracle and MSSQL have an expanding space of complementary offerings, but are slowly eating their own product ecosystems.

Or, to put that another way: Postgres focuses more on making life easier for people who write SQL; Oracle and Microsoft focus more on making life easier for the DBAs who manage the DBMS cluster that people are running SQL against.

I would rather have query parallelism, advanced partitioning, and online index creation than JSON support.

Postgres is an amazing RDBMS and the clearly the most advanced open source offering (I use it for my startup), but it will always trail the big commercial systems like SQL Server and Oracle. The query planners for both of those RDBMS' are still considerably more advanced.

I used to work in enterprise. There's nothing I miss more than Oracle's automatic partition management.

Maybe they have many of the features that are new in 11, but what about what PostgreSQL had already? I have used all three, and like Postgres a hundred times as much. https://www.linkedin.com/pulse/postgresql-vs-ms-sql-server-g... (I did not write this, just found it after a quick search)

Proposed, not implemented.

> Cutting edge features are proposed way earlier than commercial DBs start thinking about implementing them

Definitely not true. Postgres development is slow, deliberate, and the opposite of cutting edge. It's what has lead to a great platform that is reliable and flexible, but every single commercial database out there has far modern tech implemented.

In some ways that is true, and in others that is not the case. For JSON support, PG really did lead the way as far as RDBMS' go. Things like range types are something I wish MSSQL supported when I used it at my day job.

And plenty more that PostgreSQL will probably lack for the foreseeable future. Especially at the more expensive pricing tiers.

But I'm not sure it's a fair comparison, precisely because of that word, "pricing". It's amazing what you can get people to do when you're paying them big bucks to do it. Part of what makes PostgreSQL so impressive is what it does given the budget it's working with.

I think they targeted xml instead of json, and much earlier. XML is much more enterprise-ly.

> support for temporal data

native support for TimescaleDB ? That would be nice.

TimescaleDB is for time-series data, which is half-way to being one kind of temporal data.

Full support for both kinds of temporal data ("valid time" and "transaction time") requires a bit more engineering.

The PG support for the TSTZRANGE data type, operators built on it, and indices built on those operations, was part of a previous phase of adding support for "valid time" temporal data.

https://pgxn.org/dist/temporal_tables/ is an extension implementing a prototype of "transaction time" temporal data, though in modern Postgres, you can do this much yourself.

In both cases, none of the SQL syntax specific to temporal queries has been implemented yet. Rather than a distinct view and backing "current" and "history" tables and etc., you should be able to just have a table that represents all of those things, and do queries on its components using different syntax. (Sort of like how S3 versioned bucket resources work.)

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.)

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.

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/)

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.

Thank you very much! I stand to gain much from your thoughtful answer. Recently my career took a big downturn. I got laid off and found it hard to get a programming job. I am doing production support now. I feel great despair that I don't measure up as a programmer anymore. I need to get back my programming skills but I am confounded by the huge complexities of the current stacks.

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.

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.

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.

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.

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.

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.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.

> Kaizen

This is the last call of a hungry manager crowing on top of the body of an already dead software project.

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.

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.

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).

You gotta be insanely good and enjoy your craft to enjoy writing low level DB code for open source lol

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.

  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.

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.

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

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.

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.

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/

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?)

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.

Reply on news.ycombinator.com