HN Explorer

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

219 points by debo_ ↗ HN
Imagine you are starting a new project, and your backing datastore options are restricted to mysql or postgres (and/or their cloud-tailored equivalents.) What sort of functional requirements would cause you to choose one over the other?

363 comments

[ 2.6 ms ] story [ 319 ms ] thread
mgl
We choose Postgres for extensibility and stability :)
david927
I was going to make a similar joke: you look at them and choose Postgres
chunk_waffle
I dunno if this is still true but a couple of years ago MySQL was cheaper on AWS (RDS/Aurora) than Postgres.
maxmorlocke
For a typical db.t3.xlarge instance, you're talking about 29c/hour vs 27.2c per hour. That's $157.68 as the total difference for one year's runtime, when the whole instance cost for postgres would be $2540.4 for the year, or about 6%. The larger the machine, the closer to parity. Given the absolutely small difference, I hope this isn't the dividing line in any commercial project.
chunk_waffle
Again, I don't still know if this is the case, but you could use smaller instances with MySQL aurora than with Postgres, given the way our application worked it would have made a big difference for us if we had used mysql.

RDS/Aurora was our most expensive resource so we were looking at ways to cut that cost down and mysql was one option (though the way the app worked and the extensions that it relied on made it not a possibility.)

jstx1
Also interested in the responses, not because it seems like a close decision but because I would pick postgres by default for anything (anything that isn't simple enough to be done in sqlite).
tough
Pick postgres unless you have a good reason not too?
cryptonector
And even then you pick Postgres.
tough
Can't really loose with postgres, I concur
ac2u
You choose postgres.
mixmastamyk
1) The choice is Postgres if you care about your data at all.

2) Yes, if you are already HUGE and have requirements on Vitesse then by all means use it. If so, you are not asking this question—see #1.

3) It's a blog or something where it doesn't matter, use a static site generator.

bellBivDinesh
MySQL for the quick and dirty and Postgres for anything else
itake
Why is mysql better for quick and dirty? I feel like pg extensions offer a lot more "dirtiness" running inside pg than mysql has.
weaksauce
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
itake
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.

weaksauce
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.
gardenhedge
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.
mort96
Ok, here's one: When you give MySQL invalid data, its standard behavior is to just silently store garbage in your database in many cases where PostgreSQL would've told you that your data is invalid.

MySQL's handling of unicode has also been terrible historically, with way too many foot guns, but I don't know if that may be better with recent versions.

People aren't providing strong reasons because the question wasn't "what are some objective reasons for picking one over another", but "how do you pick between them". People are simply answering the question OP asked, and a lot of people's process is simply to pick PostgreSQL.

CaveTech
Hasnt been the case for a few major versions, unless you want to anthropomorphise your db and and hold it accountable for past behaviour.
ok_dad
> unless you want to anthropomorphise your db and and hold it accountable for past behaviour

No one is holding the literal bits that make up the database executable accountable here, they are indicating they don't trust the devs of MySQL/MariaDB to do a good job. Whether or not that is an accurate view on their part is arguable, but it's pretty clear from context that they don't think that several if/else statements had it out for them.

bakugo
> MySQL's handling of unicode has also been terrible historically, with way too many foot guns, but I don't know if that may be better with recent versions.

Unicode generally "just works" if the charset in use is utf8mb4. As of MySQL 8.0, this is the default.

mort96
Ah, that's good to hear. I haven't looked seriously at databases other than SQLite for a long time, it would be interesting to see a more up to date evaluation.
evanelias
> When you give MySQL invalid data, its standard behavior is to just silently store garbage

This is a common misconception, but this hasn't been the case for over 7 years. MySQL 5.7, released in Oct 2015, changed its defaults to enable strict sql_mode. All prior versions have hit end-of-life for support years ago, so there is no modern version of MySQL with this silent truncation behavior.

The only reason this problem persists is because Amazon RDS (all versions and forms, including Aurora) uses nonstandard default settings which disable strict mode!

That all said, I do believe Postgres is an excellent database, and a great choice for quite a large range of use-cases. But please, let's compare 2023 Postgres with 2023 MySQL, not 2023 Postgres with much older MySQL. It's only fair.

jbverschoor
Why do they have these settings? Because shit software used shit MySQL +data corruption.

It might be better now. But for me it’s in the same shithole as mongodb, php (old style, so no recovery there even though it’s possible to create proper code) and most JavaScript.

Other things is that people don’t even want to use Oracle’s MySQL but MariaDB. Why the hell would I want to run a fork of something, and still keep calling it something else.

The only reason for MySQL is wordpress

