Its simple AF - I just always pick the well proven PostgreSQL database.
... if that is too big I use SQLite.
Ask HN: It's 2023, how do you choose between MySQL and Postgres?
61–70 of 366 posts
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#62One big factor that keep us on MySQL is the MyRocks engine. We have huge databases with billions of rows. The MyRocks enable the use of it with heavy compression, that PostgreSQL can´t handle it, as it is much slower and uses 30x more disk usage, even with heavy TOAST tuning and/or ZFS compression.
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#63There 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…
I think that the only reasons to choose MySQL (or Maria) over Postgres for a new project are operational. Postgres is probably the better database in almost all respects, but major version upgrades are much much more of a pain on Postgres than on almost any other system I have ever used. That being said, I would choose Postgres pretty much every time for a new project. The only reason I would use Maria or MySQL would…
> but major version upgrades are much much more of a pain on Postgres than on almost any other system I have ever used.
This is a thread comparing MySQL and Postgres and your claim is that postgres is harder to do major version upgrades than anything you have used??
Context is important here, have you honestly actually upgraded a MySQL node? It’s a lesson in pain and “major” version changes happen on minor versions, like the entire query planner completely trashing performance in 5.6->5.7
Postgres has two forms of updates:
1) in place binary upgrade.
Fast, clean, simple, requires that you have the binaries for the old and the new database.
2) dump/restore.
Serialise the database into text files, load a new database and deserialise those files into it.
Slow, but works flawlessly & consistently with relatively low danger.
MySQL can only do option 2.
You can sort of fake an “update” by abusing the fact that MYSQLs replication offers no guarantees, so you can make a new server a replica; then roll over. But it is impossible to know what data was lost in that transition and MySQL will happily continue without ever telling you.
I have experienced this behaviour in large e-commerce retailers. MySQL was very popular for a very long time and I am intimately aware of operational best practices and how they are merely patching over an insane system.
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#64If you are using .NET then Postgres might be better choice. (much better support for drivers and default ORM).
If you need replication go with MySQL.
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#65Earlier quoted context omitted.
SQLite is great for its scope - but not in the same class as a full-fledged RDBMS.
Can you give a specific example of what you are missing when using SQLite?
Here's a good place to start:
https://www.sqlite.org/whentouse.html
https://www.sqlite.org/quirks.html
Full-scale RDBMSs, especially Postgres, have lots of goodies that either SQLlite doesn't have (or which it does have, but which aren't so richly featured). Once you've gotten hooked on a few of these, the distinction will feel a lot more clear.
Meanwhile the tipping points in favor of SQLite seem to be embedded systems, and its whole "service-less" architecture and plain ease of use. Which is why it still gets lots of love, for those contexts.
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#66Use MySQL if you're expecting to have to do large operational database tasks re: migrations, maintenances, with no ability to go offline. gh-ost, percona-osc, the new INSTANT DDL stuff, is all quite far ahead in MySQL-land. Additionally, Vitess and Planetscale are making huge strides in MySQL performance. There are more people and guides in the world to help recover even the most mutilated of MySQL databases. MySQL g…
This is wrong. MySQL does not support transactional DDL, so you cannot run migration and abort them in the middle. Always use postgresql. It's more logical, more extensible, saner, supports many extensions and is more predictable. MySQL is inconsistent crap, that trades away consistency, correctness and stability for a little bit of performance in standard use cases. Do yourself a favor and always use postgreSQL. I s…
[deleted]
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#67PostgreSQL every time, unless you have a specific reason, or as already pointed out, you're sure you don't just need SQLite. PSQL in my experience has vastly better tooling, community, and is ahead of the curve tech wise. Same with extensions availability. Or perhaps you need to move away from it to say CockroachDB, or similar which is much easier.
nit: psql is the command line client. postgres or pg are the more common shortenings of PostgreSQL.
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#68Friends 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), bad transactions (MyIsam lacks transaction integrity), and the number of Mysql gotchas being a really long list influenced me.
In time I actually found out that I had underestimated one of the most important attributes of Postgres that was a huge strength over Mysql: the power of community. Because Postgres has a really superb community that can be found on Libera Chat and elsewhere, and they are very willing to help out, I think Postgres has a huge advantage over Mysql. RhodiumToad [Andrew Gierth] https://github.com/RhodiumToad & davidfetter [David Fetter] https://www.linkedin.com/in/davidfetter are incredibly helpful folks.
I don't know that Postgres' licensing made a huge difference or not but my perception is that there are a ton of 3rd party products based on Postgres but customized to specific DB needs because of the more liberalness of the PG license which is MIT/BSD derived https://www.postgresql.org/about/licence/
Some of the PG based 3rd party DBs:
Enterprise DB https://www.enterprisedb.com/ - general purpose PG with some variants
Greenplum https://greenplum.org/ - Data warehousing
Crunchydata https://www.crunchydata.com/products/hardened-postgres - high security Postgres for regulated environments
Citus https://www.citusdata.com - Distributed DB & Columnar
Timescale https://www.timescale.com/
Why Choose PG today?
If you want better ACID: Postgres
If you want more compliant SQL: Postgres
If you want more customizability to a variety of use-cases: Postgres using a variant
If you want the flexibility of using NOSQL at times: Postgres
If you want more product knowledge reusability for other backend products: Postgres
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#69Earlier quoted context omitted.
I think that the only reasons to choose MySQL (or Maria) over Postgres for a new project are operational. Postgres is probably the better database in almost all respects, but major version upgrades are much much more of a pain on Postgres than on almost any other system I have ever used. That being said, I would choose Postgres pretty much every time for a new project. The only reason I would use Maria or MySQL would…
> but major version upgrades are much much more of a pain on Postgres than on almost any other system I have ever used. This is a thread comparing MySQL and Postgres and your claim is that postgres is harder to do major version upgrades than anything you have used?? Context is important here, have you honestly actually upgraded a MySQL node? It’s a lesson in pain and “major” version changes happen on minor versions,…
MySQL doesn't use SemVer. MySQL 5.6 vs 5.7 are different "release series", and switching between them is considered a "major" version change.
MySQL absolutely fully supports in-place binary upgrades, saying otherwise is pure FUD. And the upgrade process in MySQL doesn't even iterate over your table data in any way, so claiming it will cause "data loss" is also pure FUD.
At Facebook we automated rolling in-place updates of our entire fleet, with new point builds of fb-mysql going out several times a month, to the largest MySQL deployment in the world. Worked flawlessly and this was a full decade ago.
MySQL is widely considered easier to upgrade (relative to Postgres) because MySQL's built-in replication has always been logical replication. Replicating from an older-version primary to a newer-version replica is fully supported. When upgrading a replica set, the usual dance is "upgrade the replicas in-place one at a time, promote one of the replicas to be the new primary while temporarily booting out the old primary; upgrade the old primary and then rejoin the replica set".
Re: Ask HN: It's 2023, how do you choose between MySQL and Postgres?
#70By choosing SQLite. No server process and a single file per DB which I can put wherever I like.
> By choosing SQLite.
It's a good reminder to give some thought to whether one actually needs MySQL|Postgres. If not, SQLite is the way to go. Most of my code that uses a DB is using SQLite.
But obviously, if you actually need MySQL|Postgres then SQLite is not an option.