How We Optimized Hybrid Transactional/Analytical Processing (HTAP) Workloads with SQL Server Columnstore Indexes: A Comprehensive Case Study

Part 2: Exploring Hybrid Transactional/Analytical Processing

Aleh Belausau

Published in

Emerline Tech Talk

13 min readAug 19, 2024

Part 1: OLAP vs. OLTP
Part 2: Exploring Hybrid Transactional/Analytical Processing ⬅️

Introduction

In data management, understanding the differences between Online Transaction Processing (OLTP) and Online Analytical Processing (OLAP) is crucial for designing high-performing and effective systems.

Sometimes, a system needs both transactional integrity and analytical depth, which implies the use of both OLTP and OLAP. In this case, you have two options: employ separate database systems or use a hybrid approach that integrates the features of both. We did exactly this by adopting SQL Server with columnar indexing for one of our solutions.

If you would like to explore topics such as Online Transaction Processing, Online Analytical Processing, and a detailed comparison table of OLAP vs. OLTP, you can refer to Part 1 of this case study.

In Part 2 of this case study, you will explore the following topics:

Challenges and Requirements of Our Service
Challenges with Using Both OLTP and OLAP Systems Simultaneously
What is Hybrid Transactional/Analytical Processing and Its Benefits
HTAP Storage Integration Strategies

Now that we have a clear understanding of the differences between OLAP and OLTP in various aspects, let’s break down the requirements for our service and determine what type of storage is suitable for our needs.

Problem Statement

Let’s break down the requirements for our service to understand why neither pure OLTP nor pure OLAP storages were suitable for our solution.

Service Description

Our service is designed to provide comprehensive monitoring and control of survey participants’ data, featuring near-real-time dashboards with dynamic filters and continuous updates based on each event in the system. The service ensures that users can interact with the system through the UI, make necessary adjustments to survey traffic settings, and observe immediate changes reflected in the dashboard.

Key Features

Let’s determine the key features of our future service:

Near-Real-Time Dashboards

Dynamic Filters: Users can apply dynamic filters to the dashboard to view specific segments of survey participants’ data traffic by relevant criteria.
Continuous Updates: The dashboard updates in near-real-time, displaying the most recent data as soon as it is available. This ensures that users always have access to the latest survey traffic information.

User Interactions and Updates

Interactive UI: The user interface allows users to interact with the survey participants’ data traffic, apply filters, and perform actions such as adjusting survey data flow.
Immediate Feedback: Any changes made by users are immediately reflected in the dashboard, providing instant feedback and confirmation of the actions taken.
Event-Driven Updates: The system responds to each event in the survey data traffic network, updating the dashboard and UI accordingly. This ensures that users are always aware of the current state of survey traffic.

Traffic Setup and Control

Traffic Configuration: Users can configure survey traffic settings, such as defining traffic rules and setting limits. These configurations help optimize traffic flow and improve overall survey traffic management.
Real-Time Adjustments: The system allows for real-time adjustments to traffic settings based on the latest data and user inputs. This flexibility helps in responding quickly to changing traffic conditions.

Requirements Analysis for the Survey Participants’ data Monitoring and Control Service

Let’s analyze the requirements for our service using the provided in Part 1 tables and determine whether OLAP, OLTP, or both are suitable to support these requirements:

Analytical Queries and Reporting

Why Needed: It’s important to have analytical queries and reporting in our service because it enables the monitoring of survey participants’ data traffic patterns and the generation of insights. This aligns with the key feature of near-real-time dashboards with dynamic filters, allowing for comprehensive data analysis and informed decision-making.
Type of Workload: This is an analytical workload, focused on read-heavy operations and complex data analysis, which requires the capabilities of an OLAP system.

Real-Time Data Manipulation

Why Needed: Real-time data manipulation is crucial for our service because it allows immediate updates and interactions with the survey traffic control system. This ensures that user changes are instantly applied and reflected in the system, supporting the key feature of continuous updates and immediate feedback.
Type of Workload: This is a transactional workload characterized by frequent write operations and real-time data processing, which requires the capabilities of an OLTP system.

Transactional Processing

