Hacker News Reader

Comment by beermonster | Hacker News Reader

How does it compare to MariaDB?

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.

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.

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

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

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?

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

It's better.

This [1] seems to be a more discerning answer

[1] https://hevodata.com/learn/mariadb-vs-postgresql/

FYI, the section on partitioning is wrong there.

Postgres supports table partitioning perfectly fine[1].

1. https://www.postgresql.org/docs/current/ddl-partitioning.htm...

Less West Side Story more Jungle Book.

Reply on news.ycombinator.com