From 6 to 7 February, the PGCONF.RUSSIA 2015 conference , which I am one of the organizers, will be held in the Digital October Business Center in Moscow. PostgreSQL is one of the most promising modern freely distributed DBMS, actively developing and in many cases no longer inferior to the flagship commercial Oracle DBMS, but superior in some ways to it. At the same time, Postgres is distributed under a very free license, close to BSD and MIT licenses, which allows you to do anything with it - even sell on its own behalf. Therefore, there are no obstacles in the creation of commercial DBMS and application systems on the basis of the postgres, and many use this. This, in turn, provides an opportunity to participate in the development of more people, and more actively nurture new ideas. On the pages of this post we will talk about how this DBMS arose and developed, what its strengths and weaknesses are, including from the point of view of wide distribution.

What is Postgres

In the world there are so many database management systems. The most famous, of course, is Oracle, today it is the generally accepted DBMS No. 1 in the world. Of the freely distributed databases, the most well-known and popular is MySQL. However, in recent years, PostgreSQL, a freely distributed object-relational database management system (ORDBMS), is the most developed interest in the world, the most developed of the open DBMS. The growth of migration requests from proprietary systems to PostgreSQL proves that it is a real alternative to commercial DBMS.



What is the reason for the growing popularity of Postgres? First of all, it is very carefully thought out, flexible and due to this it has a number of advantages.

')

Postgres was created in 1986 by Michael Stonebriker, a professor at the University of California at Berkeley, as the next step after Ingres DBMS. The foundations of its devices and mechanisms for ensuring extensibility were laid already then. Interestingly, at that time, the use of the SQL language was not yet considered mandatory for relational databases. And when Postgres, already learning the SQL language, became a free product in 1996, it became known as the intricate word PostgreSQL ("postgrell"), but for the convenience of pronunciation, the old name "postgres" is also preserved and used.



In recent years, PostgreSQL has experienced a booming bloom associated with its penetration into industrial solution markets. Therefore, it deliberately adds the capabilities necessary for its use in serious projects - replication, means for storing unstructured data, row level security. Performance with each new version increases.



Abroad to PostgreSQL show great interest. Many companies provide commercial support and customization services - in particular, the American company EnterpriseDB, the British The Second Quandrant and the French Dalibo are involved in this. By the way, the director of the latter, Jean-Paul Argudo, in his report on PGCONF.RUSSIA 2015, will share the experience of providing commercial services in relation to the free distributed product and the support of French government agencies in migration from Oracle to Postgres. Also at PGCONF.RUSSIA 2015, quite well-known PostgreSQL developers from Japan and China will speak on the reasons for its high popularity in these countries.

Major PostgreSQL TTH

• Reliability and stability. The reliability of PostgreSQL is a well-known fact, proved by the example of many projects in which PostgreSQL has been working without failures under high loads for several years.
• Excellent support. The PostgreSQL community provides expert and quick help. Commercial companies offer their services worldwide.
• Competitive work with a large load. PostgreSQL uses multi-versioning ( MVCC ) to ensure reliable and fast performance in competitive environments under high loads.
• Scalable. PostgreSQL perfectly uses the modern architecture of multi-core processors - its performance grows linearly up to 64 cores. Clustered solutions based on Postgres-XC, Postgres-XL help with horizontal scalability.
• Cross platform PostgreSQL works under all types of UNIX-like systems, including Linux, FreeBSD, Solaris, HP / UX, Mac OS X, and also under MS Windows.
• Extensibility PostgreSQL source codes are available, making it possible to add new functionality for your project without additional problems. Extensibility PostgreSQL allows you to create new data types and access methods.
• Availability. PostgreSQL is distributed under a license close to BSD, which does not impose any restrictions on commercial use and does not require royalties.

PostgreSQL technical details

