45
I have been with this doubt for a long time, I see some people recommending the use of Postgresql, but it is not clear the advantages and disadvantages compared to Mysql. I want to define the bank that I will use in my project and I would like to know which is the best.
47
First a reminder: Mysql is no longer "100% public", its owner is Oracle, while Postgresql is 100% public and supports several other 100% public projects, such as Postgis.
Mysql was well defended in the discussion, and I do not disagree with most... It remains for me, as a user of Postgresql, for years, to defend it a little.
For open source software to compete with rather aggressive competitors on lobby and in the 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, she has created a free installation package plug-and-play great, who left behind Mysql.
Mysql’s "basic hosting" only remained strong and diffused by the power of LAMP, years ago, when proprietary software tried to blatantly exploit providers... It has become tradition, Mysql is as "pop" as Wordpress in a hosting, or as natural as SQL Lite in an Android app.
Hosting critical systems, enterprise 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 is for the company to internalize its data-center). Anyway, when "basic hosting" (the cheapest) offers Postgresql, the price is the same as Mysql.
SGBD totalmemte Transactional (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. Old Mysql did not have ROLLBACK, but that’s what made it faster (!)...
Scalability is a vague and broad term, but in general it means "ability to manipulate a growing portion of work evenly, or be prepared to grow". In this sense, who has the ability 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.
SQL Object-Oriented: again Postgresql fulfills the promise, always allowed inheritance, as well as definition of custom types, etc..
Distributed database: such "asynchronous data calls" (already cited here) are one of the key pieces, which allow Postgresql to actually behave like distributed (Mysql is not able to 100%).
Several commands are "Mysql only", it is not very "big pattern"... Whereas Postgresql, it respects the standards when it proposes to do so, and has done in most of its modules, in object orientation, and has done in the XML since version 8.4, that did not exist in Mysql (today 5.6 is still restricted to two functions).
For those who question Postgresql compliance, please read the SQL Conformance: it is adherent to most of the standard SQL:2011, thing that Mysql is not.
There’s just opinions, without worrying about supporting them, or remembering if they’re really popular:
If you will need transactions (ROLLBACK), it is already a sign that you do not need Mysql... Postgresql was born with this.
If you are the programmer and the manager of your project, do not get "tied to the basics", require your professionalism infrastructure (it is very convenient to say that you do not know), require SQL with greater language expressiveness, scalability, etc.
With more and more powerful servers, it no longer makes sense to use the larger justification of Mysql, which was "save CPU". If need be even save, think about Sqlite.
With the ease of virtualization on Web servers - you can have a Microsoft application on a VM, and its SQL service on Postgresql under Linux on another VM -, you no longer need to attach the entire system to "Oracle solutions" or "Microsoft solutions"... It makes sense to use interoperable standards and free software.
26
You have not given many parameters of how you intend to use the database. This makes a lot of difference. I will try to put the two main scenarios.
He also didn’t say which bank he or the people he works with know best. Perhaps because he doesn’t know any of them. In almost any scenario, using what you’re used to is the best way, it doesn’t matter much other reasons. In cases you don’t know any, it’s best to look for a solution that solves any scenario you come across. In this case Postgresql is the best solution. Pay attention, it is better than other solutions in few scenarios, in most cases any BD solution will meet the need.
If you’re going to use it for simple, normal websites, you’ll probably want something simple to install (although you’ll probably use a hosting where the bd is already installed), to maintain and to program the access. This is a case where Mysql usually does better than Postgresql. Postgresql is certainly more complex, no doubt about it.
But if you want something simple, then it’s better to think about the third way. Sqlite is simpler, more standardized and more reliable than Mysql. His only problem is that it doesn’t allow large-scale. Officially they say that 99% of websites can use Sqlite quietly. I say from my experience it is at least 99.9%. In most scenarios it will scale even better than Mysql. When entering a scenario where Mysql has problems, Mysql may have problems as well.
I’m not saying Mysql isn’t good. Of course it is. But the big reason people use it is because everyone uses it and not because it’s necessary, because it does something better.
For enterprise applications, Postgresql shines brighter than Mysql. There are numerous cases of Mysql usage in corporate environment with huge success, do not understand that Mysql is not good in this scenario. Sqlite already gets more restricted, it can be used only on very small networks and starting to lose one of its advantages compared to Mysql which is reliability.
Keep in mind that of all the best known comics he is the one who is not full acid. Even the Sqlite is.
Postgresql does not owe much to systems commonly used in enterprise applications such as Oracle and SQL Server. It even has some unique features. Mysql solves almost all situations well, it is possible to solve others with a little more effort or difficulties, but it leaves something to be desired in more complex scenarios.
I decided to put an answer because there are several errors in the answers posted so far. Actually the answer from Peter Krauss is very good. I’ll start with her having a few points that might be more enlightened.
It has been said that Mysql does not have ROLLBACK. This is not an absolute truth. First we need to understand one of the features of Mysql. It allows multiple Storage Engines.
You can understand what Peter Krauss said, because almost everyone associates Mysql with the Storage engine called Myisam. He is the one who gave the performance advantage to Mysql for many years, it is used in the majority of scenarios, mainly for websites. Myisam doesn’t really have ROLLBACK. But Innodb, which is another Storage engine, does have ROLLBACK. But that doesn’t help so much. Because in any case, Mysql has difficulties in maintaining the so-called full ACID. That is, the database may get lost in some situations. Very specific, but it happens. The Mysql architecture, independent of the chosen Storage engine has difficulty keeping the data in order in some extreme cases.
Some people think that the only advantage of Mysql is when used with Myisam, which has some advantages, especially performance. Others think that Innodb is the only way forward with Mysql by giving more tools, being more scalable and more reliable in some scenarios. I am of the opinion that in scenarios where Myisam is a good solution, Sqlite is better. And in scenarios where Innodb is good, Postgresql is better.
Postgresql is more powerful but has the cost of being more complex. The great advantage of Mysql in relation to Postgresql is to be simpler, and with Innodb this is almost lost.
So his answer isn’t wrong, but it’s not complete.
Scalability is possible in both cases, and in some ways it’s even easier in Mysql in most scenarios where it really needs a lot of scale, which is rare. But Postgresql has given priority to improve this situation and it is not far from being easier to scale massively in Postgresql. Note that I nay said Mysql is better at this point. Postgresql has many more ways and tools to scale or avoid complex solutions in medium cases, some already cited in the other answer. Almost everyone overestimates the need for BD scalability. The hardware is so powerful today that complex scalability schemes are rarely needed. Almost everything you read in blogs about scalability today is talking about websites that are hugely successful like the OS. It’s not for the common cases. These cases are so dominated you don’t have to talk so much. Postgresql is better for Scale up (running more on the same machine) and Mysql is easier, just easier, not better, in Scale out (using multiple machines to scale). Postgresql uses up to the last stalk of the machine to then you need more machines, Mysql, you need more machines quickly, but still in 2014 it is easier to add machines to Mysql. In 2015 this may be different.
Forget object-oriented SQL. This did not work anywhere. This was something that should have existed in Postgresql but was abandoned long ago. There are some tools there, but they are problematic, limited and do not receive updates. You don’t see serious applications using this feature and it’s something questionable in the myocardium of cases.
Finally and already starting to question the other answers, Mysql is totally outside the ANSI standard and invents some crazy that causes shivers in various administrators and programmers. In fact these crazy things are the main vantgens of Mysql, they are the ones that facilitate its use. You have to choose what you want. But don’t say that Mysql is standardized. I don’t know how an answer that says this can get a lot of votes. Probably Mysql fan boys voted.
Postgresql you have to know how to use to work. Mysql works a lot of things even without knowing why. Until you go crazy to find out why it doesn’t work anymore. In Mysql there is more or less correct. Some people like this.
Today it is not clear that it is easier to install Mysql than Postgresql. Of course you have to know some things to do better, you have to know the installer of Enterprisedb. And if you think installation is a problem, I don’t know what you’re going to do to run a comic book that’s absurdly more complex. Rarely you install, this should not be taken into consideration. But if you have a scenario that makes many installations, you will become so experienced that no matter which is easier, you will master anything. And in these scenarios, Next, Next, Next, Finish is the last thing you should use.
If your concern is with security choose Postgesql, but if it is scalability go from Mysql.
Safety or reliability? You need to define well what you are talking about. Security, reliability or scalability, if you want the best, is Postgresql. I will repeat, Mysql is only simpler, therefore worse. Both are safe, each has its own specific advantage.
I will reaffirm here that even Innodb does not support full ACID. It is ACID in certain circumstances. That is, it is not ACID. I tried to find a website that shows problems that Innodb suffers, but I could not.
Postgresql supports some variations of Storage. They are not enough to be completely separate, so different, but you can choose the best variant for each situation and this is well integrated with every database. Unlike Mysql the separate engine cannot do some things because Mysql hinders or prevents it. Postgresql tries to use the best way to store in the same engine.
It also has several forms of indexing. For optimization, choosing the correct indexing method is better than the main data Storage. This makes Postgresql beat more powerful systems in certain scenarios.
Want maximum performance and do not need reliability? Postgresql supports it too. It’s just not the default and is not recommended. The biggest problem with Postgresql is that people don’t know everything he’s capable of, it’s not lack of capacity. It opts for a more "Enterprise" or more professional path, leaving you optionally leave it more "loose" as Mysql is by default.
There are ways to make Mysql a little better, but then the simplicity of it is over. There is no free lunch.
In fact when you try to do some more complex things, Postgresql has more appropriate tools (better Joins, Ubqueries, Ctes, custom functions, more powerful data structures, extensibility, etc).
By the number of Mysql users, you should have more professionals with a capital P working with it, which shows that the technology is not important. But as a percentage, you will find more Professionals using Postgresql. It does not get along well with amateurs. Interestingly Sqlite should be more successful among amateurs, but perhaps these are so amateur that they can’t even find Sqlite. Or maybe the fact that Sqlite requires you to do things correctly, even simply, keeps amateurs who care about the result away, not the right result.
Note that the administrator’s ability to make the database work well is always more important than the chosen technology.
In one of the links I posted has an extensive comparison of the two, so I will not go into detail.
Beware of comparing old versions. Especially when only one of them is old.
Much of what I said here is a simplification. Keep this in mind. Nothing is database can be analyzed so simplistically. Some people can easily think it’s different.
I don’t think so, but if you think popularity is important: http://db-engines.com/en/ranking Reread what Peter Krauss wrote before giving much value to it. And note that this ranking has its own criteria. Sqlite is the most used database in the world. It must have multiple Sqlite databases on your computer and you don’t even know.
In the Wikipedia has a comparison of several systems that helps a lot to get an initial sense of the differences.
I particularly use Postgresql in most cases. It solves everything I need, never leaves me hanging. In cases where simplicity is more important, Sqlite solves my problem. It’s been a while since I found a case where I need simplicity and a power greater than what Sqlite gives but not so much that Mysql doesn’t have. So Mysql stayed for the cases where I am required to use it for non-technical reasons.
You’ll have to judge for yourself what your case is. I’ve given a good amount of information and I’ve indicated where you can find a head-to-head comparison of them.
Any mistakes here? It’s possible, but I did my best for the moment.
12
This site explains some things that may serve your answer.
Source: http://www.infowester.com/postgremysql.php
Here is a brief summary:
Mysql or Postgresql, which to use? Both are very good and do not do ugly in the face of paid alternatives. In addition, they have features and advantages in common, which means that for most applications, both can be used. In fact, the correct thing is not to try to figure out which is best, but in which situation one or the other should be used.
Postgresql is optimized for complex applications, that is, involving large volumes of data or dealing with critical information. Thus, for a medium/high-sized e-commerce system, for example, Postgresql is more interesting, since this DBMS is able to handle satisfactorily the volume of data generated by the query and sale operations.
Mysql, in turn, is focused on agility. So, if your application needs fast returns and does not involve complex operations, Mysql is the most suitable option as it is optimized to provide fast data processing and short response time without requiring too much hardware. If you need, for example, a database to store the content of your site, your forum or need to maintain a user register of a portal, Mysql "serves as a glove", because such applications do not need the advanced features that Postgresql offers.
In short: if your application requires something more from DBMS than a simple data storage, opt for Postgresql. One can also add: excellent documentation and an active and helpful community.
9
To choose the best DBMS solution for your project you need to know more information about the architecture, for example, whether it is a WEB application with layered architecture or client/server, etc. However some features are general and affect all applications.
There are several other important items to consider when choosing DBMS. This list above is just the tip of the Iceberg. A more complete answer depends on more information about the project and application.
5
Postgresql is excellent. I implanted in a client, small/medium company, 7 years ago, and never had any "bug". Without even replacing the client’s application system, keeping all the original programs so easy, was replaced the, unstable, Program accesses by Postgersql. Easy to maintain. in small and medium enterprises, even needs Dbmanager. A smart programmer can parameterize, implement and provide support and maintenance. Very fast, safe and powerful. It can be used by large companies because it is robust, efficient and effective. Nothing owes to Oracle.
And... for free, really.
Browser other questions tagged mysql postgresql
You are not signed in. Login or sign up in order to post.
in the standardization part, you cited "today in the nested 5.1 is restricted in two functions.". The current version of Mysql is 5.6, also restricted to the same two functions. I took the liberty of editing your reply. Hugs
– gmsantos
Thank you! I kept your technical corrections and added grammar... With your comment I took to reread, and discover my editorial errors (should have many more, are worse than software errors, never end!)
– Peter Krauss
I would like to complement your answer regarding the part you say that "Mysql is no longer "100% public", its owner is Oracle". This is 100% correct. However, the community has made a Fork Mysql source code and created the Mariadb, which replaces Mysql very well and is under Gplv2 license.
– humungs
Would not be serialization instead of seriation? (Said the bore :))
– Clodoaldo Neto
@Clodoaldoneto The quick votes surprised me, Postgresql has its fans there and he was missing be defended... But to not be fanatical, and ensure accuracy, I opened the answer to Wiki: anyone can edit to fix. About the seriation, vide link, apparently this is the term (not to be confused with "XML serialization" and other structures).
– Peter Krauss
It was enough to talk about PL/SQL, which is partially supported by PG in pgPL/SQL. Other than that, I’m even thrilled with the answer!
– Vinícius Gobbo A. de Oliveira
@Viníciusgobboa.deOliveira, Yes (!) was missing, as well as remember that can still create high performance functions with C, and write functions reusing algorithms ready Javascript, Java, Perl, R, etc. ... But note that I opened the answer as WIKI, you can edit it yourself!
– Peter Krauss
Postgres is a better DB to store JSON than specialized Nosql!
– gustavohenke