Designing Succinct Secondary Indexing Mechanism by Exploiting Column Correlations (Extended Version)

Database administrators construct secondary indexes on data tables to accelerate query processing in relational database management systems (RDBMSs). These indexes are built on top of the most frequently queried columns according to the data statistics. Unfortunately, maintaining multiple secondary indexes in the same database can be extremely space consuming, causing significant performance degradation due to the potential exhaustion of memory space. In this paper, we demonstrate that there exist many opportunities to exploit column correlations for accelerating data access. We propose HERMIT, a succinct secondary indexing mechanism for modern RDBMSs. HERMIT judiciously leverages the rich soft functional dependencies hidden among columns to prune out redundant structures for indexed key access. Instead of building a complete index that stores every single entry in the key columns, HERMIT navigates any incoming key access queries to an existing index built on the correlated columns. This is achieved through the Tiered Regression Search Tree (TRS-Tree), a succinct, ML-enhanced data structure that performs fast curve fitting to adaptively and dynamically capture both column correlations and outliers. Our extensive experimental study in two different RDBMSs have confirmed that HERMIT can significantly reduce space consumption with limited performance overhead in terms of query response time and index maintenance time, especially when supporting complex range queries.

💡 Research Summary

The paper addresses the growing memory pressure caused by maintaining many secondary indexes in modern main‑memory relational database management systems (RDBMSs). Traditional approaches either limit the number of indexes based on a space budget or apply compression and sampling techniques that only modestly reduce storage while increasing lookup cost. The authors propose a fundamentally different solution: exploiting “soft functional dependencies” (approximate column correlations) that naturally exist in many datasets.

Their system, called HERMIT, consists of two components. First, a Tiered Regression Search Tree (TRS‑Tree) is built for a target column M that lacks a dedicated index but is highly correlated with an already indexed host column N. The TRS‑Tree recursively partitions the value range of M into k‑ary sub‑ranges. For each leaf sub‑range, it fits a simple linear regression model n = β·m + α ± ε, where ε is a confidence interval derived from a user‑defined error bound. If the regression error for a sub‑range exceeds the bound, the range is split further; otherwise the leaf stores the model and a hash‑based outlier buffer for points that do not fit the model. Internal nodes only hold pointers to child ranges, enabling fast navigation.

Second, query processing proceeds in three phases. (1) The predicate on M is fed to the TRS‑Tree, which returns an estimated N‑range using the stored regression models. (2) The existing N‑index (the “host index”) is scanned with this N‑range (and any additional predicates) to retrieve a candidate set of tuple identifiers. (3) The base table is accessed to validate the original M‑predicate, discarding false positives introduced by the approximation. This two‑hop access pattern adds a small overhead for range queries because the candidate set is dramatically reduced, while point queries incur a modest penalty.

HERMIT supports inserts, deletes, and updates. When a modification affects a leaf, the regression parameters are recomputed; if the error bound is violated, the leaf is split or merged, allowing the structure to adapt dynamically to data‑distribution changes. The fan‑out k and error‑bound are tunable knobs that trade memory consumption against lookup accuracy.

The authors implemented HERMIT in two commercial main‑memory DBMSs (a MySQL‑InnoDB variant and VoltDB) and evaluated it on synthetic correlated workloads as well as TPCH‑style benchmarks. Results show that HERMIT reduces secondary‑index memory usage by 70 % on average compared with fully materialized indexes. Query latency for range queries increases by only 5 %–15 %, which the authors deem acceptable given the space savings. Point‑query latency is higher but still competitive in memory‑constrained environments. Outlier handling incurs modest extra memory, and performance degrades when the outlier fraction becomes large.

Key contributions are: (1) automatic detection and exploitation of soft column correlations for index design; (2) a regression‑based hierarchical tree that compresses the correlation function while supporting dynamic updates; (3) a practical system that integrates with existing host indexes to provide correct query results with far lower memory footprint.

Limitations include reliance on linear regression within each leaf, which may be insufficient for highly non‑linear relationships, and the overhead of maintaining outlier buffers when many points deviate from the model. Future work suggested includes extending the model to multi‑column and non‑linear regressions (e.g., polynomial or neural‑network based), automatic parameter tuning, and more sophisticated outlier management.