Columnar database systems can process complex mixed workloads on a single node. In case of increasing and peak analytical processing demand, we can offload read-only queries to replicas. Partial replication, i.e., duplicating only data subsets to additional nodes, is more cost-efficient than full replication for two primary reasons: (i) Partial replicas require less storage and can be set up faster. (ii) Partial replicas must synchronize only stored data subsets, allowing better scalability. However, determining which queries to offload is challenging for larger workloads because queries access overlapping data subsets and cause synchronization costs.

Indexes are important data structures for database tuning. However, finding the best indexes for a given workload is challenging. In this demonstration, we present our extensible open-source index selection evaluation platform and the corresponding interactive result analysis tool. The platform provides an automatic setup of the database, workload, and cost evaluation, which is otherwise often tedious work when evaluating index selection approaches. Users can also connect the platform to their own existing database and evaluate indexes for custom workloads. Our platform comprises multiple state-of-the-art index selection approaches, which can be used as baselines for new index selection proposals. Further, we present an application for thoroughly analyzing the selected database indexes. One can observe which indexes are used for which queries and their effect on processing costs. Also, it is possible to adapt the resulting index selections (i.e., add, remove, or change an index) and observe the impact. In this process, the application helps to understand the effects of indexes, improve index selections, and craft new index selection approaches.

Selective queries are not uncommon in large-scale data analytics, for example, when drilling down into a specific customer in a dashboard. Traditionally, selective queries are accelerated by creating secondary indexes. However, because of their large size, expensive maintenance, and difficulty to tune and automate, indexes are typically not used in modern cloud data warehouses or data lakes. Instead, such systems rely mostly on full table scans and lightweight optimizations like min/max filtering, whose effectiveness depends heavily on the data layout and value distributions.We propose SPA as the vision for automatically optimizing selective queries for immutable copy-on-write data formats. SPA adaptively indexes subsets of the data in an incremental and workload-driven manner. It makes fine-grained decisions and continuously monitors their benefit, dynamically allocating an optimization budget in a way that bounds the additional cost of indexing. Furthermore, it guarantees a performance improvement in the cases where indexes—potentially partial ones—prove to be beneficial. When indexes lose their benefit due to a shifting workload, they are gradually deconstructed in favor of optimizations that accommodate recent trends. As SPA does not require information about updates performed on the data, it can also be employed as an accelerator for systems that do not control the data, e.g., in cloud data lakes.

Load balancing queries that access the same data fragments to the same node improves caching for a memory-efficient scale-out. However, to suitably allocate fragments to multiple nodes is a highly challenging problem, particularly when nodes might fail. The problem is to find a good balance between memory efficiency and allocating enough fragments to nodes to obtain robustness through load balancing flexibility. Existing allocation approaches are either not memory-efficient or result in load imbalances, both degrading cost/performance. In this paper, we present an optimal approach and a scalable heuristic, based on three mutually supportive linear programming models, to calculate memory-efficient fragment allocations that guarantee to distribute the workload evenly - even in the case of node failures. We demonstrate the applicability and the effectiveness of our three-step approach using numerical as well as end-to-end evaluations for TPC-H and TPC-DS workloads. We find that our robust solutions clearly outperform state-of-the-art heuristics by achieving a better workload distribution with even less memory.

Database replication is a mechanism to achieve scalability, for example, by executing queries independently on replica nodes. Partial replication is an approach to minimize the overall memory consumption of a replication cluster while still enabling a balanced load distribution among nodes to scale the query throughput linearly with the number of replicas. Partial replication reduces the cluster costs, speeds up data synchronization, and improves caching. However, load balancing may become skewed in the case of unexpected query distributions, unfavorable query timings, or node failures. To simulate and visualize the load balancing behavior for specific data fragment allocations, we implemented an interactive application. It allows users to retrace and evaluate the end-to-end performance of partially replicated database systems in varying experiments. Using our tool, we find that existing allocation approaches are either not memory-efficient or may result in load imbalances when nodes fail. We show that our novel robust allocation strategy achieves a better workload distribution with even less memory.

