Replacing Oracle with PostgreSQL and the ability to work with partitioning within a DLP system
Today we would like to touch upon a topic that is very important for DLP solutions - the choice of a DBMS for data storage. It so happened historically that most Russian DLPs use Oracle Database for these purposes. This imposes certain financial restrictions on customers: the cost of Oracle licenses is laid down in the cost of a DLP system. This creates a certain filter that reduces the audience of product users: not everyone can afford Oracle DBMSs, both technically and financially.
Now, when import substitution moves around the country, the public sector (and not only) is generating demand for DLPs that support free DBMSs. This is a very tangible impulse, but, heading towards free DBMSs, 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 criteria for choosing a DBMS were quite simple: the volume and the ability to divide the archive into operational and “on demand”.
Regarding the volume of the database, there were certain limitations that were derived empirically: it should not exceed 3-4 TB (this is about 5 million intercepted messages). Above these values, a significant degradation of the system was observed: the load on IO due to the need to regularly run queries to delete old data, vacuuming, lower query execution speed (in large samples, tables were actually scanned completely). Therefore, if the estimate of the required volume went beyond these figures, 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 on the DBMS. Until 2016, the Solar Dozor DLP system supported partitioning exclusively on Oracle DBMS.
We understood that customer budgets were limited, and sometimes they simply could not 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 any number of databases, both Oracle and PostgreSQL, to it. At the same time, for each database, you can specify whether it is possible to search on it or archive messages in it. Therefore, at first we decided to create several PostgreSQL DBMS instances on the same or different servers and write data into them with a period of 1 month in turn. After the expiration of the storage period, the oldest base was recreated - and so on in a circle. But this solution was quite expensive in terms of support and suited only 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 the databases were created within the same PostgreSQL instance. The corresponding utilities were written for all operations with sections, and such a scheme worked quite well for several customers. Based on it, some even migrated with Oracle.
However, this implementation still had a number of problems related to the features of a number of services in Solar Dozor. With a large number of sections, it was necessary to keep at the same time more than 1000 connections per instance. In addition, it was not very convenient to update the data schema when new releases were released.
In the end, we decided to use the partitioning that PostgreSQL itself offers us. That is, for all tables in which there is a key that refers to the date the message was placed, inherited labels are created with a restriction on this key. The data is put into the desired set of tables by the 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 period of which the request is being made; if it is necessary to remove the section from circulation, the tables are removed from the base inheritance, after which they are dumped. Management utilities have been written for all such sections: offline / online translation, adding, deleting, dump, etc.
This implementation became a real replacement for Oracle, allowing you to keep dozens of terabytes of data online 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 Oracle partitioning wants to switch to PostgreSQL, we suggest that he write the new data to PostgreSQL, and leave the old data to Oracle until they become obsolete.
We found it inappropriate to write special procedures for migrating data from Oracle to PostgreSQL, because both their development and the actual migration work are 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 stored in the DLP system. For DLP tasks, it is important to know when information was transmitted and was intercepted. Thus, all intercepted messages have a clear link to the time line. Therefore, we use RANGE partitioning by the date of interception, more precisely, by the date of receipt of the message for processing in the DLP system as the one closest to the moment of interception. So, when using partitioning, the data is broken into pieces by time periods. Usually a period of one week is used.
What are the benefits of such a breakdown? Most often, requests within the 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 will 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 a significant number of various indexes are built in parallel on both a partitioned and a non-partitioned scheme, which allows not to perform full table scans and read less data from disk. But here we largely leave it up to the DB optimizer whether it is necessary to do a full scan or is it better to use the index. If partitioning is used, we always fall into a fairly clear piece of data,
Query execution speed is only one of the benefits of using partitioning. Another plus is the ability to greatly simplify the management of the data retention period. If there is no partitioning, you have to make a request to delete old data daily or weekly, otherwise, when certain volumes of the database are reached, problems with its performance and maintenance begin. Partitioning reduces the deletion of old data to a simpler deletion operation for a particular section. In Oracle, for this it is enough to remove two partition table spaces and their data files from the disk system. In PostgreSQL, you need to delete the databases created for this section.
In the upcoming Solar Dozor release, PostgreSQL partitioning will be implemented using a 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 divide the data archive into operational and the so-called “demand archive”. What is the essence of this separation?
For most customers, it is important to have intercepted data for the last 3-6-12 months in direct / operational access. Anything older than this period, some are generally ready to remove. However, many people want to keep this data in order to be able to conduct investigations based on the full amount of intercepted messages. This can be dictated both by the company's internal security standards, and by the requirement to comply with a number of standards. Long shelf life requires tens of terabytes of disk space. In addition, SAS 10k disks are usually designed for an online archive, and typing these tens of terabytes is quite expensive. In a partitioned scheme, data that goes beyond the period of operational availability can be easily disconnected from the search engine and placed on slower and cheaper storage systems. A number of customers buy SATA disks for offline archive storage, while others use tape libraries, which is especially important if you need to store 200-300 terabytes of data that will be needed only a few times for the entire storage period. It is implemented in the following way:
Oracle
Table spaces of an outdated section are transferred offline, after which the data files of this section are transferred to the storage prepared for them. When it becomes necessary to search for a period related to a disconnected section, the data files are transferred back, the section is connected, and any queries can be made on it. After that, the section is turned off and retracted.
PostgreSQL
The obsolete section is switched offline, then we dump (pg_dump) the tables (or databases) responsible for this section, and delete these tables from the database. The dump is stored in the prepared offline storage. Further, by analogy with Oracle, when this data is in demand, the deleted tables are restored from the dump (pg_restore). Sections are translated online, after which you can do a search on them. At the end of the investigation, the section goes offline, the tables are deleted - the place is freed.
To perform all these disconnect / connect operations, you do not need to be a qualified Oracle or PostgreSQL DBMS administrator. To do 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 moving sections to another directory are available directly from the web interface. Automating the creation and deactivation of sections within the DBMS removes the issue of labor costs for maintenance. All that remains is to copy the disabled sections to the prepared repositories, but they do not require special qualifications of administrators.
As a result, thanks to the partitioning in the Solar Dozor DBMS, we have achieved increased product performance and search speed. The archive has become easier to manage - the intercepted data is easily unloaded, freeing up disk resources, and, if necessary, loaded 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 data for any period of time and at the same time do not spend much on the storage system.
Now, when import substitution moves around the country, the public sector (and not only) is generating demand for DLPs that support free DBMSs. This is a very tangible impulse, but, heading towards free DBMSs, 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 criteria for choosing a DBMS were quite simple: the volume and the ability to divide the archive into operational and “on demand”.
Regarding the volume of the database, there were certain limitations that were derived empirically: it should not exceed 3-4 TB (this is about 5 million intercepted messages). Above these values, a significant degradation of the system was observed: the load on IO due to the need to regularly run queries to delete old data, vacuuming, lower query execution speed (in large samples, tables were actually scanned completely). Therefore, if the estimate of the required volume went beyond these figures, 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 on the DBMS. Until 2016, the Solar Dozor DLP system supported partitioning exclusively on Oracle DBMS.
We understood that customer budgets were limited, and sometimes they simply could not 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 any number of databases, both Oracle and PostgreSQL, to it. At the same time, for each database, you can specify whether it is possible to search on it or archive messages in it. Therefore, at first we decided to create several PostgreSQL DBMS instances on the same or different servers and write data into them with a period of 1 month in turn. After the expiration of the storage period, the oldest base was recreated - and so on in a circle. But this solution was quite expensive in terms of support and suited only 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 the databases were created within the same PostgreSQL instance. The corresponding utilities were written for all operations with sections, and such a scheme worked quite well for several customers. Based on it, some even migrated with Oracle.
However, this implementation still had a number of problems related to the features of a number of services in Solar Dozor. With a large number of sections, it was necessary to keep at the same time more than 1000 connections per instance. In addition, it was not very convenient to update the data schema when new releases were released.
In the end, we decided to use the partitioning that PostgreSQL itself offers us. That is, for all tables in which there is a key that refers to the date the message was placed, inherited labels are created with a restriction on this key. The data is put into the desired set of tables by the 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 period of which the request is being made; if it is necessary to remove the section from circulation, the tables are removed from the base inheritance, after which they are dumped. Management utilities have been written for all such sections: offline / online translation, adding, deleting, dump, etc.
This implementation became a real replacement for Oracle, allowing you to keep dozens of terabytes of data online 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 Oracle partitioning wants to switch to PostgreSQL, we suggest that he write the new data to PostgreSQL, and leave the old data to Oracle until they become obsolete.
We found it inappropriate to write special procedures for migrating data from Oracle to PostgreSQL, because both their development and the actual migration work are 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 using database partitioning in a DLP system
Productivity increase
All intercepted messages are stored in the DLP system. For DLP tasks, it is important to know when information was transmitted and was intercepted. Thus, all intercepted messages have a clear link to the time line. Therefore, we use RANGE partitioning by the date of interception, more precisely, by the date of receipt of the message for processing in the DLP system as the one closest to the moment of interception. So, when using partitioning, the data is broken into pieces by time periods. Usually a period of one week is used.
What are the benefits of such a breakdown? Most often, requests within the 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 will 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 a significant number of various indexes are built in parallel on both a partitioned and a non-partitioned scheme, which allows not to perform full table scans and read less data from disk. But here we largely leave it up to the DB optimizer whether it is necessary to do a full scan or is it better to use the index. If partitioning is used, we always fall into a fairly clear piece of data,
Reducing the burden on administrators
Query execution speed is only one of the benefits of using partitioning. Another plus is the ability to greatly simplify the management of the data retention period. If there is no partitioning, you have to make a request to delete old data daily or weekly, otherwise, when certain volumes of the database are reached, problems with its performance and maintenance begin. Partitioning reduces the deletion of old data to a simpler deletion operation for a particular section. In Oracle, for this it is enough to remove two partition table spaces and their data files from the disk system. In PostgreSQL, you need to delete the databases created for this section.
In the upcoming Solar Dozor release, PostgreSQL partitioning will be implemented using a standard method using inherit tables. Thus, in the new version, deleting a section will be reduced to deleting the tables responsible for this section.
Reduce storage costs
The big advantage of data partitioning in DLP is the ability to divide the data archive into operational and the so-called “demand archive”. What is the essence of this separation?
For most customers, it is important to have intercepted data for the last 3-6-12 months in direct / operational access. Anything older than this period, some are generally ready to remove. However, many people want to keep this data in order to be able to conduct investigations based on the full amount of intercepted messages. This can be dictated both by the company's internal security standards, and by the requirement to comply with a number of standards. Long shelf life requires tens of terabytes of disk space. In addition, SAS 10k disks are usually designed for an online archive, and typing these tens of terabytes is quite expensive. In a partitioned scheme, data that goes beyond the period of operational availability can be easily disconnected from the search engine and placed on slower and cheaper storage systems. A number of customers buy SATA disks for offline archive storage, while others use tape libraries, which is especially important if you need to store 200-300 terabytes of data that will be needed only a few times for the entire storage period. It is implemented in the following way:
Oracle
Table spaces of an outdated section are transferred offline, after which the data files of this section are transferred to the storage prepared for them. When it becomes necessary to search for a period related to a disconnected section, the data files are transferred back, the section is connected, and any queries can be made on it. After that, the section is turned off and retracted.
PostgreSQL
The obsolete section is switched offline, then we dump (pg_dump) the tables (or databases) responsible for this section, and delete these tables from the database. The dump is stored in the prepared offline storage. Further, by analogy with Oracle, when this data is in demand, the deleted tables are restored from the dump (pg_restore). Sections are translated online, after which you can do a search on them. At the end of the investigation, the section goes offline, the tables are deleted - the place is freed.
To perform all these disconnect / connect operations, you do not need to be a qualified Oracle or PostgreSQL DBMS administrator. To do 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 moving sections to another directory are available directly from the web interface. Automating the creation and deactivation of sections within the DBMS removes the issue of labor costs for maintenance. All that remains is to copy the disabled sections to the prepared repositories, but they do not require special qualifications of administrators.
Summary
As a result, thanks to the partitioning in the Solar Dozor DBMS, we have achieved increased product performance and search speed. The archive has become easier to manage - the intercepted data is easily unloaded, freeing up disk resources, and, if necessary, loaded 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 data for any period of time and at the same time do not spend much on the storage system.
