Replacing Oracle with PostgreSQL and the ability to work with partitioning inside a DLP system
Today we would like to touch on a very important topic for DLP solutions - the choice of a DBMS for data storage. So historically, most Russian DLPs use the Oracle Database for this purpose. This imposes certain financial restrictions on customers: the cost of Oracle licenses is included in the cost of a DLP system. This creates a certain filter that reduces the audience of product users: Oracle DBMS can not afford everything - both technically and financially.
Now, when import substitution is sweeping the country, the public sector (and not only) forms the demand for DLP, supporting free DBMS. This is a very tangible impulse, but, throwing in the direction of free DBMS, it is important to maintain the convenience, performance and functionality of the product. This article will discuss how we solved this problem by implementing PostgreSQL support and developing a partitioning scheme in Solar Dozor.
The Solar Dozor system uses a relational DBMS as the main storage system. Until recently, the selection criteria for the DBMS were quite simple: the volume and the ability to divide the archive into operational and “on demand”.
')
Regarding the size of the database, there were certain limitations derived empirically: it should not exceed 3-4 TB (this is about 5 million intercepted messages). Above these values ​​there was a significant degradation of the system: the load on the IO due to the need to regularly run queries for deleting old data, vacuming, reducing the speed of query execution (in large samples, in fact, a full table scan was performed). Therefore, if the estimate of the required volume was beyond these figures, the Oracle DBMS was always used for data storage.
The second criterion - the ability to divide the archive into operational and “on demand” - can be read as the need to implement partitioning into a DBMS. Up until 2016, the Solar Dozor DLP system supported partitioning exclusively on Oracle DBMS.
We understood that the budgets of customers are limited, and sometimes they simply cannot afford to purchase Oracle licenses. Therefore, in some projects with a storage capacity of 10-15 TB, we began to use PostgreSQL.
The architecture and capabilities of the Solar Dozor system allow you to connect to it any number of databases, both Oracle and PostgreSQL. In addition, for each database, you can specify whether it is possible to search for it or archive messages into it. Therefore, we first decided to create several PostgreSQL DBMS instances on the same or different servers and write data into them one by one in turn. After the expiration of the storage period, the oldest database was re-created - and so on in a circle. But this solution was quite expensive in terms of support and suited only to a very narrow circle of customers.
The next implementation of data partitioning was also built on the idea of ​​“one section - one database”, but now all databases were created within the same PostgreSQL instance. For all operations of working with sections, the corresponding utilities were written, and this scheme worked quite well for several customers. Relying on it, some even migrated from Oracle.
However, this implementation still had a number of problems related to the features of the work of a number of services in Solar Dozor. With a large number of sections, it was necessary to hold simultaneously more than 1000 connections per instance. In addition, it was not very convenient to update the data schema at the release of new releases.
In the end, we decided to use partitioning, which PostgreSQL itself offers us. That is, for all tables in which there is a key that refers to the date the message was laid, inherited labels are created with a restriction on this key. Storing data in the desired set of tables is done by a trigger, which is created separately for each section. Such a scheme gives us all the necessary functionality: when querying, only those data sections are raised from the disk for the duration of the request; if it is necessary to take a section out of circulation, the tables are derived from the baseline inheritance, after which they are dumped. Utilities for management are written for all such sections: transfer to offline / online, add, delete, dump, etc.
This implementation has become a real replacement for Oracle, allowing you to keep dozens of terabytes of data in real-time access and dozens more in the archive on slower storage systems. We offer all new customers to do partitioning on PostgreSQL. If an existing customer with partitioning on Oracle wants to switch to PostgreSQL, we suggest that he write new data to PostgreSQL, and leave the old data to Oracle until they become obsolete.
We found it impractical to write special procedures for migrating data from Oracle to PostgreSQL, because the development itself and the migration work itself is very expensive, especially if the customer has disabled data from any old versions of the product. And there are very few such requests.
All intercepted messages are saved to the DLP system. For DLP tasks, it is important to know when the information was transmitted and intercepted. Thus, all intercepted messages are clearly linked to the time line. Therefore, we use RANGE partitioning by the interception date, more precisely, by the date the message was received for processing in the DLP system as closest to the time of interception. So when using partitioning, the data is broken into pieces by time periods. A time period of one week is usually used.
What advantages can such a breakdown of data provide? Most often, requests within a DLP system are made for very limited, short periods of time. Thus, if we store data for 3-6 months and request information for 1-2-4 weeks, we significantly increase the speed of the solution already due to the fact that less data is read from the disk system. It is clear that both on a partitioned and non-partitioned scheme, a considerable number of various indexes are built in parallel, which make it possible not to perform a full table scan and to read less data from a disk. But here we largely leave it to the discretion of the database optimizer, whether it is necessary to do a full scan or better to use an index. If partitioning is used, we always find ourselves in a fairly clear piece of data that takes up a limited amount of disk space.
Query execution speed is only one of the advantages of using partitioning. Another plus is the ability to simplify the management of data retention times. If there is no partitioning, it is necessary to make a request for deleting old data daily or weekly, otherwise when a certain database volume is reached, problems with its performance and maintenance begin. Partitioning allows you to reduce the removal of old data to a simpler operation of deleting a specific section. In Oracle, all you need to do is to delete two tablespaces of the section and their data files from the disk system. In PostgreSQL, you need to delete the databases created for this section.
In the next release of Solar Dozor, partitioning on PostgreSQL will be implemented by the standard method using inherit tables. Thus, in the new version, deleting a section will be reduced to deleting the tables responsible for this section.
The big advantage of data partitioning in DLP is the ability to split the data archive into operational and so-called “demand repository”. What is the essence of this division?
For most customers, it is important to have direct / online access to captured data from the last 3-6-12 months. Anything older than this period, some are generally willing to delete. However, many people want to save this data in order to be able to conduct investigations, relying on the full volume of intercepted messages. This can be dictated by both the company's internal security standards and the requirement of compliance with a number of standards. Long shelf life requires dozens of terabytes of disk system. In addition, for an operational archive, SAS 10k disks are usually designed, and it is rather expensive to type these tens of terabytes. In a partitioned scheme, data that goes beyond the operational availability period can be easily disconnected from the search engine and placed on slower and cheaper storage systems. Some customers buy SATA-disks for storing offline-archive, others use tape libraries, which is especially important if you need to store 200-300 terabytes of data, which will be needed only a few times during storage. This is implemented in the following way:
Oracle
The table spaces of the outdated section are transferred to offline, after which the data files of this section are transferred to the storage prepared for them. When there is a need to search for the period relating to the disabled section, the data files are transferred back, the section is connected, and any queries can be made on it. After that, the section shuts down and retracts back.
PostgreSQL
The outdated section is translated offline, then we make a dump (pg_dump) of the tables (or databases) responsible for this section, and we delete these tables from the database. The dump is stored in the prepared offline storage. Further, by analogy with Oracle, when this data is required, the deleted tables are restored from the dump (pg_restore). Sections are translated online, after which you can search for them. At the end of the investigation, the section is transferred offline, the tables are deleted - the space is freed.
To perform all these disconnect / connect operations, you do not need to be a qualified Oracle or PostgreSQL DBMS administrator. For this there are specialized utilities that allow you to view the status of sections, connect, disconnect, delete them, etc. In the case of Oracle, part of the operations for disconnecting / connecting sections and transferring sections to another directory are available directly from the web interface. Automating the creation and deactivation of sections within a DBMS removes the question of labor costs for maintenance. All that remains is to copy the disconnected sections to the prepared repositories, but they do not require special administrative qualifications.
As a result, thanks to partitioning in the Solar Dozor DBMS, we have achieved improved product performance and search speed. The archive has become easier to manage - it is easy to unload intercepted data, freeing disk resources, and, if necessary, download back. All this is done automatically, so you do not need to hire a separate qualified specialist to manage the DBMS. Finally, the customer can store the data for any period of time and at the same time do not spend much money on the storage system.
Now, when import substitution is sweeping the country, the public sector (and not only) forms the demand for DLP, supporting free DBMS. This is a very tangible impulse, but, throwing in the direction of free DBMS, it is important to maintain the convenience, performance and functionality of the product. This article will discuss how we solved this problem by implementing PostgreSQL support and developing a partitioning scheme in Solar Dozor.
Oracle vs PostgreSQL
The Solar Dozor system uses a relational DBMS as the main storage system. Until recently, the selection criteria for the DBMS were quite simple: the volume and the ability to divide the archive into operational and “on demand”.
')
Regarding the size of the database, there were certain limitations derived empirically: it should not exceed 3-4 TB (this is about 5 million intercepted messages). Above these values ​​there was a significant degradation of the system: the load on the IO due to the need to regularly run queries for deleting old data, vacuming, reducing the speed of query execution (in large samples, in fact, a full table scan was performed). Therefore, if the estimate of the required volume was beyond these figures, the Oracle DBMS was always used for data storage.
The second criterion - the ability to divide the archive into operational and “on demand” - can be read as the need to implement partitioning into a DBMS. Up until 2016, the Solar Dozor DLP system supported partitioning exclusively on Oracle DBMS.
We understood that the budgets of customers are limited, and sometimes they simply cannot afford to purchase Oracle licenses. Therefore, in some projects with a storage capacity of 10-15 TB, we began to use PostgreSQL.
How we implemented partitioning on PostgreSQL
The architecture and capabilities of the Solar Dozor system allow you to connect to it any number of databases, both Oracle and PostgreSQL. In addition, for each database, you can specify whether it is possible to search for it or archive messages into it. Therefore, we first decided to create several PostgreSQL DBMS instances on the same or different servers and write data into them one by one in turn. After the expiration of the storage period, the oldest database was re-created - and so on in a circle. But this solution was quite expensive in terms of support and suited only to a very narrow circle of customers.
The next implementation of data partitioning was also built on the idea of ​​“one section - one database”, but now all databases were created within the same PostgreSQL instance. For all operations of working with sections, the corresponding utilities were written, and this scheme worked quite well for several customers. Relying on it, some even migrated from Oracle.
However, this implementation still had a number of problems related to the features of the work of a number of services in Solar Dozor. With a large number of sections, it was necessary to hold simultaneously more than 1000 connections per instance. In addition, it was not very convenient to update the data schema at the release of new releases.
In the end, we decided to use partitioning, which PostgreSQL itself offers us. That is, for all tables in which there is a key that refers to the date the message was laid, inherited labels are created with a restriction on this key. Storing data in the desired set of tables is done by a trigger, which is created separately for each section. Such a scheme gives us all the necessary functionality: when querying, only those data sections are raised from the disk for the duration of the request; if it is necessary to take a section out of circulation, the tables are derived from the baseline inheritance, after which they are dumped. Utilities for management are written for all such sections: transfer to offline / online, add, delete, dump, etc.
This implementation has become a real replacement for Oracle, allowing you to keep dozens of terabytes of data in real-time access and dozens more in the archive on slower storage systems. We offer all new customers to do partitioning on PostgreSQL. If an existing customer with partitioning on Oracle wants to switch to PostgreSQL, we suggest that he write new data to PostgreSQL, and leave the old data to Oracle until they become obsolete.
We found it impractical to write special procedures for migrating data from Oracle to PostgreSQL, because the development itself and the migration work itself is very expensive, especially if the customer has disabled data from any old versions of the product. And there are very few such requests.
What was achieved with the help of database partitioning in the DLP system
Productivity increase
All intercepted messages are saved to the DLP system. For DLP tasks, it is important to know when the information was transmitted and intercepted. Thus, all intercepted messages are clearly linked to the time line. Therefore, we use RANGE partitioning by the interception date, more precisely, by the date the message was received for processing in the DLP system as closest to the time of interception. So when using partitioning, the data is broken into pieces by time periods. A time period of one week is usually used.
What advantages can such a breakdown of data provide? Most often, requests within a DLP system are made for very limited, short periods of time. Thus, if we store data for 3-6 months and request information for 1-2-4 weeks, we significantly increase the speed of the solution already due to the fact that less data is read from the disk system. It is clear that both on a partitioned and non-partitioned scheme, a considerable number of various indexes are built in parallel, which make it possible not to perform a full table scan and to read less data from a disk. But here we largely leave it to the discretion of the database optimizer, whether it is necessary to do a full scan or better to use an index. If partitioning is used, we always find ourselves in a fairly clear piece of data that takes up a limited amount of disk space.
Reducing the burden on administrators
Query execution speed is only one of the advantages of using partitioning. Another plus is the ability to simplify the management of data retention times. If there is no partitioning, it is necessary to make a request for deleting old data daily or weekly, otherwise when a certain database volume is reached, problems with its performance and maintenance begin. Partitioning allows you to reduce the removal of old data to a simpler operation of deleting a specific section. In Oracle, all you need to do is to delete two tablespaces of the section and their data files from the disk system. In PostgreSQL, you need to delete the databases created for this section.
In the next release of Solar Dozor, partitioning on PostgreSQL will be implemented by the standard method using inherit tables. Thus, in the new version, deleting a section will be reduced to deleting the tables responsible for this section.
Reduced storage costs
The big advantage of data partitioning in DLP is the ability to split the data archive into operational and so-called “demand repository”. What is the essence of this division?
For most customers, it is important to have direct / online access to captured data from the last 3-6-12 months. Anything older than this period, some are generally willing to delete. However, many people want to save this data in order to be able to conduct investigations, relying on the full volume of intercepted messages. This can be dictated by both the company's internal security standards and the requirement of compliance with a number of standards. Long shelf life requires dozens of terabytes of disk system. In addition, for an operational archive, SAS 10k disks are usually designed, and it is rather expensive to type these tens of terabytes. In a partitioned scheme, data that goes beyond the operational availability period can be easily disconnected from the search engine and placed on slower and cheaper storage systems. Some customers buy SATA-disks for storing offline-archive, others use tape libraries, which is especially important if you need to store 200-300 terabytes of data, which will be needed only a few times during storage. This is implemented in the following way:
Oracle
The table spaces of the outdated section are transferred to offline, after which the data files of this section are transferred to the storage prepared for them. When there is a need to search for the period relating to the disabled section, the data files are transferred back, the section is connected, and any queries can be made on it. After that, the section shuts down and retracts back.
PostgreSQL
The outdated section is translated offline, then we make a dump (pg_dump) of the tables (or databases) responsible for this section, and we delete these tables from the database. The dump is stored in the prepared offline storage. Further, by analogy with Oracle, when this data is required, the deleted tables are restored from the dump (pg_restore). Sections are translated online, after which you can search for them. At the end of the investigation, the section is transferred offline, the tables are deleted - the space is freed.
To perform all these disconnect / connect operations, you do not need to be a qualified Oracle or PostgreSQL DBMS administrator. For this there are specialized utilities that allow you to view the status of sections, connect, disconnect, delete them, etc. In the case of Oracle, part of the operations for disconnecting / connecting sections and transferring sections to another directory are available directly from the web interface. Automating the creation and deactivation of sections within a DBMS removes the question of labor costs for maintenance. All that remains is to copy the disconnected sections to the prepared repositories, but they do not require special administrative qualifications.
Results
As a result, thanks to partitioning in the Solar Dozor DBMS, we have achieved improved product performance and search speed. The archive has become easier to manage - it is easy to unload intercepted data, freeing disk resources, and, if necessary, download back. All this is done automatically, so you do not need to hire a separate qualified specialist to manage the DBMS. Finally, the customer can store the data for any period of time and at the same time do not spend much money on the storage system.
Source: https://habr.com/ru/post/324242/
All Articles