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

201–210 of 366 posts

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

#201

Post by pierat »

za3faran wrote: post #170
pierat wrote: post #59

Friends dont let friends use #Horracle software. That includes VirtualBox, MySQL, Horracle Cloud. Just step back. Walk away. Do not pass go, do not collect $20000 lawyers fees for unintended actions.

That's quite silly. VirtualBox is great, and so is MySQL. They're also both OSS, so no lawyers in the sense you're implying.

Then you don't know what you're talking about, and are ignorant of the risks of using VirtualBox.

That VirtualBox extension pack? That aint free... well, it is for personal use only because they're not shaking individuals down. However, Oracle watches what domains download that extension pack, and sues companies when too many employees download it.

You can see that in this reddit thread: https://www.reddit.com/r/sysadmin/comments/d1ttzp/oracle_is_...

And this article also addresses the risks of dealing with Horracle.

And a company I worked for had dealings with them as well. Again, Horracle played dirty and did bullshit, except over Horracle DB itself.

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

#202

Post by tommy_axle »

It depends on the project but a couple things that would help me pick MySQL (MariaDB) more often (wish list) are:

- Row-level security (RLS) - depending on the tenancy and DB level safe-guards you need Postgres supports it and MariaDB doesn't - transactions for schema changes - Postgres (and even sqlite) support this but MariaDB doesn't. Due to not having it, if there's a faulty migration manual clean-ups might be required to get it back to the state you need

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

#203

Post by bkuehl »

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 and eventually gave up, with MySQL being a pretty easy switch with some minor stored procedure rewrites.

Also it tends to do things way differently than every other DB. VACUUM is just a completely different concept that can footgun you pretty fast.

Postgres is pretty powerful but it has certainly made some interesting design choices.

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

#206

Post by OJFord »

kiernanmcgowan wrote: post #105
gmac wrote: post #83

Postgres. Fast, full-featured, rock-solid, and a great community. I think many of us can’t be bothered to go over (again) the issues we’ve had with MySQL in the past. The last straw for me was about ten years ago, when I caught MySQL merrily making up nonsense results for a query I’d issued that accidentally didn’t make any sense. Very likely this particular issue, and others like it, have been fixed in the meantime.…

Having used postgres for the past decade, I tried MySQL for a side project to see whats changed with it. The sad answer is that it feels like nothing has changed - Oracle seems to have let what used to be a core technology of the industry languish. I'm sure there are use cases where MySQL will be the better choice over postgres, but the future for the stack looks bleak.

It took me until here to realise we were talking about MySQL, not SQLite, because honestly 'in 2023' isn't that the comparison, pg vs sqlite?

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

#207

Post by justin_oaks »

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.

One thing that bit me on MySQL is that triggers don't execute on cascade deletes/updates: https://bugs.mysql.com/bug.php?id=11472

That issue was filed in 2005 and it still isn't fixed.

Another gripe I have is that MySQL's JSON Path capabilities are much more limited than the Postgres JSON Path capabilities. MySQL doesn't have filter expressions nor any operators/methods but Postgres does. Don't get me wrong, neither is jq, but I hate having to jump through extra hoops to get my JSON in the right format.

Compare

https://www.postgresql.org/docs/current/functions-json.html#...

and

https://dev.mysql.com/doc/refman/8.0/en/json.html#json-path-...

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

#208

Post by boloust »

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…

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 for efficient range queries on a single clustering key by making all other queries slower.

This tradeoff may or may not be worth it depending on your query patterns. In my experience, you can often get away with adding some subset of columns to a non-clustered index to make it covering, and get efficient range queries without making a copy of the entire dataset.

And even with clustered indexes, as soon as you want a range query that's not supported by your clustered index, you're faced with the exact same choices, except that you have to pay the cost of the extra B-tree traversals.

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

#210

Post by callalex »

JohnBooty wrote: post #146
kiernanmcgowan wrote: post #105

Earlier quoted context omitted.

Having used postgres for the past decade, I tried MySQL for a side project to see whats changed with it. The sad answer is that it feels like nothing has changed - Oracle seems to have let what used to be a core technology of the industry languish. I'm sure there are use cases where MySQL will be the better choice over postgres, but the future for the stack looks bleak.

Oracle seems to have let what used to be a core technology of the industry languish I think slowly squeezing the life from MySQL was a very explicit goal for them. After the big wins (Wal-Mart, etc) MySQL had 15-20 years ago I think it was very clear MySQL was going to chip away at more and more of Oracle's business. I wonder how much Oracle spends on MySQL every year? They're spending a lot of money to keep MySQL at…

You are looking for long term planning where there is none. The only relevant question for those in charge of the project is: “How do I make my quarterly report to investors look slightly better than last quarter’s?”
Post reply on HN