Column DBMS - principle of operation, advantages and scope
The mid-2000s was marked by a rapid growth in the number of column-based DBMS. Vertica, ParAccel, Kognito, Infobright, SAND and others added to the club column DBMS and diluted the proud loneliness of Sybase IQ, which founded it in the 90s. In this article I will talk about the reasons for the popularity of the idea of column-based data storage, the principle of operation and the use of column DBMS.
To begin with, popular nowadays relational DBMS — Oracle, SQL Server, MySQL, DB2, Postgre, and others — are based on an architecture that dates back to the 1970s, when radio receivers were transistor, long sideburns, flared trousers, and in the world of DBMS hierarchical and networked data management systems prevailed. The main task of the databases at that time was to support the massive transition from paper-based business to computer-based accounting that began in the 1960s. A huge amount of information from paper documents was transferred to the database of accounting systems, which had to securely store all incoming information and, if necessary, quickly find it. Such requirements determined the architectural features of relational database management systems, which have remained virtually unchanged up to the present: line-by-line data storage, indexing records, and logging of operations.
Line-by-line data storage is usually understood as the physical storage of the entire row of a table as one record, in which the fields follow one after another, and after the last field of the record, in the general case, the first record follows. Something like this:
[A1, B1, C1], [A2, B2, C2], [A3, B3, C3] ...
')
where A, B and C are fields (columns), and 1,2 and 3 are the record number (rows).
Such storage is extremely convenient for frequent operations of adding new lines to the database, which is usually stored on a hard disk - in this case, a new record can be added entirely in just one pass of the drive head. The significant speed limits imposed by the NCWM also necessitated the maintenance of special indexes that would allow to find the desired record on the disk in the minimum number of passes of the HDD head. Usually, several indices are formed, depending on which fields you want to search for, which sometimes increases the amount of database on disk. For failure tolerance, traditional DBMSs automatically duplicate operations in the logs, which leads to an even larger space on the disks. As a result, for example, the average Oracle Oracle database takes up 5 times more disk space than the amount of useful data in it. For a mid-dataset database on DB2, this ratio is even greater - 7: 1.
However, in the 1990s, with the proliferation of analytical information systems and data warehouses used for management analysis of information accumulated in accounting systems, it became clear that the nature of the load in these two types of systems is radically different.
If transactional applications are characterized by very frequent small operations of adding or changing one or several records (insert / update), then in the case of analytical systems the picture is just the opposite - the greatest load is created by relatively rare but heavy samples (select) of hundreds of thousands and millions of records, often with groupings and the calculation of the total values (the so-called aggregates). The number of write operations at the same time is low, often less than 1% of the total. And often the record goes in large blocks (bulk load). Note that analytical samples have one important feature - as a rule, they contain only a few fields. On average, in a user’s analytical SQL query, they rarely exceed 7–8. This is explained by the fact that the human mind is not able to properly perceive information in more than 5-7 sections.
However, what happens if you select, for example, only 3 fields from a table in which there are only 50? Due to line-by-line data storage in traditional DBMS (required, as we remember, for frequent operations of adding new records in accounting systems) absolutely all lines with all fields will be read. This means that it does not matter whether we need only 3 fields or 50, from the disk, in any case, they will all be read completely , passed through the disk I / O controller and transferred to the processor, which will only select the ones needed for the request. Unfortunately, disk I / O channels are usually the main constraint on the performance of analytical systems. As a result, the effectiveness of a traditional DBMS may be reduced by 10–15 times due to the inevitable reading of unnecessary data. Moreover, the effect of Moore's law on the I / O speed of disk drives is much weaker than on the speed of processors and memory volumes. So, apparently, the situation will only get worse.
Column DBMS are intended to solve this problem. The main idea of column DBMS is data storage not by rows, as traditional DBMS do, but by columns. This means that from the point of view of the SQL client, the data is presented as usual in the form of tables, but physically these tables are a collection of columns, each of which is essentially a table from one field. At the same time, physically on the disk the values of one field are stored sequentially one after the other - approximately as
[A1, A2, A3], [B1, B2, B3], [C1, C2, C3], etc.
Such data organization leads to the fact that when executing select in which only 3 fields from 50 fields of the table appear, only 3 columns will be physically read from the disk. This means that the load on the I / O channel will be approximately 50/3 = 17 times less than when executing the same query in a traditional DBMS.
In addition, when storing data with collocation, there is a great opportunity to compress the data strongly, since data in a single column of a table is usually of the same type, which cannot be said about a row. Compression algorithms can be different. Let's give an example of one of them - the so-called Run-Length Encoding (RLE):
If we have a table with 100 million records made during one year, then in the “Date” column, in fact, no more than 366 possible values will be stored, since there will be no more than 366 days in a year (including leap years). Therefore, we can replace 100 million sorted values in this field by 366 pairs of values of the form <date, number of times> and store them on disk in this form. At the same time, they will occupy approximately 100 thousand times less space, which also contributes to an increase in the speed of execution of queries.
From the developer's point of view, columnar DBMSs generally comply with ACID and largely support the SQL-99 standard.
Summary
Column DBMS are designed to solve the problem of the inefficient operation of traditional DBMS in analytical systems and systems in the vast majority of “read” operations. They allow on cheaper and low-power equipment to get an increase in query execution speed of 5, 10 and sometimes even 100 times, while, due to compression, the data on the disk will be 5-10 times less than in the case of traditional DBMS.
Column DBMS also has drawbacks - they are slow to write, they are not suitable for transactional systems, and, as a rule, due to their “youth”, there are a number of limitations for a developer who is used to developed traditional DBMS.
Column DBMS are usually used in business intelligence class analytical systems (ROLAP) and analytical data warehouses. Moreover, data volumes can be quite large - there are examples of 300-500TB and even cases with> 1PB of data.
Links for further reading:
[1] Translation of the article by M. Stonebrucker "One Size Fits All": An Idea Whose Time Has Come and Gone "- citforum.ru/database/articles/one_size_fits_all
[2] A story about how Zynga uses Vertica for a real-time gaming platform. It can be found at this link - tdwi.org/blogs/wayne-eckerson/2010/02/zynga.aspx
[3] The only open source version of commercial column DBMS I know is InfoBright Community Edition www.infobright.org
On a tip from Oleg Tsibulnyak:
[4] LucidDB - Initially opensource column DBMS. Positioned as a MySQL replacement for analytical tasks www.luciddb.org
Ps. If there are more interesting materials about column DBMS - let's link, paste in the text.
To begin with, popular nowadays relational DBMS — Oracle, SQL Server, MySQL, DB2, Postgre, and others — are based on an architecture that dates back to the 1970s, when radio receivers were transistor, long sideburns, flared trousers, and in the world of DBMS hierarchical and networked data management systems prevailed. The main task of the databases at that time was to support the massive transition from paper-based business to computer-based accounting that began in the 1960s. A huge amount of information from paper documents was transferred to the database of accounting systems, which had to securely store all incoming information and, if necessary, quickly find it. Such requirements determined the architectural features of relational database management systems, which have remained virtually unchanged up to the present: line-by-line data storage, indexing records, and logging of operations.
Line-by-line data storage is usually understood as the physical storage of the entire row of a table as one record, in which the fields follow one after another, and after the last field of the record, in the general case, the first record follows. Something like this:
[A1, B1, C1], [A2, B2, C2], [A3, B3, C3] ...
')
where A, B and C are fields (columns), and 1,2 and 3 are the record number (rows).
Such storage is extremely convenient for frequent operations of adding new lines to the database, which is usually stored on a hard disk - in this case, a new record can be added entirely in just one pass of the drive head. The significant speed limits imposed by the NCWM also necessitated the maintenance of special indexes that would allow to find the desired record on the disk in the minimum number of passes of the HDD head. Usually, several indices are formed, depending on which fields you want to search for, which sometimes increases the amount of database on disk. For failure tolerance, traditional DBMSs automatically duplicate operations in the logs, which leads to an even larger space on the disks. As a result, for example, the average Oracle Oracle database takes up 5 times more disk space than the amount of useful data in it. For a mid-dataset database on DB2, this ratio is even greater - 7: 1.
However, in the 1990s, with the proliferation of analytical information systems and data warehouses used for management analysis of information accumulated in accounting systems, it became clear that the nature of the load in these two types of systems is radically different.
If transactional applications are characterized by very frequent small operations of adding or changing one or several records (insert / update), then in the case of analytical systems the picture is just the opposite - the greatest load is created by relatively rare but heavy samples (select) of hundreds of thousands and millions of records, often with groupings and the calculation of the total values (the so-called aggregates). The number of write operations at the same time is low, often less than 1% of the total. And often the record goes in large blocks (bulk load). Note that analytical samples have one important feature - as a rule, they contain only a few fields. On average, in a user’s analytical SQL query, they rarely exceed 7–8. This is explained by the fact that the human mind is not able to properly perceive information in more than 5-7 sections.
However, what happens if you select, for example, only 3 fields from a table in which there are only 50? Due to line-by-line data storage in traditional DBMS (required, as we remember, for frequent operations of adding new records in accounting systems) absolutely all lines with all fields will be read. This means that it does not matter whether we need only 3 fields or 50, from the disk, in any case, they will all be read completely , passed through the disk I / O controller and transferred to the processor, which will only select the ones needed for the request. Unfortunately, disk I / O channels are usually the main constraint on the performance of analytical systems. As a result, the effectiveness of a traditional DBMS may be reduced by 10–15 times due to the inevitable reading of unnecessary data. Moreover, the effect of Moore's law on the I / O speed of disk drives is much weaker than on the speed of processors and memory volumes. So, apparently, the situation will only get worse.
Column DBMS are intended to solve this problem. The main idea of column DBMS is data storage not by rows, as traditional DBMS do, but by columns. This means that from the point of view of the SQL client, the data is presented as usual in the form of tables, but physically these tables are a collection of columns, each of which is essentially a table from one field. At the same time, physically on the disk the values of one field are stored sequentially one after the other - approximately as
[A1, A2, A3], [B1, B2, B3], [C1, C2, C3], etc.
Such data organization leads to the fact that when executing select in which only 3 fields from 50 fields of the table appear, only 3 columns will be physically read from the disk. This means that the load on the I / O channel will be approximately 50/3 = 17 times less than when executing the same query in a traditional DBMS.
In addition, when storing data with collocation, there is a great opportunity to compress the data strongly, since data in a single column of a table is usually of the same type, which cannot be said about a row. Compression algorithms can be different. Let's give an example of one of them - the so-called Run-Length Encoding (RLE):
If we have a table with 100 million records made during one year, then in the “Date” column, in fact, no more than 366 possible values will be stored, since there will be no more than 366 days in a year (including leap years). Therefore, we can replace 100 million sorted values in this field by 366 pairs of values of the form <date, number of times> and store them on disk in this form. At the same time, they will occupy approximately 100 thousand times less space, which also contributes to an increase in the speed of execution of queries.
From the developer's point of view, columnar DBMSs generally comply with ACID and largely support the SQL-99 standard.
Summary
Column DBMS are designed to solve the problem of the inefficient operation of traditional DBMS in analytical systems and systems in the vast majority of “read” operations. They allow on cheaper and low-power equipment to get an increase in query execution speed of 5, 10 and sometimes even 100 times, while, due to compression, the data on the disk will be 5-10 times less than in the case of traditional DBMS.
Column DBMS also has drawbacks - they are slow to write, they are not suitable for transactional systems, and, as a rule, due to their “youth”, there are a number of limitations for a developer who is used to developed traditional DBMS.
Column DBMS are usually used in business intelligence class analytical systems (ROLAP) and analytical data warehouses. Moreover, data volumes can be quite large - there are examples of 300-500TB and even cases with> 1PB of data.
Links for further reading:
[1] Translation of the article by M. Stonebrucker "One Size Fits All": An Idea Whose Time Has Come and Gone "- citforum.ru/database/articles/one_size_fits_all
[2] A story about how Zynga uses Vertica for a real-time gaming platform. It can be found at this link - tdwi.org/blogs/wayne-eckerson/2010/02/zynga.aspx
[3] The only open source version of commercial column DBMS I know is InfoBright Community Edition www.infobright.org
On a tip from Oleg Tsibulnyak:
[4] LucidDB - Initially opensource column DBMS. Positioned as a MySQL replacement for analytical tasks www.luciddb.org
Ps. If there are more interesting materials about column DBMS - let's link, paste in the text.
Source: https://habr.com/ru/post/95181/
All Articles