Positional update handling in column stores

Abstract

In this paper we investigate techniques that allow for on-line updates to columnar databases, leaving intact their high read-only performance. Rather than keeping differential structures organized by the table key values, the core proposition of this paper is that this can better be done by keeping track of the tuple position of the modifications. Not only does this minimize the computational overhead of merging in differences into read-only queries, but this makes the differential structure oblivious of the value of the order keys, allowing it to avoid disk I/O for retrieving the order keys in read-only queries that otherwise do not need them-a crucial advantage for a column-store. We describe a new data structure for maintaining such positional updates, called the Positional Delta Tree (PDT), and describe detailed algorithms for PDT/column merging, updating PDTs, and for using PDTs in transaction management. In experiments with a columnar DBMS, we perform microbenchmarks on PDTs, and show in a TPC-H workload that PDTs allow quick on-line updates, yet significantly reduce their performance impact on read-only queries compared with classical value-based differential methods.

Vectorized UDFs in Column-Stores

“…MonetDB alleviates this issue by making use of delta structures [36]. Instead of writing the updates directly to disk, they are stored in delta structures that are kept in memory.…”

Vectorwise: A Vectorized Analytical DBMS

“…PFOR [2]), hybrid PAX/DSM storage [3], and bandwidth sharing by concurrent queries (Cooperative Scans [4]). Additionally, column-friendly differential update schemes (PDTs [5] were devised.…”

Integration of vectorwise with ingres

“…Like its ancestor MonetDB, X100 is a column store database server designed for read-mostly applications performing complex analytic queries on large volumes of data. That said, it also employs sophisticated updating mechanisms based on Positional Delta Trees (PDTs) that give it acceptable performance in update applications from moderate to heavy [2].…”

“…The MScan operator creates a data flow containing the sno and qty columns from the sp base table. MScan stands for Merge-Scan, as it scans columnar data from disk while merging in possible committed updates present in the PDTs [2]. Aggr performs a hash aggregation grouping the input flow on sno and computing the sum of qty values for each group.…”

“…In 2008, Ingres Corporation (renamed as Actian Corporation in September 2011) entered into a commercial agreement with VectorWise BV, a spin-off company from the Dutch research institute CWI (Centrum voor Wiskunde en Informatica). VectorWise was created to bring to market the technology that was developed by the MonetDB/X100 project [1,2,10]. The goal of the agreement was to integrate the VectorWise technology with the Ingres database server to produce a viable commercial RDBMS that provided the accepted components of enterprise class database management together with the extreme performance of the VectorWise engine as demonstrated in the numerous research papers generated from the MonetDB/X100 project.…”