Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Ask HN: It's 2023, how do you choose between MySQL and Postgres?

news.ycombinator.com

Post reply on HN
Print view View original

171–180 of 366 posts

Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?

#171

Post by craigkerstiens »

Having answered this a ton over the years, don't want to really take shots at MySQL. But Postgres stands in pretty unique ground.

1. It's solid as a really reach data platform (more than just a relational database). It's extension framework is quite unique compared to others. It's JSONB support was the first among other relational databases and is feature rich and performant. Multiple index types. Transactional DDL. The list goes on.

2. No central owner. A lot of open source is source code is open, but it's maintained by a central company.

3. I mentioned extensions, but really that is understated. It can do really advanced geospatial, full text search, time series, the list goes on.

Having explained this a ton of times first 10 years ago - https://www.craigkerstiens.com/2012/04/30/why-postgres/ and then again 5 years later with and updated version, most recently tried to capture more of this in an updated form on the Crunchy Data blog - https://www.crunchydata.com/why-postgres

Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?

#172

Post by asdfman123 »

dijit wrote: post #36

There is almost no good reason to choose MySQL over PostgreSQL for any operational reason, I did a deep dive many moons ago (before major improvements in performance to postgres) and people were saying that MySQL was faster. I found that not to be true and the differences have only gained even more favour towards postgres. also, I assume you mean MariaDB as MySQL is owned by Oracle and I would greatly implore anyone…

The main problem with herpes is the stigma against it. Don't besmirch it by associating with Oracle.

Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?

#173

Post by stouset »

donatj wrote: post #109

Unpopular opinion on HN apparently, but MySQL - It's less featureful, and I'd consider that a strong virtue in the YAGNI camp - less to go wrong, less mental overhead. - Maintenance is simpler and far less necessary in my general experience. - Replication is simpler and more reliable. - You can tell the query optimizer what to do. When this is needed, you'll be thankful. It's a godsend. That said, I wouldn't run Orac…

> It's less featureful, and I'd consider that a strong virtue in the YAGNI camp - less to go wrong, less mental overhead.

This doesn't really hold water in my opinion.

It's not like PostgreSQL is some minefield of misfeatures and quirky behavior. Some of these features exist, but have zero impact on you unless you actually opt to use them. But if you end up needing to: they're there, and you can just start using them.

Compare this to MySQL where they simply don't exist no matter how much you may need them. Need to apply an index to the result of a function to quickly fix a performance issue in prod? Sorry, you can't. Need window functions to accurately compute some analytics in a sane period of time? Sorry, you can't. The list of things you can do in PostgreSQL that you simply can't with MySQL is massive and grows every day.

The odds that you'll want, need, or greatly benefit at least one of these features is not small. Having the flexibility of knowing these features exist should you ever have a use-case for them is massive.

Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?

#174

Post by hamilyon2 »

0xbadcafebee wrote: post #100
dijit wrote: post #36

There is almost no good reason to choose MySQL over PostgreSQL for any operational reason, I did a deep dive many moons ago (before major improvements in performance to postgres) and people were saying that MySQL was faster. I found that not to be true and the differences have only gained even more favour towards postgres. also, I assume you mean MariaDB as MySQL is owned by Oracle and I would greatly implore anyone…

> avoid Oracle as if it has herpes herpes isn't that bad. most people will get it in their lifetime. 1 in 6 people have hsv-2, the less common variant. trying to avoid herpes is like trying to avoid chickenpox (although herpes isn't nearly as harmful as chickenpox). you should avoid Oracle like it's a blood pathogen.

As a person who has herpes firmly in his nerves, I would say don't underestimate herpes.

Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?

#175

Post by pawelduda »

donatj wrote: post #109

Unpopular opinion on HN apparently, but MySQL - It's less featureful, and I'd consider that a strong virtue in the YAGNI camp - less to go wrong, less mental overhead. - Maintenance is simpler and far less necessary in my general experience. - Replication is simpler and more reliable. - You can tell the query optimizer what to do. When this is needed, you'll be thankful. It's a godsend. That said, I wouldn't run Orac…

What's the ratio of solving DB perf issues by optimizing it and letting the planner do its work, to telling it what to do? For me it's like 1000:1.

And that one case I remember was perfectly solvable the regular way, with a little more time.

Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?

#176

Post by spacedcowboy »

tbarbugli wrote: post #156
spacedcowboy wrote: post #148

Earlier quoted context omitted.

This. For anything at home, I would use MySQL just for those things. The psql client feels very primitive by comparison to me - even though it isn't.

I highly suggest investing time learning psql, autocomplete works great and it has a ton of useful slash commands. \d for instance shows you the list of tables. Awesome tool