justinclift
> But please, let's compare 2023 Postgres with 2023 MySQL, not 2023 Postgres with much older MySQL. It's only fair.

Heh Heh Heh

On a humorous note, the official MySQL page (in the early 2000's) comparing MySQL vs other databases had the same problem.

They'd list the latest and greatest MySQL version, but compare it against archaic versions of the others. Clearly on purpose, because "Marketing" probably.

Asked them (via official @postgresql.org email address) to please update that page to more a recent PostgreSQL, for fairness. And was completely ignored of course.

So it's kind of amusing to see a request for fairness in the opposite direction (which I agree with anyway) ~20 years later. ;)

AdamJacobMuller
A lot of the MySQL issues historically have been fixed. UTF-8 is better now, invalid data handling is better (by default even! though your distros default config probably puts you back in permissive mode!) but regardless I'm still using Postgres every single time.

The fact is that MySQL historically was terrible for complex schemas with complex types while postgres was a pleasure to work with. MySQL had a huge performance edge for many years but that came at a cost of resiliency and reliability. MySQL has greatly improved on these key areas and Postgres has also made significant performance improvements. Systems these days are also so powerful that the database probably isn't your benchmark.

Regardless, I always use Postgres every single time because I am just scarred from years of dealing with MySQL. What even is MySQL is also an open question at this point, there's MySQL and MariaDB and Percona flavors and the founder of Percona was just ousted and I can't be bothered to put in mental energy to untangle all this to even figure out what MySQL I should be developing against.

Compare this to Postgres where the community seems to have an extremely steady hand and constant progress. There's no forks, there's no infighting, there's no drama, there's a great regular release schedule with incremental improvements.

bcrosby95
In MySQLandia, we do not speak of utf8, we only speak of utf8mb4.
et-al
If you're doing any sort of spatial logic (e.g. mapping), you'll want to use PostGIS.
l5ymep
Postgres has a worse implementation of MVCC. It results in more bloat being produced, and slightly slower updates in a highly concurrent environment. Most businesses don't operate at the scale where this matters. But on the flip side, the tooling and developer experience is much better.
nicoburns
A few reasons:

- Transactional DDL statements (schema modifications)

- Better support for UPSERT operations

- Better JSON support (including ability to index into JSON columns)

- the RETURNING statement to return data that was inserted/updated

In general Postgres is a lot more featureful than MySQL.

eqvinox
Array data type.

This has saved my ass a bunch of times. Not even as a column type, just in complex queries that otherwise became unwieldy monsters. The usual "started out simple but now it's a frankenbase" and you're stuck with a shitty schema. (The one thing worse than refactoring code: refactoring databases!)

In one case I was able to replace a 15-minute process with thousands of queries with one single query that aggregated all the data into a bunch of arrays. It completed in a few seconds. (Doing it without arrays would have been possible, but duplicated a lot of data in the result set.)

justin_oaks
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-...

iamwil
The difference is not significant enough to matter for most projects, esp just starting out. Hence, I mostly choose Postgres, since I don't like Oracle as a company very much.
[deleted]
(comment deleted)
dylan604
Whenever I see MySQL, my brain automatically sees MariaDB. What is this Oracle thing you speak of ;-)
evanelias
MySQL and MariaDB have diverged quite a bit. I recently wrote a roundup of just the differences in table functionality and DDL, and despite keeping the post focused to that relatively-narrow topic, the list of differences is getting VERY long: https://www.skeema.io/blog/2023/05/10/mysql-vs-mariadb-schem...
dylan604
>MySQL and MariaDB

Again, what is this thing you are trying to compare? I just see MariaDB and MariaDB =)

However, because of my blindness to actual MySQL, I have totally not paid attention to any differences between the two. I guess "drop in replacement" isn't actually true any more. Thanks for the info

MrThoughtful
By choosing SQLite.

No server process and a single file per DB which I can put wherever I like.

mort96
I like SQLite. But I really wish its default behavior wasn't to simply allow garbage data into the database. If I have an int column, don't let me accidentally store a string.
eesmith
https://www.sqlite.org/stricttables.html

"In a CREATE TABLE statement, if the "STRICT" table-option keyword is added to the end, after the closing ")", then strict typing rules apply to that table. ... The STRICT keyword at the end of a CREATE TABLE statement is only recognized by SQLite version 3.37.0 (2021-11-27) and later.

  sqlite> create table x (a int);
  sqlite> insert into x values ('hello');
  sqlite> select * from x;
  hello
  sqlite> drop table x;
  sqlite> create table x (a int) strict;
  sqlite> insert into x values ('hello');
  Runtime error: cannot store TEXT value in INT column x.a (19)
