4 ms·

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 be

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

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