6 ms·

How should one decide whether to go with MySQL or Postgres for a greenfield project?

add-sub-mul-div 1y ago
Pre-existing expertise with MySQL and lack of time or inclination to learn something new is the only reason I could think of not to go with Postgres.
jedberg 1y ago
At this point I'm not sure why anyone would choose MySQL. Any advantage it had pretty much evaporated with these hosted solutions. For example, MySQL was easier to get running and connect to. These cloud offerings (Planetscale, Supabase, Neon, even RDS) have solved that. MySQL was faster for read heavy loads. Also solved by the cloud vendors.
- cortesoft 1y ago
  > At this point I'm not sure why anyone would choose MySQL Because I have used MySQL for over 20 years and it is what I know!
  - jedberg 1y ago
    Fair enough, but I assume most of that is in the administration of MySQL? Which is all now abstracted away by the cloud vendors. If you're running it yourself I could see why you'd do that, but if you're mostly just using it now, Postgres can do all the same things in the database pretty much the same way, plus a whole lot more.
    - cortesoft 1y ago
      Its both operating MySQL and creating applications that use it. Additionally, almost all my workloads run in our own datacenters, so I haven't yet been able to offload the administration bits to the cloud.
- bri3d 1y ago
  At large scale I'd say MySQL is still a competitor for a few reasons: * Scale-out inertia: yes, cloud vendors provide similar shading and clustering features for Postgres, but they're all a lot newer. * Thus, hiring. It's easier to find extreme-scale MySQL experts (although this erodes year by year). * Write amplification, index bloat, and tuple/page bloat for extremely UPDATE heavy workloads. It is what it is. Postgres continues to improve, but it is fundamentally an MVCC database. If your workload is mostly UPDATEs and simple SELECTs, Postgres will eventually fall behind MySQL. * Replication. Postgres replication has matured a ridiculous amount in the last 5-10 years, and to your point, cloud hosting has somewhat reduced the need to care about it, but it's still different from MySQL in ways that can be annoying at scale. One of the biggest issues is performing hybrid OLAP+OLTP (think, a big database of Stuff with user-facing Dashboards of Stuff). In MySQL this is basically a non-event, but in Postgres this pattern requires careful planning to avoid falling afoul of max_standby_streaming_delay for example. * Neutral but different: documentation - Postgres has better-written user-facing documentation for user-facing functions, IMO. However, _if_ you don't like reading source code, MySQL has better internals documentation, and less magic. However, Postgres is _very_ well written and commented, so if you're comfortable reading source, it's a joy. A _lot_ of Postgres work, in my experience, is reading somewhat vague documentation followed by digging into the source code to find a whole bunch of arbitrary magic numbers. If you don't believe me , as an exercise, try to figure out what `default_statistics_target` _actually_ does. Anyway, I still would choose a managed Postgres solution almost universally for a new product. Unless I know _exactly_ what I'm going to be doing with a database up-front, Postgres will offer better flexibility, a nicer feature-set, and a completely acceptable scale story.
  - jashmatthews 1y ago
    > hybrid OLAP+OLTP .... in Postgres this pattern requires careful planning to avoid falling afoul of max_standby_streaming_delay for example This is a really gnarly problem at scale I've rarely seen anyone else bring up. Either you use max_standby_streaming_delay and queries that conflict with replication cause replication to lag or you use hot_standby_feedback and long running queries on the OLAP replica cause problems on the primary. Logical Decoding on a replica in also needs hot standby feedback which is a giant PITA for your ETL replica.
