I use MySQL for everything - always have.
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.
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.
- 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.
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.
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.
Apparently MySQL does not run delete triggers in case the rows are deleted due to a foreign key cascade.
Eveytime I used its slightly advanced features, I ran into such problems. With PostgreSQL I do not need to think if this would work.
The plugin ecosystem is pretty astonishing. Foreign Data Wrappers... I'm not hands on so much any more but there were a lot of things back when I was.
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!
() 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.
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.