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

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. But I just got the sense that MySQL was developed by people who didn’t quite know what they were doing, and that people who really did know what they were doing weren’t ever likely to be attracted to that to fix it.

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 Oracle MySQL. I opt for MariaDB on smaller projects and AWS Aurora MySQL for larger projects. Aurora scales insanely well, and replication lag is almost non-existent.

In my general experience MySQL was always significantly faster but it's been a number of years since I've worked with Postgres and the comments here seem to indicate that that may no longer be the case. YMMV

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

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.

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.

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.

Having answered this a ton over the years, don't want to really take shots at MySQL. But Postgres stands in pretty unique ground.

1. It's solid as a really reach data platform (more than just a relational database). It's extension framework is quite unique compared to others. It's JSONB support was the first among other relational databases and is feature rich and performant. Multiple index types. Transactional DDL. The list goes on.

2. No central owner. A lot of open source is source code is open, but it's maintained by a central company.

3. I mentioned extensions, but really that is understated. It can do really advanced geospatial, full text search, time series, the list goes on.

Having explained this a ton of times first 10 years ago - https://www.craigkerstiens.com/2012/04/30/why-postgres/ and then again 5 years later with and updated version, most recently tried to capture more of this in an updated form on the Crunchy Data blog - https://www.crunchydata.com/why-postgres

https://web.archive.org/web/20211206040804/https://blog.sess...

From a former MySQL developer:

> let me point out something that I've been saying both internally and externally for the last five years (although never on a stage—which explains why I've been staying away from stages talking about MySQL): MySQL is a pretty poor database, and you should strongly consider using Postgres instead.

Pick postgres unless you have a good reason not too?

I choose Postgres every time, because that is what I am familiar with.

But nerdy stuff:

Postgres stores data linearly (in heap - which has nothing to do with the heap data structure used for sorting, it just means pile of data). If you need to have fast access to data, you need to add secondary indexes - and the secondary indexes point to location in the heap as "this is where you find the data".

MySQL stores data in a tree - a table is a tree sorted by primary key. You can create secondary indexes and instead of a pointer they contain the primary key value.

That means for example that data with similar primary key will be located physically nearby each other, in MySQL but not in Postgres. At the same time, inserting new data with random (like UUID) primary key in MySQL will write all over the table, but will mostly "append at the end" in Postgres.

Postgres also implements MVCC with Serializable Snapshot Isolation - so data that someone changes exists in multiple copies and needs to be cleaned up later - but there's no locking. MySQL relies on locks instead so there's no duplication but you might see transactions waiting for each other. I don't remember if MySQL implements a proper serializable isolation - but that is not really the default on any database anyway.