n_u 1y ago
From my position MySQL pros: The MySQL docs on how the default storage engine InnoDB locks rows to support transaction isolation levels is fantastic. [1] This can help you better architect your system to avoid lock contention or understand why existing queries may be contending for locks. As far as I know Postgres does not have docs like that. MySQL uses direct I/O so it disables the OS page cache and uses its own buffer pool instead[2]. Whereas Postgres doesn't use direct I/O so the OS page cache will duplicate pages (called the "double buffering" problem). So it is harder to estimate how large of a dataset you can keep in memory in Postgres. They are working on it though [3] If you delete a row in MySQL and then insert another row, MySQL will look through the page for empty slots and insert there. This keeps your pages more compact. Postgres will always insert at the bottom of the page. If you have a workload that deletes often, Postgres will not be using the memory as efficiently because the pages are fragmented. You will have to run the VACUUM command to compact pages. [4] Vitess supports MySQL[5] and not Postgres. Vitess is a system for sharding MySQL that as I understand is much more mature than the sharding options for Postgres. Obviously this GA announcement may change that. Uber switched from MySQL to Postgres only to switch back. It's a bit old but it's worth a read. [6] Postgres pros: Postgres supports 3rd party extensions which allow you to add features like columnar storage, geo-spatial data types, vector database search, proxies etc.[7] You are more likely to find developers who have worked with Postgres.[8] Many modern distributed database offerings target Postgres compatibility rather than MySQL compatibility (YugabyteDB[9], AWS Aurora DSQL[10], pgfdb[11]). My take: I would highly recommend you read the docs on InnoDB locking then pick Postgres. [1] https://dev.mysql.com/doc/refman/8.4/en/innodb-locking.html https://dev.mysql.com/doc/refman/8.4/en/innodb-locking.html [2] https://dev.mysql.com/doc/refman/8.4/en/memory-use.html https://dev.mysql.com/doc/refman/8.4/en/memory-use.html [3] https://pganalyze.com/blog/postgres-18-async-io https://pganalyze.com/blog/postgres-18-async-io [4] https://www.percona.com/blog/postgresql-vacuuming-to-optimize-database-performance-and-reclaim-space/ https://www.percona.com/blog/postgresql-vacuuming-to-optimiz... [5] https://vitess.io/ https://vitess.io/ [6] https://www.uber.com/blog/postgres-to-mysql-migration/ https://www.uber.com/blog/postgres-to-mysql-migration/ [7] https://www.tigerdata.com/blog/top-8-postgresql-extensions https://www.tigerdata.com/blog/top-8-postgresql-extensions [8] https://survey.stackoverflow.co/2024/technology#1-databases https://survey.stackoverflow.co/2024/technology#1-databases [9] https://www.yugabyte.com/ https://www.yugabyte.com/ [10] https://aws.amazon.com/rds/aurora/dsql/ https://aws.amazon.com/rds/aurora/dsql/ [11] https://github.com/fabianlindfors/pgfdb https://github.com/fabianlindfors/pgfdb
sgammon 1y ago
planetscale now supports both :)

Pre-existing expertise with MySQL and lack of time or inclination to learn something new is the only reason I could think of not to go with Postgres.

jedberg 1y ago

At this point I'm not sure why anyone would choose MySQL. Any advantage it had pretty much evaporated with these hosted solutions. For example, MySQL was easier to get running and connect to. These cloud offerings (Planetscale, Supabase, Neon, even RDS) have solved that. MySQL was faster for read heavy loads. Also solved by the cloud vendors.

cortesoft 1y ago
> At this point I'm not sure why anyone would choose MySQL Because I have used MySQL for over 20 years and it is what I know!
- jedberg 1y ago
  Fair enough, but I assume most of that is in the administration of MySQL? Which is all now abstracted away by the cloud vendors. If you're running it yourself I could see why you'd do that, but if you're mostly just using it now, Postgres can do all the same things in the database pretty much the same way, plus a whole lot more.
  - cortesoft 1y ago
    Its both operating MySQL and creating applications that use it. Additionally, almost all my workloads run in our own datacenters, so I haven't yet been able to offload the administration bits to the cloud.
