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

301–310 of 366 posts

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

#301

Post by frodowtf »

srcreigh wrote: post #147
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…

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…

That was a great read in contrast to all the "there's no reason to use mysql" nonsense in this thread

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

#303

Post by singron »

srcreigh wrote: post #147
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…

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…

If your pg database improves with CLUSTER, you can use pg_repack instead to achieve the same effect without downtime. Besides reordering the heap, it will also clear out bloat from the heap and indexes. I highly recommend partitioning if you have heavy write traffic on large tables since that will keep overhead low and make it complete faster.

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

#305

Post by paulddraper »

arp242 wrote: post #225
paulddraper wrote: post #219

Earlier quoted context omitted.

MySQL is certainly faster for writes. https://www.uber.com/blog/postgres-to-mysql-migration/

I would be wary putting too much stock in a seven year old post on the topic; lots of stuff has changed. Specifically, at least one person claimed that a patch greatly improved PostgreSQL for this use case: https://news.ycombinator.com/item?id=26285452

The MVCC requirement is still there.

It's like saying C is faster than Ruby. It always will be.

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

#308

Post by thfuran »

0xf8 wrote: post #293
erhaetherth wrote: post #280

Earlier quoted context omitted.

> something maybe like Git, most immediately comes to mind, as an example of the de-facto standard for distributed VCS basically since … 1995 when Linus decided to create it? hot take. might be the most popular, maybe even by a large margin, but I think you'll find a good chunk of people who have actually tried different VCSs don't think it's the best.

Yah, I think that’s probably true. But that’s also hard to reconcile with the reality of the adoption trending consistently away from any alternative and only towards Git. And “large margins” are indeed pretty objectively the case (from the largest developer surveys the breakdown 10 years ago was like 70% Git to everything, growing to ~95% in 2022). Usually the phenomenon you’re describing, leads to other alternative…

Are you serious? Anyone who disagrees with you about what VCS is best must be intentionally contrarian?

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

#309

Post by evanelias »

arp242 wrote: post #272
evanelias wrote: post #267

Earlier quoted context omitted.

Eh, just from a SQL query alone, there's no way to see that (lower(email)) is indexed, or see column data types etc. That metadata lives in the table definition, which is a normal place for it, so it seems reasonable for the collation / case-insensitivity to not be explicit in the query text. Besides, MySQL also supports functional indexes, so you could do the (lower(email)) approach in MySQL too if you really want!

No, you can't see everything, but you can see the exact comparison it's making. Is that useful? You can decide that for yourself but I like being able to see as much of the logic (and thus verify the correctness) in the query itself. Also helps with copy/paste and some other things. I never said you can't use functional indexes in MySQL. Someone said "you can't do this in PostgreSQL" and I just commented "here's (one…

My apologies, I misunderstood "It's more explicit than MySQL" to imply that you were saying that approach couldn't be used in MySQL.

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

#310

Post by doyourhomework »

api_or_ipa wrote: post #135

Practically speaking, they're very similar. Mysql and Postgres differ in their approaches to replication & clustering, which can have a big impact to high availability, high volume database configurations.

If I need disk encryption for very basic security in my production database, I know which one I choose, and it doesn't start with a P.
Post reply on HN