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

241–250 of 366 posts

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

#241

Post by srcreigh »

JohnBooty wrote: post #237
srcreigh wrote: post #221

Earlier quoted context omitted.

Strange take when FB, twitter, Square and new startups such as Faire(#4 valued private YC co) are all using MySQL to some/large degree. Stripe uses MySQL too in combination with other DBs including Postgres.

You're not considering the timeline of events. I'm unfamiliar with Faire but the rest were already using MySQL at the time of Oracle's acquisition in 2010. Switching backends would have been rough for those companies and this was... 2010, meaning Postgres was not nearly as performant or full featured as it is today. As mentioned in other comments, FB's investment in customizing MySQL has been extensive. They've poure…

Imo mysql is decent enough for many years, and PG has relatively major outstanding architecture problem (see my Other comment in this thread)

I don’t think either DB is bad, but the whole MySQL is dead and needs serious work idea doesn’t make sense to me. What problem does MySQL still have that needs fixing in your opinion?

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

#242

Post by evanelias »

arp242 wrote: post #232
bkuehl wrote: post #203

A lot of comments for Postgres, but it's the only major DB in 2023 that does not let you choose your character collation when creating a database. That is pretty much a deal breaker day 1. Guess you'll be doing a tolower() on every db search and not use indices which will kill performance or using column collation casts on every search query. I just don't get it. I once tried to migrate a SQL Server DB to Postgres an…

> 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-insensitive) vs "as" (accent-sensitive), and also the language/region conventions used for sorting purposes.

You can choose collation at the column-level granularity, as well as setting defaults at the table, schema, and server levels. It's completely explicit and very configurable.

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

#243

Post by connormcd »

JohnBooty wrote: post #237
srcreigh wrote: post #221

Earlier quoted context omitted.

Strange take when FB, twitter, Square and new startups such as Faire(#4 valued private YC co) are all using MySQL to some/large degree. Stripe uses MySQL too in combination with other DBs including Postgres.

You're not considering the timeline of events. I'm unfamiliar with Faire but the rest were already using MySQL at the time of Oracle's acquisition in 2010. Switching backends would have been rough for those companies and this was... 2010, meaning Postgres was not nearly as performant or full featured as it is today. As mentioned in other comments, FB's investment in customizing MySQL has been extensive. They've poure…

(Full disclosure, I work for Oracle)

Anyone who says no investment has been into MySQL I suspect never took the time to read the features/release notes for MySQL 8

https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html

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

#244

Post by jhas78asd »

scosman wrote: post #196

Postgres for almost anything. Robustness, ecosystem, accuracy, all top notch. One exception: I did migrate two very large tables (15B+ rows) from Postgres to MySQL for performance reasons. InnodB (MySQL storage engine) can arrange the records on page by primary key, and if you have a multi-value PK (user_id, uuid) it means all records from a user are in the same set of pages. Huuuuge improvement over having your data…

Did you implement table partitioning with Postgres or consider that before moving?

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

#245

Post by evanelias »

JohnBooty wrote: post #237
srcreigh wrote: post #221

Earlier quoted context omitted.

Strange take when FB, twitter, Square and new startups such as Faire(#4 valued private YC co) are all using MySQL to some/large degree. Stripe uses MySQL too in combination with other DBs including Postgres.

You're not considering the timeline of events. I'm unfamiliar with Faire but the rest were already using MySQL at the time of Oracle's acquisition in 2010. Switching backends would have been rough for those companies and this was... 2010, meaning Postgres was not nearly as performant or full featured as it is today. As mentioned in other comments, FB's investment in customizing MySQL has been extensive. They've poure…

Facebook maintains a patch-set, not a full fork. They still track Oracle's releases and apply their patches on top.

Facebook definitely has the resources to migrate to Postgres, if there was any motivating reason to do so. Indeed, Facebook developed their own LSM-based key-value store (RocksDB) and MySQL storage engine based on it (MyRocks) and then migrated the majority of their db fleet to that. In total that's massively more work than migrating to Postgres would have been.

Part of the reason was that MyRocks provides better compression than pretty much any other possible choice, and that adds up to an astronomical amount of cost savings at Facebook's scale. In contrast, what would Facebook have gained from moving to Postgres instead?

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

#246

Post by doyourhomework »

Funny to be so blind, no one mentionned how the Heatwave analytics engine of MySQL is 10 years ahead of what PostgreSQL can do, oh but that's because of Oracle so it doesn't count right?

Mixing OLTP and blazing fast analytic queries on the same database, at the very same time, removing ETL needs...

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

#247

Post by tapoxi »

connormcd wrote: post #243
JohnBooty wrote: post #237

Earlier quoted context omitted.

You're not considering the timeline of events. I'm unfamiliar with Faire but the rest were already using MySQL at the time of Oracle's acquisition in 2010. Switching backends would have been rough for those companies and this was... 2010, meaning Postgres was not nearly as performant or full featured as it is today. As mentioned in other comments, FB's investment in customizing MySQL has been extensive. They've poure…

(Full disclosure, I work for Oracle) Anyone who says no investment has been into MySQL I suspect never took the time to read the features/release notes for MySQL 8 https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html

Didn't 8 ship 5 years ago?

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

#248

Post by manv1 »

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 can, but you have to quote it? Fuck.

Until recently (which to be fair might be 8-10 years ago) postgres' performance monitoring tools sucked compared to mysql. I know at one point in the last 10 years they still used sunos4 as their base configuration because you know, the OS had been EOL for like a decade at that point.

MySQL is fire and forget. psql (or postgres or pg or postgresql?) is not fire and forget. It's twitchy and requires constant vigilance. I don't want a piece of infrastructure that requires constant vigilance.

That's not to say I won't use it. It's geo stuff is really great. It's JSON support is better than MongoDB's, from what I've heard. Row level security is awesome. But are those features good enough to overcome psql's quirks? Sometimes.

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

#249

Post by Nathanba »

justin_oaks wrote: post #211
OrvalWintermute wrote: post #68

Friends don't let their friends choose Mysql :) A super long time ago (decades) when I was using Oracle regularly I had to make a decision on which way to go. Although Mysql then had the mindshare I thought that Postgres was more similar to Oracle, more standards compliant, and more of a real enterprise type of DB. The rumor was also that Postgres was heavier than MySQL. Too many horror stories of lost data (MyIsam),…

The license issues are relevant. I've had to convert an application from using MySQL to using Postgres because we wanted to distribute copies of it. The Postgres license allowed us to distribute it without running afoul of GPL, which MySQL uses. Even distributing the MySQL JDBC driver with your software means that you need to provide source code of the Java app that uses it. I used MySQL by default for years. If you…

couldn't you have used the maria db connector? it's LGPL https://mariadb.com/kb/en/about-mariadb-connector-j/

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

#250

Post by jhas78asd »

jpgvm wrote: post #189

Generally speaking it should always be PostgreSQL. It's the default choice for a number of reasons but chief among them is just that it's higher quality. That is it's developed to higher standards due to community bar being really high (thanks Tom Lane, et al for your stewardship) and testing and analysis of changes to the database being ingrained into the culture. By pursuing correctness first before performance for…

Thanks for calling out table partitioning. Besides implementing it at one level, multiple levels can be used simultaneously (eg list and range). Tables can be grouped and split out to their own database (aka functional sharding/vertical sharding) and again partitioned. This all takes more effort and investment but keeps you on PostgreSQL. As you said fillfactor can be tuned, more HOT updates. Even analyzing whether the Updates could be turned into inserts that are written at a high rate, not incurring bloat, and then fewer updates are made at a rate that does not outrun Vacuum.
Post reply on HN