Why relational DBMSs are great for startups: An example from the history of the development of Kato messenger

Historically, the development of computers went along with the progress in database management - this was due to the fact that among the tasks solved by researchers and practitioners, the huge role was played (and continue to play at the moment) of data processing, compact storage and fast retrieval.

Accordingly, technological progress was not only in the areas of increasing the power of processors, memory, or reducing the size of devices, but also in improving the efficiency of working with data. As a result, a large number of different database management systems (DBMS) appeared.
')
In our product, Kato messenger for corporate communications, PostgreSQL is used. Today we would like to remind the history of this wonderful tool and show the advantages of its use for start-ups in the field of information technology.

Relational model and SQL

Traditionally, there are three main directions of DBMS on various data models on which these directions are based - network, hierarchical, relational.

The relational data model, now the most popular and advanced of the three named, was originally developed in the late 60s of the last century by a British scientist, an employee of IBM, Edgar Codd . In 1970, he published his first work on the relational data model, “ A Relational Model for Large Shared Data Banks ”.



Creator of the relational data model Edgar Codd

Codd proposed a set of 8 basic operations that can be performed on data, and this set marked the beginning of relational algebra . On the basis of algebra created by Codd in the mid-70s of the last century, a programming language for working with data in relational databases called SQL, which was standardized in 1986, began to develop (among many others).

Research at Berkeley: The Postgres Appearance

One of the first relational database management systems was the open system Ingres - it was created by researchers from Berkeley who were interested in IBM publications about their Project R relational database project and tried to develop their own system. Ingres used a non-SQL query language — it was called QUEL .

Subsequently, Michael Stonebreyker, previously engaged in the creation of Ingres, together with his students in Berkeley launched a new project - Post In gres (Postgres). The new system was developed from 1986 to 1995 and used the follower QUEL - the POSTQUEL query language.



Michael Stonebreaker

Later, Stonebriker founded several companies that developed DBMSs (examples: Illustra , bought by Informix ; StreamBase Systems ; Vertica , bought by HP; VoltDB ).

His students who participated in the work on Postgres created their own version of the database, in which POSTQUEL was replaced by SQL. The project was originally called Postgres95, and got its current name - PostgreSQL - after the transfer of this development by the University of California Berkeley in the hands of a team of enthusiasts.

Database Problems: The Birth of NoSQL

In the late nineties and the beginning of the 2000s, a situation developed in the database market in which there were a considerable number of popular databases, but each of them had serious drawbacks. In the case of commercial Oracle, IBM DB2 and Microsoft SQL Server, this disadvantage was very substantial prices, and the most popular free MySQL project had limited functionality (for example, stored procedures, triggers and views appeared in this DBMS only in 2005).

At the same time, PostgreSQL, despite the fact that its developers did a tremendous amount and in general very high-quality work, could not boast of high speed and ease of administration, which limited its use in commercial projects.

The problems of existing products that use SQL and the relational model in general, led enthusiasts to create databases that work with the use of other standards - so many projects were born that can be combined into a general category of NoSQL .



A number of NoSQL databases emerged (some well-known examples: MongoDB, Redis, Riak). The development of this direction followed the path of fragmentation and the creation of highly specialized products.

DBMS and startups

The emergence of a large number of new NoSQL-development at some point changed the attitude in start-ups to traditional SQL-systems - they began to be perceived by the creators of IT projects as too complicated, old-fashioned and difficult to work in modern dynamic applications.

Gradually, however, it turned out that the NoSQL DBMS has the following critical (and very unpleasant) feature - they are good for solving only very narrowly defined tasks. This property automatically made the use of conditional MongoDB in a startup a very risky step - at the initial stage, a conditional MongoDB may be an ideal choice for a set of tasks, but at the moment when a startup changes its strategy somewhat (and this happens almost always), some other DBMS may turn out to be more suitable for solving problems in the new formulation. Most likely, the "move" to this other DBMS will be too complicated and expensive operation, which start-up business will not be able to perform.

On the other hand, during the rapid development of the DBMS from the NoSQL category, the developers of traditional relational DBMSs also did not sit idly. In particular, the creators of PostgreSQL have worked on the performance, ease of administration and documentation of their project, as a result of which, at the end of the two thousand years, it became the exact, fast and modern weapon necessary for older bearded, pot-bellied and bald uncles. in the arsenal of any "technology from the hipster."

PostgreSQL and Kato Messenger

Developers from the Kato team were involved in various technology companies and start-ups (for example, in the Rdio project) and experienced the advantages and disadvantages of working with many existing DBMS (and simultaneously almost all possible rakes associated with building a system from scratch). As a result, starting work on our project, we chose PostgreSQL.

For commercial projects, it is very important to have good opportunities for scaling one aspect or another. Each project has its own aspects - for example, in Kato we need to scale the base of the message history in the rooms.



The immutability of the data scheme is one of the popular advantages of NoSQL. The hstore module (by the way, made by Muscovites) from PostgreSQL allows you to write keys and values ​​in the table columns, which saves developers from having to constantly change the data scheme while adding new product functionality. At the same time, it is possible to create indexes.

In PostgreSQL 9.2, a new type has appeared - JSON . Unlike hstore, the JSON type supports nested structures, which makes PostgreSQL a convenient tool for working with documents. It is also important that you can create GIN indexes for the jsonb type, which allows you to quickly search for JSON objects.

The introduction of hstore and JSON type made it possible to create databases in NoSQL style inside PostgreSQL tables, which allows using the advantages of NoSQL and SQL at the same time.

Here are some typical operations to illustrate the capabilities of the hstore module.

Create a hstore extension and a table with a hstore column:

postgres=# create extension hstore; WARNING: => is deprecated as an operator name DETAIL: This name may be disallowed altogether in future versions of QL. CREATE EXTENSION postgres=# create table hstore_test (data hstore); CREATE TABLE 

Add the entry hstore, where the two keys are 'a' with the value 'hello' and 'b' with the value 'world':

 postgres=# insert into hstore_test values (hstore(array['a', 'hello', 'b', 'world'])); INSERT 0 1 postgres=# 

We look at the value of the key 'a':

 postgres=# SELECT data->'a' FROM hstore_test; ?column? ---------- hello (1 row) postgres=# ▄ 

Find out if the keys 'a' and 'c' exist:

 postgres=# select data ? 'a', data ? 'c' from hstore_test; ?column? | ?column? ----------+-------------- t | f (1 row) 

Change the value of the key 'b':

 postgres=# update hstore_test set data = data || ('b' => 'world!'); UPDATE 1 postgres=# select data->'b' from hstore_test; ?column? -------------- world! (1 row) 

All operations with the hstore type are described in the relevant section of the PostgreSQL project documentation .

In Kato messenger, the hstore tables are used to store settings and attributes of various objects: accounts, rooms, teams, and organizations.

Rings of history

The story goes in circles, and very often the phrase “everything is new - this is a well forgotten old” is true - many current trends in the field of building databases and working with data have been comprehended and anticipated by the creators of the relational model and the SQL developers.

PostgreSQL is a canonical example of a project that constantly incorporates the results of research from the world's leading experts. As a result, this DBMS acts as a kind of universal designer, and start-ups, using its details, can very quickly create working commercial products without fear of being stumped due to an unexpected expansion of the nomenclature of problems to be solved.