Ask HN: PostgreSQL or MySQL?

You're severely underestimating the amount of effort that went into MyRocks. The development and deployment was a 3+ year effort spanning quite a few different teams.

Automating the physical rollout (as you correctly described) is the easy part. That doesn't account for all the many difficult spots that occurred prior to it: the massive complexity of mysql storage engine development in general; huge numbers of various performance edge-cases; converting replication-related system tables to MyRocks in order to achieve crash-safe replication; developing online hot-copy for MyRocks from scratch; schema change support; adding tons of new status vars and system vars; fast bulk import to MyRocks which is necessary for the replica migration to even be possible; updating hundreds of thousands of lines of automation code written under the assumption of InnoDB being the only storage engine in use and using various InnoDBisms...

The MyRocks migration wasn't a project I personally worked on, but I'm very aware of what was involved. It appears you joined FB PE in 2017 and therefore missed much of this historical context? I'm not really sure why you would have such strong opinions about it.

You say that FB is using MySQL because "changing is basically impractical", but also say MyRocks "provided major wins", which seems to be a contradiction. In any case, I'm not aware of any pg feature that provides compression ratios anywhere near that of MyRocks, and pg is only recently even adopting an arch that supports pluggable storage engines at all. In combination it's really hard to make a case that FB is using MySQL just due to historical investment and inability to change.

Honestly I would also not be surprised if FB moves some core tiers off of MySQL to a pure-RocksDB solution at some point in the future. The number of intermediate data services and proxies make this sort of thing absolutely possible. For the same reason, in theory a move to another db like pg would be completely possible without needing to run both in parallel for years (again, just talking in theory; moving to pg just doesn't make practical sense).

> The right thing to have said would be that the query patterns that are used are extremely simple selects over a single table

For UDB, sure. What about all the other MySQL tiers? The non-UDB MySQL footprint at FB, despite being a minority of FB's MySQL fleet, is still larger than the vast majority of other companies' relational database fleets worldwide. The range of use-cases in the DBaaS (XDB) tier alone spans a massive combination of different database features and access patterns.

Thank you all, I've tried to read all the comments. I think I have to clarify the questions I asked:

1. Is related to our upcoming startup, we chose MySQL + Redis, but some said use PostgreSQL

(However, my friend explained that MySQL before v8 was weak and after v8 has tried to come back to its salad days, this is why he chose MySQL)

2-3-4: In the industry, especially in the industry I'm active, Digital Built Environment Industry, there's a lot of invaluable schemas, especially XML(XMI) that I feel "these schemas convert structured data to semi-structured data" which is not good and efficient

For this reason, I'm thinking of SQL (Server) and SQLite (File) to preserve structured data

There's a vitally important schema in the built environment industry called Industry Foundation Classes (IFC) [1] which is based on EXPRESS/STEP and also has XML(XMI) schema too, but I want to convert the schema to SQL and SQLite

A friend did an invaluable job and after years introduced a correct method to convert it to SQL [2][3] but don't have any idea of developing the schema based on SQLite [4] that will accelerate the IFC development

[1] https://www.buildingsmart.org/about/what-is-openbim/ifc-intr...

[2] https://forums.buildingsmart.org/t/ifc-for-relational-databa...

[3] https://forums.buildingsmart.org/t/store-modify-and-retrieve...

[4] https://forums.buildingsmart.org/t/ifc-sqlite-project/1359

I'd say, use MySQL 8 unless you have a particular reason not to. You get more resource online when you search around and toolings are better and those are as important as features to many and I doubt MySQL 8 lacks much for most people's use cases.

My first goto database is always SQLite. Only when SQLite is unable to handle the requirements, for example, too many writes, do I look at PostgreSQL.

Question on this area. What have you found to be the best way to migrate an existing app to Postgres?

We plan to migrate to Postgres, but with every LOC written we're entrenching ourselves into Mysql. We all want Postgres, but at the same time I just can't see the transition happening with each passing day.

Thoughts?

edit: I should be clear too. I think our goal would be to not require a full rewrite spanning weeks. So an active translation layer, or replication.. or something tricky.. but I just don't foresee us being able to spend a month putting everything on hold so we can migrate.