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

261–270 of 366 posts

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

#261

Post by NightMKoder »

I’m going to go for the esoteric opinion: MariaDB. Specifically to get system versioned tables. Imagine having the full change history of every single row for any odd task that you need without the performance penalty of keeping historical data in the same table. It can be a huge amount of leverage.

If that’s not your interest, I will admit that Postgres array support is far ahead of any of the MySQLs. Most ORMs don’t use it but you can get optimal query prefetching via array subqueries.

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

#262

Post by stouset »

evanelias wrote: post #181
stouset wrote: post #173

Earlier quoted context omitted.

> 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 the…

Your examples regarding MySQL's features are not correct. Need to apply an index to the result of a function? No problem, use a functional index, supported since October 2018: https://dev.mysql.com/doc/refman/8.0/en/create-index.html#cr... Need to use a window function? No problem, supported since April 2018: https://dev.mysql.com/doc/refman/8.0/en/window-functions.htm...

Fair. I picked two instances that had long been personally painful with MySQL and didn’t exist when I finally fled to greener pastures.

There are far more examples than just those two though. :)

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

#263

Post by heyoni »

NightMKoder wrote: post #261

I’m going to go for the esoteric opinion: MariaDB. Specifically to get system versioned tables. Imagine having the full change history of every single row for any odd task that you need without the performance penalty of keeping historical data in the same table. It can be a huge amount of leverage. If that’s not your interest, I will admit that Postgres array support is far ahead of any of the MySQLs. Most ORMs don’…

I’m 100% with you regarding system versioned tables. However, I think they’re coming to Postgres soon-ish. I was following the tracker for a while and it looked like it was done.

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

#265

Post by arp242 »

evanelias wrote: post #242
arp242 wrote: post #232

Earlier quoted context omitted.

> doing a tolower() on every db search and not use indices If you create the index with lower() it will uses that; e.g. "create index on tbl (lower(email))" and then "select * from tbl where lower(email)=lower($1)". That's more or less the standard way to do this but there are some other ways as well. It's more explicit than MySQL, so in that way it's better. It's more effort and easy to forget, and in that way it's…

> It's more explicit than MySQL, so in that way it's better. It sounds like you're under the impression that MySQL just makes everything case-insensitive and is silent about this? That's decidedly not the case. MySQL 8 ships with 41 different character sets, supporting a total of 286 different collations. Collation names explicitly include "ci" (case-insensitive) vs "cs" (case-sensitive), as well as "ai" (accent-inse…

There is no way to see from the query itself if it's case-sensitive or insensitive; that is what I meant.

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

#266

Post by arp242 »

jhas78asd wrote: post #253
arp242 wrote: post #229

Earlier quoted context omitted.

> I forget if there's an equivalent for the first one Not really; PostgreSQL doesn't store the original query, so you'll need to re-create it from pg_class, pg_attribute, and all of that (which is really what \d and such in psql do). The easiest way is probably pg_dump, but it's best to just get used to \-commands because it's really just the same thing.

Find the SQL from meta commands. Example: https://dba.stackexchange.com/a/131031

That is not equivalent to "show create table" at all.

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

#267

Post by evanelias »

arp242 wrote: post #265
evanelias wrote: post #242

Earlier quoted context omitted.

> It's more explicit than MySQL, so in that way it's better. It sounds like you're under the impression that MySQL just makes everything case-insensitive and is silent about this? That's decidedly not the case. MySQL 8 ships with 41 different character sets, supporting a total of 286 different collations. Collation names explicitly include "ci" (case-insensitive) vs "cs" (case-sensitive), as well as "ai" (accent-inse…

There is no way to see from the query itself if it's case-sensitive or insensitive; that is what I meant.

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!

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

#268

Post by evanelias »

stouset wrote: post #262
evanelias wrote: post #181

Earlier quoted context omitted.

Your examples regarding MySQL's features are not correct. Need to apply an index to the result of a function? No problem, use a functional index, supported since October 2018: https://dev.mysql.com/doc/refman/8.0/en/create-index.html#cr... Need to use a window function? No problem, supported since April 2018: https://dev.mysql.com/doc/refman/8.0/en/window-functions.htm...

Fair. I picked two instances that had long been personally painful with MySQL and didn’t exist when I finally fled to greener pastures. There are far more examples than just those two though. :)

Sure, and there are also plenty of examples of things MySQL can do that Postgres can't.

There's no silver bullets in software or databases, no "one size fits all". Just various trade-offs between different design decisions.

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

#269

Post by dijit »

throw0101b wrote: post #235
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…

> There is almost no good reason to choose MySQL over PostgreSQL for any operational reason Galera is the main one I can think of: * https://galeracluster.com/library/documentation/tech-desc-in... * https://mariadb.com/kb/en/what-is-mariadb-galera-cluster/ * https://packages.debian.org/search?keywords=galera I'm not aware of any multi-master, active-active(-active) replication system that is open source for PostgreSQ…

Last time I looked at Galera it had a limit at 5TiB of data. Which is fine I guess.

In the Postgres space there is citusdb which provides multi master.

There is also BDR from 2ndquadrant if you want a paid/supported solution. https://www.enterprisedb.com/products/edb-postgres-distribut...

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

#270

Post by arp242 »

manv1 wrote: post #248

The main reason I prefer mysql over PostgreSQL is that mysql is just more consistent - in its commands, quirks, etc. Postgres - is it pg, pgsql, psql, postgres, postgresQL? The answer is "yes." Plus the case behavior for tables and column names drives me crazy. It's like some leftover VMS shit. I mean seriously fix it. Can you or can you not use a capital letter for a table/column name? I can never remember. Or you c…

> at one point in the last 10 years they still used sunos4 as their base configuration because you know,

What exactly do you mean with this? I tried to find some more information, and all I could find were some fixes from 2001[1] (SunOS 4 was supported until 2003), a minor refactor in 2008 with "SunOS 4 is probably broken anyway"[2], and that's pretty much it. SunOS 4 was moved to "Unsupported Platforms" with the release of 7.3, in 2002.[3]

[1]: https://postgrespro.com/list/thread-id/1598869

[2]: https://www.postgresql.org/message-id/20081211091708.0726075...

[3]: https://www.postgresql.org/docs/7.3/supported-platforms.html

Post reply on HN