• High level of compliance with ANSI SQL 92, ANSI SQL 99 and ANSI SQL 2003, 2011.
• Interfaces for Tcl, Perl, C, C ++, PHP, JSON, ODBC, Embedded SQL in C, Python, Ruby, Java and others.
• Integration of data protection with the operating system (SE-Linux).
• Representations, sequences, inheritance, outer joins, subqueries, referential integrity, window functions, CTE (recursive queries).
• User functions, stored procedures, triggers.
• Procedural languages ​​PL / PgSQL, PL / Perl, PL / Python, PL / Java and others.
• An extensible set of data types with support for indexes (GiST, GIN, SP-GiST).
• Built-in full-text search with support for all European languages.
• Native support for semi-structured data (xml, json, jsonb) with index support.
• Hot backup and replication (synchronous, asynchronous, cascade), PITR, bidirectional (BDR).
• Full ACID support, isolation levels, efficient transaction serialization.
• Functional and partial indexes.
• Internationalization, Unicode and locale support.
• Downloadable extensions, for example, PostGIS, hstore.
• SSL and Kerberos authentication support.
• Foreign Data Wrappers (writable), support for all major databases.

Postgres in Russia

In the wake of today's trend of import substitution, Postgres began to attract the attention of many government and local government agencies that use Oracle DBMS and other proprietary systems today. Specific migration steps are being taken by a number of ministries and state corporations. By the way, Europe has the same picture as ours: they, too, want, oddly enough, to “get off” from proprietary solutions. And they want a very long time. But many freely distributed solutions are still only approaching such a level of quality that they can be passed onto. PostgreSQL is one of the most serious systems.



Russian developers are making a serious contribution to the development of Postgres. This is probably one of the reasons why Postgres has been so seriously considered in various ministries lately. Not spared his attention and power structures, including the military. After all, since this is an open source system, it is possible to check its code for the presence of various spyware and sabotage bookmarks.



Thanks to the index support for working with spatial data, PostgreSQL is very widely used in geographic information systems. In particular, such famous projects as OpenStreetMap and Russian 2GIS were made on it. By the way, Postgres is at the heart of the Avito ad space.



Unfortunately, in many cases, the openness of the system and the lack of an explicit developer company hinders active distribution. After all, despite its free, like any other complex system, Postgres has a non-zero cost of ownership, requiring certain development and maintenance costs. Looking for qualified and competent professionals. And, if in the case of the same Oracle or Microsoft there is a formal certification system, then Postgres does not have such a system. Therefore, organizations and government agencies that are accustomed to or are forced to work strictly formally do not understand who they should hire. Well, they will put Postgres. And where will they get certified engineers? In Russia, they are not formally. Therefore, now the idea is in the air that the Russian developer community needs to acquire or create some kind of formal structure that will deal with certification, training and promotion of Postgres in Russia. Of course, there are private companies involved in training with Postgres. But in order for the state to recognize the certificates they issue, support is required at a high enough level. We need strong support from potential customers, we need political will, PR. Perhaps in the future it will be implemented.

Who can recommend Postgres

For any new business, Postgres is attractive in comparison with proprietary DBMS by the lack of initial investment - you do not need to buy a license. However, in these cases, the more popular MySQL is often chosen. The advantages of Postgres become more noticeable and even decisive when a database of a more or less complex structure with internal logic, replication, interaction with other databases is required, that is, when it comes to a business information system, not a few pages. major online portal, e-commerce system.



Postgres can be recommended for those projects where high reliability of work is important, the ability to update the database itself, the stored procedures without stopping the system. In new versions of Postgres, it will be possible (so far with the help of the smart DBA) even to update the version of the postgres itself without stopping the service.



Compared to other freely distributed relational DBMS, Postgres benefits technologically. It was originally laid very competent technical solutions, ahead of time. Postgres has the highest degree of flexibility and extensibility: you can create your own data types, your own index types, write built-in functions in a huge number of languages, which is no longer anywhere. For example, the dominant position of Postgres in GIS is a direct consequence of its flexibility and extensibility.

Why we organize PGCONF.RUSSIA 2015

We hope that our conference will allow the community to organize, find some forms of interaction, create new structures. We also hope to demonstrate to the major domestic customers and the state the maturity of PostgreSQL itself and the community, and to the community members to demonstrate the breadth of the tasks open to them.



We want to contribute to the formation of the community and the market, to bring together different stakeholders. Those who develop Postgres himself, who develop on it, who use it. After all, if the market for services related to Postgres develops, then Postgres itself will develop.



In general, such conferences are one of the forms of self-organization of the global community of developers and users. There are a series of international conferences that take place in Canada; in the USA are held the American conferences; in various European cities - annual pan-European. China and Japan also hold their own national Postgres conferences. European conferences usually gather between 200 and 300 people. The first major Russian conference was organized by enthusiasts last summer in St. Petersburg, attended by about 250 people. We expect about the same at PGCONF.RUSSIA 2015.



