Implementation of INSERT in PostgreSQL in a Segment Tree

Abstract

Purpose: This article is a continuation of the article [1], which considered a method of optimizing aggregation queries on a continuous range of PostgreSQL databases rows using a segment tree [2–4]. The disadvantage of the considered approach was that the operation of inserting new values into the table took too much time, as it led to a complete rebuilding of the tree. The purpose of this work is to develop a method that allows to significantly speed up the insertion of new items into a table while maintaining up-to-date information in the segment tree. In this article, we propose a method that can significantly speed up the insertion of new items into the table while maintaining up-to-date information in the segment tree. At the same time the gain in aggregating queries is fully preserved. The method is based on the peculiarity of the segment tree, which allows to update elements in it for O(log_2 N) asymptotic. Then we can reduce the problem of adding new values to updating existing elements, which can be solved more efficiently. For this purpose, additional neutral elements are allocated during the construction of the tree. When new values are added to the table, one of these additional elements is updated. When the free elements in the tree run out the tree is rebuilt, again allocating additional memory. The result of this work is the development of an algorithm for updating elements in the tree and its implementation in the form of PostgreSQL Extensions. The speed of the obtained solution has been measured. Conclusions on the results of work and plans for further optimization of operations are made.