Why Needed: Transactional processing is vital to our service as it ensures high-frequency transaction processing for real-time traffic data updates and control actions. This supports the service’s requirement for maintaining an up-to-date and responsive traffic control system.
Type of Workload: This is a transactional workload, focused on handling a high volume of short, interactive transactions, which requires the capabilities of an OLTP system.

Complex Queries and Large-Scale Analysis

Why Needed: Complex queries and large-scale analysis are necessary for our service to conduct in-depth traffic analysis and reporting. This supports the key features of generating comprehensive reports and insights from large volumes of survey traffic data.
Type of Workload: This is an analytical workload requiring the ability to perform complex queries and handle large datasets, which requires the capabilities of an OLAP system.

Data Mining and Trend Analysis

Why Needed: Data mining and trend analysis are essential for our service to understand traffic patterns and make informed decisions. This aligns with the requirement for analyzing historical data to identify long-term trends and patterns.
Type of Workload: This is an analytical workload, focused on exploring and analyzing historical data to identify patterns, which requires the capabilities of an OLAP system.

Handling Large Data Volumes

Why Needed: Handling large data volumes is important for our service to support long-term traffic analysis and pattern recognition. This ensures that the system can store and analyze vast amounts of traffic data over time.
Type of Workload: This is an analytical workload requiring the ability to store and analyze large datasets, which requires the capabilities of an OLAP system.

High Concurrency

Why Needed: High concurrency is critical for our service to allow multiple users to interact with the system simultaneously without performance degradation. This is essential for a traffic monitoring system where multiple users may make changes and query the system simultaneously.
Type of Workload: This is a transactional workload focused on supporting concurrent access and real-time processing, which requires the capabilities of an OLTP system.

The proportion of OLAP and OLTP workloads in our service.

Based on the above analysis, it’s clear that neither pure OLAP nor pure OLTP can independently meet all the requirements of our service. Each has strengths that are essential for different aspects of the service:

OLTP is essential for real-time data manipulation, transactional processing, high concurrency, and immediate data updates.
OLAP is necessary for analytical queries, complex data analysis, data mining, and handling large data volumes.

The Traffic Monitoring and Control Service requires a hybrid approach that leverages both OLAP and OLTP workloads to meet its diverse set of requirements. The combination of real-time data manipulation and complex analytical capabilities ensures that the system can provide near-real-time updates, support dynamic user interactions, and perform in-depth traffic analysis. This hybrid approach ensures the service is both responsive and capable of generating valuable insights from traffic data.

Challenges of Using Both OLTP and OLAP Systems Simultaneously

Using both OLTP and OLAP systems at the same time poses significant challenges, making this approach impractical for our complete data solution. These challenges affect performance, maintenance, and resource use, ultimately preventing us from providing an integrated and efficient system.

Here is a list of the most challenging aspects of this approach:

Data Synchronization Complexity: Synchronizing data between OLTP and OLAP systems in real-time is complex and error-prone, leading to potential data inconsistencies and increased latency.
Architectural Complexity: Managing two separate systems increases the architectural complexity, requiring different infrastructure setups, specialized knowledge, and tools for each environment.
Redundant Resources: Maintaining separate OLTP and OLAP systems results in redundant resources, such as duplicate storage and computational power, leading to higher costs and inefficient resource utilization.
Increased Maintenance Overheads: Performing regular maintenance tasks, such as backups, indexing, and updates, on both OLTP and OLAP systems separately, significantly increases the administrative burden and requires more extensive monitoring and management.
Inefficient Resource Utilization: The need to allocate separate resources for OLTP and OLAP systems leads to inefficiencies, as peaks in transactional and analytical workloads do not always align. This results in periods of idle capacity and underutilized resources, increasing operational costs.

Due to these issues, the simultaneous use of separate OLTP and OLAP systems is not a feasible solution for our needs. We need an integrated approach that can seamlessly handle both transactional and analytical workloads, optimizing performance, reducing maintenance complexity, and efficiently utilizing resources.

HTAP systems are designed to address these challenges by combining transactional and analytical processing capabilities within a single system. Let’s delve into Hybrid Transactional/Analytical Processing.

What is HTAP?

HTAP, or Hybrid Transactional/Analytical Processing, is a data processing paradigm that integrates transactional and analytical workloads into a single database system. This approach contrasts with traditional systems, which typically separate these workloads into OLTP and OLAP systems.

