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

211–220 of 366 posts

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

#211

Post by justin_oaks »

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 use it for internal-only apps then the GPL doesn't matter. But once you cross the line into sharing what you've built with others, MySQL is a non-starter unless you want to GPL everything you are sharing.

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

#212

Post by OJFord »

TehShrike wrote: post #41
adoxyz wrote: post #17

Choose whichever one you/your team is more familiar with. Both are battle-tested and proven and will likely scale to whatever needs you have.

This is the correct answer. Whichever one you start out with, you will be annoyed if you switch to the other one 5 years later. I started out with mysql, and when I started working on a postgres project, I was shocked at some of the ways it was lacking (how am I supposed to store email addresses in a database without collations?). But when postgres folks grouse about stuff in mysql, I'm usually nodding along and sayi…

> how am I supposed to store email addresses in a database without collations?

Not familiar with MySQL so trying to look that up, but with a constraint? Or just don't do that? - SO answer I found says 'it's much more useful to have johndoe@ and JohnDoe@ treated as the same than it is to support case sensitive email addresses'.. ok, it's also incompliant, but whatever's 'more useful’ I guess!

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

#213

Post by [deleted] »

srcreigh wrote: post #180
Shorel wrote: post #165

Earlier quoted context omitted.

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…

[deleted]

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

#214

Post by [deleted] »

srcreigh wrote: post #180
Shorel wrote: post #165

Earlier quoted context omitted.

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…

[deleted]

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

#215

Post by srcreigh »

ideal_gas wrote: post #184
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…

(Admitting bias: I've only ever worked with postgres in production with update-heavy tables so I've dealt with more of its problems than MySQL's) Postgres also has other gotchas with indexes - MVCC row visibility isn't stored in the index for obvious performance reasons (writes to non-indexed columns would mean always updating all indexes instead of HOT updates [1]) so you have to hope the version information is cach…

Interesting. PG docs don’t clarify whether visibility map gets updated for HOT update. Maybe even HOT update spoils index only scans. Although I can’t see why-no new index entries, heap visibility status hasn’t changed for any indexes.. wish to find some answers here but I could not.

Wrt secondary indexes, yes and no. There is a cost to traverse a B-tree for point lookups. Also, foreign keys may now be composite keys if primary key is composite as in the Dropbox example.

If the secondary index is very different from the primary, it will be more expensive. However it’s pretty common to at least use a “user_id” as the first part of the primary key. This will make partial full scans a lot faster for queries regarding a single user; only need to scan that users data, and it comes at a 1-2 order of magnitude cheaper disk read cost. So you’d need a secondary index only if the data you need is spread across 1000s of pages (megabytes of data for a single user in one table) and you’re looking for only a handful of rows randomly located in that sequence.

Twitter is a characteristic case where you need many different clustered sets for the same data (tweets) to power different peoples feeds. I believe twitter just stores many copies of tweets in different clusters in Redis- basically the same as having a (author_id, ts) primary key tweets table and a (follower_id, ts) primary key feed table, both having tweet data inlined. If one clustered table isn’t enough, use two.

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

#216

Post by xtracto »

None of them if you need to do something like pivot tables with dynamic number of columns. Postgres has some half baked pivoting functionality library, but you MUST define the column names in advance, which kind of defeats the purpose of a pivot table functionality.

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

#217

Post by srcreigh »

boloust wrote: post #208
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…

When using clustered indexes, one tradeoff is that if a non-clustered index isn't covering for a query, it will need to perform B-tree traversals to find rows in the clustered index. This can significantly increase the amount of (at least logical) IO compared to heap tables, where the non-clustered indexes can refer directly to the row id. Because you can only have a single clustered index, you're effectively paying…

Appreciate the thoughtfulness. I believe the branching factor of even wide keyed tables don’t add significant cost to point lookups. At most one or two extra disk pages needing to be read.

Example: 80 bytes keys gives you branching factor of roughly 100. 10M rows and you can pack say 20 rows per page. That’s a 4GB table, give or take. That btree still only has 3 intermediate layers and primary data on a 4th layer. (Calculation is log(10M/20/0.75)/log(100)+1.) The first two layers take up less than a megabyte of ram and are therefore easily cached. So you wind up only needing 2 disk reads for the final two layers. Unless Postgres is caching the entire index for point lookups, it should come out about even.

Can’t find any resource saying that btree height exceeds 5, so I’m thinking it’s at worst 2x the (very small) disk read cost vs Postgres.

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

#218

Post by spprashant »

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…

Am I missing something here? Postgres does allow you to choose character collation when creating a database, as well as when creating new columns.

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

#219

Post by paulddraper »

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…

MySQL is certainly faster for writes.

https://www.uber.com/blog/postgres-to-mysql-migration/

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

#220

Post by buglungtung »

Absolutely Posgtres!!! Yesterday I had to migrate from postgres to mysql and I found some disadvantage things about mysql in my use case

There is no returning for insert/update/delete what forces me do a second query to get new data. Why dont just return what I have updated? Because it is some thing like: UPDATE wallets SET balance = balance + 1

I have to give up json function because its hard to work with it on mysql. Have to do the aggregation in my code instead

No uuid v4 support by default

Post reply on HN