Say Hello to your old new friend

Today, a variety of open DBMS stand face to face against a massive, clumsy and expensive enterprise systems like SQL Server and Oracle. open DBMS Often well work better than closed systems, not yielding even in functionality.

Of all the open database management systems, the most intelligent, efficient and functional system is Postgres, which has rightly attracted more and more attention.

The mere name of the product is a genuine interest in the reader: "Hey, what the hell is it so called?!" This question is well answered one of the developers — Tom lane, on the mailing list pgsql-novice:

the Correct name is PostgreSQL or Postgres, but not “postgre”. Probably, adopted in 1996, the decision to call the system instead of a simple Postgres PostgreSQL is the only monstrous mistake in the history of the project. And I think now it's too late to change something.

The Postgres project was an attempt of processing very old DBMS Ingres with the goal of creating modular, mashtabiruemuju, productive database management system that is convenient for the end user those times. Thus, it was assumed that the name “POSTgres” will be a reference to the parent project — “After Ingres”. So it turned out.

Who cares?

In fact, it is concerned not so much people. The rapid growth of popularity of language of PHP was accompanied by a rather rapid development of a more "friendly" platform MySQL. Yes, the MySQL project has developed much faster than the more rigorous platform for Postgres. Unfortunately, instead of friendliness we have to talk about nonsense.

This is a strong statement, but let's try to substantiate it. For example, here is this video that I recently posted this on Tekpub called "Danger MySQL". Result watch this video, if you do not have the ability or desire to watch it:

the

the
• MySQL, being not strict enough DBMS, cheerfully ignores your settings and constraints (she just wants to help);
the
• it is easy to insert an empty string "" column with the prohibition of adding of empty values;
the
• it will happily insert useless date (like 0000-00-00) columns with the dates when the prohibition of adding of empty values;
the
• she knows how to divide by zero and return NULL when calculating 1/0;
the
• it returns NULL by dividing line to zero;
the
• if you try to insert the value 1000 column with a length equal to two, then it just slightly will round the value to 99.

All this should give you a reason to think about using MySQL: this system does not verify your data, but it tries to seem "convenient" price of data integrity. I think it's pretty bad.

If none of the above has not put you to death, I have only one word about you: Oracle.

Fast, scalable, and fun

The functionality of Postgres is quite extensive, and many people are not even aware of the possibilities of this DBMS. It is worth noting that the system has such syntactic sugar as:

the

the
• keyword "infinity", meaning "greater than any value entered" — it works for numbers and dates;
the
• delicate expression to date: “today”, “tomorrow”, “yesterday” (and in Postgres 9.2 would be even better);
the
• wonderful data types: arrays, IP addresses (with IPv6 support), as well as spatial data types (lines, squares, circles);
the
• inheritance of tables is a clever possibility to inherit one table from another;
the
• full text search on unstructured text in natural language from the box.

All these vozmozhnosti meaningless if the system is not able to show scalability and high proizvoditelnosti. Fortunately, these things have Postgres full order.
I have a friend. His name is Rob Sullivan and he is DBA. Together we conducted a little experiment: I downloaded the Postgres data file StackOverflow that contains six million text entries. After that, we uncovered their tools (for database queries), and began to optimize our system by comparing its behavior with the behavior of SQL Server. Postgres is not just shown comparable performance, it in many cases greatly exceeded the decision from Microsoft!

Some words should be spent on indexing. Due to inheritance tables we can easily partitionierung table and make the most of the best DBMS performance by reducing the volume index. Of course, this can be implemented by SQL Server, but this task is not so obvious, and requires purchasing an expensive license.

And that's not all! Right out of the box Postgres knows how to compress the table on the fly: this approach is called “TOAST” derived from the phrase “Automatic Table Compression”. Thanks to the data compression reducing the consumption of RAM and disk space.

Thanks to TOAST, we managed to reduce the space occupied by the data from StackOverflow with twenty-four gigabytes to six gigabytes, and it's pretty major savings. This feature is free and included in the standard distribution of Postgres. To use this feature in SQL Server, you must obtain an Enterprise license.

Five things you don't know

About the capabilities of Postgres, you can write a lot, a lot. Instead, I'll just give you the link to my presentation from NDC 2012 conference: "Five things you don't know about PostgreSQL". In the report, I considered such details as:

the

the
• run queries to Twitter by means of foreign data wrappers;
the
• writing functions in JavaScript using the V8 engine from Google;
the
• killer tricks with MySQL;
the
• blocking prevention with built-in Postgres mechanism pictures;
the
• inheritance tables;
the
• fancy data types.

Hope you like it.

Article based on information from habrahabr.ru