Interestingly, Oracle has very similar design to Postgres (though it uses rollback segment for old data, so there's no bloat and vacuum but you might get "snapshot too old" error) while MS SQL Server is also tree and lock-based database like MySQL.

Does this impact you? It might, like in cases where MySQL performs terribly due to UUID keys or Postgres can't vacuum fast enough due to high volume of updates or something. Or you're implementing money settlement logic and need proper serilizable transactions, who know. But it is cool to know the implementation details.

The Postgres query optimizer is more powerful than the MySQL query optimizers [1]. It generally scales better for OLTP. Also tons of extensions that can accelerate your workload.

[1] - https://ieeexplore.ieee.org/document/9537400

The main reason I prefer mysql over PostgreSQL is that mysql is just more consistent - in its commands, quirks, etc.

Postgres - is it pg, pgsql, psql, postgres, postgresQL? The answer is "yes."

Plus the case behavior for tables and column names drives me crazy. It's like some leftover VMS shit. I mean seriously fix it. Can you or can you not use a capital letter for a table/column name? I can never remember. Or you can, but you have to quote it? Fuck.

Until recently (which to be fair might be 8-10 years ago) postgres' performance monitoring tools sucked compared to mysql. I know at one point in the last 10 years they still used sunos4 as their base configuration because you know, the OS had been EOL for like a decade at that point.

MySQL is fire and forget. psql (or postgres or pg or postgresql?) is not fire and forget. It's twitchy and requires constant vigilance. I don't want a piece of infrastructure that requires constant vigilance.

That's not to say I won't use it. It's geo stuff is really great. It's JSON support is better than MongoDB's, from what I've heard. Row level security is awesome. But are those features good enough to overcome psql's quirks? Sometimes.

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 comfortable with.

For anything involving location, choose Postgres because PostGIS is just so good.

At our company, we provide Postgres 24x7 support. We have partners that provide support for other databases, and for some projects we work together with companies that have multiple databases.

We have over the years compared the rate of production incidents Postgres vs MySQL. It's roughly 1:10 (MySQL has around 10 times more production incidents than Postgres).

You may consider this anecdotal evidence, but numbers managed here are quite significant.

The gist is that Postgres is not perfect nor free from required maintenance and occasional production incidents. But for the most part, it does the job. MySQL too, but with (at least from an operational perspective) many more nuances.

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

I find PostgreSQL permission management quite convoluted. In MySQL it is simple to query for what grants a user has, but in PostgresSQL you need to write 100 lines of SQL to do the same... and you can't run \du and other commands without psql. Why couldn't they just come up with `SHOW` shortcuts that work in any SQL client?

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.

I know it seems dumb, but postgres really needs to add the simple developer experience stuff like:

SHOW CREATE TABLE;

SHOW TABLES;

SHOW DATABASES;

SHOW PROCESSLIST;

CockroachDB added these aliases ages ago.

Generally speaking it should always be PostgreSQL.

It's the default choice for a number of reasons but chief among them is just that it's higher quality. That is it's developed to higher standards due to community bar being really high (thanks Tom Lane, et al for your stewardship) and testing and analysis of changes to the database being ingrained into the culture.

By pursuing correctness first before performance for many years PostgreSQL has built a stronger foundation that is now paying dividends in terms of both performance but also ability to ship powerful features quickly. This has generally resulted in the gap between MySQL and PostgreSQL only continuing to widen over the last 10 years.

So when would you consider picking MySQL?

To me that comes down to exactly one set of use-cases and that is workloads that are fundamentally incompatible with VACUUM. The PostgreSQL MVCC system requires that table heap be maintained by the VACUUM process to both ensure safety (txid wraparound) and reclaim/reuse heap storage. This process is very expensive for workloads that do a lot of updates, especially on indexed columns (as indices need VACUUMing also), less of an issue for non-indexed columns if you can use HOT (heap only tuple) updates and tune the target fill ratio of heap pages appropriately.

In most cases it's highly unlikely your business is going to reach the level of write load where these deficiencies in write behaviour actually matter but it is possible. Uber famously migrated from PostgreSQL primarily because their experiences with write amplification and VACUUMing.

If for instance though your data consists of a smaller live hot set and a warm set that is less frequently updated and easily separable by a deterministic factor like time you can very easily use PostgreSQL table partitioning to isolate the two and continue to scale for a very very long time on pure PostgreSQL.

In practice this may be fixed in PostgreSQL one day, there was a project called zheap to implement an UNDO log style storage system for PostgreSQL (which would avoid all the VACUUM maintenance etc) but it stalled out, largely I believe because it wasn't able to provide obvious wins quick enough to stimulate further interest. However OrioleDB has picked up the torch now and does in fact seem to be showing very impressive results.

If such a storage engine is merged in my mind there will no longer any reason to consider MySQL for production workloads.

I've been using MariaDB (MySQL) as a hobbyist for years. I just set up a couple myqsql servers with phpmyadmin on Raspberry PIs and use them for local development. Basic crud apps, etc.

I've always assumed that PostgreSQL is a step up, but never really bothered to look into what I get for the effort. Do I really get anything if I'm not trying to make apps at scale?

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.

I’m going to go for the esoteric opinion: MariaDB. Specifically to get system versioned tables. Imagine having the full change history of every single row for any odd task that you need without the performance penalty of keeping historical data in the same table. It can be a huge amount of leverage.

If that’s not your interest, I will admit that Postgres array support is far ahead of any of the MySQLs. Most ORMs don’t use it but you can get optimal query prefetching via array subqueries.

A lot of comments for Postgres, but it's the only major DB in 2023 that does not let you choose your character collation when creating a database. That is pretty much a deal breaker day 1. Guess you'll be doing a tolower() on every db search and not use indices which will kill performance or using column collation casts on every search query. I just don't get it.

I once tried to migrate a SQL Server DB to Postgres and eventually gave up, with MySQL being a pretty easy switch with some minor stored procedure rewrites.

Also it tends to do things way differently than every other DB. VACUUM is just a completely different concept that can footgun you pretty fast.

Postgres is pretty powerful but it has certainly made some interesting design choices.

Postgres for anything with a single database size < 4TB.

But if you need something that can handle 100TB+, go Vitess(mysql compatible).

Friends dont let friends use #Horracle software.

That includes VirtualBox, MySQL, Horracle Cloud. Just step back. Walk away. Do not pass go, do not collect $20000 lawyers fees for unintended actions.

Why would I choose MySQL in any year? There is no context you can provide to this question where I wouldn't always choose Postgres.

Its simple AF - I just always pick the well proven PostgreSQL database.

... if that is too big I use SQLite.

Postgres for almost anything. Robustness, ecosystem, accuracy, all top notch.

One exception: I did migrate two very large tables (15B+ rows) from Postgres to MySQL for performance reasons. InnodB (MySQL storage engine) can arrange the records on page by primary key, and if you have a multi-value PK (user_id, uuid) it means all records from a user are in the same set of pages. Huuuuge improvement over having your data for a user spread out over N different pages. Memory cache way more efficient. Orders of magnitude speed up on cold queries, better cache hit rate, and cost reduction from smaller servers.

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

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.

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.

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.

We choose Postgres for extensibility and stability :)

