5 ms·

How does it compare to MariaDB?

by beermonster 5y ago

rubenv 5y ago
It's better.
- beermonster 5y ago
  This [1] seems to be a more discerning answer [1] https://hevodata.com/learn/mariadb-vs-postgresql/ https://hevodata.com/learn/mariadb-vs-postgresql/
  - Tostino 5y ago
    FYI, the section on partitioning is wrong there. Postgres supports table partitioning perfectly fine[1]. 1. https://www.postgresql.org/docs/current/ddl-partitioning.html https://www.postgresql.org/docs/current/ddl-partitioning.htm...
0xdeadbeefbabe 5y ago
Less West Side Story more Jungle Book.
nelgaard 5y ago
They are both very impressive systems. I have done many projects in both PostgreSQL and MariaDB/MySQL I do tend to get attached to the DB of my last project because I discover new cool things. And there are some differences. But I cannot think of a PostgreSQL project that would not also have worked great in MySQL or a MySQL project that could not have been done with PostgreSQL. I have re-implemented systems that used non open-opensource DBMS's on Windows and the convenience and performance (on the same hardware) were just amazing when using PostgreSQL or MariaDB.
- SahAssar 5y ago
  > But I cannot think of a PostgreSQL project that would not also have worked great in MySQL or a MySQL project that could not have been done with PostgreSQL. Can MySQL do GIS related features like PostGIS? Also IIRC to index JSON in MySQL you need to add generated columns for all the properties/paths you want to index, which can get pretty hairy.
  - dagw 5y ago
    Can MySQL do GIS related features like PostGIS? It can[1] for most of the common cases, but it lacks many of the more advanced features PostGIS has. So if you have a MySQL database up and running, you probably don't have to switch to PostGIS just because you want to add some geospatial data. However if starting a new geospatial project then you should almost certainly choose PostGIS. [1] https://dev.mysql.com/doc/refman/8.0/en/spatial-types.html https://dev.mysql.com/doc/refman/8.0/en/spatial-types.html
- majewsky 5y ago
  Is there a MySQL equivalent for "FOR UPDATE SKIP LOCKED"? That's a feature that I've fallen in love with because it has completely eradicated the need to have a message queue like RabbitMQ or whatever. For example, for distributing jobs to background workers, I just insert them into a jobs table and have the workers poll for jobs with: DELETE FROM jobs WHERE id = ( SELECT id FROM jobs FOR UPDATE SKIP LOCKED ) RETURNING *; Then just commit the respective transaction on success or rollback on failure. Would this look as simple as that in MySQL?
  - evanelias 5y ago
    MySQL 8 supports FOR UPDATE SKIP LOCKED [1], but does not have a RETURNING clause. MariaDB 10.5+ supports a RETURNING clause, and 10.6 now has FOR UPDATE SKIP LOCKED. This is quite new though (10.6 went GA a month ago) so there might be some wrinkles. [1] https://mysqlserverteam.com/mysql-8-0-1-using-skip-locked-and-nowait-to-handle-hot-rows/ https://mysqlserverteam.com/mysql-8-0-1-using-skip-locked-an...
ploxiln 5y ago
Postgres has always prioritized correctness, and more sophisticated datatype and query features ... mysql/mariadb have had more consistent performance and easier/more-flexible replication. I'm thinking about auto-vacuum and logical replication to upgraded versions... I've had much less trouble with mysql/mariadb around these topics many years ago, than I've had with Postgres relatively recently. But also, I don't care so much about fancy data types and query features, I stick to the basics.

How does it compare to MariaDB?

rubenv 5y ago

It's better.

beermonster 5y ago
This [1] seems to be a more discerning answer [1] https://hevodata.com/learn/mariadb-vs-postgresql/ https://hevodata.com/learn/mariadb-vs-postgresql/
- Tostino 5y ago
  FYI, the section on partitioning is wrong there. Postgres supports table partitioning perfectly fine[1]. 1. https://www.postgresql.org/docs/current/ddl-partitioning.html https://www.postgresql.org/docs/current/ddl-partitioning.htm...

0xdeadbeefbabe 5y ago

Less West Side Story more Jungle Book.

nelgaard 5y ago

They are both very impressive systems. I have done many projects in both PostgreSQL and MariaDB/MySQL I do tend to get attached to the DB of my last project because I discover new cool things. And there are some differences. But I cannot think of a PostgreSQL project that would not also have worked great in MySQL or a MySQL project that could not have been done with PostgreSQL. I have re-implemented systems that used non open-opensource DBMS's on Windows and the convenience and performance (on the same hardware) were just amazing when using PostgreSQL or MariaDB.

SahAssar 5y ago
> But I cannot think of a PostgreSQL project that would not also have worked great in MySQL or a MySQL project that could not have been done with PostgreSQL. Can MySQL do GIS related features like PostGIS? Also IIRC to index JSON in MySQL you need to add generated columns for all the properties/paths you want to index, which can get pretty hairy.
- dagw 5y ago
  Can MySQL do GIS related features like PostGIS? It can[1] for most of the common cases, but it lacks many of the more advanced features PostGIS has. So if you have a MySQL database up and running, you probably don't have to switch to PostGIS just because you want to add some geospatial data. However if starting a new geospatial project then you should almost certainly choose PostGIS. [1] https://dev.mysql.com/doc/refman/8.0/en/spatial-types.html https://dev.mysql.com/doc/refman/8.0/en/spatial-types.html
majewsky 5y ago
Is there a MySQL equivalent for "FOR UPDATE SKIP LOCKED"? That's a feature that I've fallen in love with because it has completely eradicated the need to have a message queue like RabbitMQ or whatever. For example, for distributing jobs to background workers, I just insert them into a jobs table and have the workers poll for jobs with: DELETE FROM jobs WHERE id = ( SELECT id FROM jobs FOR UPDATE SKIP LOCKED ) RETURNING *; Then just commit the respective transaction on success or rollback on failure. Would this look as simple as that in MySQL?
- evanelias 5y ago
  MySQL 8 supports FOR UPDATE SKIP LOCKED [1], but does not have a RETURNING clause. MariaDB 10.5+ supports a RETURNING clause, and 10.6 now has FOR UPDATE SKIP LOCKED. This is quite new though (10.6 went GA a month ago) so there might be some wrinkles. [1] https://mysqlserverteam.com/mysql-8-0-1-using-skip-locked-and-nowait-to-handle-hot-rows/ https://mysqlserverteam.com/mysql-8-0-1-using-skip-locked-an...

ploxiln 5y ago

Postgres has always prioritized correctness, and more sophisticated datatype and query features ... mysql/mariadb have had more consistent performance and easier/more-flexible replication. I'm thinking about auto-vacuum and logical replication to upgraded versions... I've had much less trouble with mysql/mariadb around these topics many years ago, than I've had with Postgres relatively recently. But also, I don't care so much about fancy data types and query features, I stick to the basics.