I don't need it often enough to invest the time. I generally set up a database as backing store to some project, fiddle with it until I'm happy it's working at the scale/performance I want, and then move on to something else.

During those few weeks I'm actively using the database on the project, I can either get frustrated beyond belief with the CLI for Postgres, or just use what's at hand with MySQL. In fact, these days SQLite is getting more of my attention anyway, and I wrote a small CLI for it a decade or so back (before the sqlite3 client gave us most of the below) to provide:

- Timings for the queries (in fact I called it 'tsql')

- Aligned-column displays, with | separators between columns and index-indicators

- Ability to parse some blobs (Plists on the Mac, for example) and display

- Things like "SHOW DATABASES", "SHOW TABLES", "SHOW TABLES LIKE" etc.

Mainly I wrote it to do some benchmarking, but I eventually preferred it over sqlite3 as the CLI.

Note that all this is personal stuff - When I do commercial stuff, the answer is always "what is best understood by the people maintaining it afterwards"...

Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?

#177

Post by yakshaving_jgt »

za3faran wrote: post #169
0xbadcafebee wrote: post #100

Earlier quoted context omitted.

> avoid Oracle as if it has herpes herpes isn't that bad. most people will get it in their lifetime. 1 in 6 people have hsv-2, the less common variant. trying to avoid herpes is like trying to avoid chickenpox (although herpes isn't nearly as harmful as chickenpox). you should avoid Oracle like it's a blood pathogen.

> most people will get it in their lifetime Citation needed.

Here you go.

https://www.who.int/news/item/28-10-2015-globally-an-estimat...

Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?

#178

Post by enneff »

unethical_ban wrote: post #134
0xbadcafebee wrote: post #100

Earlier quoted context omitted.

> avoid Oracle as if it has herpes herpes isn't that bad. most people will get it in their lifetime. 1 in 6 people have hsv-2, the less common variant. trying to avoid herpes is like trying to avoid chickenpox (although herpes isn't nearly as harmful as chickenpox). you should avoid Oracle like it's a blood pathogen.

hello, fellow person with herpes! (I assume) The worst part about having it is having to talk about having it. It's really not bad as a condition separate from societal concern.

It’s not so bad for most people but if you’re one of the unfortunate few who suffer chronic symptoms it can be truly awful. Not worth playing that lottery if you can avoid it.

Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?

#179

Post by LinuxBender »

For my little hobby sites I use whichever best fits the application. If an application has a specific need for a function that is in Postgres and not MySQL then I use Postgres.

In my former work life we used Percona MySQL for the commercial support and very fast response to fix bugs and add features, but we also used Postgres and Oracle. In those cases it was more important to have awesome DBA's that could do anything with any database. I learned a lot from them and they earned a lot of respect from me. One of them could find significant design flaws in the application just be reviewing the schema. They cut our memory usage in half and bought us time to wait for the server vendors to support more memory in their next motherboard release.

Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?

#180

Post by srcreigh »

Shorel wrote: post #165
srcreigh wrote: post #147

Earlier quoted context omitted.

Postgres is >50x slower for range queries(example below) and is akin to using array-of-pointers (ie Java) whereas MySQL supports array-of-struct (C). Illustration from Dropbox scaling talk below. Sneak peek photo [1] (from [2]). Just imagine its literally 500-1000x more convoluted per B-tree leaf node. That's every Postgres table unless you CLUSTER periodically. [1]: https://josipmisko.com/img/clustered-vs-noncluster…

You are confusing two concepts here. In InnoDB, the tables are always ordered by the primary key when written to actual disk storage. This is not the same as "having a primary key", Postgres also has primary keys. It just stores the PK index separately from the bulk of the data. Oracle also has primary keys, even if the order of the rows is different to the key order. In Oracle, when the rows are stored in the same o…

I said primary index, not primary key (primary key and primary index is synonymous in mysql Dropbox example). Primary index is database theory lingo for storing all the primary row data inside a B-tree. It’s synonymous with what you say IOT although that’s a new term for me.

You’re incorrect about IOT reordering the entire table at least wrt mysql. MySQL uses a B-tree to store rows, so at most it’s insertion sort on a B-tree node and rare b-tree rebalance. Most b-tree leaf nodes have empty space to allow for adding new data without shifting more than a few hundred other rows. Also, non-IOT tables also need to do a similar process to write to each of its indexes. Last, it’s sort of a tossup since if you’re appending to an IOT table frequently, the right edge of the B-tree is likely cached. (similarly for any small number of paths through the primary index B-tree). At worst Postgres heap will need to surface one new heap disk page for writing, although I’m sure they have some strategy for caching the pages they write new data to.

Sorry to spam this info! Glad to see we both love databases and I’m always please to see engagement about this topic!

Post reply on HN