For which tasks is MySQL more suitable and for which PostgreSQL?

Question

MySQL

I would like to hear non-religious disputes, and comparisons of what is better / worse. Namely FOR what tasks what DBMS was sharpened and will approach more? What is better to use in a universal online store script?

Answer 1

The tasks are about the same. For an online store, any will do. Both DBMS are time-tested and are used, among other things, in large and high-load projects.
For cheap sites, MySQL is better. is on any hosting. Those. if you write a boxed CMS in PHP, then MySQL is better. If it's a cloud-based website builder, then it doesn't matter.
MySQL supports both transactions and full-text search and geo data, but something is implemented in the MyISAM engine, and something in InnoDB. Those. if we need both, then we will have to use different engines for tables, and this breaks, for example, referential integrity. Progress has one engine and it's all there.
MySQL some time ago had the most clumsy, read slow join'ov algorithm. In this regard, Postgres was ahead. Well, as ahead, it gave an advantage on clumsily written applications with a large number of joins. Can this be considered an advantage? But in the latest versions of MySQL 5.6, there are faster kinds of joins. Personally, I don’t care, because joins were slow in the first place, and secondly they didn’t scale, so I didn’t use them. Yes, and Django can do a fast join at the application level with a simple ORM method - prefetch_related.
JSONB is a Postgres technology that allows you to save JSON to a table. I would not call this a special advantage, storing JSON in MySQL is not a problem, usually in a text field, just do the processing at the application level and that's it. In Django, again, this is done by installing some kind of django-jsonfield battery or writing 15 lines of code at once. Those. there is no obvious strong advantage here, especially since such things are in any case more convenient to store in Monge.
Arrays in Postgres. It would be cool if we could not only store, but also build an index on them, as in Monge. But no, Postgres offers to build full-text indexes on them. Well, if we are talking about full-text search, then any large site already has a search engine, such as Sphinx or ElasticSearch, respectively, I can serialize arrays in MySQL into a string and index them with this engine.
Here the advantage is clearly on the side of Monga (although we do not consider it here), Postgesu still has to grow.
Postgres can build indexes on XML and apparently JSON. Just think, if someone has already said that we have a full-text engine on the project, then we can build an index on anything without Postgres.
Full text search. This is where Postgres really has an advantage over both MySQL and MongoDB. Postgres supports the Russian language at the level of stemming, which in many cases is enough for the wave. If you need morphology, then search engines should be used, not a database. MySQL does not support languages, and MongoDB supports Russian, but does not support the AND operator (damn, how can that be?!).
In general, so far I see full-text search as such a clear advantage of Postgres, for projects in which for some reason there are no full-fledged full-text engines. But with the installation of any engine, this advantage disappears.

Answer 2

PostgreSQL is better in every aspect, including response speed.
There is no longer any reason to use MySQL.
PostgreSQL can be sold with your own product. MySQL is not.
PostgreSQL knows about arrays, MySQL doesn't.
PostgreSQL is capable of json, MySQL is not.
PostgreSQL knows how to DateTime with timezones, MySQL doesn't.
PostgreSQL knows how to work with time intervals, MySQL does not.
PostgreSQL can handle unicode normally, MySQL can't.
PostgreSQL can DISTINCT on selected columns, MySQL can't.
PostgreSQL is able to limit index values ​​by conditions, MySQL is not.
PostgreSQL has schemas, MySQL does not.
PostgreSQL has table inheritance, MySQL does not.
PostgreSQL has normal JOIN optimization, MySQL doesn't.
PostgreSQL is capable of materialized views, MySQL is not.
PostgreSQL can do PLSQL, MySQL can't.
PostgreSQL is capable of Python functions, MySQL is not.
PostgreSQL knows keys from external sources, MySQL does not.
PostgreSQL is capable of normal (nested) transactions, MySQL is not.
MySQL has problems installing and uninstalling its services under Windows, PostgreSQL does not.
̶P̶O̶S̶T̶G̶R̶E̶S̶Q̶L̶ ̶U̶̶E̶̶T ̶A̶̶̶̶̶̶̶̶̶̶̶̶̶̶̶̶̶̶̶̶̶̶̶̶̶̶̶̶, ̶m̶y̶S̶Q̶L̶ ̶-̶ ̶N̶̶T.

Answer 3

3 things need to be clarified before chilling
1. The database model in the 6th normal form can be designed by units
2. Understand how all these schemas, catalogs, views and materialized views fit into SOA, how to test, what functions to store where, how to put triggers, how clean up logs, how to separate read / write views within CQRS - only a few know, but even less is used in practice.
3. PostgreSQL can be made faster and more efficient than MySQL / MongoDB / Oracle, but not vice versa, although you can mow everywhere :), and there is a lot of black magic that is simply inaccessible to mere mortals. PostgreSQL is too easy to customize, and you can get just wild performance gains, especially when it comes to introducing some custom data types, indexes and aggregation functions. In other solutions "step to the right, step to the left - execution". If you want a simple solution that "just works out of the box" - you definitely shouldn't use PostgreSQL.

Answer 4

And something else. Postgres is more focused on data processing inside the database, on their integrity.
MySQL is more focused on fast response for page generation

Answer 5

Postgres supports SQL99 and SQL2008, which is what the muscle lacks. The conversation should also be conducted in the context of engines, of which the muscle does not have one at all.

Answer 6

Hell holivar. Use what you know/like. For a small-medium store, a dolphin is better - easier and cheaper, for large / highly reliable projects, you will already have to launch an elephant / something else serious.