Can someone hit me with a few reasons why you would use Postgres over MySQL? I don’t have any familial affinity to any database, but I’m not sure what the benefits to Postgres are relative to MySQL.
Some things that are nice in Postgres and missing in MySQL:
- Create constraints as NOT VALID and later VALIDATE them. This allows you to create them without expensive locks.
- explain (analyze, buffers). I miss this so much.
- Row level security.
- TOAST simplicity for variable text fields. MySQL has so many caveats around row size and what features are allowed and when. Postgres just simplifies it all.
- Rich extension ecosystem. Whether it's full text search or vector data, extensions are pretty simple to use (even in managed environments, a wide range of extensions are available).
Is that (and more) enough for me to migrate a large MySQL to postgres? No. But I would bias towards postgres for new projects.
I mostly have used AWS Aurora there, which is significantly better than vanilla MySQL or Postgres and both are similar enough.
In Aurora, Postgres has Aurora Limitless (in preview) which looks pretty fantastic.
As far as running yourself, Postgres actually has some advantages.
Supporting both streaming replication and logical replication is nice. Streaming replication makes large DDL have much less impact on replica lag than logical replication. As an example, if building a large index takes 10 minutes then you will see a 10 minute lag with logical replication since it has to run the same index build job on the replica once finished on the primary. Whereas streaming replication will replicate as the index is built.
Postgres 16 added bidirectional logical replication, which allows very simple multi-writer configurations. Expect more improvements here in the future.
The gap really has closed pretty dramatically between MySQL and Postgres in the past 5 years or so.
I do scaling and performance work, mostly with Rails apps, but a significant amount of the work is database level and not language specific. I've used both postgres and MySQL (and a few other databases) going back to 2000.
The best thing I can hear from a company when I start is "We use Postgres". If they're using postgres then I know there's likely a far smoother path to performance than with MySQL. It has better tooling, better features, better metadata.
I'll give you the one that matters to me: in MySQL, you can't do DDL statements (create table, alter table, etc) inside a transaction. MySQL will implicitly commit after each DDL statement.
This is exactly what I would argue. PostgreSQL makes it straightforward to create extremely powerful extensions.
PostGIS is one such extension, and I would argue that if your use case involves geospatial data, then PostGIS alone is enough of a reason to use PostgreSQL!
Back when I selected which database to get more fluent in one of the concerns I had about MySQL is that it was decribed as playing loose and fast with type casts, at least compared to Postgres.
Stability, reliability, consistency. If you do anything with Unicode you're also much better in PostgreSQL. Faster indexes, smaller base install (), and much more complete SQL language support.
() note: when PHP was taking off, MySQL had a smaller install base. This has long since changed - PostgreSQL hasn't grown much over the years, and MySQL has, at least since the last time I worked on both circa 2015-ish.
Friendly nitpick, because I had a double-take when reading the second paragraph: Be careful to differentiate between "install base" and "base install". In both cases, you are referring to "installed size of the database in its base configuration". But "install base" commonly means "number of installations". So I was very confused when the second paragraph was implying (using the standard meaning for "install base") that the number of Postgres installations had not grown over the years.
PostgreSQL supports more SQL features and data types out of the box. Also, it looks like MySQL development has stalled after purchasing by Oracle. PostgreSQL has exciting new features in every release, I forgot when anything significant happened in the MySQL world. It's frozen for like a decade now. There're some new releases, but you won't find anything exciting in the change log.
I know this isn’t a technical reason, but my main reason is “eww, gross, it smells like Oracle here.” I’ve been around long enough to know that even being in the same zip code as Oracle is a bad idea.
I use MySQL mostly, but I would love to have a few features from the Postgres world; namely, the better full-text search, key value store, queue and vector search. A lot of projects I have never reach the scale where I need these to be separate data products so the perfectly fine Postgres versions would suffice.
CrateDB might be a good fit for full text and vector search (it’s SQL database but has dedicated clauses for FT and VS).
Curious how do you use PG for key/value and queue - do you use regular tables or some specific extensions?
I can imagine kv being a table with primary key on “key” and for queue a table with generated timestamp, indexed by this column and peek/add utilising that index.
Can someone hit me with a few reasons why you would use Postgres over MySQL? I don’t have any familial affinity to any database, but I’m not sure what the benefits to Postgres are relative to MySQL.