Ask HN: Why does HN adore Postgres so much? | Hacker News Reader

Ask HN: Why does HN adore Postgres so much?

13 pointsapi19 comments

Postgres is very capable and its support for SQL is very complete, but it’s also clunky and hard to manage beyond the simplest use cases. Setting it up in HA configurations is intimidating to the point that even experienced admins fear doing it for serious use cases and rely on managed Postgres at cloud providers instead.

Pg really feels like 1980s or 1990s “hand cranked” Unix software.

MySQL/MariaDB and especially CockroachDB and TiDB are alternatives that are massively more approachable especially in HA setups. Then there are NoSQL databases, graph databases, etc.

What is it that makes something as arcane and clunky as Postgres such a darling?

View on HN

You are looking at it from operational point of view. As a developer I care about different things: 1.Ops side is someone else problem. AWS takes care of that, so it just works. Otherwise, ops take care of that, so I also don't care. 2. MySQL is absolutely horrible from developer point of view. It works for most basic cases, but it's so primitive that anything non-trivial breaks it. Significant portion of its documentation is dedicated to listing all possible ways things are broken and what you need to watch out for. Postgres just works. You can combine 2,3 or more features and it all just works as you expected. 3. Pg has way more features. You have tons of data types, index types and extensions that cover enormous amount of usecases. Whatever you want to do, Pg can do it. Maybe some specialized db would be better for full-text search or GIS or storing json, but Pg will get you far (or will just do great). MySQL is nowhere near this extensibility. 4. Maybe it's historical, but I've seem way more corrupted Mysql cases than Pg. It is rock-solid. 5. As a result of that, I see MySQL vanishing from people minds. More and more products work with Pg only (or Pg and SQLite).

> CockroachDB and TiDB are alternatives that are massively more approachable

Maybe. CockroachDB claims Pg compatibility (though docs say nothing about extensions). TiDB has some interesting features, but MySQL compatibility scares me [2]. Neither of them has level of support from cloud providers that Postgres has.

YugabyteDB

Has anyone used it under heavy load in production? Experiences?

Yes. See https://www.yugabyte.com/success-stories/

Source: I work there. Feel free to join on slack and ask about your use-case.

“It doesn’t matter because it’s just an ops problem” assumes that it’s okay to just throw a bunch of money at it via astronomical cloud fees or salaries for dedicated ops people.

I’ve recently started working on a concept for this kind of engineering: “zero interest rate architecture.” This would refer to designs that lead to huge recurring cash burn because you can always just raise more money.

> “It doesn’t matter because it’s just an ops problem” assumes that it’s okay to just throw a bunch of money at it via astronomical cloud fees or salaries for dedicated ops people.

It's okay to have different roles and responsibilities within organisation and it's okay to outsource certain aspects of operations. AWS RDS costs essentially the same for PG vs MySQL (PG is about 3% more expensive) and I doubt it would be much different in ops salary if you do it yourself, so it's not astronomical.