In Russia, unlike in many other countries, there are members of the international core development team for PostgreSQL, that is, the key, fundamental developers of Postgres. One of them, Fedor Sigaev, works with us at Mail.Ru Group. At one time, he created important tools for Postgres, which allowed working with arrays and unstructured data . Together with Oleg Bartunov, another Russian kernel developer, he created a full-text search module.



Fedor will also speak at our conference (more than 40 speakers were announced in three parallel sections). As an extended announcement, we want to talk a little bit about the content of his report.

PostgreSQL extensibility - challenges and prospects

Historically, Postgres was created as a database that does not have a hard-wired set of rules, hard-wired types. It allows you to add your own types, create your own functions, etc. Of course, not everything could be done right away, many of the ideas that were laid down are still evolving. Say it was only in the mid-2000s that Postgres finally got rid of the “knowledge” of the semantic meaning of operation signs. Then this knowledge was generalized, and now it is possible to explain to the Postgres kernel exactly how to use the index to speed up the search by expressions with any operation signs. This made it possible with the help of special types of indices to speed up the search for the occurrence of an element in a set (array), on the intersection of sets, and then - full-text search.



At one time, full-text search was born as a module consisting of pluggable types and pluggable indexes. There are quite a few such extensions. We can mention LTree - this is a module and a type that allows you to store trees in a database and work with them quite effectively.



So Postgres allows you to create your own types and functions. In the report, Fyodor will talk about how this is best done. The simplest way is to create a new type: to do this, it’s enough to write two functions that turn a simple text string into an internal representation and vice versa, and start these functions with a special SQL command. With this, Postgres can already live and replicate. But in order to get some sense from the created data type, you need to define some functions and operations over them. If you need index support for searching for some operation, you also need to write a few functions.



There are operations on data types that are not too trivial, such as the proximity (similarity) of two lines. For example, the search task is for the phrase “black hole” to find a “black hole” in the database. In fact, it is not so easy to eliminate the alternation of terms without losing meaning, and at the same time finding the necessary lines (and there are still typos!). No new types are introduced here; instead, two strings are compared, given the measure of similarity of Levenshtein. Those who have tried to work with it know that the search for it works only by complete brute force. Therefore, at one time the pg_trgm module was created, which represents a string in the form of a set of trigrams, to index these sets and search quite effectively.



Also, the report will talk about the possibility of using this module for index search over some subsets of regular expressions - how you can do something completely unusual with strings using the existing Postgres capabilities. Foreign data wrappers (fdw), wrappers over data sources that are not Postgres itself will be mentioned separately. Now this functionality has evolved so much that even transactions are supported.



Postgres Achilles' heel is, strangely enough, the connection of new types of indices. This operation is rather rare, but the flexibility embodied in it has not yet been fully implemented. In general, Postgres is already rich in indices: it has BTree, Rtree, inverse index (GIN), generalized index (GIST), spatial generalized index (SP-GIST). However, sometimes there are tasks that do not fall into the traditional database, for example, Bloom filters. At first glance, it is enough to create a new type of index and insert it into several system labels in the system catalog.



The problem is that such pluggable indexes do not have access to WAL (write ahead log), which is the basis for disaster recovery and replication. As a result, this index is not replicated at all, because replication goes through WAL. In order for the new index to be written in WAL, it is necessary to patch the core of the system, some extensions are not enough here. But we expect that in version 9.6 it will be possible to connect indexes using WAL.



Also in his report Fedor touches the topic of exclusive constraints. Everyone knows about the unique constraints used when a field must be unique in a table. But, for example, imagine a schedule in which for each lecture the audience, time and duration are indicated. You can rent an audience for three hours, or for 15 minutes. It is required that lectures do not overlap in space-time. For this, unique constraints are naturally not suitable. This will help exclusive constraints, so you can ensure that these two time intervals in the same audience do not overlap.

Future plans

In one of the upcoming articles, Oleg Tsaryov, another speaker of PGCONF.RUSSIA 2015 from Mail.Ru Group, will talk about some fundamental differences between MySQL and Postgres and give some tips on how to migrate from one to another. This material will be useful to all those who are not satisfied with the features and features of MySQL, as well as those who take the first steps in Postgres. If you have questions to Oleg or to me - ask them in the comments.