PostgreSQL is a community thing and MySQL is Oracle. Maybe make some basic benchmarks for comparison?

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.

By choosing SQLite.

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

If you're a gigacorp you might have reasons to go with a customized version of MySQL otherwise pick Postgres.

I dunno if this is still true but a couple of years ago MySQL was cheaper on AWS (RDS/Aurora) than Postgres.

If I were starting from zero knowledge, I'd choose Postgres, but you're not. Unless you require on particular database extensions (full-text search, PostGIS, etc), or you're operating at the kind of scale where all bets are off, it won't matter very much; use the one your team has more expertise with. MySQL sucked twenty years ago, but it's had twenty years of people beating on it, so it largely sucks in precise, well-understood ways now. This is nearly as good as being good.

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.

It depends on my requirements and context, really. THESE DAYS with docker, postgres is almost a no-brainer for basically anything that _I_ do, anyway. If I even need a heavy write load or external-access-via-network then PG, otherwise for my mostly simple CRUD needs SQLITE, quite honestly.

I have a production app on MySql, but that was before docker and MySQL was a fair bit easier to setup then. That was my need at the time.

Always Postgres. It will be more painful to migrate in the future versus starting with it.

Easy. Choose Postgres. I worked with Oracle for 15 years before moving to Postgres. In my experience, Postgres has been better documented, more performant, better standards compliant, etc. Now, you are asking about MySQL and not Oracle, but I’ve never heard anyone say MySQL is better than Oracle, while I have heard that about Postgres many times.

You choose postgres.

For myself it’s about what I know and that’s MySQL / Mariadb

If there is a problem I have probably seen it. MySQL won’t start for some reason I know what to do. Adding and removing users , permissions, running with selinux, optimizing queries, indexes - all with in my scope. Backups and restores, working with snapshots, MySQL relocation or master slave. I have been able for years to upgrade mysql versions with out much hassle. I know the table structure well enough I could downgrade in almost all cases - with adjustments I once downgraded mysql 8 to 5.7 because years ago it was too slow.

Now if I had the desire (I’m not past 40 so meh) or i didn’t have such a difference in knowledge I’d probably seriously look at postgresql. So my suggestion is go with in your circle of competence. Regardless if you go with mysql it is a fine database that many use at a high scale.

For my little hobby sites I use whichever best fits the application. If an application has a specific need for a function that is in Postgres and not MySQL then I use Postgres.

In my former work life we used Percona MySQL for the commercial support and very fast response to fix bugs and add features, but we also used Postgres and Oracle. In those cases it was more important to have awesome DBA's that could do anything with any database. I learned a lot from them and they earned a lot of respect from me. One of them could find significant design flaws in the application just be reviewing the schema. They cut our memory usage in half and bought us time to wait for the server vendors to support more memory in their next motherboard release.

Previously -

https://news.ycombinator.com/item?id=35599118

https://news.ycombinator.com/item?id=12166585

There was a thread asking this a month ago, here's what I wrote then:

https://news.ycombinator.com/item?id=35618726

I worked for many years with MySQL and then started working in a company that used PostgreSQL. Things I liked from PostgreSQL:

  - Better SQL standard support (modernish?).
  - JSONB type, also with indexes! Very useful to have JSON fields to allow dynamic fields or avoid sub-querying some tables (i.e. I mean caching some computed results in a JSON field).

I have to say that it was in the 2018-2019, maybe MySQL has improved since then.

I don't use either but I don't think I've seen MySQL mentioned in any meaningful way in almost a decade at this point. If forced, knowing only that, I'd choose postgres.

"PostgreSQL supports most of the major features of SQL:2016. Out of 177 mandatory features required for full Core conformance, PostgreSQL conforms to at least 170. In addition, there is a long list of supported optional features."[1]

