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

281–290 of 366 posts

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

#281

Post by ants_a »

viraptor wrote: post #200
pawelduda wrote: post #175

Earlier quoted context omitted.

What's the ratio of solving DB perf issues by optimizing it and letting the planner do its work, to telling it what to do? For me it's like 1000:1. And that one case I remember was perfectly solvable the regular way, with a little more time.

The problem is that when you you need to tell the planner what to do, you can do it in MySQL, but not postgres. Imagine you've got a production database with lots of traffic which suddenly can't handle anything because it inserted an extra row which tipped the balance and now takes seconds to process a common query. Do you know how to fix the table statistics quickly? Do you know how to change that query to force the…

There is the pg_hint_plan extension that gives most of what you would want in a hinting system.

I think a better fix lies in the direction of making queries more of a first class object with options to nail down plans, add custom logic to pick plans dependent on parameter values, etc.

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

#282

Post by ants_a »

pyuser583 wrote: post #271
dijit wrote: post #97

Earlier quoted context omitted.

If you say what you’re trying to actually achieve I can help with a solution, but asking if it supports an arbitrary feature is not going to get the answer you want because depending on what you’re actually using an archive table for, Postgres might have something already built in but it will almost assuredly not be exactly like an archive table storage type.

Sorry, ARCHIVE is a MySQL storage engine. It supports only non destructive transactions: INSERT, REPLACE, and SELECT, but not DELETE and UPDATE. It’s an excellent alternative to use a WORM drive when you’re trying to preserve everything (say, a list of financial transaction). I’ve looked for something like this in Postgres (which I love!), but sadly it doesn’t seem supported. https://dev.mysql.com/doc/refman/8.0/en/a…

Why is revoking permissions to run deletes/updates not an option?

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

#283

Post by Someone »

jesterson wrote: post #274
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.…

Working with MySQL (MariaDB, but doesn't make much difference). Never get any issues that couldn't be explained by architectural or development mistakes. Just as example - how do you create read-only user (SELECT only) in Postgres? In MySQL it's extremely simple and it works, while in Postgres it's a nightmare to create and maintain

> Just as example - how do you create read-only user (SELECT only) in Postgres? In MySQL it's extremely simple and it works, while in Postgres it's a nightmare to create

Isn’t that

  GRANT SELECT ON ALL TABLES IN SCHEMA foo TO bar;
?

> and maintain

If you mean you want to grant a user select rights to whatever table gets created in the future (a somewhat questionable idea from a security viewpoint):

  ALTER DEFAULT PRIVILEGES IN SCHEMA foo GRANT SELECT ON TABLES TO bar;
I think both are possible in PostgreSQL 9 and later (https://www.postgresql.org/docs/9.0/sql-grant.html , https://www.postgresql.org/docs/9.0/sql-alterdefaultprivileg...)

That version is from 2010.

I guess the lesson is that both these systems evolve fairly rapidly. You can’t use quirks you remember from over 5 years ago to judge their current versions.

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

#285

Post by 0xf8 »

JohnBooty wrote: post #155
dijit wrote: post #122

Earlier quoted context omitted.

1. The log-replication method of upgrading can be performed using the built-in logical replication facilities as well as using external logical replication systems such as pglogical, Slony, Londiste, and Bucardo. Most of which have existed essentially forever. 2. Failovers of any database are not instant, but they are indeed quick! So let’s not claim that you can do an upgrade with zero downtime. 3. In-place upgrades…

While you two have agreed on approximately nothing, this has been an informative discussion and I do thank you both.

I echo the sentiment and think yours is likely the most pertinent takeaway having made it this far absent reaching any consensus whatsoever haha.

It was nevertheless a pretty epic journey of dialectic discourse plunging _deep_ into the esoteric and nuanced realm of expert-level technical minutiae. A mostly intellectual journey, albeit distinctly punctuated by an undertone of emotional angst that steadily progressed in its growing intensity in a manner proportional to the magnitude of your collective disagreement… epic indeed.

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

#286

Post by davidgerard »

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.

> I'm sure there are use cases where MySQL will be the better choice over postgres, but the future for the stack looks bleak.

see, I'm pretty sure there basically weren't. It lucked out at the right moment in the late 1990s. Also, Slashdot used it.

The only use case I can think of is when you want an application, and it requires or is highly optimised to MySQL. Otherwise, it should actively be avoided.

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

#287

Post by davidgerard »

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…

yeah, that's the use case "I run an application that requires or is highly optimised for MySQL".

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

#289

Post by 0xf8 »

spudlyo wrote: post #82

This is like asking how you'd choose between Emacs and Vim, Mac and PC, Monoliths and Microservices, Functional and Object Oriented .. you're likely going to elicit a lot of passion and not a ton of objective information. For most applications, either choice is going to be just fine. Use what your team has the most experience with. If you have no experience, try them both out and go with whatever you're most comforta…

Nah, trivially the correct answers are:

Vim, *NIX (so Mac), monoliths, and lambda calculus all the way—FP!

jk, but FWIW I think sometimes, in rare instances, there does exist a pretty unequivocal consensus “right answer” to this sort of question … maybe such as like: Git vs any other distributed VCS ?

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

#290

Post by funcDropShadow »

robertlagrant wrote: post #107
TehShrike wrote: post #41

Earlier quoted context omitted.

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…

> 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?) How long ago was this? :)

According to the documentation of Postgres 12 [1] it is possible to use so called non-deterministic collations, which may express case-insensitivity. If that is what you need.

The documentation of Postgres 11 [2] states that this was not possible:

    Note that while this system allows creating collations that “ignore case” or  “ignore accents” or similar (using the ks key), PostgreSQL does not at the moment allow such collations to act in a truly case- or accent-insensitive manner. Any strings that compare equal according to the collation but are not byte-wise equal will be sorted according to their byte values.
[1]: https://www.postgresql.org/docs/12/collation.html [2]: https://www.postgresql.org/docs/11/collation.html
Post reply on HN