mort96
Yeah, I know about that, and I'm doing that on all my tables these days. It's just sad that the default behaviour is to allow garbage data, and that if you ever forget to put 'strict' on your tables you'll have a perfectly functional application with no sign that anything is wrong until you suddenly find corrupt data in your database.
lisasays
SQLite is great for its scope - but not in the same class as a full-fledged RDBMS.
MrThoughtful
Can you give a specific example of what you are missing when using SQLite?
endisneigh
Multi writer, access through the internet without 3rd party software, etc.
mort96
At a certain scale, you'll want replication or replication, which SQLite doesn't really do AFAIK. At a scale below that, you'll probably want to be able to have multiple web servers talking to one database server, which SQLite doesn't really do either. I also think SQLite's performance during heavy write workloads is worse than PostgreSQL's?

Basically, AFAIK, SQLite becomes problematic once you need more than one computer to handle requests.

justinclift
Just to point out, there are now SQLite replication and various "distributed database" projects which seem to work fairly well.

They're probably not as battle tested as the PostgreSQL ones, but they are around, have users, and are actively developed.

The ones I remember off the top of my head:

* https://litestream.io

* https://github.com/rqlite/rqlite <-- more of a "distributed database using RAFT" type of thing

* https://github.com/canonical/dqlite

mort96
Yeah, there are SQLite forks or products built on SQLite which have these sorts of features, but SQLite doesn't. They remains reasons why someone may want to use another database (such as PostgreSQL, or an SQLite fork, or a database which uses SQLite as a storage engine) instead of SQLite.

Honestly though, if I need these sorts of distribution features, I would probably prefer the database to have them built in. I don't really see the point in using SQLite at that scale.

lisasays
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.

Avamander
Performance, especially after a while and certain size.
jjav
> 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.

Doctor_Fegg
For anything involving location, choose Postgres because PostGIS is just so good.
frizlab
Good to know! I’m starting a project that will use location, and I chose Postgres, I’m happy to hear this :-)
solarkraft
PostGIS is somewhat of a standard interface for geolocation data and one of the main interfaces of QGis, the other being based on SQLite. It's kind of cool how powerful it is, being a "real" database instead of some more contrived format.

My adventures with QGis+PostGIS have also led me to discover the fun fact that even setting the TLS mode to "enforce" may enforce that you're using TLS ... but not that the certificates are correct. Silly. It also won't use the system's store when set to "full". Oh well.

By the way: I find Railway to be a great home for low-use Postgres/PostGIS databases. You basically pay for operations and the free tier is totally fine for semi-casual use (about 1-2k entities) with a few people from across the pond.

adoxyz
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.
TehShrike
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 saying "yeah, that would be nice".

They're both great options. If anybody on your team is already an expert at one of them, use that one.

robertlagrant
> 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? :)

TehShrike
3 years ago. From this comment thread https://news.ycombinator.com/item?id=35908169 I infer that Postgres still doesn't support collations.
robertlagrant
Oh - is this ci collations, not collations in general?
TehShrike
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.
funcDropShadow
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
OJFord
> 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!

vosper
Having used both in production, I agree with the above. It's not going to make or break your business or project.

I will also add that their are giant companies relying on both databases with great success. Facebook still runs on MySQL, and contribute back to it. Youtube I'm not sure about, but it did run on MySQL for a long time, well after it got massive. I'm sure examples exist for Postgres (Amazonm since they moved off Oracle?)

bombcar
And if you have experience with one and try to use the other, you may end up foot gunned by something you didn't know about.
m0llusk
PostgreSQL is a community thing and MySQL is Oracle. Maybe make some basic benchmarks for comparison?
azurelake
MySQL is still ahead operationally (no vacuum, group replication, gh-ost, optimizer hints, etc.). I would choose it unless one of the Postgres extensions is a killer fit for your project.
NuSkooler
PostgreSQL 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.

[deleted]
(comment deleted)
CuriouslyC
The only instance where I'd choose mysql over postgres is if your database needs are very simple, but you need to be able to scale hard, and your devops aren't skilled enough to manage an advanced postgres setup.
duiker101
Most answers seem written by fanboys rather than legit answers.

I would say go with what you know and are most comfortable with. You are more likely to get the better outcome.

chunk_waffle
This.

I've heard countless times that Postgres is better and I've watched talks where they show how loosey-goosey MySQL is with some things but I know how to backup, restore, tune, secure and replicate MySQL. I grok it's permissions in depth more than I ever have with Postgres and I've even written a mysql plugin in C so I have that in my toolbox if I need it. So I'd by default, usually go with MySQL (or in some cases SQLite.) but if I didn't have to administer or maintain it, and someone else was handling that I think I'd be fine with Postgres too.