[1] https://www.postgresql.org/docs/current/features.html

It depends on the project but a couple things that would help me pick MySQL (MariaDB) more often (wish list) are:

- Row-level security (RLS) - depending on the tenancy and DB level safe-guards you need Postgres supports it and MariaDB doesn't - transactions for schema changes - Postgres (and even sqlite) support this but MariaDB doesn't. Due to not having it, if there's a faulty migration manual clean-ups might be required to get it back to the state you need

Completely tangential, but I need to do some transformations on some CSVs and decided I'd just import them into a local database, run my SQL, and export. The CSVs had the literal strings TRUE/FALSE in a column. MySQL would require me to import into a varchar column and then transform; Postgresql automatically converted it as a boolean.

Plenty of big reasons, but it's the small quality of life stuff that makes a lot of difference.

Absolutely Posgtres!!! Yesterday I had to migrate from postgres to mysql and I found some disadvantage things about mysql in my use case

There is no returning for insert/update/delete what forces me do a second query to get new data. Why dont just return what I have updated? Because it is some thing like: UPDATE wallets SET balance = balance + 1

I have to give up json function because its hard to work with it on mysql. Have to do the aggregation in my code instead

No uuid v4 support by default

Easy. Always postgres.

I also use the same logic applied to document databases. Mongo or Postgres? Postgres

Also pub sub. Postgres or redis? Postgres

Use postgres until it’s not technically feasible to use it anymore.

I flipped a coin, it landed on its side so I went with SQLite.

Used mysql for over 10 years. I went through the pain of migrating several production databases to postgresql. I don’t regret it for a while range of reasons. My advice: use Postgres unless you have a very good reason not to, which you probably don’t.

> What sort of functional requirements would cause you to choose one over the other?

Simple: I don't like having headaches. Therefore, I chose postgres.

Easy. I keep using MySQL because that's all I know.

Kinda j/k. I'm intrigued my Postgres but I don't start that many new projects. It has at least 1 feature that MySQL doesn't -- deferred referential integrity which there's no good workaround for that I'm aware of. Arises extremely rarely when you need to have 2 tables have a FK to eachother.

What's to choose? Postgres all the way.

Funny to be so blind, no one mentionned how the Heatwave analytics engine of MySQL is 10 years ahead of what PostgreSQL can do, oh but that's because of Oracle so it doesn't count right?

Mixing OLTP and blazing fast analytic queries on the same database, at the very same time, removing ETL needs...

> What sort of functional requirements would cause you to choose one over the other?

MySQL failed me big time in the past, so my functional requirement would be "don't f** with my data". And so far, PostgreSQL and SQLite never did. Don't have time to give second chance, don't need to.

That's EASY

PostgreSQL. The answer has always been PostgreSQL, even at the height of MySQL's popularity and LAMP craze.

It is a VASTLY better piece of software and ecosystem - yet it's boring at the same time - all things I demand from the DB system whether it's a toy project or an enterprise app.

None of them if you need to do something like pivot tables with dynamic number of columns. Postgres has some half baked pivoting functionality library, but you MUST define the column names in advance, which kind of defeats the purpose of a pivot table functionality.

Friends 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

For MySQL users today, there are modern cloud-native replacement solutions, such as tidb(tidbcloud.com), where tidb (github.com/pingcap/tidb) provides a MySQL-compatible syntax layer but it's not MySQL.

Interests: I work at TiDB

There is nothing to argue about.

Postgres is better in almost every possible aspect you can come up with.

So you choose mysql in 2023 only if you have very very specific requirements and constraints to justify the sacrifice.

Am I the only one who thinks postgresql’s timestamp and timestamptz types are incredibly stupid?

I just want to either save a local date and time, or an utc timestamp. Postgresql’s timestamp(tz) types do neither and both at the same time.

Practically speaking, they're very similar. Mysql and Postgres differ in their approaches to replication & clustering, which can have a big impact to high availability, high volume database configurations.

What is go-to solution for Postgres to have data encrypted on disk?

I think that MySQL is more popular in enterprise as there's transparent encryption in enterprise version that's single click.

Go with postgres. Unless you need php/wordpress, then pick mysql.

Am I working on a Wordpress site ? Yes -> MySql No -> Postgres

I choose mysql because they don't want to install extensions (geo stuff) at work and we are using many PHP things that use mysql. And WordPress. Choose boring tech or something.

I would quit that job and get a gig or project where I can make my own decisions. Then stick everything in JSON files or whatever seems convenient for my particular problem.

Always PostgreSQL. Unless if MySQL protocol is absolutely needed.

