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

321–330 of 366 posts

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

#321

Post by yencabulator »

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…

Saying that REPLACE is non-destructive is a bit weird. Overwriting data is destructive, by definition -- and even if ARCHIVE is append-only underneath, it seems this use of REPLACE is very much a "logical overwrite", as there doesn't seem to be any kind of time travel view of the old state.

Also, you might be interested in Parquet, perhaps as seen through Delta Lake https://delta.io/ or Postgres Foreign Data Wrappers like https://github.com/adjust/parquet_fdw -- Delta Lake's simple "LSM of Parquet files in an object store" design is pretty sweet.

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

#322

Post by evanelias »

irrational wrote: post #314
evanelias wrote: post #268

Earlier quoted context omitted.

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.

What are the “plenty of examples of things MySQL can do that Postgres can’t”? I honestly can’t think of one, much less “plenty”.

These are just a few random ones off the top of my head!

* Handling several thousand connections per second without needing a proxy or pool (MySQL's connection model is thread/conn vs Postgres using process/conn)

* Handle extremely high volume of primary key range scan queries (MySQL's InnoDB uses a clustered index, see https://news.ycombinator.com/item?id=35909053)

* Handle workloads that lean heavily on UPDATE or DELETE, without major MVCC pain (see https://ottertune.com/blog/the-part-of-postgresql-we-hate-th... for example)

* Semi-synchronous logical replication, for environments which cannot tolerate any data loss during failover; this ensures the statements have reached at least one replica but without the huge latency of synchronous replication

* Use index hints, to ensure random index stats changes don't cause unexpectedly negative query plan adjustments (see discussion in subthread https://news.ycombinator.com/item?id=35909340)

* Handle very high-volume OLTP workloads using direct I/O, since InnoDB's buffer pool design is completely independent of filesystem/OS caching

* Achieve best-in-industry compression by using the MyRocks storage engine (MySQL's pluggable storage engine design has a lot of tradeoffs but it is inherently what makes this even possible)

* Use UNSIGNED int types, to store twice as high max value in the same number of bytes, if you know negative numbers are not going to be present

* Use case-insensitive or accent-insensitive collations out-of-the-box without having to monkey with confusing user-defined CREATE COLLATION

* Ease-of-use commands like SHOW CREATE TABLE

* Silly cosmetic things like the ability to reorder columns in an ALTER TABLE (see https://wiki.postgresql.org/wiki/Alter_column_position)

* A tooling ecosystem which includes multiple battle-tested external online schema change tools, for safely making alterations of any type to tables with billions of rows

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

#323

Post by dijit »

Volundr wrote: post #84
dijit wrote: post #78

Earlier quoted context omitted.

Because it depends so much on your storage engine and schema, I have never seen it recommended because there are circumstances where you have data which is unrepresentative unless you are very careful or you don’t actually use the expressiveness of the DB. I mean, I’ve also seem my share of “ERROR 1071 (42000) at line xxx: Specified key was too long; max key length is xxx bytes” randomly that basically means the mach…

FWIW while I use Postgres for my own development I've had to administer a number of MySQL servers for other devs. Upgrades have always been updating the MySQL package, restarting MySQL, then running `mysql_upgrade`, and restart the server again. I'm pretty sure the mysql_upgrade has even been missed a number of times and it's worked fine. I won't say it's impossible you ran into issues doing this, but it is the docum…

as long as you upgrade with a minor version, you will have the same experience with postgres.

11.0->11.2 will work totally fine, with no command needed.

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

#324

Post by tonymet »

donatj wrote: post #109

Unpopular opinion on HN apparently, but MySQL - It's less featureful, and I'd consider that a strong virtue in the YAGNI camp - less to go wrong, less mental overhead. - Maintenance is simpler and far less necessary in my general experience. - Replication is simpler and more reliable. - You can tell the query optimizer what to do. When this is needed, you'll be thankful. It's a godsend. That said, I wouldn't run Orac…

less features, simpler admin, more compatibility, more familiarity. I agree

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

#325

Post by weaksauce »

itake wrote: post #318
weaksauce wrote: post #54

Earlier quoted context omitted.

yeah that's a weird take. if you want quick and dirty you use sqlite and if you need something more you go with postgres. some replication things are nicer in mysql apparently but postgres is the better option for most workflows

Are there easy offsite backup solutions for SQLite? The easy deploy tools (digital ocean apps, doku, beanstalk, heroku, etc) destroy the instance (losing state). You need to configure an offsite backup tool for your db or you have to manually setup a server to live forever. Having a separate db machine is so much easier to setup and plays better with the diy tools.

if you're working with ephemeral services like those then no you'd never use sqlite because they are temporary. in my mind quick and dirty being a backing store for something like a local script where you wouldn't want to spin up a full server for... or a small webserver that you are running locally. if you are going to do a proper website you'd ideally want to have something with more flexibility and data guarantees. sqlite is grand for a specific sort of read heavy or single access writes workflow.

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

#326

Post by TehShrike »

robertlagrant wrote: post #277
TehShrike wrote: post #137

Earlier quoted context omitted.

3 years ago. From this comment thread https://news.ycombinator.com/item?id=35908169 I infer that Postgres still doesn't support collations.

Oh - is this ci collations, not collations in general?

A lot of the collations are useful in different circumstances – but the CI ones probably come up the most often, and are necessary to store email addresses in a way where you store the case information that the user typed in, but can do case-insensitive lookups against in the future.

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

#327

Post by Thetawaves »

MySQL - Better performance and tune-ability. Higher connection limits/lower connection costs. Better multi-master and clustered replication story. 2+ decades in extremely high data and availability environments (telecom).

Postgres - Better SQL semantics.

Having supported both in 'bet the business' scenarios, I would choose MySQL hands down. Operating state of the art HA postgres clusters today feels like running MySQL clusters in 2005.

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

#328

Post by EamonnMR »

michaelcampbell wrote: post #300
EamonnMR wrote: post #150

Earlier quoted context omitted.

I would disagree on maintenance being simpler. I have never had Postgres randomly munge a table and require me to run a command to fix it.

My needs are meager (simple CRUD, low volume), but I haven't had that happen in MySQL either, in over 15 years of running it in production. Not saying it can't happen, but I don't think it's a common occurrence.

WordPress may be uniquely bad.

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

#329

Post by EamonnMR »

jjeaff wrote: post #227
EamonnMR wrote: post #150

Earlier quoted context omitted.

I would disagree on maintenance being simpler. I have never had Postgres randomly munge a table and require me to run a command to fix it.

I have not had that happen in MySQL either, at least, not with innodb. what command would that be? I do remember getting bad tables with myisam tables a decade ago, sometimes after a bad shutdown.

I believe this was the command in question: https://dev.mysql.com/doc/refman/8.0/en/repair-table.html

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

#330

Post by justin_oaks »

Nathanba wrote: post #249
justin_oaks wrote: post #211

Earlier quoted context omitted.

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/

You're quite right. The LGPL license would allow you to distribute the connector along with a non-GPL application.

This is good to know. Thanks!

Post reply on HN