DEV Community

PostgreSQL or MySQL: What Should I Choose for My Full-Stack Project?

Leandro Nuñez on April 09, 2024

Choosing the right database is a pivotal decision for full-stack developers, impacting everything from application performance to scalability. Post...

Read full post

Leandro Nuñez • Apr 10

So, it really boils down to a bunch of stuff like how much data you're dealing with, the setup, and honestly, the trickiest part has got to be dealing with all the constraints and foreign keys. That's the real headache, in my opinion. But if you've got some experience with MySQL under your belt, then jumping into PostgreSQL shouldn't be too rough.

Syndesi • Apr 10

MariaDB is the default replacement for MySQL on most Linux distributions these days :)

Leandro Nuñez • Apr 10

According to statista, the next linux alternative to MySQL is PostgreSQL
statista.com/statistics/809750/wor...

Also of 2024:
geeksforgeeks.org/most-popular-dat...

But MariaDB would be a great alternative. I will cover that in another article! Thanks for your comment.

Miles • Apr 11

MySQL and MariaDB are diverging enough to be considered different engines with different feature sets.

For example use MariaDB if you want/need temporal tables, exclusion constraints, a native UUID type, user-defined functions as a column default, support for sequences, or reference a temporary table twice within a single statement.

Use MySQL if you want/need CTEs in a subquery, descending indexes, expression indexes, the ability to reliably use it as a queue, or use LATERAL joins.

Neither is a strict superset or subset. Then again, if you're looking for the engine supporting the most features, you need to look closer at Postgres. The only thing really missing there from either MariaDB or MySQL is temporary tables (which you can work around with community-supported options).

Leandro Nuñez • Apr 11

Then, I don't get the first comment. Why MariaDB would be the alternative to MySQL if it's considered a complete different engine?
The idea of the article is to set the differences on the most-feature-sharing engines used and what to check before choosing. It has no relation on which one is the better. Thanks for your comment!

Miles • Apr 12

MariaDB forked from MySQL years ago. They are wire-compatible with one another, so a MySQL client can connect to a MariaDB server and a MariaDB client can connect to a MySQL server. You can use admin tools like MySQLWorkbench and phpmyadmin with both as well.

So unless you're using a feature that doesn't exist in the other one, you can swap between them relatively easily.

The same is true for products like Yugabyte, CockroachDB, and Postgres; they are all wire-compatible with one another. Easily swap out as long as you're not using an engine-specific feature even though they have very different performance envelopes.

Leandro Nuñez • Apr 12

Still don't see the point of the comment.
This article is specific to MySQL and PostgreSQL. I'm really sorry if not adding MariaDB as an option made you fell unconfortable or similar. That was not my intention at all.
I was trying to showcase the two closest engines.
Perhaps, you want me to cover MySQL or MariaDB in another article.
Just let me know.
Thanks for your comment.

Tanzim Ibthesam • Apr 10

Nice how many days can it take for someone to switch from mysql to postgres?

Miles • Apr 11

Depends on how much MySQL-specific syntax you're using. Here are some examples:

Function definitions can be quite different.
Query variables are what you'd use in MySQL prior to the introduction of CTEs.
Table and column comments can't be inline on Postgres.

Beyond that, you'll need to replace your database client library. For Java it's trivial due to JDBC. If you're using Node for example, because there's no common DB interface, you'd have to make code changes wherever SQL is invoked.

If you're using a popular ORM without punching out to native queries, not long at all. If you have a bunch of native queries or a bespoke ORM that only supports MySQL, you're in for some work.

Miles • Apr 11

There's a better option for Postgres.

WITH new_employee AS (
    INSERT INTO employees (name, role, hire_date)
         VALUES ('Jane Doe', 'Developer', '2023-01-10')
      RETURNING employee_id
)
UPDATE project_assignments
   SET project_id = 2
  FROM (SELECT employee_id FROM new_employee) AS subquery
 WHERE employee_id = subquery.employee_id;

Why? Sometimes the id isn't a sequence. Sometimes you might not know the name of the sequence. Though rare, sequences can be renamed, meaning you'd have to change all your queries referencing it. Sometimes the id is passed in from the user (common for UUIDs and custom id types like ULID). Sometimes it's an autogenerated UUID instead of a int/bigint.

With this variation you can handle any id type, not just ones from sequences, AND it can be included in either a separate transaction or the current one due to no longer needing explicit BEGIN/COMMIT (CTEs are considered a single statement), AND it allows you insert multiple employees at the same time rather than just one at a time!

As usual there is no MySQL equivalent, because MySQL doesn't support the RETURNING clause and therefore does not support this level of flexibility.

Leandro Nuñez • Apr 11

thanks for sharing!