I kind of learned Git rebasing thanks to the postgres github repo, so I tend to use it nowadays. Does mysql even have a public github repo?

Just choose Postgres. If you want something more nimble, go with Redis (which does support persistant storage, contrary to popular belief).

pick the database that you are most familiar with

It's pretty easy, it's always Postgres. MySQL has been awful at every place I've had the misfortune of using it.

Actually I would like to know just ONE advantage MySQL has over PostgreSQL... are there actually any?

> It's 2023, how do you choose between MySQL and Postgres?

Easy, you pick Postgres. There's no reason to ever use MySQL.

I think the answer is really easy: you should choose the one you or/and your team has more experience with.

In 2023 you don't even debate between the two. The decision to go with Postgres is instant and by default.

Easy. You don't ever pick MySQL because it's essentially garbage.

Can it(the database) do simple pub/sub without third-party software? :)

Easy: SQLite

piggybacking on this: does anyone know a Postgres alternative to PlanetScale?

the answer is:

* always use Postgres.

* if you need something else, it better have an actual reason.

MySQL/MariaDB is only for when you have a framework that requires it or is highly optimised for it (e.g., WordPress or MediaWiki).

Its not even a choice anymore. One is far better than all of the others.

It's OracleSQL instead of MySQL now ?

Can I actually work with it locally and on a production server?

Sigh. Hoping dang will swoop in and close this flame war...

If row level security is a need, I’ll go with Postgres

Neither. Use either TiDB or YugabyteDB.

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

By going with Apache Solr instead

Simple: just pick Postgres :)

Same way you did in 2003. You pick Postgres. There has been zero times mySQL has been a better choice.

postgres just because I'm more familiar with it, and the extension ecosystem (TimescaleDB, PostGIS, etc etc).

That said, I'm not going to be sad with MySQL, though I'd probably go with MariaDB just because of full open source (note, I don't know any details there, being a postgres guy)

You choose Postgres.

Most of the answers here are choosing Postgres over MySQL, But there are cases where MySQL is far better than Postgres.

1. MySQL's Replication is simple, more reliable and takes up less disk space than Postgres.

2. Ability to control the optimization of queries with Optimizer hints. At scale, to lower tail latencies you will definitely need to help the query planner since you have some domain knowledge about your app and queries that the planner doesn't have. Yes, In PG you can use pg_hint_plan, but still it is not an official solution.

3. MySQL has better connection scaling without a separate component like pgBouncer. Also, cost of connection creation is lower due to MySQL's thread per connection model vs Postgres' process per connection model

4. Collations suck in Postgres since it decided to depend on the OS for collation support. Version updates of other packages (glibc) or the OS can corrupt your database due to this collation mess. The fix for this is to use ICU collations but even they have multiple limitations (You can't specify a non-deterministic collation at the database level etc)

5. Postgres's MVCC implementation is the most inefficient among modern databases [1] Not only is it inefficient, it causes maintenance headaches with managing and tuning auto_vaccuum. It also causes increased disk usage, write amplification (entire row is rewritten on each update and index is updated even if the column being updated is not indexed) and increased bloat due to multiple dead copies of the rows. If you use wide tables (100s of columns) with updates (not even high frequency but moderate updates), MySQL will be far better. Heap-only-tuples (HOT) will only be helpful if your rows are narrow and there is sufficient free space in the page of the row being updated. For wide-tables, most often this is not the case, so even HOT won't be much helpful. Almost everyone would have read Uber's story of moving to MySQL [2], but even if you are not at Uber scale, Postgres's MVCC implementation, and its associated pains are better avoided. Unfortunately attempts to fix this in Postgres (zheap) have been long abandoned.

6. In MySQL (InnoDB), the rows are physically ordered by Primary Key. This improves cache-hit ratio and performance if most of you queries involve selecting or ordering by the PK, or a prefix of the PK in case of composite PKs.

So, if you need performance at scale, reliable replication for HA, less maintenance, good connection scaling, go for MySQL. If your app depends on a extension or FDW that only Postgres has, then choose Postgres.

Often, people may complain about some obscure SQL syntax that does not work in MySQL or that Postgres correctly implements but mostly there will be an alternative you can use in MySQL.

[1] - https://ottertune.com/blog/the-part-of-postgresql-we-hate-th... [2] - https://www.uber.com/en-US/blog/postgres-to-mysql-migration/

postgresql always, that’s how :)

By choosing Postgres. Next!

just pick MySQL. If you want 100 half-baked non-production-ready features pick Postgres.

MySQL for the quick and dirty and Postgres for anything else

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.