3 ms·
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 e
by phamilton 3y ago
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.
- dvnguyen 3y agoHow about HA and horizontal scaling? I’ve heard that MySQL excels in that area.
- phamilton 3y agoI 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.