I have had this question for a long time, I see some people recommending the use of PostgreSQL, but the advantages and disadvantages in relation to MySQL are not clear. I want to define the database that I will use in my project and I would like to know which is the best.
First a reminder: MySQL is no longer "100% public", it is owned by Oracle, whereas PostgreSQL is 100% public and supports several other 100% public projects like PostGIS .
MySQL was well defended in the discussion, and I don't disagree with most of it… It remains for me, then, as a PostgreSQL user, for years, to defend it a little.
For open software to compete with aggressive competitors in lobbying and marketing , such as Oracle and MS-SQL-Server, companies have emerged that provide the same type of support, such as EnterpriseDB — in addition to paid support, it created a package Great plug-and-play free installation that left MySQL behind.
MySQL's "basic hosting" only remained strong and pervasive by the power of LAMP years ago, when proprietary software shamelessly tried to exploit providers… It's become tradition, MySQL is as "pop" as WordPress on a web host, or as natural as SQL Lite in an Android app.
Hosting critical systems, business and more sophisticated applications requires PostgreSQL as an OpenSource solution. Of course, the number of "non-basic" providers on the market is much smaller (the common thing is for the company to internalize its data-center). However, when "basic hosting" (the cheapest) offers PostgreSQL, the price is the same as MySQL.
Totally transactional DBMS (that COMMIT/ROLLBACK thing): PostgreSQL has always been, and includes all the standard DDL commands for that. Internally it avoids "locking" using multiversion concurrency control (MVCC) and seriation . The old MySQL didn't have ROLLBACK, but that's what made it faster (!)…
Scalability is a somewhat vague and broad term, but it usually means "the ability to handle a growing piece of work evenly, or be prepared to grow." In this sense, who has the capacity to grow a lot and a lot in data volume without deteriorating performance, or grow a lot in terms of complexity or use of more modules, is PostgreSQL.
Object Oriented SQL : again PostgreSQL fulfills its promise, it always allowed inheritance, as well as definition of custom types , etc.
Distributed database : such "asynchronous data calls" (already mentioned here) are one of the key pieces that allow PostgreSQL to really behave as distributed (MySQL is not 100% capable).
Several commands are "only MySQL", it's not very "standard"… While PostgreSQL, it strictly respects the standards when it sets out to do so, and has done in most of its modules, in orientation to object, and it's even done in XML since version 8.4 , which didn't even exist in MySQL (today in 5.6 it's still restricted to two functions ).
For those who question PostgreSQL's compliance, please read SQL Conformance : it adheres to most of the SQL:2011 standard , which MySQL is not.
Preferences and trends
Thats just opinions, without worrying about supporting them, or remembering if they sãore really popular:
If you're going to need transactions (ROLLBACK), it's a sign that you don't need MySQL… PostgreSQL was born with that.
If you are the programmer and manager of your project, don't get "tied to the basics", demand professionalism from your infrastructure (it's easy to say you don't know), demand SQL with greater language expressiveness, scalability, etc.
With more and more powerful servers, it no longer makes sense to use MySQL's bigger justification, which was "save CPU". If you really need to save, think about SQLite .
With the ease of virtualization on web servers — you can have a Microsoft application in one VM, and its SQL service in PostgreSQL under Linux in another VM — no longer need to tie the entire system to "Oracle solutions" or "Microsoft solutions "… It makes sense to use interoperable standards and free software.