Greet your old new friend

Today, a variety of open source DBMSs face off against massive, clumsy, and expensive “corporate” systems, such as SQL Server and Oracle. Often, open database systems work better than closed systems, not yielding even in functionality.



Of all the open database management systems, the most intelligent, productive and functional system is Postgres, which deservedly attracts more and more attention.



The name of the product alone is of genuine interest to the reader: “Hey, why the hell is it called that ?!” This question was answered well by one of the developers, Tom Lane, on the pgsql-novice mailing list:

The correct name is PostgreSQL or Postgres, but not “postgre”. Probably, the decision made in 1996 to name the PostgreSQL system instead of the simple Postgres is the only monstrous mistake in the entire history of the project. And I think that now it is too late to change something.

The Postgres project was an attempt to recycle the very old Ingres DBMS in order to create a modular, scalable, productive database management system, convenient for the end user of those times. Thus it was assumed that the name “POSTgres” would be a reference to the parent project - “After Ingres”. It happened.

')

Who cares?

In fact, it doesn’t worry too many people. The rapid growth of the popularity of the PHP language was accompanied by a rather intensive development of a more “friendly” MySQL platform. Yes, the MySQL project was developing much faster than the more rigorous Postgres platform. Unfortunately, instead of friendliness we have to talk about nonsense.



This is a strong statement, but let's try to justify it. For example, here is this video that I recently uploaded on Tekpub called “ The Dangers of MySQL ”. I will give a brief summary of the video if you do not have the opportunity or desire to watch it:

• MySQL, being not a strict DBMS, defiantly ignores your settings and integrity constraints (it just wants to help);
• it will easily insert an empty string "" in the column with a ban on adding empty values;
• she will happily insert useless dates (such as 0000-00-00 ) into columns with dates when forbidding the addition of empty values;
• it can divide by zero and return NULL when calculating 1/0 ;
• it returns NULL when dividing a string by zero;
• if you try to insert a value of 1000 into a column with a length of two, it will just round this value to 99 little .

All this should give you a reason to think about using MySQL: this system does not check your data, but it tries to seem like a “convenient” price of data integrity. I think this is pretty bad.



If none of the above horrified you, then I have a single word about you: Oracle.

Fast, scalable, fun

The functionality of Postgres is quite extensive, and many people do not even know about the capabilities of this database. It is worth noting that the system has such syntactic sugar as:

• the key word “infinity”, meaning “more than any entered value” - works for numbers and dates;
• gentle expressions for working with dates: “today”, “tomorrow”, “yesterday” (and everything will be even better in Postgres 9.2);
• Remarkable data types: arrays, IP addresses (with IPv6 support), and spatial data types (lines, squares, circles);
• table inheritance is a tricky way to inherit one table from another;
• full-text search in unstructured text in natural language - out of the box.

All these possibilities are meaningless if the system is not able to show scalability and high performance. Fortunately, Postgres has a complete order with these things.



I have a friend. His name is Rob Sullivan and he is DBA. Together with him, we conducted a small experiment: we loaded into Postgres a StackOverflow data archive containing six million text entries. After that, we uncovered our tools (for database queries) and began to optimize our system, comparing its behavior with the behavior of SQL Server. Postgres not only showed comparable performance, it in many cases significantly exceeded the Microsoft solution!



Individual words should devote to indexing. Thanks to table inheritance, we can easily partition tables and squeeze better performance out of the DBMS due to the reduced index size. Of course, this can be implemented by means of SQL Server, but this problem is not solved so clearly, and also requires the purchase of an expensive license.



And that is not all! Right out of the box, Postgres can compress tables on the fly: this approach is called “TOAST”, derived from the phrase “Automatic Table Compression”. Compressed data reduces both RAM and disk space.



Thanks to TOAST, we were able to reduce the space occupied by data from StackOverflow from twenty-four gigabytes to six gigabytes, and this is a pretty big savings. This feature is free and included in the standard Postgres distribution. To use this feature in SQL Server, you must purchase an Enterprise license.

Five things you do not know

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

• executing Twitter requests using foreign data wrappers;
• writing functions on JavaScript using Google’s V8 engine;
• killer tricks with MySQL;
• prevention of blocking using the snapshot mechanism built into Postgres;
• table inheritance;
• plague data types.

I hope you will like it.