[deleted]
(comment deleted)
harryvederci
I flipped a coin, it landed on its side so I went with SQLite.
dijit
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 and everyone to avoid Oracle as if it has herpes.

There are a lot of historic problems with MySQL accepting invalid data, committing data even when there are constraint issues, and having very poor transactional isolation, I am not sure if these have improved.

Truthfully, the only benefits you gain from using MariaDB or MySQL are:

* Memory tables

* Having inconsistent replicas (which can be useful when you want your downstream to have less data than your upstream and you know it won’t get updated.)

noodlesUK
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 be if I thought I later would want to have something like Vitess, for which I think there isn't really an equivalent for Postgres.
dijit
> 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.

evanelias
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".

dijit
Facebook has, at minimum, 3 teams maintaining MySQL. including a team who genuinely modifies it into submission. so much that they needed 1,700 patches to port their modified version to 8.0.

It is not relevant to the discussion to discuss how Facebook has managed to munge it to work reasonably well by pouring thousands of hours of engineer time into the effort; and MySQLs in-place upgrades absolutely do not work the way you describe consistently.

I know this because I have been in the code, and only after having experienced it. Maybe some of your lovely colleagues has helped out your particular version to be marginally more sane.

It genuinely must be nice having a dozen people who can work around these issues though, I certainly wouldn’t consider it an operational win, most companies have no DB automation engineers, or DB performance engineers or MySQL infrastructure engineers.

> Replicating from an older-version primary to a newer-version replica is fully supported.

Here also be dragons, as eluded to. I know it works quite often, I have used it.

FWIW: I ran global AAA online-only game profile systems on a handful of Postgres machines at about 120k transactions/s in 2016, I would have needed 5x as many instances to do it in MySQL, and this was only tiny part of our hosted infra.. which included a global edge deployment of game servers, auth servers, matchmaking, voice bridges and so on.

and we only had two people responsible for the entire operation

evanelias
Please educate me on how my statement about MySQL upgrades is incorrect, I'd love to hear this. I've been using MySQL for 20 years, and while 2 of those years were at Facebook, 18 were not. I've performed MySQL upgrades in quite a wide range of environments, and what you're saying here about lack of in-place upgrades or eating data is simply not aligned with reality.

I haven't made any comments regarding performance comparisons, and have also run extremely large DB footprints with tiny teams, but I don't see how any of that is relevant to the specific topic of new-version upgrade procedure!

dijit
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 machine needs manual recovery.

God help you if you don’t have innodb_file_per_table enabled to begin with too.

I know you want me to cite exactly. That will take me time to find because I stopped caring about MySQL 7 years ago, but I will dig for you.

evanelias
> 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 machine needs manual recovery.

What? This error has nothing to do with upgrades, nothing to do with manual recovery, and hasn't been a common problem for many many years.

In old versions of MySQL, it just meant you needed to configure a few things to increase the InnoDB index limit to 3072 bytes, instead of the older limit of 767 bytes:

innodb_file_per_table=ON innodb_large_prefix=ON innodb_file_format=barracuda

and then ensure the table's row_format is DYNAMIC or COMPRESSED.

But again, all of this happens by default in all modern versions of MySQL and MariaDB.

Should it have been the defaults much earlier? Absolutely yes, MySQL used to have bad defaults. It doesn't anymore.

dijit
The error I gave is a similar one to the one I used to get with “major” upgrades that happened when Ubuntu decided it was time to upgrade.

It happens and I seriously never claimed that it was an ultra common problem, merely that upgrades in Postgres are more intentional and not painful except for a little extra work between major versions. The standard upgrade path within major versions; 9.x or 10.x or 11.x or 12.x is working just the same as MySQL, except I have much more experience of MySQL completely fumbling their “automatic unattended” upgrade or even the mysql_upgrade command.

Mostly because in the real world outside of engineering cultures databasen are massively abused, ISAM tables that are constantly updated, InnoDB ibdata1 in the terabytes, poor configs, replicas that have skipped a few queries, column changes inside a transaction that failed but actually modified data, it happens. Usually I am called in to clean the mess.

Major difference here is that Postgres doesn’t leave a mess, so I never have the kind of issues that I am describing in this thread with it, and you don’t because I am guessing that you’re there when they’re installed, someone with knowledge was actively maintaining. or you have a lot of people to help with shortcomings.