Traditional architecture VS HTAP architecture

The HTAP architecture typically includes the following components:

Unified Storage Engine: A single storage engine that supports both row-based and column-based storage formats.

Row-based storage is efficient for transactional operations.
Column-based storage is efficient for analytical queries.

In-Memory Processing: Many HTAP systems leverage in-memory processing to speed up both transactional and analytical workloads. Data can be kept in memory for real-time analytics while also being durable for transactions.

Concurrency Control: Advanced concurrency control mechanisms ensure that transactional and analytical workloads can run concurrently without performance degradation or data inconsistency.

Benefits of HTAP

HTAP systems offer several significant advantages:

Real-Time Analytics: HTAP provides immediate insights from transactional data without the need for ETL processes.

Simplified Architecture: By reducing the need for separate systems, HTAP simplifies the overall architecture. This leads to a lower total cost of ownership due to fewer systems to manage and maintain.

Data Consistency: HTAP ensures a single source of truth with consistent data across both transactional and analytical workloads, avoiding data synchronization issues.

Performance: HTAP systems are optimized for both types of workloads, ensuring high performance for transactions and analytics. In-memory processing and advanced concurrency controls further enhance overall system performance.

Use Cases

HTAP systems are suitable for a variety of use cases, including:

Real-Time Fraud Detection: Financial institutions can detect and respond to fraudulent activities as they occur by analyzing transactional data in real-time.

Personalized Customer Experiences: E-commerce platforms can offer personalized recommendations and promotions based on real-time customer interactions.

Operational Intelligence: Businesses can monitor and analyze operational metrics in real-time to optimize processes and improve efficiency.

IoT Analytics: IoT systems can analyze sensor data in real-time to detect anomalies, predict maintenance needs, and optimize operations.

Challenges and Considerations of Implementing HTAP

Despite the advantages, HTAP systems also come with challenges:

Complexity: Designing and implementing HTAP systems can be complex, requiring careful consideration of data models, storage formats, and concurrency controls.

Cost: In-memory processing and high-performance storage can be expensive, particularly at scale.

Maturity: HTAP is a relatively new paradigm, and not all systems are mature or proven at large scales.

In conclusion, HTAP represents a significant shift in database architecture, aiming to provide seamless integration of transactional and analytical processing. By addressing the limitations of traditional systems, HTAP enables real-time analytics, simplified architecture, and consistent data management, making it a compelling choice for modern data-driven applications. That’s why we decided to implement HTAP storage for our service.

HTAP Storage Integration Strategies

To effectively implement an HTAP solution for our service, we need a well-planned integration strategy that leverages the strengths of both transactional and analytical processing. Here are the key steps and considerations for implementing an HTAP solution, applicable to any database system supporting HTAP:

Step 1: Select the Right Database System

Choose a database system that supports HTAP. Ensure the system can efficiently handle both row-based and column-based storage formats to optimize for OLTP and OLAP workloads.

How do we choose SQL Server? This topic requires an additional article. We will reveal our decision-making framework for selecting the right storage solution in the near future, so stay tuned. It’s also worth mentioning that we considered SQL Server and Snowflake as the two main candidates for this HTAP storage.

Step 2: Configure Storage Formats

Row-Based Storage: Utilize row-based storage for transactional data to ensure rapid write and read operations necessary for real-time updates and user interactions.
Column-Based Storage: Leverage column-based storage for analytical data to facilitate fast and efficient querying, aggregation, and analysis.

To optimize SQL Server for HTAP storage, it is essential to configure the storage formats appropriately. Utilize row-based storage for transactional data to ensure rapid write and read operations necessary for real-time traffic updates and user interactions. This approach follows a relational model ideal for maintaining data integrity and supporting transactional operations. This can be done in SQL Server using traditional tables, which store data row-wise.

For example:

CREATE TABLE Users (
UserID INT PRIMARY KEY,
UserName NVARCHAR(100) NOT NULL,
Email NVARCHAR(255) NOT NULL,
CreatedAt DATETIME DEFAULT GETDATE()
);
CREATE CLUSTERED INDEX idx_Users_UserID ON Users(UserID);

CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
UserID INT,
ProductID INT,
Quantity INT,
OrderDate DATETIME DEFAULT GETDATE(),
FOREIGN KEY (UserID) REFERENCES Users(UserID)
);
CREATE CLUSTERED INDEX idx_Orders_OrderID ON Orders(OrderID);

Leverage column-based storage for analytical data to facilitate fast and efficient querying, aggregation, and analysis of traffic patterns. This approach often uses a denormalized flat table model to optimize for read-heavy operations and complex queries. SQL Server provides columnstore indexes for this purpose, which store data in a column-wise format, optimized for analytical queries.

For example:

CREATE TABLE TrafficAnalysis (
AnalysisID INT,
RecordDate DATETIME,
Location NVARCHAR(100),
TrafficVolume INT,
AverageSpeed DECIMAL(5, 2),
IncidentCount INT
);
CREATE CLUSTERED COLUMNSTORE INDEX idx_TrafficAnalysis ON TrafficAnalysis;

Step 3: Enable In-Memory Capabilities

Utilize the in-memory processing features available in your chosen HTAP database system to speed up both transactional and analytical workloads. Ensure the database supports in-memory OLTP for high-speed transactional processing and in-memory column store indexes or similar features for real-time analytics.

You can achieve this in SQL Server using memory-optimized tables and natively compiled stored procedures.

Step 4: Implement Advanced Concurrency Controls

Use the database’s concurrency control mechanisms, such as multi-version concurrency control or snapshot isolation, to manage concurrent transactions and analytical queries without locking issues.
Optimize the configuration to balance throughput and consistency, ensuring that transactional and analytical workloads coexist without performance degradation.

You can implement these advanced concurrency controls in SQL Server using features like snapshot isolation. Additionally, SQL Server offers hints such as NOLOCK and READPAST to further manage concurrency. NOLOCK allows a query to read uncommitted data, reducing blocking but potentially returning dirty reads. READPAST skips locked rows, ensuring that a query does not get blocked by locked rows, which is useful for processing queues or tables with heavy concurrent transactions.

Step 5: Design Real-Time Dashboards

Develop interactive dashboards that can display real-time data within a custom-built web application tailored to the specific needs of your traffic monitoring and control service.
Ensure the dashboards are responsive, allowing users to apply filters, perform drill-downs, and see real-time updates based on the latest data.

We built a solid domain API and used the BFF (Backend for Frontend) approach for our web application. The API itself is quite advanced and deserves a separate article to cover its details.

Step 6: Maintain Data Consistency

Ensure a single source of truth by keeping the data consistent across transactional and analytical workloads.
Use the database’s transactional consistency features to maintain data integrity, ensuring that updates from OLTP operations are reflected accurately in OLAP queries.

All data from the regular data flow into the columnstore table is populated through database triggers. After being inserted into the row table as a single transaction, the data passes through a pipeline of business rules, where some measures are calculated on the fly to ensure data integrity.

Step 7: Optimize Performance

Implement indexing strategies that cater to transactional and analytical needs, such as clustered indexes for OLTP tables and non-clustered columnstore indexes in OLAP tables.
Enhance performance for both types of workloads by using query optimization techniques, such as query hints and execution plan tuning.

Next part provides all the details regarding performance optimization, and we will describe the implementation in more detail there.

Step 8: Monitor and Adjust

Continuously monitor system performance and workload patterns using the database’s monitoring tools.
Adjust configurations, indexing, and resource allocations to maintain optimal performance and meet changing workload demands.

SQL Server has robust monitoring mechanisms that can be leveraged for this purpose, such as SQL Server Profiler, Query Store, and more.

Conclusion

In this part of our case study on optimizing hybrid workloads with SQL Server column store indexes, we identified the need for a hybrid approach to handling our service’s diverse requirements.

Additionally, we highlighted the difficulties of using separate OLTP and OLAP systems simultaneously and explained our integration strategy for a Hybrid Transactional/Analytical Processing solution.

In the next part of this case study, we will explore implementing the Hybrid solution using SQL Server with column-store indexes. We will cover the technical steps, strategies for in-memory processing, concurrency control, data consumption, and performance optimization. Additionally, we will explain why SQL Server was chosen as the preferred platform for this implementation, highlighting its advantages and suitability for our specific requirements.

Stay tuned for a detailed analysis of the practical elements of this hybrid approach.