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.
- 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.