I get it though. you’ve got your sunk cost knowledge of MySQL and you’ve been on large support teams for it. Maybe you’re afraid I’m suggesting that this knowledge goes out the window. and it has gotten better, but I wouldn’t give my kids watered down led infused soft drinks just because I had suffered through led poisoning. I remember coming to blows with you in other threads over the years because you think MySQL can be saved or is totally fine, but honestly, just, no.

evanelias
I'm primarily a software engineer, not a member of "large support teams". I've also worked for many years as an independent consultant, brought in when things go wrong, certainly not when they were first "installed". I'm not "afraid" of anything concerning my knowledge going "out the window". If MySQL suddenly disappeared worldwide, I could happily pivot to some other area of software engineering, or I could simply retire. Please stop make assumptions about other people who you know nothing about.

I'm responding to you because you're repeatedly posting factually incorrect items, for years. For example you and I have directly discussed the "MySQL doesn't use SemVer" thing before on HN, and yet here you are again in this thread, claiming 5.6 to 5.7 should be a "minor" upgrade.

Anyway, to the topic at hand, as others have also mentioned in this thread: historically the difficulty with Postgres upgrades has been the lack of cross-version replication, due to Postgres WAL replication being a low-level physical replication system. This made it difficult to perform an upgrade while keeping your site fully online. Perhaps the newer logical replication support makes this easier these days. I hope to learn more about it someday. If you can share your process for upgrading a Postgres cluster while keeping it online, that would be helpful and informative.

dijit
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 are extremely fast and you can test the speed using a physical replica before hand, usually it’s a couple of seconds though the docs say minutes.

4. MySQLs major version being in the minor position is exactly the kind of “you should be sure you know what you’re doing but we won’t make it obvious” territory that I really despise.

JohnBooty
While you two have agreed on approximately nothing, this has been an informative discussion and I do thank you both.
0xf8
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.

Volundr
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 documented and supported upgrade path.

I love Postgres, but as someone whose maintained both for years, upgrades (at small scale) are the one area where I'd say MySQL has Postgres beat.

dijit
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.

Volundr
Right, but now go from 11->12, which is the equivalent of the upgrade path I was describing for MySQL. I either need to install both versions and use pg_upgrade to convert the binary files, then remove 11 (and extensions may break this flow) or do pg_dump/restore.

Minor versions on both Postgres and MySQL are painless, just install and restart the server. Major upgrades on MySQL are significantly less painful.

otabdeveloper4
Does Postgres have binlog replication yet?
dijit
Yes, for over half a decade at least, but “binlog” is a MySQL term, for postgresql it has the much more apt name: write-ahead log.

it is the only official, in-binary replication mechanism.

evanelias
Postgres WAL replication is a physical replication stream. MySQL binlog replication is a logical replication stream, a higher-level abstraction which is independent of the storage engines.

Postgres does separately support logical replication now, but it has some limitations, such as not permitting replication of DDL: https://www.postgresql.org/docs/current/logical-replication-...

LammyL
Is there a good way to do case-insensitive accent-insensitive collations yet in postgresql? It’s been a holdup for using that for some use cases like searching for data, like a person’s name, in pgsql when the casing or accents don’t match perfectly.

Mssql has had this for ever, and I’m pretty sure MySQL has it as well.

dijit
Maybe this helps: https://stackoverflow.com/posts/11007216/revisions ?

My gut tells me that I would do it in the query itself though, and not rely on the collation. Maybe I am misunderstanding.

sjamaan
Look under "Update for Postgres 12 or later", there they create a collation, an index to make use of it and then a query to make use of it:

   SELECT \* FROM users WHERE name = 'João' COLLATE ignore_accent;
EwanToo
Not really, no, it's doable but not easily
pyuser583
Does Postgres have an archive mode?
dijit
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.
pyuser583
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/archive-storage-engi...

ants_a
Why is revoking permissions to run deletes/updates not an option?
[deleted]
(comment deleted)
yencabulator
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.

0xbadcafebee
> avoid Oracle as if it has herpes

herpes isn't that bad. most people will get it in their lifetime. 1 in 6 people have hsv-2, the less common variant. trying to avoid herpes is like trying to avoid chickenpox (although herpes isn't nearly as harmful as chickenpox).

you should avoid Oracle like it's a blood pathogen.

unethical_ban
hello, fellow person with herpes! (I assume)

The worst part about having it is having to talk about having it. It's really not bad as a condition separate from societal concern.

stavros
I find similar societal concern when I tell friends I use Oracle.
r2_pilot
Having Oracle experience on the resume is a positive, I suppose, but I'm not sure it's been worth the exposure.
enneff
It’s not so bad for most people but if you’re one of the unfortunate few who suffer chronic symptoms it can be truly awful. Not worth playing that lottery if you can avoid it.
[deleted]
(comment deleted)
soperj
Chickenpox is actually caused by a herpesvirus. herpes varicella zoster.
za3faran
> most people will get it in their lifetime

