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

I had the misfortune of inheriting a MySQL 5.6 database and I had to manage it for a year and a half. It wasn't very big (less than 100GB), but:

- for some reason it hang periodically and had to be restarted during the night for no apparent reason

- compared to Postgres, the tooling is garbage (both for backups and even more for general database administration)

- essential features are missing, the most important one of which, for me, was proper CSV import/export. CSV-related functionality is so broken and terribly inconvenient to use. In a specific case I had to write a program to export millions of records manually since MySQL could not generate correct CSV export due to some columns containing text with special characters, quotes, newlines. Any combination of the export parameters ("ENCLOSED BY", "ESCAPED BY" and all the other garbage options) failed in one way or another. I even tried to use non-standard characters like \x15 and \r for column and row separation but even that failed. With Postgres, "with csv header" is simple and works every time.

I also managed bigger Postgres databases (up to tens of terabytes) and never had the issues I encountered with MySQL.

You're basically saying something that isn't free, isn't free.

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.

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/

[dead]