before Postgresql was big, the only opensource databases i saw in use was MySQL(later Mariadb) and SQLite. SQLite support is single application and MySQL suffers from problems like incomplete utf8 support(unless you use utf8mb4), or the problem of the database engines. Postres configuration and running multiple servers on one domain is from my perspective much easier. Some minor features are more interesting for me: native uuid, row level locks ("SELECT * FROM mytable FOR UPDATE SKIP LOCKED LIMIT 1;" is an query where you can get one row and process it like an queue, but if it fails (error or crash of client) it can rollback and give to the next worker.

1. I don't know.

2. Relative to their importance in 2023, HN disproportionately upvotes headlines mentioning Mozilla, Linux desktop frameworks (#1 recently!), and other technologies that were extremely important in 2003.

3. Based on that (imperfect) evidence, my hypothesis is that HN voters are heavily drawn from people who have been hackers for two decades are more *and who haven't fully updated their perspectives on software*. An implication of this hypothesis is that there are some topics which are underweighted on HN relative to their importance. I do not know how to test this hypothesis.

postgres wiki has list of different non-command line ways to use postgres [0]. UML/gui type tools perhaps would be helpful[1].

Configuring setups from scratch is always annoying. Checking out how others have handled/documented/automated HA setups is nice way to narrow down setup/understanding time. few search engine results: [2][3][4]

[0]a : http://wiki.postgresql.org /wiki/Community_Guide_to_PostgreSQL_GUI_Tools

[0]b : http://wiki.postgresql.org/wiki/Design_Tools

[1] : http://pgmodeler.io/

[2] : https://ubuntu.com/blog/postgresql-high-availability

[3] : https://www.postgresql.org/docs/current/high-availability.ht...

[4] : https://jfrog.com/community/devops/highly-available-postgres...

I'm surprised nobody has mentioned licenses. PostgreSQL has an open license and you can use it for any purpose you want. It's also not controlled by Oracle.

Something that puzzles me: open license, yet so arcane that it usually must be consumed as a service that costs far more than closed DB licenses usually did.

At some point complexity and lack of polish becomes indistinguishable from closed source. An open system I can’t use easily myself is not open even if the source is available.

But in those days, in addition to the license prices for closed source DBs, you also had to pay all the hardware costs to run them on, and hire an army of DBAs to support and maintain them for you.

Running HA Postgres is not easy and requires a lot of niche/specialized knowledge. But running one of the big commercial DBs back in the pre-cloud days required just as much specialized knowledge at the DB level. It required network engineers and Sysadmins. It was far from cheap to run at scale.

Compared to apl, every database is pretty bloated. but more easily understood.

A form of 80's "sql" pushed out the more elegant upgrade for "sql". aka forerunner which postgress is based on.

"SQL" was a workaround to needing a degree(s) in underlying theory/how to build hardware/software from scratch and degree in practical use application(s).

Updated/modern 70's pre-sql , non-postgres example:

Kleene[0] / de brujin[1] programming a complete database system from scratch (pawk/neovim) using ebnf/bnf in place of more modern data structures with an wasm compiled awk /python for browser use might make postgres seem like a darling. Does make for lot of academic/discussion material.

[0]a : https://ai.dmi.unibas.ch/_files/teaching/fs16/theo/slides/th...

[0]b : https://www.cl.cam.ac.uk/teaching/0708/MathCompTh/kleene.pdf

[1] : https://en.wikipedia.org/wiki/De_Bruijn_graph

Why postgress: readily available/accessible documentation / choice of level of support / free to mix/match of tools of choice (vendor, 3rd party, diy your own).

Per stated alternatives: look pretty clunky compared to DIY setup using HAproxy for awk language based webserver running on a raided/distributed pi cluster with various levels of fail over support. Directly implimenting just what need to get job done using programming language on top of a hardware solution provides for very lean/customized solution. Although, does assume very familiar with HW/SW setup and extensive familiarity of various stacks/programming concepts. SQL front end can be implimented in awk as a front end to awk.

"from scratch" issues:

Not exactly an actively suported specific DB area/db tools package (cockroachDB / TiDB / NoSql / graph DB, etc).

Not an actively supported turnkey database management system under common ide/environment (postgres, Oracle, etc.)

Flexibility limited to level of knowledge of various db areas/tools under common ide/environment (postgres, Oracle, etc.)

Having had to migrate a messy legacy system from old MySQL to modern MariaDB, I can confidently say that I never wish to work ever again with anything MySQL related. Postgres is much more boring than MySQL, and that's a good thing.

Edit: in case you're curious, part of the reason why I found it so difficult was because of MySQL's take on UTF-8 support.

I think it's the same reason teenagers in the US don't want to be seen with Android phones.

MariaDB is not fashionable or high status.

People think they are making all these decisions on a rationale basis. But humans, including software engineers, are herd animals.

The drive to conform will push humans to do all sorts of irrational things. I still use vim (neovim) despite decades of hating certain things about it.

I think also that many developer's only real way of judging engineering is whether it conforms to certain stack or process patterns that they have heard of. Use the wrong stack or leave out some process and in their eyes you must not be a serious engineer. They aren't capable of or interested in looking into the details of the requirements and execution to judge you.

Because with MySQL, to wrap every column with `` in a query is very clumsy. In most of time, to write mysql query, one should use an ORM.

It's not the case with PostgreSQL.

What? You don’t have to quote column names in MySQL unless you use reserved words or names with spaces or special characters. Postgres has the same requirement.

Using an ORM or not is a different question, nothing to do with either MySQL or Postgres.

Herd mentality. Most use cases would be better served by the second generation open source relational databases such as YugabyteDB, TiDB or YDB.

Reply on news.ycombinator.com