Benchmarking Databases for Real-Time Analytics Applications

Choosing the right database for analytics is hard. With many options available, each is optimized for different use cases.

Some databases are built for real-time analytics in customer-facing applications, where low-latency queries and high-ingest performance are essential. Others are designed for internal BI and reporting and optimized for large-scale aggregations and batch processing. Some databases are general-purpose, handling both transactions and analytics, while others specialize in analytical workloads.

Benchmarks can help—but only if they reflect your actual workload.

Several benchmarks, such as ClickBench, TPC-H, and TPC-DS, evaluate the performance of databases for analytics. However, they are not representative of real-time analytics.

To fill this gap, we’ve created RTABench, a new benchmark to assist developers in evaluating the performance of different databases in real-time analytics scenarios.

Key patterns in real-time analytics include:

• Multi-table Joins: Quickly combining data from several tables.

• Selective Filtering: Fast lookups for the most recent and specific data.

• Pre-aggregated Results: Using pre-calculated materialized views for prompt responses.

While denormalizing data can speed up queries, it complicates management and raises costs. Real-time applications favor normalized schemas and joining data at query time.

Introducing RTABench

To meet the need for real-time analytics workloads, we developed RTABench, a benchmark designed to test databases with these specific requirements. RTABench focuses on essential query patterns such as joins, filtering, and pre-aggregations.

How RTABench Works

RTABench models an order tracking system with normalized tables to mimic real-time analytics applications. It uses around 171 million order records and evaluates databases with 40 different queries, including basic counts, selective filtering, multi-table joins, and pre-aggregated queries.

RTABench categorizes databases into:

1. General-purpose databases: e.g., PostgreSQL and MySQL.

2. Real-time analytics databases: Optimized for quick insights, often secondary databases.

3. Batch analytics databases: Primarily for historical data and excluded from real-time benchmarks.

Benchmark Results: What We Learned

RTABench results are published at rtabench.com. While performance varies based on workload characteristics, this benchmark reveals some interesting insights:

1. General-purpose databases perform better on RTABench than on ClickBench. That’s expected—RTABench uses a normalized schema similar to real applications, while ClickBench is based on a denormalized dataset optimized for batch analytics.

2. TimescaleDB is 1.9x faster than ClickHouse on RTABench, even though it’s 6.8x slower on ClickBench. This is likely because TimescaleDB is optimized for real-time analytics applications, which often rely on normalized schemas and selective aggregations, while ClickHouse shines in denormalized, columnar analytics with large-scale aggregations.

3. Incremental materialized views offer massive speedups. They deliver up to hundreds or even thousands of times faster performance than querying the raw data (from seconds to a few milliseconds), demonstrating their value for real-time analytics. However, among the databases tested, only ClickHouse and TimescaleDB support them.

4. ClickHouse is the leader in data loading and storage efficiency. It’s 4.8x faster at loading data and uses 1.7x less disk than the next best database.

5. PostgreSQL was the fastest general-purpose database. The most popular database among developers demonstrates its versatility. With indexing, it’s only 4.1x slower than TimescaleDB on raw queries—but it can’t match the performance of incremental materialized views, which PostgreSQL doesn’t support.

Check out the full results here.

Community contributions to RTABench are encouraged! Whether it's adding new databases, improving existing queries, or making configuration tweaks, we hope you’ll contribute to the repo. All tools, datasets, and results are available on GitHub, with the latest version here.