Ask HN: Is PostgreSQL better than MySQL?

PostGIS has no parallel in MySQL/MariaDB world.

My impression from working on an app with a Postgres cluster of 128-ish instances is that Postgres is excellent when your largest table fits on 1 instance, and kinda lacking when you need >1 instance for your largest table.

MySQL has very mature cluster managers like Vitess / Planetscale. Citus for Postgres was bought by Microsoft and doesn’t seem as popular as Vitess.

The ecosystem: seems like PostgreSQL is a bit more popular, especially with offerings like PostGIS, PostGraphile, PostgREST and the many other projects you see occasionally. That said, it's nice to see MySQL/MariaDB as mostly compatible alternatives to one another, so that you don't technically "keep all of your eggs in a single basket" and there's stuff like Percona out there.

The functionality: from what I've heard and seen, PostgreSQL is commonly touted as the solution with more correctness in regards to SQL, but also has great support even for things like JSON, as well as PL/pgSQL is pretty nice to use, in addition to custom types and a whole lot of other useful stuff. That said, not every system needs than and the MySQL/MariaDB approach of having databases instead of databases and schemas is a bit simpler. That said, transactional DDL in PostgreSQL is a big plus, as someone else mentioned.

The performance: it probably depends on what you're doing. Benchmarking both for your particular workload is probably the way to go, though historically using pgBouncer has been a good idea for PostgreSQL. In the current day and age, both should be decent choices, especially with partitioning and clustering.

The tooling: here I'll come out and say that I like MySQL Workbench more than pgAdmin, both because it feels more like a native piece of software, but also because they have really great ER diagram functionality that when coupled with forwards/reverse engineering and schema sync can allow you to plan and alter your schema in a visual way: https://dev.mysql.com/doc/workbench/en/wb-design-engineering... (and it gives you SQL at the end of the day, so you can further alter things as necessary, really nice for both exploring the schema, so you don't need someting like DBVis as much, as well as for quick edits). That said, pgAdmin has built in support for displaying geospatial data (e.g. PostGIS) which is pretty great since you don't need something like QGIS as much.

The developer experience: both are good. Both have containers that are easy to run, both are easy to connect to and work with (though MySQL drivers sometimes aren't shipped with other software out of the box), both are widely supported by a variety of tools (DataGrip is one of the better ones out there as well), and they also have managed cloud offerings from many vendors, if needed. Both will scale up and down pretty decently, though PostgreSQL seems to use a bit less memory in containers and has faster startup when I last checked, though that's a minor detail. Either will be easier than trying to find a trustworthy Oracle XE container image, or to build your own with some DB setup and initialization scripts that actually work and don't throw errors about the DB being closed when the container restarts.

At the end of the day, it's hard to go wrong with either of them, much like even SQLite can be a good choice for some workloads. Right now I'm building a personal project with MariaDB, but Keycloak more or less demands PostgreSQL, so running that as well. Though in regards to MariaDB one could probably mention the whole SPAC situation that went down a bit ago.

I appreciate having basic GIS/spatial stuff built in to MySQL8, vs having to do extension stuff in pg for postgis.

MySQL 8.0 has JSON column support: https://dev.mysql.com/doc/refman/8.0/en/json.html