bri3d 1y ago
At large scale I'd say MySQL is still a competitor for a few reasons: * Scale-out inertia: yes, cloud vendors provide similar shading and clustering features for Postgres, but they're all a lot newer. * Thus, hiring. It's easier to find extreme-scale MySQL experts (although this erodes year by year). * Write amplification, index bloat, and tuple/page bloat for extremely UPDATE heavy workloads. It is what it is. Postgres continues to improve, but it is fundamentally an MVCC database. If your workload is mostly UPDATEs and simple SELECTs, Postgres will eventually fall behind MySQL. * Replication. Postgres replication has matured a ridiculous amount in the last 5-10 years, and to your point, cloud hosting has somewhat reduced the need to care about it, but it's still different from MySQL in ways that can be annoying at scale. One of the biggest issues is performing hybrid OLAP+OLTP (think, a big database of Stuff with user-facing Dashboards of Stuff). In MySQL this is basically a non-event, but in Postgres this pattern requires careful planning to avoid falling afoul of max_standby_streaming_delay for example. * Neutral but different: documentation - Postgres has better-written user-facing documentation for user-facing functions, IMO. However, _if_ you don't like reading source code, MySQL has better internals documentation, and less magic. However, Postgres is _very_ well written and commented, so if you're comfortable reading source, it's a joy. A _lot_ of Postgres work, in my experience, is reading somewhat vague documentation followed by digging into the source code to find a whole bunch of arbitrary magic numbers. If you don't believe me , as an exercise, try to figure out what `default_statistics_target` _actually_ does. Anyway, I still would choose a managed Postgres solution almost universally for a new product. Unless I know _exactly_ what I'm going to be doing with a database up-front, Postgres will offer better flexibility, a nicer feature-set, and a completely acceptable scale story.
- jashmatthews 1y ago
  > hybrid OLAP+OLTP .... in Postgres this pattern requires careful planning to avoid falling afoul of max_standby_streaming_delay for example This is a really gnarly problem at scale I've rarely seen anyone else bring up. Either you use max_standby_streaming_delay and queries that conflict with replication cause replication to lag or you use hot_standby_feedback and long running queries on the OLAP replica cause problems on the primary. Logical Decoding on a replica in also needs hot standby feedback which is a giant PITA for your ETL replica.

n_u 1y ago

From my position MySQL pros: The MySQL docs on how the default storage engine InnoDB locks rows to support transaction isolation levels is fantastic. [1] This can help you better architect your system to avoid lock contention or understand why existing queries may be contending for locks. As far as I know Postgres does not have docs like that. MySQL uses direct I/O so it disables the OS page cache and uses its own buffer pool instead[2]. Whereas Postgres doesn't use direct I/O so the OS page cache will duplicate pages (called the "double buffering" problem). So it is harder to estimate how large of a dataset you can keep in memory in Postgres. They are working on it though [3] If you delete a row in MySQL and then insert another row, MySQL will look through the page for empty slots and insert there. This keeps your pages more compact. Postgres will always insert at the bottom of the page. If you have a workload that deletes often, Postgres will not be using the memory as efficiently because the pages are fragmented. You will have to run the VACUUM command to compact pages. [4] Vitess supports MySQL[5] and not Postgres. Vitess is a system for sharding MySQL that as I understand is much more mature than the sharding options for Postgres. Obviously this GA announcement may change that. Uber switched from MySQL to Postgres only to switch back. It's a bit old but it's worth a read. [6] Postgres pros: Postgres supports 3rd party extensions which allow you to add features like columnar storage, geo-spatial data types, vector database search, proxies etc.[7] You are more likely to find developers who have worked with Postgres.[8] Many modern distributed database offerings target Postgres compatibility rather than MySQL compatibility (YugabyteDB[9], AWS Aurora DSQL[10], pgfdb[11]). My take: I would highly recommend you read the docs on InnoDB locking then pick Postgres. [1] https://dev.mysql.com/doc/refman/8.4/en/innodb-locking.html https://dev.mysql.com/doc/refman/8.4/en/innodb-locking.html [2] https://dev.mysql.com/doc/refman/8.4/en/memory-use.html https://dev.mysql.com/doc/refman/8.4/en/memory-use.html [3] https://pganalyze.com/blog/postgres-18-async-io https://pganalyze.com/blog/postgres-18-async-io [4] https://www.percona.com/blog/postgresql-vacuuming-to-optimize-database-performance-and-reclaim-space/ https://www.percona.com/blog/postgresql-vacuuming-to-optimiz... [5] https://vitess.io/ https://vitess.io/ [6] https://www.uber.com/blog/postgres-to-mysql-migration/ https://www.uber.com/blog/postgres-to-mysql-migration/ [7] https://www.tigerdata.com/blog/top-8-postgresql-extensions https://www.tigerdata.com/blog/top-8-postgresql-extensions [8] https://survey.stackoverflow.co/2024/technology#1-databases https://survey.stackoverflow.co/2024/technology#1-databases [9] https://www.yugabyte.com/ https://www.yugabyte.com/ [10] https://aws.amazon.com/rds/aurora/dsql/ https://aws.amazon.com/rds/aurora/dsql/ [11] https://github.com/fabianlindfors/pgfdb https://github.com/fabianlindfors/pgfdb

sgammon 1y ago

planetscale now supports both :)