Citation needed.

yakshaving_jgt
Here you go.

https://www.who.int/news/item/28-10-2015-globally-an-estimat...

za3faran
Probably in certain countries, given that it is mainly an STD. There are many conservative nations where this won't be an issue hopefully.
yakshaving_jgt
As it says in the linked article, it’s a global epidemic.

If you are an adult of typical sexual activity, it is likely you have already had sex with someone infected with herpes.

That doesn’t mean you have contracted it — carriers aren’t always shedding the virus.

I’m not sure I see any correlation between a country being conservative and an absence of sexually transmitted infection; the 10 countries where HIV is most prevalent are all (as far as I’m aware) relatively conservative.

Furthermore, here are some statistics from Wikipedia on HSV which may be referring to some of these conservative countries you’re referring to:

> Turkey— High levels of HSV-1 (97%) and HSV-2 (42%) were found amongst pregnant women in the city of Erzurum in Eastern Anatolia Region, Turkey. In Istanbul however, lower HSV-2 seroprevalence was observed; HSV-2 antibodies were found in 4.8% of sexually active adults, while HSV-1 antibodies were found in 85.3%. Only 5% of pregnant women were infected with HSV-2, and 98% were infected with HSV-1. Prevalence of these viruses was higher in sex workers of Istanbul, reaching levels of 99% and 60% for HSV-1 and HSV-2 prevalence respectively.

> Jordan— The prevalence of HSV-2 in Jordan is 52.8% for men and 41.5% for women.

> Israel— HSV-1 seroprevalence is 59.8% in the population of Israel and increases with age in both genders but the adolescent seroprevalence has been declining as in most industrialized nations. An estimated 9.2% of Israeli adults are infected with HSV-2. Infection of either HSV-1 or HSV-2 is higher in females; HSV-2 seroprevalence reaches 20.5% in females in their 40s. These values are similar to levels in HSV infection in Europe.

> Antibodies for HSV-1 or HSV-2 are also more likely to be found individuals born outside of Israel, and individuals residing in Jerusalem and Southern Israel; people of Jewish origin living in Israel are less likely to possess antibodies against herpes. Among pregnant women in Israel a small scale cross sectional study found the prevalence of HSV-2 infection was 13.3% and that of HSV-1 was 94.9%. The HSV-2 infection rate was 3-fold higher among immigrants from the former Soviet Union (27.5%) than among Israeli-born Jewish and Arab women (9%). Approximately 78% of HSV-2 infections in Israel are asymptomatic. HSV-1 causes 66.3% of genital herpes in the Tel Aviv area.

> Syria— Genital herpes infection from HSV-2 is predicted to be low in Syria although HSV-1 levels are high. HSV-1 infections is common (95%) among healthy Syrians over the age of 30, while HSV-2 prevalence is low in healthy individuals (0.15%), and persons infected with other sexually transmitted diseases (9.5%). High risk groups for acquiring HSV-2 in Syria, include prostitutes and bar girls; they have 34% and 20% seroprevalence respectively.

za3faran
> If you are an adult of typical sexual activity, it is likely you have already had sex with someone infected with herpes.

Again, not in conservative populations where marriage is the typical way to have sexual relations. Syria is one example that you quoted. A smart person would get his/her partner tested if they suspect anything before getting married.

yakshaving_jgt
I'm trying to work out which logical fallacy you're employing here. No True Scotsman? Personal Incredulity?

In any case, assuming that infidelity or anything else sinful/haram doesn't occur in "conservative" populations strikes me as frightfully naïve.

The data is in. Arabs get herpes too.

za3faran
Infidelity does happen, but it's much much less common and is severely looked down upon.

Edit: it seems that HSV-1 is not an STD, but it causes genital herpes if engaging in oral sex? Which can explain the big gap between the two in conservative cultures.

hamilyon2
As a person who has herpes firmly in his nerves, I would say don't underestimate herpes.
scotty79
Suddenly the analogy got very accurate.
srcreigh
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-nonclustered-index.w...

[2]: https://josipmisko.com/posts/clustered-vs-non-clustered-inde...

Mind boggling how many people aren't aware of primary indexes in MySQL that is not supported at all in Postgres. For certain data layouts, Postgres pays either 2x storage (covering index containing every single column), >50x worse performance by effectively N+1 bombing the disk for range queries, or blocking your table periodically (CLUSTER).