While finding the best selection of indexes is an important task the problem appears highly challenging as (i) indexes mutually affect their impact on performance and (ii) the number of index combinations can be enormous. Current selection approaches have limitations when problems are large and ignore the fact that future workloads are partially stochastic. In this paper, we propose a solver-based approach to find effective index selections for large-scale workloads. Our decomposition concept allows to deal with large candidate sets and makes it possible to address risk-averse problem versions, where multiple potential future workloads are taken into account. We demonstrate the applicability and the effectiveness of our approach for the TPC-DS benchmark workload. Our numerical results show that compared to state-of-the-art LP approaches index selections can be computed orders of magnitudes faster while still obtaining near-optimal performance results.

In replication schemes, replica nodes can process read-only queries on snapshots of the master node without violating transactional consistency. By analyzing the workload, we can identify query access patterns and replicate data depending to its access frequency. In this paper, we define a linear programming (LP) model to calculate the set of partial replicas with the lowest overall memory capacity while evenly balancing the query load. Furthermore, we propose a scalable decomposition heuristic to calculate solutions for larger problem sizes. While guaranteeing the same performance as state-of-the-art heuristics, our decomposition approach calculates allocations with up to 23% lower memory footprint for the TPC-H benchmark.

Increasing demand for analytical processing capabilities can be managed by replication approaches. However, to evenly balance the replicas' workload shares while at the same time minimizing the data replication factor is a highly challenging allocation problem. As optimal solutions are only applicable for small problem instances, effective heuristics are indispensable. In this paper, we test and compare state-of-the-art allocation algorithms for partial replication. By visualizing and exploring their (heuristic) solutions for different benchmark workloads, we are able to derive structural insights and to detect an algorithm's strengths as well as its potential for improvement. Further, our application enables end-to-end evaluations of different allocations to verify their theoretical performance.

The introduction of columnar in-memory databases, along with hardware evolution, has made the execution of transactional and analytical workloads on a single system both feasible and viable. Yet, doing analytics directly on the transactional data introduces an increasing amount of resourceintensive aggregate queries which can slow down the overall system performance in a multi-user environment. To increase the scalability of a system in the presence of multiple such queries, we propose an aggregate cache in the general delta-main architecture that provides an ecient means to handle costly aggregate queries by applying incremental materialized view maintenance and query compensation techniques. Handling aggregate queries based on joins of multiple tables however is still a challenge as query compensation can be very expensive in the delta-main architecture of columnar in-memory databases. Our analysis of enterprise applications has revealed several data schema and workload patterns that can be leveraged for addressing performance of query processing using the aggregate cache. We contribute by presenting an approach to transport the application object semantics into the database system, becoming object-aware, and optimize the query processing using the aggregate cache by applying partition pruning and predicate pushdown in such general delta-main architecture. Our experimental validation using customer data and workloads confirms that this type of optimizations enables ecient usage of the aggregate cache for an even higher share of aggregate queries as one mean to scale the system.

Database workloads generated by enterprise applications are comprised of short-running transactional as well as long-running analytical queries with resource-intensive aggregations. The expensive aggregate queries can be significantly accelerated by using materialized views. This speed-up, however, comes with the cost of materialized view maintenance which is necessary to guarantee consistency when the underlying data changes. While several view maintenance strategies are applicable in the context of an in-memory column store, their performance depends on various factors, most importantly the ratio between queries accessing the materialized view and queries altering the base data, called insert ratio. As a contribution in this paper, we propose algorithms that determine the best-performing view maintenance strategy based on the currently monitored factors. Using our novel materialized aggregate engine, we are able to switch between view maintenance strategies on demand. We have created cost models for the identified view maintenance strategies that determine at which insert ratio it is advisable to switch to another strategy. Our benchmarks in SanssouciDB reveal that for all identified workloads, switching between maintenance strategies is more beneficial than staying with a single strategy.