Oracle vs PostgreSQL. Why choosing Oracle can be a smart decision.
Reading numerous articles on Habré about successful migration from Oracle to PostgreSQL, an inexperienced reader may get the impression that PostgreSQL is no worse, and even better than Oracle. And the choice is obvious. And Hundreds of thousands of companies that end up paying billions of dollars to Oracle companies are simply wasting money. But I will try to dissuade you, where-where, and in large companies they can count money. And their decisions are by no means erroneous.
The purpose of the article is to create a seed of doubt in the soul of the reader who is trying to choose between relational databases that work in the versioner mode.Why the versioner mode? There is not a big choice here, and there are worthy rivals in blockers and the choice is even more difficult. (As a result, there is a free version of DB2 for small databases).
I am not an Oracle database expert, although I have worked with this database for many years and not only with it. All I can do is use its advantages and achieve optimal speed. I am not a PostgreSQL expert either (I have never used it in production).
Reading articles on successful migration, I understand that these companies did not need Oracle, or the database was initially chosen incorrectly. They used only a small fraction of the capabilities of this database. Only therefore they could make a decision on migration and implement it. Simply, if you use the full power of this database, you will never have a desire to migrate because it is akin to writing your application almost from scratch.
Let's finally talk about the performance advantages that Oracle provides and based on this information you will find the answer for yourself.
Well, as they say, the devil is in the details, while Oracle has worked these details much better. Oracle never ceases to amaze with each version. And it does not matter which database you transfer to after Oracle - you will always have a feeling: Well, damn, in Oracle it has been around for a long time, or in Oracle it is better.
I'm pretty sure that with the same hardware and using all the features of PostgreSQL and Oracle, you can get better performance with less effort on ORACLE.
PS In no case do not consider this article as a PR Oracle database.
I well understand that there are certain things that are better done in PostgreSQL. But in general, Oracle in this segment of the database №1.
I did not specifically touch on the things related to administration. Just imagine that you can transfer the date file from disk to disk or restore the “broken block” in the Online state. And you can make the transition to the new server without stopping the database. And these are not new features. At Oracle everything is much better there, and I am not the administrator of the DB.
Previously, somewhere before version 10, the admin was almost always needed. Now the need for admin has fallen heavily, though the qualifications of the admin are now needed higher. Perhaps, in version 15, the concept of “admin” DB will be a thing of the past :)
And Pl / SQL is more thoughtful than others, although of course this is not C # :). True, this is purely individual.
Well, I did not touch on things that poorly help in speed.
PSS And yes, I hardly remembered all the possibilities. Only those who were on the “surface” So add in the comments. I will enable in upd.
The purpose of the article is to create a seed of doubt in the soul of the reader who is trying to choose between relational databases that work in the versioner mode.Why the versioner mode? There is not a big choice here, and there are worthy rivals in blockers and the choice is even more difficult. (As a result, there is a free version of DB2 for small databases).
I am not an Oracle database expert, although I have worked with this database for many years and not only with it. All I can do is use its advantages and achieve optimal speed. I am not a PostgreSQL expert either (I have never used it in production).
Reading articles on successful migration, I understand that these companies did not need Oracle, or the database was initially chosen incorrectly. They used only a small fraction of the capabilities of this database. Only therefore they could make a decision on migration and implement it. Simply, if you use the full power of this database, you will never have a desire to migrate because it is akin to writing your application almost from scratch.
Let's finally talk about the performance advantages that Oracle provides and based on this information you will find the answer for yourself.
- Partition (8i) . Partition - allows you to increase the amount of data with virtually no effect on the overall speed. A nice and important bonus is index partitioning. In PostgreSQL partitions will appear only in version 10. Prior to this, inheritance (INHERITS) is a dirty hack. Yes, and the possibility of partitioning in Oracle increase with each version.
- Merge (8i) . Yes, yes, the same Merge that is already in MSSQL for many years (2008) and which will not even be in PostgreSQL 11. It gives an increase in speed tenfold compared to single operations. Yes, I know that PostgreSQL supports subqueries and you can implement everything through Insert select and clever update. But this is not the same.
- RESULT_CACHE (Select) (11g). At Oracle, this technology appeared relatively recently. If you use this technology wisely, it gives dozens and hundreds of times a gain in some things. The main thing is to learn how to "clever" use it.
- Option INMEMORY (12c) There is no equivalent for PostgreSQL. The real increase on some requests hundreds of times.
- Optimizer + tuning queries . Starting with 11g, it almost turned into a click next-> next to EM. PostgreSQL is more complicated with this and the lack of an EM equivalent in general is rather uncomfortable.
PL / SQL. Yes, I know about the diversity of languages in PostgreSQL. But Oracle is constantly improving the language with an eye on speed.- Compilation . The bikode happens during save (in PostgreSQL during the first call in session + query plan during the first execution). Starting from 10g in Oracle, compilation into native code is possible.
- Native Integer - significantly speeds up work with numbers. In PostgreSQL, you can use other more suitable languages (translators). In orakla this can also be solved using Java and C.
- PL / SQL context switch. Oracle is very concerned about optimizing this indicator, improving it from version to version. To reduce delays, switch the “context” to come up with BULK COLLECT and FORALL and more.
Given the state of PostgreSQL languages, this task is not at all important at this stage. - Result Cache (function) (11g) With proper use, the responsiveness of the application as a whole can grow several times while not requiring much effort. On some requests dozens of times.
Well, as they say, the devil is in the details, while Oracle has worked these details much better. Oracle never ceases to amaze with each version. And it does not matter which database you transfer to after Oracle - you will always have a feeling: Well, damn, in Oracle it has been around for a long time, or in Oracle it is better.
I'm pretty sure that with the same hardware and using all the features of PostgreSQL and Oracle, you can get better performance with less effort on ORACLE.
PS In no case do not consider this article as a PR Oracle database.
I well understand that there are certain things that are better done in PostgreSQL. But in general, Oracle in this segment of the database №1.
I did not specifically touch on the things related to administration. Just imagine that you can transfer the date file from disk to disk or restore the “broken block” in the Online state. And you can make the transition to the new server without stopping the database. And these are not new features. At Oracle everything is much better there, and I am not the administrator of the DB.
Previously, somewhere before version 10, the admin was almost always needed. Now the need for admin has fallen heavily, though the qualifications of the admin are now needed higher. Perhaps, in version 15, the concept of “admin” DB will be a thing of the past :)
And Pl / SQL is more thoughtful than others, although of course this is not C # :). True, this is purely individual.
Well, I did not touch on things that poorly help in speed.
PSS And yes, I hardly remembered all the possibilities. Only those who were on the “surface” So add in the comments. I will enable in upd.