In Postgres the messiness loading primary data after reaching the B-tree leaf nodes pollutes caches and takes longer. This is because you need to load one 8kb page for every row you want, instead of one 8kb with 20-30 rows packed together.

Example: Dropbox file history table. They initially used autoinc id for primary key in MySQL. This causes everybodys file changes to be mixed together in chronological order on disk in a B-Tree. The first optimization they made was to change the primary key to (ns_id, latest, id) so that each users (ns_id) latest versions would be grouped together on disk.

Dropbox scaling talk: https://youtu.be/PE4gwstWhmc?t=2770

If a dropbox user has 1000 files and you can fit 20 file-version rows on each 8kb disk page (400bytes/row), the difference in performance for querying across those 1000 files is 20 + logN disk reads (MySQL) vs 1000 + logN disk reads (Postgres). AKA 400KiB data loaded (MySQL) vs 8.42MiB loaded (Postgres). AKA >50x improvement in query time and disk page cache utilization.

In Postgres you get two bad options for doing this: 1) Put every row of the table in the index making it a covering index, and paying to store all data twice (index and PG heap). No way to disable the heap primary storage. 2) Take your DB offline every day and CLUSTER the table.

Realistically, PG users pay that 50x cost without thinking about it. Any time you query a list of items in PG even using an index, you're N+1 querying against your disk and polluting your cache.

This is why MySQL is faster than Postgres most of the time. Hopefully more people become aware of disk data layout and how it affects query performance.

There is a hack for Postgres where you store data in an array within the row. This puts the data contiguously on disk. It works pretty well, sometimes, but it’s hacky. This strategy is part of the Timescale origin story.

Open to db perf consulting. email is in my profile.

JohnBooty
Thanks for that informative link. It's rare in these sorts of discussions.
Shorel
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 order as the keys in the primary index, it is a special case and these tables are called IOT, index ordered tables.

The disadvantages of IOT are that inserts are slower, because in a normal table, the data is appended to the table, which is the fastest way to add data, and only the index needs to be reordered. In an IOT, the entire table storage is reordered to take the new data into account.

Select queries, OTOH, are much faster when using IOT, for obvious reasons, and this is what you describe in your comment.

If you use TEXT, BLOB, or JSON fields, even in MySQL, the actual data is stored separately.

srcreigh
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 a B-tree node and rare b-tree rebalance. Most b-tree leaf nodes have empty space to allow for adding new data without shifting more than a few hundred other rows. Also, non-IOT tables also need to do a similar process to write to each of its indexes. Last, it’s sort of a tossup since if you’re appending to an IOT table frequently, the right edge of the B-tree is likely cached. (similarly for any small number of paths through the primary index B-tree). At worst Postgres heap will need to surface one new heap disk page for writing, although I’m sure they have some strategy for caching the pages they write new data to.

Sorry to spam this info! Glad to see we both love databases and I’m always please to see engagement about this topic!

[deleted]
(comment deleted)
[deleted]
(comment deleted)
ideal_gas
(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 cached in the visibility map or else don't really get the benefit of index only scans.

But OTOH, I've read that secondary indexes cause other performance penalties with having to refer back to the data in clustered indexes? Never looked into the details because no need to for postgres which we've been very happy with at our scale :)

[1] https://www.postgresql.org/docs/current/storage-hot.html

srcreigh
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.

scosman
I made same comment elsewhere before finding this comment. I can vouch for this speed up in the ratio of records per page. It is very real. Only applies for small records where you can pack many rows into a page, and where you can cleanly partition by user/tenant, but that’s common enough.

I will say: we kept every table we didn’t have to migrate for perf reasons in Postgres, and never regretted it.

Edit: and the index “fix” for Postgres doesn’t work often. Postgres will validate the row on disk, even if it’s in the index, if the page’s visibility map isn’t set. If you data isn’t write once, there’s a decent chance your page is dirty and it will still make the “heap” fetch.

boloust
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.

srcreigh
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.

arp242
I've definitely run in to these kind of issues and learned about them the hard way, but I found that in PostgreSQL it's quite a bit easier to understand what is actually going on due to better documentation and tooling, and I've found this very valuable. Maybe it's just because I've spent more time with PostgreSQL than MariaDB, but MariaDB has often left me quite a bit more confused (on performance, but also other topics).
frodowtf
That was a great read in contrast to all the "there's no reason to use mysql" nonsense in this thread
srcreigh
That's what I'm going for! I love this type of info, so against the grain and useful it's basically a scandal. :-) Check out your sibling comment about pg_repack though, it's a really awesome PG perf improvement tool.
singron
If your pg database improves with CLUSTER, you can use pg_repack instead to achieve the same effect without downtime. Besides reordering the heap, it will also clear out bloat from the heap and indexes. I highly recommend partitioning if you have heavy write traffic on large tables since that will keep overhead low and make it complete faster.
srcreigh
Thanks. I actually thought of similar pg_repack concept on my own a couple days ago (influenced by gh-ost probably..) I was googling stuff like how to switch a PG replica to master. I was imagining having 2 dbs and running CLUSTER on the inactive one. Probably wouldn't work but anyways I found about pg_repack after researching.

