From my position
MySQL pros:
The MySQL docs on how the default storage engine InnoDB locks rows to support transaction isolation levels is fantastic. [1] This can help you better architect your system to avoid lock contention or understand why existing queries may be contending for locks. As far as I know Postgres does not have docs like that.
MySQL uses direct I/O so it disables the OS page cache and uses its own buffer pool instead[2]. Whereas Postgres doesn't use direct I/O so the OS page cache will duplicate pages (called the "double buffering" problem). So it is harder to estimate how large of a dataset you can keep in memory in Postgres. They are working on it though [3]
If you delete a row in MySQL and then insert another row, MySQL will look through the page for empty slots and insert there. This keeps your pages more compact. Postgres will always insert at the bottom of the page. If you have a workload that deletes often, Postgres will not be using the memory as efficiently because the pages are fragmented. You will have to run the VACUUM command to compact pages. [4]
Vitess supports MySQL[5] and not Postgres. Vitess is a system for sharding MySQL that as I understand is much more mature than the sharding options for Postgres. Obviously this GA announcement may change that.
Uber switched from MySQL to Postgres only to switch back. It's a bit old but it's worth a read. [6]
Postgres pros:
Postgres supports 3rd party extensions which allow you to add features like columnar storage, geo-spatial data types, vector database search, proxies etc.[7]
You are more likely to find developers who have worked with Postgres.[8]
Many modern distributed database offerings target Postgres compatibility rather than MySQL compatibility (YugabyteDB[9], AWS Aurora DSQL[10], pgfdb[11]).
My take:
I would highly recommend you read the docs on InnoDB locking then pick Postgres.
[1] https://dev.mysql.com/doc/refman/8.4/en/innodb-locking.html
[2] https://dev.mysql.com/doc/refman/8.4/en/memory-use.html
[3] https://pganalyze.com/blog/postgres-18-async-io
[4] https://www.percona.com/blog/postgresql-vacuuming-to-optimiz...
[5] https://vitess.io/
[6] https://www.uber.com/blog/postgres-to-mysql-migration/
[7] https://www.tigerdata.com/blog/top-8-postgresql-extensions
[8] https://survey.stackoverflow.co/2024/technology#1-databases
[9] https://www.yugabyte.com/
[10] https://aws.amazon.com/rds/aurora/dsql/
[11] https://github.com/fabianlindfors/pgfdb