Oracle vs PostgreSQL. Why choosing Oracle can be a reasonable solution
Reading numerous articles on the hub about the successful migration from Oracle to PostgreSQL, an inexperienced reader may have the impression that PostgreSQL is no worse, or even better, Oracle. And the choice is obvious. And Hundreds of thousands of companies that ultimately pay billions of dollars to Oracle just spend money on the wind. But I'll try to disbelieve 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 generate a grain of doubt in the reader's soul, which is trying to make a choice between relational databases that work in the versioner mode.
Why is the mode of the versioner? Here the choice is not great, and in blockers there are worthy contenders and the choice is even more difficult. (What's just a free version of DB2 for small databases).
I'm not an Oracle database expert, although I've 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'm not even an expert at PostgreSQL (I've never used it in production).
Reading articles about successful migration - I understand that these companies did not need Oracle or the database was originally chosen is not true. They used only a small fraction of the capabilities of this database. That's why they could make a decision about migration, and implement it. Simply if you use the full power of this database - you will never have a desire to migrate for it is akin to write your application almost from scratch.
Let's finally talk about the advantages for speed that Oracle gives and based on this information you will find the answer for yourself.
Partition (8i) . Partition - enables the growth of data volume with little or no impact on overall performance. A nice and important bonus is the partisation of the indices. PostgreSQL partitions will appear only in version 10. Prior to this, inheritance (INHERITS) is a dirty hack. And the possibility of partitioning in Oracle increases with each version.
Merge (8i) . Yes, yes, the same Merge which is already in MSSQL for many years (2008) and which will not even be in PostgreSQL 11. It gives an increase in the speed of dozens of times compared to single operations. Yes, I know that PostgreSQL supports subqueries and you can implement everything via Insert select and a cunning update. But this is not the same.
RESULT_CACHE (Select) (11g). At Oracle, this technology has appeared relatively recently. If you use this technology with the mind - it gives a gain in some things in tens and hundreds of times. The main thing to learn is "clever" to use it.
Option INMEMORY (12c) There is no analogue to PostgreSQL. The real increase on some queries is hundreds of times.
Optimizer + tuning requests . Starting at 11g, it almost turned into next-> next click in EM. PostgreSQL with this is more complicated and the lack of an EM analog in general is quite uncomfortable.
PL /SQL. Yes, I know about the variety of languages in PostgreSQL. But Oracle is constantly improving the language with a focus on performance.
Compilation . The bacode occurs during the save (in PostgreSQL during the first call in the session + the query plan during the first execution). Starting with 10g in Oracle, it is possible to compile into native code.
The native Integer - greatly speeds up the work with numbers. In PostgreSQL, you can use other more suitable languages (translators). In the oracle, this can also be solved using Java and C.
Switching the "context" of PL /SQL. Oracle is very concerned about optimizing this indicator, improving it from version to version. For y less delays Switching the "context" of the BULK COLLECT and FORALL and not only.
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 without requiring much effort. On some queries dozens of times.
Well, as they say, the devil is in the details, and Oracle has these details worked out much better. Oracle does not cease to amaze with each version. And it does not matter which database you will change after Oracle - you will always have a feeling: Well, a pancake, Oracle already has it for a long time, or it's done better in Oracle.
I'm pretty sure that with the same hardware and using all the capabilities of PostgreSQL and Oracle, you can get better performance, with less effort on ORACLE.
P.S. Do not consider this article as a PR database of Oracle.
I understand very well that there are necessarily things that are better in PostgreSQL. But in general, Oracle in this segment of Database # 1.
I did not specifically touch 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 new servers without stopping the operation of the database. And these are not new features. At Oracle there everything is much better, and I'm not an administrator of the database.
Previously, somewhere up to version 1? the administrator was needed almost always. Now the need for an admin has fallen dramatically, though the administrator's skills are now needed higher. Perhaps in the version of edak 1? the concept of "admin" DB will become a thing of the past :)
And Pl /SQL is more thoughtful than others, although of course it's not C # :). True, this is purely individual.
Well, I did not touch things that do little to speed things up.
P.S.S. And yes, I hardly remember all the possibilities. Only those who were on the "surface" So add in the comments. I'll include it in the upd.
It may be interesting