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

31–40 of 366 posts

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

#31

Post by CaveTech »

mort96 wrote: post #16
gardenhedge wrote: post #10

If Postgres was that much better than MySQL then you would expect to see exact reasons on why to pick it. Every comment so far has not listed any reason.

Ok, here's one: When you give MySQL invalid data, its standard behavior is to just silently store garbage in your database in many cases where PostgreSQL would've told you that your data is invalid. MySQL's handling of unicode has also been terrible historically, with way too many foot guns, but I don't know if that may be better with recent versions. People aren't providing strong reasons because the question wasn't…

Hasnt been the case for a few major versions, unless you want to anthropomorphise your db and and hold it accountable for past behaviour.

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

#32

Post by mort96 »

MrThoughtful wrote: post #25
lisasays wrote: post #22

Earlier quoted context omitted.

SQLite is great for its scope - but not in the same class as a full-fledged RDBMS.

Can you give a specific example of what you are missing when using SQLite?

At a certain scale, you'll want replication or replication, which SQLite doesn't really do AFAIK. At a scale below that, you'll probably want to be able to have multiple web servers talking to one database server, which SQLite doesn't really do either. I also think SQLite's performance during heavy write workloads is worse than PostgreSQL's?

Basically, AFAIK, SQLite becomes problematic once you need more than one computer to handle requests.

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

#35

Post by eesmith »

mort96 wrote: post #19
MrThoughtful wrote: post #12

By choosing SQLite. No server process and a single file per DB which I can put wherever I like.

I like SQLite. But I really wish its default behavior wasn't to simply allow garbage data into the database. If I have an int column, don't let me accidentally store a string.

https://www.sqlite.org/stricttables.html

"In a CREATE TABLE statement, if the "STRICT" table-option keyword is added to the end, after the closing ")", then strict typing rules apply to that table. ... The STRICT keyword at the end of a CREATE TABLE statement is only recognized by SQLite version 3.37.0 (2021-11-27) and later.

  sqlite> create table x (a int);
  sqlite> insert into x values ('hello');
  sqlite> select * from x;
  hello
  sqlite> drop table x;
  sqlite> create table x (a int) strict;
  sqlite> insert into x values ('hello');
  Runtime error: cannot store TEXT value in INT column x.a (19)

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

#36

Post by dijit »

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 and everyone to avoid Oracle as if it has herpes.

There are a lot of historic problems with MySQL accepting invalid data, committing data even when there are constraint issues, and having very poor transactional isolation, I am not sure if these have improved.

Truthfully, the only benefits you gain from using MariaDB or MySQL are:

* Memory tables

* Having inconsistent replicas (which can be useful when you want your downstream to have less data than your upstream and you know it won’t get updated.)

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

#37

Post by bakugo »

mort96 wrote: post #16
gardenhedge wrote: post #10

If Postgres was that much better than MySQL then you would expect to see exact reasons on why to pick it. Every comment so far has not listed any reason.

Ok, here's one: When you give MySQL invalid data, its standard behavior is to just silently store garbage in your database in many cases where PostgreSQL would've told you that your data is invalid. MySQL's handling of unicode has also been terrible historically, with way too many foot guns, but I don't know if that may be better with recent versions. People aren't providing strong reasons because the question wasn't…

> MySQL's handling of unicode has also been terrible historically, with way too many foot guns, but I don't know if that may be better with recent versions.

Unicode generally "just works" if the charset in use is utf8mb4. As of MySQL 8.0, this is the default.

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

#38

Post by erulabs »

Use MySQL if you're expecting to have to do large operational database tasks re: migrations, maintenances, with no ability to go offline. gh-ost, percona-osc, the new INSTANT DDL stuff, is all quite far ahead in MySQL-land. Additionally, Vitess and Planetscale are making huge strides in MySQL performance. There are more people and guides in the world to help recover even the most mutilated of MySQL databases. MySQL gets more love in extremely large enterprise-level organizations, and it shows.

Use Postgres if you need some of the quite-good extensions, most notably PostGIS, or if you just want things to work; most documentation will be postgres flavored. Postgres gets more love from web-developers, and it shows.

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

#40

Post by l5ymep »

gardenhedge wrote: post #10

If Postgres was that much better than MySQL then you would expect to see exact reasons on why to pick it. Every comment so far has not listed any reason.

Postgres has a worse implementation of MVCC. It results in more bloat being produced, and slightly slower updates in a highly concurrent environment. Most businesses don't operate at the scale where this matters. But on the flip side, the tooling and developer experience is much better.
Post reply on HN