I'm now super interested in the perf aspects of running pg_repack. It would definitely require scratch storage space to be able to copy over the largest table in the DB (I'd guess 2.1x the largest table vs 2x the total DB size). I imagine repacking isn't as efficient as putting stuff into a B-tree. But I wouldn't expecting it to be anything like 50x worse like I portrayed above.

singron
It does CREATE TABLE ... AS SELECT * FROM table ORDER BY, which isn't too bad. Then it runs CREATE INDEX for all your indexes, which is usually faster than CREATE INDEX CONCURRENTLY. However, while these are running, a trigger is inserting records for all new writes into a special log table. Once all the indexes are created, it replays that log table until it's empty, and then it briefly locks the source table, flushes the last bit of the log table, and swaps the tables.

If you have a lot of write traffic, that log table will get really big, and pg_repack will have to do a lot of work to replay it. It's possible that the log table grows faster than pg_repack can replay it, which will cause it to never finish and eventually exhaust your storage space. You can also run into an issue where the log table never gets completely empty, and pg_repack never initiates that final lock and swap.

Partitioning helps a lot because it divides both the size of the tables and the write traffic to each table.

za3faran
MySQL is free, regardless of Oracle's ownership.
asdfman123
The main problem with herpes is the stigma against it. Don't besmirch it by associating with Oracle.
eyelidlessness
The only reason I’d consider MariaDB, which I’m surprised I don’t see mentioned currently, is its bitemporal features. There are whole worlds of problems solved that are almost universally badly done in schema/business logic instead.

Granted I haven’t had to make a decision like this for several years, I’ve hardly even touched a database except to debug some stuff on localhost that’s outside of my explicit purview. So maybe Postgres solutions have narrowed the gap on this without my knowing it.

paulddraper
MySQL is certainly faster for writes.

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

arp242
I would be wary putting too much stock in a seven year old post on the topic; lots of stuff has changed. Specifically, at least one person claimed that a patch greatly improved PostgreSQL for this use case: https://news.ycombinator.com/item?id=26285452
paulddraper
The MVCC requirement is still there.

It's like saying C is faster than Ruby. It always will be.

supergram
> * Memory tables

PostgreSQL has memory tables too.

throw0101b
> 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 PostgreSQL.

davidgerard
yeah, that's the use case "I run an application that requires or is highly optimised for MySQL".
jaachan
We ran a Galera cluster for 4 days and it died on the first ALTER TABLE we did. Only option was to reduce it down to a Primary / Replica setup. I really can't recommend anyone using this.
avinassh
how do multi master MySQL handles conflicts?
erulabs
Use 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 gets more love in extremely large enterprise-level organizations, and it shows.

Use Postgres if you need some of the quite-good extensions, most notably PostGIS, or if you just want things to work; most documentation will be postgres flavored. Postgres gets more love from web-developers, and it shows.

fzeindl
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 switched 15 years ago and never looked back. Have done 15-50 projects since in psql.

[deleted]
(comment deleted)
amflare
Spoken like someone who switched 15 years ago and never looked back.
WJW
With a sane migration tool like pt-osc or gh-ost you can absolutely abort in the middle. What's more, you can pause in the middle or even slow down in the middle based on arbitrary logic (ie, pause migration if replication delay rises above a certain value). Postgres is nice and transactional DDL has its place but postgres stopped halfway through IMO. Vanilla Postgres > vanilla MySQL, but the migration story of MySQL + tooling is so far beyond Postgres + tooling that it's not even funny.

That said, if you don't expect to have tables with 100m+ rows, even vanilla postgres will be good enough.

barrkel
Production MySQL databases of any significant size use pt-osc or gh-ost for schema changes, and these can be throttled, paused, aborted and so on.
MangoCoffee
pick the database that you are most familiar with
MrResearcher
Postgres doesn't have type hints and it might create a false impression of robustness until it messes up table statistics and does FULL SCAN against a table with millions of rows ignoring all indicies. It happens super rarely though, so if you run anything critical, you'll probably be down only for a few hours once a year or two. Be ready for this to happen though.

Apart from that (and noticeably higher memory consumption), Postgres is most likely preferable.