PostgreSQL vs Oracle

Comparison from the developer's perspective




just say — I have nothing against the placement of business logic in stored functions, if available in the architecture of the system and is justified on a number of practical considerations that are beyond the scope of this article.

C Oracle I have an old and close relationship. Saw lots of great architecture and code, and a lot of terrible "salep". Oracle provides developers with the inexhaustible abyss of opportunities, and almost always is needed right now "chip".
Overall, Oracle is an amazing tool, and I do not get tired to wonder how all this wealth can work in principle and to work steadily.

About two years ago I moved from the Enterprise world free diving, where the Oracle machine with her $47k for core — out of reach.
One of the first freelance projects was a small billing for sub-satellite operator. The question arose of a choice of RDBMS. MySQL immediately fell away because of the immaturity of a procedural language, the choice fell on PostgreSQL.

As you work on this and following projects I have compiled a subjective list of pros and cons of PostgreSQL compared to Oracle from the developer's point of view the database. It represent to your attention:

PostgreSQL vs Oracle


PRO:

the
    the
  • Pseudo type serial — combines the best features of MySQL auto_increment and sequence of Oracle.
    • the
  • you Can write functions in pure SQL. For example, a function consisting of a single update c returning return the ID of the newly added values. Eliminates the need for explicit declarations of variables in which data is selected and which is then returned by the return statement.
    • the
  • with a Wonderful which you can use as the query to use not only select but also insert, update, delete returning, and that it is possible to do recursive (replaces Orlowski connect by). Moreover supersedes Orlowski multitabling insert all.
    • the
  • Generate_series instead of perversions of type select level from dual connect by level < n
    • the
  • Very powerful mechanism of control of integrity of data aka CONSTRAINTS for example EXCLUDE allows you to make a cunning check the OTHER rows when you insert a new (otherwise we would have to write trigger), REFERENCES (foreign key) c action when you delete or change the entries referenced in the table. For example, constraint constname references tablename on delete cascade will delete related records when deleting the parent.
    • the
  • Wonderful but potentially dangerous (like triggers) the system of rules (RULES) allowing to substitute the text of the query sent to the server. Through it, for example, implements VIEW.
    • the
  • section WHERE in definition of index can reduce the index size without resorting to the creation of functional indexes and unreadable conditions of the type where decode(status,1,1,null)=1
    • the
  • LIMIT OFFSET to avoid hemorrhoids rownum, sorting and subselects.

    • Nice documentation, devoid of dryness and monstrous (and meticulousness) Orlovskoe.


CONS:

the
    the
  • syntax Unpleasant anachronisms, like the need of escapethe body HF, like this:
    the 
     function test() returns void as
$$
begin
end;
$$ language plpgsql;

    Or need to write perform, to cause procedure by name:
    perform my_proc(); instead of just my_proc();
    • the
  • Miserable States for dividing through inheritance of tables and triggers (or rules).
    • the
  • there is No mechanism for jobs on the server side, all the processes have to be initiated outside of the database (for example, cron).
    • the
  • there are No packages for stored functions. You have to use for grouping the diagrams, but it's not quite the same.
    • the
  • MERGE.
    • the
  • No transaction control in stored functions. Can be control transactions outside of this more proper approach, but I lack the ability to do an explicit commit or rollback directly in HF.
    • the
  • Can compile without errors HF, explicitly referencing non-existent objects. For example sampling from a non-existent table. Error learn only when run this piece of HF, and not at compile time, as in Oracle.


Opinion


A good addition to this list would be compared from the standpoint of the DBA, but then, as they say, I'm not quite Copenhagen. It would be very interesting in the future to see such a comparison on habré.

Insights? PostgreSQL has room to grow, but now in the development of projects of very large scale it looks pretty decent next to what really hide — a benchmark of the market for RDBMS.
Article based on information from habrahabr.ru

Комментарии

Отправить комментарий

Популярные сообщения из этого блога

Monitoring PostgreSQL + php-fpm + nginx + disk using Zabbix

Изображение
a Lot of information in the network according to Zabbix, and a lot of bespoke templates, I want to introduce to the audience his modification. Zabbix is a very convenient and flexible monitoring tool. Want a hundred monitor, I want a thousand stations, and do not want to — watch a single server, take the cream in all sections. I would not mind to give to github, if anyone collects similar. It so happened that we decided to put on the hosting database wrapper php-fpm+nginx. As the database is postgres. Thoughts to collect data on the machine was before the purchase of hosting is needed, this is useful! Magic kick up the backside to the implementation of the system gave the brakes a hard drive on our VDS stations at the beginning of the script every minute, put the time and measured by the speed in the file, and then build graphs in Excel to compare to as it was/became, take quantitative statistics. And this is just one option! And suddenly the fault is not VDS, and our a
Далее...

Templates ESKD and GOST 7.32 for Lyx 1.6.x

Изображение
for more than six months I use a word processor Lyx. Built-in templates is more than enough to write lecture notes, prepare for seminars and to do simple reports for laboratory work. But last semester I had to make two course of the project in accordance with the requirements ESKD. I have begun work in Lyx, but without special templates of documents, my reports in the editor looked like this: A little work, a little reading of the document "Customizing LyX: Features for the Advanced User" and it turned out where the beam: You can use immediately. If you have experience of working in Lyx, problems will not arise. Just in case the attached sample documents. Description of how to make such templates are not yet doing. Someone will ask — will tell. ESKD Screenshot menu select unit type: Group "sections and subsections" appeared due to the fact that the document can be sections with sections without subsections — for their handling of para
Далее...

Customize your Google

Изображение
After writing this post, I was interested in the search parameters which you can use for convenient operation. Given this issue, in Runet I'm more or less full information is not found. But! It turns out Google for a long time and for all shown . Let's start simple — www.google.com/search?q=Запрос this is the simplest query, in which all options are disabled or parameters are used which are stored in your cookies. Next I will talk about the most interesting and used parameters. If you want to know more, you here . Even though it is called the search parameter, I will use the word token. So any of the marks are for the test, insert the end of the line (if your line is of the same kind as just above). Marker search by country &cr=countryRU Putting this token, we get the results from the specified country. List of acronyms used. Example Asus Finland UPDATE (thanks to Soutlan noticed the error) Token language results &lr
Далее...