Optimizing materialized view selection for spatial queries in hybrid transactional/analytical processing database

Abstract

In this project, we aimed to develop Hybrid Transactional/Analytical Processing (HTAP) database that enables efficient analytical and transaction processing on the same in-memory data store. Specifically, we extended the architecture to handle spatial data, enabling efficient querying and processing of geospatial information commonly used in location-based analysis. As real-time geospatial data becomes increasingly central to modern applications, HTAP architecture that support both fresh data ingestion and low-latency spatial querying have grown in importance. To support this, we employed a combination of Materialized View generation techniques and a write-optimized storage engine.

MyRocks, a write-optimized storage engine for MySQL based on RocksDB, was used for this project. MyRocks storage engine offers high performance for write-intensive operations and efficient data compression. To improve the reading performance, precomputed queries, better known as Materialized Views, were leveraged. This allowed the overall architecture to have a balanced approach that efficiently handles both write-intensive transactional workloads and read-intensive analytical queries.

To evaluate the performance of the proposed HTAP system, we utilized a real-world spatial workload comprising over 187,000 city geometries, 65,000 geotagged tweets, and 9,000 points of interest (POIs). A set of spatial SQL queries was utilized to simulate analytical workloads involving spatial joins, spatial aggregations, vector search, and temporal filters.

Efficient processing of these queries was achieved by implementing and comparing several spatially aware Materialized View generation strategies that pre-aggregate data by geographic regions and minimize the cost of spatial joins and filtering during query execution. The results demonstrated that these strategies significantly improved query performance, with some complex spatial queries seeing a reduction from over one hour to under 20 seconds.

In conclusion, the proposed Hybrid Transactional/Analytical Processing (HTAP) system has shown promising results in addressing the demands of transactional and analytical processing on the same data store, particularly for spatial workloads. The combination of MyRocks' write-optimized capabilities and the strategic use of Materialized View demonstrated substantial improvements in query performance. Future work may focus on integrating this approach into a larger open-sourced geospatial database system to support real-world spatial analytics.