Scaling AI and analytics means making data more accessible to everyone in the organization, but that also comes with growing costs. Every query your team runs against a cloud data platform eats into your budget, especially when dashboards refresh on a schedule, embedded analytics serve end users, or AI agents are constantly querying your data sources. And those prices can quickly spiral out of control. 

Understanding the actual cost of a single query becomes more important than ever. Whether you're building internal dashboards or powering a public-facing data app, every query to your data warehouse has a price, and cumulatively, they rack up a large bill. That’s why we put Cube Cloud to the test. Using a standardized benchmark, we compared the cost-per-query of Cube Cloud against direct queries to a modern cloud data platform with the cost-per-query model outlined in this [Select.dev blog post](https://select.dev/posts/cost-per-query#complete-sql-query). We'll show why Cube's compute model offers more predictable and lower costs per query, especially at high throughput.

## How Compute Pricing Works
Let’s break down how compute-based pricing works in both scenarios.

### 1. Modern Cloud Data Warehouses
Most modern platforms charge based on the following:

- __Compute time__: Measured in seconds or minutes, typically billed in "credits" or "slots."
- __Concurrency scaling__: Simultaneous users can spin up additional compute clusters.
- __Query complexity__: Longer or more complex queries consume more resources.
- __Data Scanned__: The amount of data read to execute the query.

So when 10 users hit a dashboard at once, you're not necessarily paying for 10 separate compute sessions but increasing total compute time. The cost-per-query can be estimated by pro-rating the warehouse cost over the number of queries completed during that time, as outlined in [Select.dev's cost-per-query model](https://select.dev/posts/cost-per-query#complete-sql-query). This is exactly how we calculated query costs in our benchmark, by measuring how many queries ran within a fixed window and then allocating the total compute cost proportionally.

To make the comparison fair and realistic, we also ran a separate [load test](https://cube.dev/blog/comparative-analysis-of-load-tests-on-cube-store-and-cloud-data-warehouse) to identify the maximum __QPS (queries per second)__ the data warehouse could sustain for this specific query type. This allowed us to optimize concurrency and throughput to minimize cost-per-query, a common practice in real-world production environments, where teams tune dashboards and batch queries to get the most out of their warehouse time.

### 2. Cube Cloud
Cube Cloud works differently:

- __Pre-aggregations__ and __Cube Store__ eliminate the need to hit the warehouse for most queries. However, building pre-aggregations could be costly since these long-running queries require considerable computing power.
- Cube costs are based on __API instance and worker compute usage__, not query volume or amount of data scanned.
- You're effectively __paying for throughput__, not for query complexity.

## Benchmarking Query Cost: Cube vs Modern Data Warehouse
We ran a benchmark using the industry-standard __TPC-H__ dataset at __scale factor 100__ to compare query cost predictability and performance. The test simulates a realistic dashboard scenario, with users issuing an increasing number of concurrent requests. Each query will contain a random set of parameters for the filters, ensuring we have a low cache-hit ratio to showcase the cost difference and be closer to a more realistic scenario.

![](https://ucarecdn.com/d0c57ed7-45ca-46c3-9e6c-45ff4f9fef92/)

Here's the exact SQL query used in the benchmark:
```sql
SELECT
  "part"."P_TYPE" "item_information__part_type",
  "region"."R_NAME" "item_information__region_name",
  "customer"."C_MKTSEGMENT" "item_information__market_segment",
  "lineitem"."L_SHIPMODE" "item_information__ship_mode",
  count(
    CAST(
      CONCAT(
        "lineitem"."L_ORDERKEY",
        "lineitem"."L_LINENUMBER"
      ) as TEXT
    ) || CAST("lineitem"."L_ORDERKEY" as TEXT)
  ) "item_information__count",
  sum("lineitem"."L_DISCOUNT") "item_information__total_discount",
  sum("lineitem"."L_EXTENDEDPRICE") "item_information__total_price",
  sum("lineitem"."L_EXTENDEDPRICE") - sum("lineitem"."L_DISCOUNT") - sum("lineitem"."L_TAX") "item_information__total_profit",
  sum("lineitem"."L_QUANTITY") "item_information__total_quantity",
  sum("lineitem"."L_EXTENDEDPRICE") - sum("lineitem"."L_DISCOUNT") "item_information__total_revenue"
FROM
  (
    SELECT *
    FROM TPCH_100.LINEITEM
    WHERE L_LINESTATUS = 'O' AND L_RETURNFLAG = 'N'
  ) AS "lineitem"
  LEFT JOIN "TPCH_100"."PART" AS "part" ON "lineitem"."L_PARTKEY" = "part"."P_PARTKEY"
  LEFT JOIN (
    SELECT *
    FROM TPCH_100.ORDERS
    WHERE O_ORDERSTATUS = 'O'
  ) AS "orders" ON "lineitem"."L_ORDERKEY" = "orders"."O_ORDERKEY"
  LEFT JOIN "TPCH_100"."CUSTOMER" AS "customer" ON "orders"."O_CUSTKEY" = "customer"."C_CUSTKEY"
  LEFT JOIN "TPCH_100"."NATION" AS "nation" ON "customer"."C_NATIONKEY" = "nation"."N_NATIONKEY"
  LEFT JOIN "TPCH_100"."REGION" AS "region" ON "nation"."N_REGIONKEY" = "region"."R_REGIONKEY"
WHERE
  "lineitem"."L_SHIPDATE" BETWEEN '1994-01-01' AND '1995-02-01'
  AND "part"."P_TYPE" = 'PROMO POLISHED NICKEL'
  AND "nation"."N_NAME" IS NOT NULL
  AND "region"."R_NAME" IS NOT NULL
  AND "lineitem"."L_RETURNFLAG" IS NOT NULL
GROUP BY 1, 2, 3, 4
ORDER BY 5 DESC
LIMIT 100
```

### How Cube Optimized This Query
In Cube, we modeled the same query using a __[pre-aggregation](https://cube.dev/docs/product/caching/using-pre-aggregations)__ that rolls up the relevant measures and dimensions. This reduces the complexity of the query and shifts heavy computation to the pre-aggregation layer.

Here's the pre-aggregation configuration used:
```sql
pre_aggregations:
  - name: item_rollup
    measures:
      - CUBE.total_discount
      - CUBE.total_price
      - CUBE.total_profit
      - CUBE.total_quantity
      - CUBE.total_revenue
      - CUBE.count
    dimensions:
      - CUBE.ship_mode
      - customer.market_segment
      - part.part_type
      - nation.nation_name
      - region.region_name
    allow_non_strict_date_range_match: true
    time_dimension: ship_date
    granularity: month
    partition_granularity: year
    refresh_key:
      every: 1 hour
```
__Note__: We assume a complete rebuild of this pre-aggregation once per hour, a common scenario in real-world systems, especially with slowly changing dimensions.

### Benchmark Setup

To make the comparison fairer, we ran the benchmark assuming you need a [Cube Cloud Production](https://cube.dev/docs/product/deployment/cloud/deployment-types#production-cluster) cluster turned on 24/7. That is to keep the pre-aggregations updated. Since there's a fixed cost of building pre-aggregations, there's a scenario when querying directly the Data Warehouse is cheaper than using pre-aggregations. This is a real-world consideration that has to be made when using Cube Cloud in production. When using Cube Cloud, you don't want to pre-aggregate everything; you should add pre-aggregations for queries with repeatable query patterns and a very low Cube's in-memory cache-hit ratio.  

Then, to size the Cube Cloud Production Cluster, we started with the minimum size of two API instances, and we scaled up every 100 queries to simulate the increased required throughput. When we reach the maximum of 10 API instances per Production Cluster, the next level of horizontal scaling will be using a [Cube Cloud Multi-Cluster deployment](https://cube.dev/docs/product/deployment/cloud/deployment-types#production-multi-cluster). That complicates the calculation a bit since sometimes having more Multi-Clusters than API instances is better. So, for this exercise, we will stick to regular Production Clusters and theoretically unlimited API instances.  Having a Cube Store Cluster turned on 24/7 costs [96 CCUs per day](https://cube.dev/docs/product/deployment/cloud/pricing#small-scale-deployment), and assuming a $0.3 price per credit, we get $28.8 per day, which only includes 2 API instances. There's a maximum of 10 API instances per cluster with a maximum theoretical throughput of 100 Queries Per Second; each additional API instance costs 1 CCU per hour. For the Data Warehouse the cost used was $2 per credit, using the same tier as Cube Cloud with no discounts.

That means that while a Data Warehouse could take hours to calculate 1000 queries, a Cube Cloud Production Cluster just needed 13 seconds on average to finish serving 1000 queries when scaled to 10 API instances. Read more about how Cube is consistently faster than modern Data Warehouses for OLAP queries.

### Benchmark Results
As expected at the beginning of the series, the initial cost of building pre-aggregations makes the cost per query more expensive in Cube Cloud. Still, as volume increases, the cost per query in Cube Cloud decreases drastically, while the cost per query in the Data Warehouse decreases at a slower rate.

![](https://ucarecdn.com/89b8ccd6-444d-4254-a00a-a84cf6a0fa5e/)

| Metric     | Cube Cloud with pre-aggregations     | Data Warehouse - no pre-aggregations     | Improvement - as % of change     |
| ---------- | ---------- | ---------- | ---------- |
| Avg Response Time - p50       | 1.3 seconds       | 40.7 seconds       | <mark>__-3,031%__</mark>       |
| Cost per Query at 10,000
queries per day.       | $0.0453       | $0.0528       | <mark>-14.19%</mark>       |
| Cost per Query at 20,000 
queries per day.       | $0.0230       | $0.0504       | <mark>-54.34%</mark>       |
| Cost per Query at 100,000 queries per day.       | $0.0052       | $0.0485       | <mark>-89.32%</mark>       |
| Cost per Query at 1,000,000 queries per day.       | $0.0012       | $0.0480       | <mark>-97.57%</mark>       |

Now, 1000 queries in a day is nothing in a real-world scenario. At one million queries per day, the cost per query in Cube Cloud is __$0.0012__, the cost per query in a Data Warehouse is __$0.0480__, even after increasing compute size to increase throughput. That's around __40 times cheaper__ to run your high-throughput workloads in Cube Cloud using pre-aggregations. In addition to that, running this type of query in Cube Cloud with pre-aggregations is __31 times faster__.

The main difference between the cost of both scenarios is that in the Data Warehouse, the cost grows proportionally with usage at a high rate. In Cube Cloud, there is a fixed cost for building pre-aggregations. The cost per query for using [Cube Store](https://cube.dev/docs/product/deployment#cube-store) is much lower than the cost per query in any modern Data Warehouse; in other words, Cube Store has excellent economies of scale compared to cloud data platforms.

![](https://ucarecdn.com/fd265ac3-50d5-496e-81b8-a519e3b818d0/)

The actual cost of running this experiment in Cube Cloud was no more than $200 per day; I extrapolated the results to consider having this deployment running 24/7, while the cost for the Data Warehouse was  $719.70, which was calculated using this [Select.dev blog post](https://select.dev/posts/cost-per-query#complete-sql-query). 

## Scale Smarter, Not Harder
Cube Cloud fundamentally changes the cost dynamics of querying data at scale by acting as a smart query acceleration layer. While cloud data platforms rack up charges as usage grows, Cube’s fixed-cost pre-aggregation model unlocks massive savings, especially once you cross into high-volume territory. Using pre-aggregations and in-memory stores, Cube can reduce your cost-per-query with performance and scale to match.

At 1 million queries per day, Cube is 40x cheaper and 31x faster than running the same queries directly on the warehouse. That’s not just better performance. That’s operational optimization. Now you can pay for throughput, instead of how complex a SQL query is. If you’re looking to make your analytics architecture future-ready with predictable costs and lightning-fast query times, it’s time to put Cube in the middle. [Contact sales](https://cube.dev/contact?utm_source=cube&utm_medium=blog&utm_content=query%20costs%20blog) to learn how Cube could reduce cloud data platform costs.


Paco Valdez

All Things Cube

Why Cube Cloud Makes Your Query Costs Predictable (and Lower)

In the dynamic world of [embedded analytics](https://cube.dev/use-cases/embedded-analytics), where end-users demand instantaneous results, the story of achieving sub-second query performance is not just a technical guideline, but a requirement for user satisfaction. The advanced capabilities of Cube Cloud can address the challenges of slow queries and build faster, more responsive reporting for your users. Take a look.

## The Challenge of Slow Queries: A Tale of Optimization
Imagine an analytics application, integral to a business where users are frustrated with charts that load slowly. The journey begins with understanding the root causes of these delays. Our guide identifies two primary scenarios: queries that are not pre-aggregated and those that are.

- **The Queue Graph and Low Concurrency:** In cases where queries are not pre-aggregated, we delve into the queue graph. This graph is a revealing tale of concurrency, where each bar represents a query. Sometimes,the reason for long-running queries is the queue creating bottlenecks. The solution? Increasing concurrency in Cube and the database, and considering the use of pre-aggregations. 

This is an example of very low concurrency (this example is set to 2 concurrent queries to the DB).

![Queue Graph](https://ucarecdn.com/2263acaf-055b-4e3a-bb1f-2384ad12a4d9/-/quality/smart_retina/-/format/auto/)

Each bar is a query. When the bar is solid it means it spent processing time in the DB. In this example, the first two queries in the queue are two long-running queries that hold the queue until they are done. You can see with arrows when the queries finished, a pending query starts executing.

- **Handling Pre-Aggregated Queries:** When queries are already pre-aggregated, the story takes a different turn. The focus shifts to the amount of data being queried and its granularity. For queries spanning over two months or multiple years, adjusting the granularity to days/weeks or weeks/months, respectively, becomes crucial. Additionally, managing the number of partitions and ensuring they are optimally sized (not exceeding 50 MBs for 2 cube store workers) is key. This might involve increasing the number of Cube Store Workers if you want to keep partition size large or reducing partition granularity and removing unused dimensions to decrease size of partition.

Lastly, please take into account that we recommend using at most five partitions with a single query. It’s ok to have more than five partitions built but querying more than five negatively affects performance. 

**Enhancing Performance with Indexes: The Next Chapter in Our Quest for Speed**
After navigating the intricacies of handling [pre-aggregated queries](https://cube.dev/docs/product/caching/getting-started-pre-aggregations), our journey towards achieving sub-second query performance introduces a new, powerful ally: the use of indexes. In the realm of Cube Cloud, indexes are not just tools, but catalysts that can significantly boost query speed. They act like signposts, guiding the system to retrieve data more efficiently and swiftly.

Incorporating indexes into our strategy comes with a clear directive. These indexes, when applied correctly, can drastically reduce the time taken to process queries, especially in scenarios where large datasets are involved. It's akin to finding a shortcut in a labyrinth; suddenly, paths that seemed long and winding are now quick and direct.

The art of using indexes effectively involves understanding which columns to index based on the queries frequently executed. This decision is not arbitrary but grounded in the patterns of data access and retrieval specific to each application.

For those embarking on this phase of optimization, Cube Cloud provides comprehensive guidance on harnessing the power of indexes to enhance query performance. By visiting [Using Indexes in Cube Cloud](https://cube.dev/docs/product/caching/using-pre-aggregations#using-indexes), developers and data engineers can unlock deeper insights into this crucial aspect of query optimization.

In summary, indexes stand as a testament to the continuous quest for performance excellence in the world of [embedded analytics](https://cube.dev/blog/modern-embedded-analytics). They represent the fine-tuning of an already robust system, ensuring that end users experience the joy of instant data visualization, making the wait for chart loading a relic of the past.

### Overcoming Pre-Aggregation Build Failures: A Path to Stability
Another chapter in our story addresses the challenges of pre-aggregation build failures. This issue often arises from resource constraints when multiple pre-aggregations or partitions are built simultaneously. 

The strategy here is to lower the [CUBEJS_SCHEDULED_REFRESH_CONCURRENCY](https://cube.dev/docs/reference/configuration/environment-variables#cubejs_scheduled_refresh_concurrency), thus reducing the concurrent building of pre-aggregations and partitions. Additionally, ensuring that queries hit the correct pre-aggregation and optimizing them based on memory limits and the EXPLAIN ANALYZE form crucial parts of the solution.

### Conclusion: A Story of Success
Transforming this guide into a narrative illustrates how Cube Cloud can be effectively utilized to achieve sub-second query performance in an embedded analytics environment. It's a journey that starts with identifying bottlenecks, extends through optimizing query processes and managing resources efficiently, and ends with providing users the swift, seamless experience they desire.

By following these guidelines, developers and data professionals can write their own success stories, turning slow, cumbersome analytics into a tale of speed and efficiency with Cube Cloud.


Data Engineering

Build faster, more responsive analytics for your customers with Cube Cloud

Reach Sub-Second Query Performance for Your Data App

About 6 months ago, we witnessed what Andy Pavlo [wittily characterised](https://twitter.com/andy_pavlo/status/1459251061085118465) as an "old school database benchmark gang war". The timeline of events goes like this:

* [Databricks](https://databricks.com), a data lakehouse company founded by the creators of Apache Spark, published a [blog post](https://databricks.com/blog/2021/11/02/databricks-sets-official-data-warehousing-performance-record.html) claiming that it set a new data warehousing performance record in 100 TB [TPC-DS benchmark](https://www.tpc.org/tpcds/). It was also mentioned that Databricks was 2.7x faster and 12x better in terms of price performance compared to Snowflake.
* [Snowflake](https://snowflake.com), a data warehousing company founded by ex-Oracle and ex-VectorWise experts, responded with a [blog post](https://www.snowflake.com/blog/industry-benchmarks-and-competing-with-integrity/) that critically reviewed Databricks' findings, reported different results for the same benchmark, and claimed comparable price/performance to Databricks.
* After two days, Databricks followed up with another [blog post](https://databricks.com/blog/2021/11/15/snowflake-claims-similar-price-performance-to-databricks-but-not-so-fast.html) that confirmed that they stand by the initially reported results.

While this back-and-forth between respected data vendors was informative and amusing, its collateral damage was even more substantial. Databricks shared that they have [eliminated the anti-competitive *DeWitt clause*](https://databricks.com/blog/2021/11/08/eliminating-the-dewitt-clause-for-database-benchmarking.html) from their service terms. Snowflake followed up with a [similar update](https://www.snowflake.com/blog/industry-benchmarks-and-competing-with-integrity/) to their acceptable use policy.

But what is the *DeWitt clause*?

More importantly, why would you need to know if a DeWitt clause is included in terms of service in case you'd like to do and publish a performance benchmark? Let's explore.

## Backstory of the DeWitt Clause

In 1982, at the very inception of relational databases, [David DeWitt](https://www.linkedin.com/in/david-dewitt-30189b50/), a researcher at the Department of Computer Sciences at the University of Wisconsin-Madison, was working on measuring database performance. His team wrote the Wisconsin Benchmark, tested a number of databases, and [published the results](https://pages.cs.wisc.edu/~dewitt/includes/benchmarking/vldb83.pdf).

Apparently, some folks were displeased with the results. [Michael Stonebraker](https://en.wikipedia.org/wiki/Michael_Stonebraker), a researcher at the UC Berkeley and co-creator of Ingres, called DeWitt and expressed how upset he was. However, they remained friends anyway as DeWitt recounts: in his [talk on YouTube](https://www.youtube.com/watch?v=-TIUGC4X2q8&t=420s):

<iframe width="800" height="500" src="https://www.youtube.com/embed/-TIUGC4X2q8?start=420" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>

Oracle also didn’t perform very well and reacted hastily. [According to DeWitt](https://www.eweek.com/development/db-test-pioneer-makes-history/), Oracle CEO Larry Ellison was so displeased that he called the department chair and insisted, "You have to fire this guy.” Oracle also inserted a clause in their terms of use that boiled down to the fact that one can’t publish benchmarks without getting an explicit approval from Oracle.

## DeWitt Clause vs. DeWitt Embrace Clause

Nowadays, the DeWitt clause is a common provision in end-user license agreements for proprietary software that prevents from publishing information about the software without an explicit approval from the vendor. Over time, it have become widespread in the software industry and its implications are drastic:

* People avoid publishing benchmarks about software products with the DeWitt clause.
* Moreover, some won’t even bother doing a benchmark knowing they won’t be able to publish the results.

There are debates whether DeWitt clauses are [moral or legal](https://www.dwheeler.com/essays/dewitt-clause.html) because of the implications above as well as the overall corrosive effect they have on the industry. Once one supplier adds a DeWitt clause, the others can feel that they are at a disadvantage without one. We're lucky it can also work the other way round, like in the Databricks and Snowflake case. However, open source software (OSS) licenses don’t and can't have DeWitt clauses, meaning that the OSS can be legally critiqued, but not their proprietary competitors.

At the same time, some vendors do quite the opposite to the inclusion of a DeWitt clause. Instead, they change their license to the DeWitt Embrace Clause meaning that if you disclose benchmark results for them, you automatically allow them to do the same with you. In a way, DeWitt Embrace Clause is like copyleft for the DeWitt Clause. 

## Can You Benchmark %DATABASE%?

Now, do you wonder whether some vendor has or hasn't a DeWitt clause?

Shortly after the Databricks and Snowflake debate, [Mark Callaghan](https://twitter.com/MarkCallaghanDB), an ex-MongoDB and ex-Rockset database expert, checked if some database and public cloud vendors have DeWitt clauses in their terms of use.

In this blog post, I try to extend [his research](http://smalldatum.blogspot.com/2021/11/dewitt-clause-vs-public-cloud.html) and maintain an ever-green list of database and similar vendors with regard to the DeWitt clause. (If you spot any inaccuracy, which is totally possible, please get in touch via [igor@cube.dev](mailto:igor@cube.dev?subject=DeWitt), I'll be happy to update the post.)

*Last updated: June 2, 2022.*

### 😇 Open-source vendors (without the DeWitt clause)

Open source licenses usually grant users permission to [use open source software for any purpose](https://opensource.org/docs/osd). So, by definition, open source software can't contain DeWitt clauses (because if it did, it wouldn't be open source).

Yes, you can benchmark any of these tools:

* Apache [Drill](https://github.com/apache/drill), [Druid](https://github.com/apache/druid/), [Flink](https://github.com/apache/flink), [Hive](https://github.com/apache/hive), [Kafka](https://kafka.apache.org), [Spark](https://github.com/apache/spark)
* [appwrite](https://github.com/appwrite/appwrite)
* [ClickHouse](https://github.com/ClickHouse/ClickHouse) (they even maintain a [list of benchmarks](https://github.com/ClickHouse/ClickHouse/issues/22398) on GitHub)
* [CockroachDB](https://github.com/cockroachdb/cockroach) (licensed under BSL, not [OSI-approved](https://opensource.org/licenses/alphabetical))
* [Dremio](https://github.com/dremio/dremio-oss)
* [Elasticsearch](https://github.com/elastic/elasticsearch) (licensed under SSPL, not OSI-approved)
* [ksqlDB](https://ksqldb.io)
* [Materialize](https://github.com/MaterializeInc/materialize) (licensed under BSL, not OSI-approved)
* [MongoDB](https://github.com/mongodb/mongo) (licensed under SSPL, not OSI-approved)
* [PostgreSQL](https://www.postgresql.org)
* [Presto](https://github.com/prestodb/presto)
* [Redpanda](https://github.com/redpanda-data/redpanda) (licensed under BSL, not OSI-approved)
* [QuestDB](https://github.com/questdb/questdb)
* [TimescaleDB](https://github.com/timescale/timescaledb)
* [Trino](https://github.com/trinodb/trino)

Also, some open-source vendors collaboratively maintain benchmarking suites such as [Time Series Benchmark Suite](https://github.com/timescale/tsbs) to help choose the best tools for particular workloads.

### 😇 Vendors without the DeWitt clause

Cloud vendors can restrict you from benchmarking their services but sometimes they choose not to have the DeWitt clause.

Yes, you can benchmark any of these services:

* [Ahana Cloud](https://ahana.io/terms-of-service/)
* [Altinity Cloud](https://altinity.com/legal/)
* [appwrite Cloud](https://appwrite.io) (not launched to GA yet)
* [ClickHouse Cloud](https://clickhouse.com/cloud/) (not launched to GA yet)
* [Confluent Cloud](https://assets.confluent.io/m/103033bdcb9b3fcd/original/20220201-Confluent_Cloud_Services_Agreement.pdf)
* [Firebolt](https://d7umqicpi7263.cloudfront.net/eula/product/28a79f78-8008-4c02-a971-54ef6d8ed904/898eb1bc-7c2a-4829-b479-67f5a18a51ff.pdf)
* [Materialize Cloud](https://materialize.com/terms-and-conditions/)
* [MongoDB Cloud](https://www.mongodb.com/cloud-terms-and-conditions)
* [QuestDB Cloud](https://questdb.io/terms/) (not launched to GA yet)
* [Redpanda Cloud](https://redpanda.com/cloud/)
* [Snowflake](https://www.snowflake.com/legal/acceptable-use-policy/)
* [Starburst Galaxy](https://www.starburst.io/terms/galaxy-terms-of-service/)
* [Supabase](https://supabase.com/docs/company/terms)
* [Timescale Cloud](https://www.timescale.com/legal/timescale-cloud-terms-of-service)

However, many of these cloud vendors disallow *abusive benchmarking* in their acceptable use policies. So be careful and please don't get banned.

### 🙃 Vendors with the DeWitt Embrace clause

Some cloud vendors permit you to benchmark their service but require reciprocity: you must make the benchmark reproducible and allow benchmarking of your own service or tool in response.

Yes, you can benchmark any of these services and probably get away with it:

* [Amazon Web Services](https://aws.amazon.com/service-terms/), including [Athena](https://aws.amazon.com/athena/), [Aurora](https://aws.amazon.com/rds/aurora/), and [Redshift](https://aws.amazon.com/redshift/). *You may perform benchmarks or comparative tests or evaluations (each, a “Benchmark”) of the Services. If you perform or disclose, or direct or permit any third party to perform or disclose, any Benchmark of any of the Services, you (i) will include in any disclosure, and will disclose to us, all information necessary to replicate such Benchmark, and (ii) agree that we may perform and disclose the results of Benchmarks of your products or services, irrespective of any restrictions on Benchmarks in the terms governing your products or services.*
* [CockroachDB Serverless](https://www.cockroachlabs.com/acceptable-use-policy/). *You can only disclose benchmarking tests if you (i) provide us the necessary information to replicate the tests, (ii) disclose the methodology for the benchmarking tests along with the results, and (iii) allow us to run our own benchmarking tests against your products and services at our discretion.*
* [Databricks](https://databricks.com/blog/2021/11/08/eliminating-the-dewitt-clause-for-database-benchmarking.html). *You may perform benchmarks or comparative tests or evaluations (each, a “Benchmark”) of the Platform Services and may disclose the results of the Benchmark other than for Beta Services. If you perform or disclose, or direct or permit any third party to perform or disclose, any Benchmark of any of the Platform Services, you (i) will include in any disclosure, and will disclose to us, all information necessary to replicate such Benchmark, and (ii) agree that we may perform and disclose the results of Benchmarks of your products or services, irrespective of any restrictions on Benchmarks in the terms governing your products or services.*
* [SingleStore](https://www.singlestore.com/blog/eliminating-the-dewitt-clause-for-greater-transparency-in-benchmarking/). *Customer may perform industry standard benchmarks, comparative tests or evaluations (each, a "Performance Report") of the Managed Service. If Customer performs or discloses, or directs or permits any third party to perform or disclose, any Performance Report of any of the Managed Service, Customer (i) will include in any disclosure, and will disclose to SingleStore all information necessary to replicate such Performance Report and the data from the Performance Report, and (il) agree that SingleStore may perform and disclose the results of the Performance Report of Customer's products or services, irrespective of any restrictions on Performance Report.*

### 🤬 Vendors with the DeWitt clause

These vendors chose to restrict benchmarks with DeWitt clauses.

No, you can't benchmark any of these tools and get away with it:

* [Cloudera Data Platform](https://www.cloudera.com/legal/terms-and-conditions/cloudera-online-services-terms.html). *Restrictions. Customer may not: [...] access or use the Services for purposes of monitoring availability, performance or functionality of the Services, or for any benchmarking or competitive purposes.*
* [Decodable](https://www.decodable.co/legal/tos). *Restrictions. Customer may not, and may not cause or permit others to: [...] disclose results of any benchmark tests or performance tests of the Decodable services hereunder without Decodable’s prior written consent.*
* [Dremio Cloud](https://www.dremio.com/legal/terms-of-service/). *Restrictions. Customer shall not, and shall not cause or allow any Authorized User or third party to: [...] except with Dremio’s prior written permission, publish any performance or benchmark tests or analysis relating to Dremio Cloud.*
* [Elastic Cloud](https://www.elastic.co/agreements/global/cloud-services-monthly). *Restrictions [...] You shall not: [...] access or use any Cloud Service for purposes of monitoring its availability, performance or functionality, or for any other benchmarking or competitive purposes, including, without limitation, for the purpose of designing and/or developing any competitive services.*
* [Google Cloud Platform](https://cloud.google.com/terms/service-terms), including [BigQuery](https://cloud.google.com/bigquery) & [Firebase](https://firebase.google.com). *Benchmarking. Customer may conduct benchmark tests of the Services (each a "Test"). Customer may only publicly disclose the results of such Tests if it (a) obtains Google's prior written consent, (b) provides Google all necessary information to replicate the Tests, and (c) allows Google to conduct benchmark tests of Customer's publicly available products or services and publicly disclose the results of such tests. Notwithstanding the foregoing, Customer may not do either of the following on behalf of a hyperscale public cloud provider without Google's prior written consent: (i) conduct (directly or through a third party) any Test of the Services or (ii) disclose the results of any such Test.*
* [InfluxDB Cloud](https://www.influxdata.com/legal/terms-of-use/). *Restrictions. Customer will not: [...] publish or provide any benchmark, comparison or performance test results.*
* [MariaDB SkySQL](https://legal.mariadb.com/agreements/skysql/SkySQL_Terms_of_Service_V2_2020-12-16.pdf) and [MariaDB Xpand](https://legal.mariadb.com/agreements/xpand/xpand-license-terms-v2-2021-02-24.pdf). *Benchmarking. Customer may perform benchmarks or comparative tests or evaluations (each, a "Benchmark") of the SkySQL Services. However, Customer must obtain MariaDB's prior written approval to disclose to a third party the results of any Benchmark of the SkySQL Services.*
* [Microsoft SQL Server](https://www.microsoft.com/en-us/sql-server/). *BENCHMARK TESTING. You must obtain Microsoft’s prior written approval to disclose to a third party the results of any benchmark test of the software.*
* [Oracle](https://www.oracle.com/us/corporate/contracts/lic-online-toma-ph-de-eng-2817945.pdf). *RESTRICTIONS. [...] You may not: [...] disclose results of any Program benchmark tests without Oracle's prior written consent.*
* [PlanetScale](https://planetscale.com/legal/aup). *Customer will not, and will ensure that its End Users do not: [...] access any portion of the Product for the purpose of building a similar or competitive product or service, or monitor the Product for any benchmarking or competitive purpose.*

### 😶 Vendors for which the author didn't find the data

I wasn't able to identify where a few vendors stand with regard to the DeWitt clause:

* [MySQL HeatWave](https://www.mysql.com/cloud/)
* [Yellowbrick Data](https://www.yellowbrick.com/)

If you can provide any pointers, please get in touch via [igor@cube.dev](mailto:igor@cube.dev?subject=DeWitt), I'll be happy to update the post. Also, please reach out if you'd like more databases to be featured here.

## What about Cube?

[Cube](https://cube.dev) is an open-source [headless BI](https://cube.dev/blog/headless-bi) platform that [integrates with a lot of data sources](https://cube.dev/docs/config/databases): cloud data warehouses, query engines, and databases. Many of them are designed for low latency and some of them can provide high concurrency. However, sometimes they don't; you can read our [blog post](https://cube.dev/blog/data-warehouse-performance-and-how-cube-can-help) about that.

In any case, Cube will have no problem delivering your data, with sub-second latency and high concurrency, to any [data consumer](https://cube.dev/docs/config/downstream) out there: BI tools, data notebooks, front-end apps, etc. Thanks to [Cube Store](https://cube.dev/blog/introducing-cubestore), Cube's REST, GraphQL, and SQL APIs are able to respond to any request within 200-300 milliseconds and allow for 100+ concurrent requests—and that can scale.

Please explore how Cube integrates with [databases and other tools](https://cube.dev/blog/category/data) in the data space in our blog. Also, don't hesitate to try [Cube Cloud](https://cubecloud.dev/auth/signup), join our large [Slack community](http://slack.cube.dev), and give Cube a [star on GitHub](https://github.com/cube-js/cube.js) 🌟

Igor Lukanin

Performance

All database vendors who will either praise or punish you for doing a benchmark as well as an entertaining digression into the recent history of computer science

DeWitt clause, or Can you benchmark a database and get away with it

Adnan Rahic

Cloud Data Warehouses (CDW)

Performance capabilities of data warehouses and how Cube can help

Today, with the generally available release of Cube Store, Cube.js is fully ready to provide operational analytics for any SQL-compliant database, data warehouse, or query engine.

You can use open-source [Cube.js](https://github.com/cube-js/cube.js) and [Cube Store](https://github.com/cube-js/cube.js/tree/master/rust) to build an analytical API that will respond to highly concurrent requests from your application with sub-second latency regardless of data volume or workload. Even if you have a billion-scale dataset and thousands of concurrent users, Cube.js and Cube Store will get you covered.

Cube Store, the custom pre-aggregation storage layer for Cube.js, is now generally available and strongly recommended for use in production. Let's explore the motivation and design decisions behind Cube Store and learn more about the benefits of using Cube Store for new and existing Cube.js users.

## Why Cube Store?

Cube.js is an open-source analytical API platform that allows developers to build an analytical API and a semantic layer based on data schema over any SQL-compliant data store. In the simplest scenario, Cube.js uses in-memory cache and query queue to provide better performance than the data store is able to deliver. However, it's usually the least scalable and cost-effective solution, so it's not recommended for production.

![cube store-04-3-03.png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/e8dbf9eb-5ce2-4b3f-ac44-2954ef4f6824.png)

**In a real-world scenario, Cube.js speeds up analytical queries using pre-aggregations.** These are the condensed representations of source data that are pre-computed, partitioned, stored, and refreshed in a way that allows for super-fast execution of select queries.

Historically, pre-aggregations were stored *internally*, alongside the source data in a database (e.g., PostgreSQL or MySQL), or *externally*, in a custom-provisioned instance of PostgreSQL or MySQL, as the only feasible option for read-only or cost-ineffective data sources (e.g., AWS Athena or BigQuery). Usually, they will be renewed asyncronously by a dedicated refresh worker instance. It's an acceptable solution, however, the pre-aggregation database becomes a bottleneck and limits the scalability of the analytical API.

![cube store-04-2-03.png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/ac22387e-7c1c-4c70-9a35-430f789a7cfb.png)

**Pre-aggregations in traditional databases often won't allow for high concurrency and low latency of the analytical API.** They also were the source of frustration due to poor performance in important use cases (e.g., with high cardinality of pre-aggregated data, with partitioned data, with joins across several databases), unsupported features (e.g., approximate distinct counts via HyperLogLog algorithm), or implementation details (e.g., support for long table names or support for various SQL types).

**Cube Store is designed to resolve these issues and provide a performant pre-aggregation storage layer for Cube.js.** It's a drop-in replacement for any currently used pre-aggregation storage and the go-to solution for new Cube.js apps. Being a distributed data store itself, Cube Store can be scaled horizontally with ease and predictability. 

![cube store-04-1-03.png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/4f6d753e-77d2-4d46-964f-8d2512ff9a18.png)

**From now on, Cube Store is the recommended pre-aggregation storage layer for production use.** It doesn't have the limitations of other databases used to store pre-aggregations. On top of that, Cube Store provides guaranteed high concurrency and sub-second latency, performance optimizations for analytical queries, and additional features such as data federation via cross-database joins. You'll get these advantages of Cube Store regardless of your data source and the data volume.

![cube store-01-03.png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/67ac0250-4bda-4028-ae56-ec23324bcebf.png)

## How Cube Store works

Cube Store uses a well-known and proven distributed query engine architecture. In every Cube Store cluster:

- a single *router* node handles incoming connections, manages database metadata, builds query plans, and orchestrates their execution
- multiple *worker* nodes ingest warmed up data and execute queries in parallel
- a local or cloud-based blob storage keeps pre-aggregated data in columnar format

![cube store-03-03.png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/db0e1aeb-3101-4280-b4a4-902e21bcd9a0.png)

**Cube Store was designed with performance in mind.** It's written entirely in Rust to leverage low-level optimizations, effectively making Rust the top-1 language by lines of code in Cube.js codebase. Cube Store allows for almost unlimited horizontal scalability by increasing the number of worker nodes. It stores pre-aggregated data in Parquet format which was deliberately chosen for its efficiency and performance.

**Cube Store is based on well-known open-source building blocks.** Together with [Parquet](https://en.wikipedia.org/wiki/Apache_Parquet), Cube Store uses [Apache Arrow](https://github.com/apache/arrow) for its efficient in-memory data structures and [DataFusion](https://github.com/apache/arrow-datafusion) as a query execution framework.

**Cube Store has been used in [Cube Cloud](https://cube.dev/cloud/) for more than 6 months.** A few select early access users have been exposed to Cube Store in Cube Cloud and experienced substantial performance improvements thanks to Cube Store.

## How to use Cube Store

Cube Store is ready to be used while developing new Cube.js applications and also as a drop-in replacement for the pre-aggregation storage layer in existing Cube.js apps.

**Development.** When Cube.js is run in [development mode](https://cube.dev/docs/configuration/overview#development-mode), a single-node in-process instance of Cube Store is enabled. It requires zero configuration and allows to instantly use the pre-aggregations. All you need is to mark them as *external*:

```jsx
cube(`Cube`, {
  sql: `select * from table`,

  preAggregations: {
    main: {
      type: `rollup`,
      external: true,

      // ...
    },
  },
});
```

Cube.js will build pre-aggregations and store them in Cube Store. You can review the console output of Cube.js to see if queries are served using pre-aggregations. You can also see matched rollups on the Cache tab in Cube.js Developer Playground available at [localhost:4000](http://localhost:4000).

**Production.** A Cube Store cluster should be run in production and consist of a single router instance and several worker instances. There's an official `cubejs/cubestore` [Docker image](https://hub.docker.com/r/cubejs/cubestore) as well as a [Docker Compose template](https://cube.dev/docs/caching/using-pre-aggregations#running-in-production) you can use as a starting point.

Just like Cube.js, Cube Store can be configured via [environment variables](https://cube.dev/docs/reference/environment-variables#cube-store), e.g., Cube Store nodes assume the worker role when `CUBESTORE_WORKER_PORT` is set and ingest pre-aggregated data from the blob storage specified with `CUBESTORE_GCS_BUCKET` or `CUBESTORE_S3_BUCKET`.

**Benchmarks.** Depending on the currently used pre-aggregation storage and the data volume, after switching to Cube Store, you can expect your API performance to stay at least at the same level or increase substantially. You can review and use a [benchmark template](https://github.com/cube-js/cube.js/tree/master/examples/cubestore-benchmark) leveraging [k6](https://k6.io), an open-source load testing tool, to do your own measurements prior to switching to Cube Store.

![cube store-02-03.png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/13a9bde1-5ddb-4b09-a7b1-d683acaaa42e.png)

## Next steps

**Here at the Cube.js team, we wholeheartedly encourage you to use Cube Store in development and consider migrating your production deployments to Cube Store as well.**

Please review the [release notes](https://forum.cube.dev/t/v0-27-release-notes/128) and [documentation](https://cube.dev/docs/caching/using-pre-aggregations#pre-aggregations-storage), share your experience in the *#cube-store* channel in [Slack](https://slack.cube.dev) or post to [Discourse](https://forum.cube.dev), and don't hesitate to file Cube Store-related issues on [GitHub](https://github.com/cube-js/cube.js/issues).

Also, feel free to sign up for the waiting list of [Cube Cloud](https://cube.dev/cloud/) which provides a fully managed Cube Store cluster for all deployments. Cube Cloud is currently in early access but will be generally available later this year.

News

Introducing Cube Store: High concurrency and sub-second latency for any database

_This is an advanced tutorial. If you are just getting started with Cube, I recommend checking [this tutorial](https://cube.dev/blog/cubejs-open-source-dashboard-framework-ultimate-guide/) first and then coming back here._

One of the most powerful features of [Cube](https://github.com/statsbotco/cube.js) is pre-aggregations. Coupled with [data schema,](https://cube.dev/docs/guides/recipes/code-reusability/schema-generation) it eliminates the need to organize, denormalize, and transform data before using it with Cube. The pre-aggregation engine builds a layer of aggregated data in your database during the runtime and maintains it to be up-to-date.

![pre-aggregations-schema.png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/25210eb0-4a22-4c92-aa42-8d29214ba326.png)

Upon an incoming request, Cube will first look for a relevant pre-aggregation. If it cannot find any, it will build a new one. Once the pre-aggregation is built, all the subsequent requests will go to the pre-aggregated layer instead of hitting the raw data. It could speed the response time by hundreds or even thousands of times.

Pre-aggregations are materialized query results persisted as tables. In order to start using pre-aggregations, Cube should have write access to the `stb_pre_aggregations` schema where pre-aggregation tables will be stored. 

Cube also takes care of keeping the pre-aggregation up-to-date. It performs refresh checks and if it finds that a pre-aggregation is outdated, it schedules a refresh in the background.

## Creating a Simple Pre-Aggregation

Let’s take a look at the example of how we can use pre-aggregations to improve query performance.

For testing purposes, we will use a Postgres database and will generate around ten million records using the [generate_series](https://www.postgresql.org/docs/11/functions-srf.html) function. 

```bash
$ createdb cubejs_test
```

The following SQL creates a table, `orders`, and inserts a sample of generated records into it.

```sql
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  amount integer,
  created_at timestamp without time zone
);
CREATE INDEX orders_created_at_amount ON orders(created_at, amount);

INSERT INTO orders (created_at, amount)
SELECT 
  created_at, 
  floor((1000 + 500*random())*log(row_number() over())) as amount 
FROM generate_series
  ( '1997-01-01'::date
  , '2017-12-31'::date
  , '1 minutes'::interval) created_at
```

Next, create a new Cube application if you don’t have any.

```bash
$ npm install -g cube.js
$ cubejs create test-app -d postgres
```

Change the content of  `.env` in the project folder to the following.

```bash
CUBEJS_API_SECRET=SECRET
CUBEJS_DB_TYPE=postgres
CUBEJS_DB_NAME=cubejs_test
```

Finally, generate a schema for the `orders` table and start the Cube.js server.

```bash
$  cubejs generate -t orders
$  npm run dev
```

Now, we can send a query to Cube with the `Orders.count` measure and `Orders.createdAt` time dimension with granularity set to `month`. 

```bash
curl \
 -H "Authorization: EXAMPLE-API-TOKEN" \
 -G \
 --data-urlencode 'query={
  "measures" : ["Orders.amount"],
  "timeDimensions":[{
    "dimension": "Orders.createdAt", 
    "granularity": "month", 
    "dateRange": ["1997-01-01", "2017-01-01"]
  }]
}' \
 http://localhost:4000/cubejs-api/v1/load
```

Cube will respond with [Continue wait](https://cube.dev/docs/rest-api#prerequisites-continue-wait), because this query takes more than 5 seconds to process. Let’s look at Cube logs to see exactly how long it took for our Postgres to execute this query.

```bash
Performing query completed:
{  
   "queueSize":2,
   "duration":6514,
   "queryKey":[  
      "
        SELECT   
          date_trunc('month', (orders.created_at::timestamptz at time zone 'UTC'))            \"orders.created_at_month\",
          sum(orders.amount) \"orders.amount\" 
        FROM     
            public.orders AS orders 
        WHERE ( 
          orders.created_at >= $1::timestamptz 
          AND   orders.created_at <= $2::timestamptz
        ) 
        GROUP BY 1 
        ORDER BY 1 ASC limit 10000
      ",
      [  
         "2000-01-01T00:00:00Z",
         "2017-01-01T23:59:59Z"
      ],
      []
   ]
}
```

It took 6,514 milliseconds (6.5 seconds) for Postgres to execute the above query. Although we have an index on the `created_at` and `amount` columns, it doesn't help a lot in this particular case since we're querying almost all the dates we have. The index would help if we query a smaller date range, but still, it would be a matter of seconds, not milliseconds.

We can significantly speed it up by adding a pre-aggregation layer. To do this, add the following `preAggregations` block to `src/Orders.js`:

```javascript
  preAggregations: {
    amountByCreated: {
      type: `rollup`,
      measureReferences: [amount],
      timeDimensionReference: createdAt,
      granularity: `month`
    }
  }
```

The block above instructs Cube to build and use a [rollup type](https://cube.dev/docs/pre-aggregations#rollup) of pre-aggregation when the “Orders.amount” measure and “Orders.createdAt” time dimension (with “month” granularity) are requested together. You can read more about pre-aggregation options in [the documentation reference.](https://cube.dev/docs/pre-aggregations#top)

Now, once we send the same request, Cube will detect the pre-aggregation declaration and will start building it. Once it's built, it will query it and send the result back. All the subsequent queries will go to the pre-aggregation layer.

Here is how querying pre-aggregation looks in the Cube logs: 

```bash
Performing query completed: 
{  
   "queueSize":1,
   "duration":5,
   "queryKey":[  
      "
        SELECT 
          \"orders.created_at_month\" \"orders.created_at_month\", 
          sum(\"orders.amount\") \"orders.amount\" 
        FROM 
          stb_pre_aggregations.orders_amount_by_created
        WHERE (
          \"orders.created_at_month\" >= ($1::timestamptz::timestamptz AT TIME ZONE 'UTC') 
          AND 
          \"orders.created_at_month\" <= ($2::timestamptz::timestamptz AT TIME ZONE 'UTC')
        )
        GROUP BY 1 ORDER BY 1 ASC LIMIT 10000
      ",
      [  
         "1995-01-01T00:00:00Z",
         "2017-01-01T23:59:59Z"
      ],
      [
        [
          "
            CREATE TABLE 
                stb_pre_aggregations.orders_amount_by_created 
            AS SELECT
                date_trunc('month', (orders.created_at::timestamptz AT TIME ZONE 'UTC')) \"orders.created_at_month\", 
                sum(orders.amount) \"orders.amount\"
            FROM
                public.orders AS orders  
            GROUP BY 1
          ",
          []
        ]
      ]
   ]
}

```

As you can see, now it takes only 5 milliseconds (1,300 times faster) to get the same data. 
Also, you can note that SQL has been changed and now it queries data from `stb_pre_aggregations.orders_amount_by_created`, which is the table generated by Cube to store pre-aggregation for this query. The second query is a DDL statement for this pre-aggregation table.

## Pre-Aggregations Refresh

Cube also takes care of keeping pre-aggregations up to date.  Every two minutes on a new request Cube will initiate the refresh check.

You can set up a custom refresh check strategy by using [refreshKey](https://cube.dev/docs/pre-aggregations#refresh-key). By default, pre-aggregations are refreshed every hour.

If the result of the refresh check is different from the last one, Cube will initiate the rebuild of the pre-aggregation in the background and then hot swap the old one.

## Next Steps

This guide is the first step to learning about pre-aggregations and how to start using them in your project. But there is much more you can do with them. [You can find the pre-aggregations documentation reference here.](https://cube.dev/docs/pre-aggregations#top)

Also, here are some highlights with useful links to help you along the way.

### Pre-aggregate queries across multiple cubes

Pre-aggregations work not only for measures and dimensions inside the single cube, but also across multiple joined cubes as well. If you have [joined cubes,](https://cube.dev/docs/joins) you can reference measures and dimensions from any part of the join tree. The example below shows how the `Users.country` dimension can be used with the `Orders.count` and `Orders.revenue` measures.

```javascript
cube(`Orders`, {
  sql: `select * from orders`,

  joins: {
    Users: {
      relationship: `belongsTo`,
      sql: `${CUBE}.user_id = ${Users}.id`
    }
  },

  // …

  preAggregations: {
    categoryAndDate: {
      type: `rollup`,
      measureReferences: [count, revenue],
      dimensionReferences: [Users.country],
      timeDimensionReference: createdAt,
      granularity: `day`
    }
  }
});
```

### Generate pre-aggregations dynamically

Since pre-aggregations are part of the data schema, which is basically a Javascript code, you can dynamically create all the required pre-aggregations. [This guide](https://cube.dev/docs/schema-generation) covers how you can dynamically generate a Cube schema.

### Time partitioning

You can instruct Cube to partition pre-aggregations by time using the `partitionGranularity` option. Cube will generate not a single table for the whole pre-aggregation, but a set of smaller tables. It can reduce the refresh time and cost in the case of BigQuery for example.

[Time partitioning documentation reference.](https://cube.dev/docs/pre-aggregations#rollup-time-partitioning)

```javascript
 preAggregations: {
    categoryAndDate: {
      type: `rollup`,
      measureReferences: [count],
      timeDimensionReference: createdAt,
      granularity: `day`,
      partitionGranularity: `month`
    }
  }
```

Artyom Keydunov

Optimize Cube Performance with Pre-Aggregations

It’s well known that doing analytics on production RDBMS instances is prohibitive due to lock and performance issues. The introduction of replication by many of the modern RDBMSs made this process a little bit easier, but not ideal. The next evolution step here is Serverless RDBMSs. Splitting processing power from the storage brings our understanding of how to work with a RDBMS to a new level. This gives us an opportunity for infinite DB infrastructure scaling and allows us to have both OLTP and OLAP workload at the same time.

In this article, we perform an analytics performance benchmark of AWS Aurora MySQL using the [Cube.js Serverless Analytics Framework](https://github.com/statsbotco/cube.js).

**TL;DR**: Unoptimized 100M rows test dataset ran by Serverless MySQL Aurora can be queried in 176 seconds. A query time of less than 200ms is achievable for the same dataset using multi-stage querying approach.

## Aurora MySQL Setup
Setup of Aurora MySQL is fairly simple. Just go to your [RDS Service in AWS Console](https://console.aws.amazon.com/rds/home) and select create database. 

![](https://media.graphcms.com/uAneZZ7S3GTvrD6pRwRd)

Here you should select the Aurora and MySQL compatible version that supports Serverless deployment. 
Then choose Serverless and enter your credentials to access your DB. That’s it! Your Aurora instance is configured and ready to launch.

## Preparing your dataset
For the purpose of testing, we’ll generate a 100M row dataset out of [Sakila Sample Database](https://dev.mysql.com/doc/sakila/en/). In order to do that, we’ll use slightly modified versions of Sakila tables.

First we’ll introduce the `customer` table:

```sql
CREATE TABLE customer (
  customer_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  store_id TINYINT UNSIGNED NOT NULL,
  first_name VARCHAR(45) NOT NULL,
  last_name VARCHAR(45) NOT NULL,
  email VARCHAR(50) DEFAULT NULL,
  address_id SMALLINT UNSIGNED NOT NULL,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  create_date DATETIME NOT NULL,
  last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (customer_id),
  KEY idx_fk_store_id (store_id),
  KEY idx_fk_address_id (address_id),
  KEY idx_last_name (last_name)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
```

Then the `payment` table:

```sql
CREATE TABLE payment (
  payment_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id SMALLINT UNSIGNED NOT NULL,
  staff_id TINYINT UNSIGNED NOT NULL,
  rental_id INT DEFAULT NULL,
  amount DECIMAL(5,2) NOT NULL,
  payment_date DATETIME NOT NULL,
  last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (payment_id),
  KEY idx_fk_customer_id (customer_id),
  CONSTRAINT fk_payment_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
```

*Quick hint: Aurora can’t be accessed using a public IP and is accessible only within VPC. To connect to it from your machine, you should use either VPN/SSH Tunnel or you can use AWS RDS query editor, which works just inside browser.*

To fill the `customer` and `payment` tables we’ll use a sample of the first 600 `customer` rows and the first 500 `payment` rows from [Sakila Sample Database](https://dev.mysql.com/doc/sakila/en/).

We’ll then use the `payment` table to generate actual records for the 100M `payment_big` table. Its definition is very similar to `payment`:
```sql
CREATE TABLE payment_big (
  payment_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id SMALLINT UNSIGNED NOT NULL,
  staff_id TINYINT UNSIGNED NOT NULL,
  rental_id INT DEFAULT NULL,
  amount DECIMAL(5,2) NOT NULL,
  payment_date DATETIME NOT NULL,
  last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (payment_id),
  KEY idx_fk_customer_id (customer_id),
  CONSTRAINT fk_payment_big_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
```

As MySQL doesn’t have a generator feature, we’ll introduce some helper views for that.

Integer number generator up to 16:
```sql
CREATE OR REPLACE VIEW generator_16
AS SELECT 0 n UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL 
   SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL
   SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL
   SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
   SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL 
   SELECT 15;
```

Integer number generator up to 256:
```sql
CREATE OR REPLACE VIEW generator_256 AS SELECT ( hi.n * 16 + lo.n ) AS n FROM generator_16 lo , generator_16 hi;
```

To generate actual payments, we’ll use the following SQL, which generates 2,048,000 rows at once:
```sql
INSERT INTO payment_big (customer_id, staff_id, rental_id, amount, payment_date, last_update) 
SELECT customer_id, staff_id, rental_id, amount * 10 * RAND() as amount, TIMESTAMPADD(MINUTE, generator_256.n * 1000 + 1000 * RAND(), payment_date) as payment_date, last_update
FROM payment, generator_256, generator_16
```

We’ll call it 50 times to get a table with approximately 100M rows. 
## Cube.js setup
[Cube.js](https://github.com/statsbotco/cube.js) is an open-source analytics framework that works as an intermediate between your analytics users and your database. It provides analytic SQL generation, query results caching and execution orchestration, data pre-aggregation, security, API for query results fetch, and visualization. We’ll use it mostly for caching and pre-aggregation to get a sense of how fast Aurora MySQL analytics can be and what the delay is between data ingestion and getting insight from it.

To create a Cube.js application, you’ll need to install Cube.js CLI and Serverless CLI for deployment:
```bash
$ npm install -g cubejs-cli
$ npm install -g serverless
```

Then, let’s create an app:
```bash
$ cubejs create aurora-benchmark -d mysql -t serverless
```

In `serverless.yml` you should define credentials to access your Aurora MySQL and Redis instance as well as provide vpc settings to access your resources. Please [learn more about deployment here](https://statsbot.co/cubejs/docs/deployment).

We’ll also introduce Cube.js schema definitions for our benchmark.
`schema/Customers.js`:
```javascript
cube(`Customers`, {
 sql: `select * from test.customer`,

 measures: {
   count: {
     type: `count`
   }
 },

 dimensions: {
   id: {
     sql: `customer_id`,
     type: `number`,
     primaryKey: true
   },

   email: {
     sql: `email`,
     type: `string`
   },

   name: {
     sql: `${CUBE}.first_name || ${CUBE}.last_name`,
     type: `string`
   }
 }
});
```

`schema/Payments.js`:
```javascript
cube(`Payments`, {
 sql: `select * from test.payment_big`,

 joins: {
   Customers: {
     sql: `${CUBE}.customer_id = ${Customers}.customer_id`,
     relationship: `belongsTo`
   }
 },

 measures: {
   count: {
     type: `count`
   },

   totalAmount: {
     sql: `amount`,
     type: `sum`
   }
 },

 dimensions: {
   id: {
     sql: `payment_id`,
     type: `number`,
     primaryKey: true
   },

   date: {
     sql: `payment_date`,
     type: `time`
   }
 }
});
```

That’s everything we need to deploy it so let’s do it:
```bash
$ serverless deploy -v
```

If everything is set up correctly, you should see something like this in your shell:
```bash
Service Information
service: aurora-benchmark
stage: dev
region: us-east-1
stack: aurora-benchmark-dev
resources: 16
api keys:
  None
endpoints:
  GET - https://123456789a.execute-api.us-east-1.amazonaws.com/dev/
  ANY - https://123456789a.execute-api.us-east-1.amazonaws.com/dev/{proxy+}
functions:
  cubejs: aurora-benchmark-dev-cubejs
  cubejsProcess: aurora-benchmark-dev-cubejsProcess
layers:
  None
```
Go to your main GET endpoint link which should look like this: ` https://123456789a.execute-api.us-east-1.amazonaws.com/dev/`

If everything is done right, you should see a code sandbox with a sample pie chart.

Let’s create our time series chart for the `Payments` cube. To do this let’s add `moment` import to `index.js`:

```javascript
import moment from 'moment';
```

You should add it as a dependency as well using the blue `Add Dependency` button or `package.json`.

Then replace `renderChart` and `query`:
```jsx
const renderChart = resultSet => (
  <Chart scale={{ category: { tickCount: 8 } }} height={400} data={resultSet.chartPivot()} forceFit>
    <Axis name="category" label={{ formatter: val => moment(val).format("MMM DD") }} />
    <Axis name="Payments.count" />
    <Tooltip crosshairs={{ type: 'y' }} />
    <Geom type="line" position="category*Payments.count" size={2} />
  </Chart>
);

const query = {
  measures: ["Payments.count"],
  timeDimensions: [{
    dimension: 'Payments.date',
    granularity: 'day',
    dateRange: ['2005-09-01', '2005-10-08']
  }]
};
```

After edits you should get an `index.js` similar to this:
```jsx
import React from "react";
import ReactDOM from "react-dom";

import cubejs from "@cubejs-client/core";
import { QueryRenderer } from "@cubejs-client/react";
import { Chart, Axis, Tooltip, Geom, Coord, Legend } from "bizcharts";
import moment from 'moment';

const API_URL = "https://123456789a.execute-api.us-east-1.amazonaws.com/dev"; // change to your actual endpoint

const renderChart = resultSet => (
  <Chart scale={{ category: { tickCount: 8 } }} height={400} data={resultSet.chartPivot()} forceFit>
    <Axis name="category" label={{ formatter: val => moment(val).format("MMM DD") }} />
    <Axis name="Payments.count" />
    <Tooltip crosshairs={{ type: 'y' }} />
    <Geom type="line" position="category*Payments.count" size={2} />
  </Chart>
);

const query = {
  measures: ["Payments.count"],
  timeDimensions: [{
    dimension: 'Payments.date',
    granularity: 'day',
    dateRange: ['2005-09-01', '2005-10-08']
  }]
};

const cubejsApi = cubejs(
  "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpYXQiOjE1NTExODQ0NDksImV4cCI6MTU1MTI3MDg0OX0.KLkKp2pRnw9ZlrwMGkoBlpdgGy4eol7258aKVwJLPuM",
  { apiUrl: API_URL + "/cubejs-api/v1" }
);

const App = () => (
  <div style={{ textAlign: 'center', fontFamily: 'sans-serif' }}>
   <h1>Payments Count</h1>
    <QueryRenderer
      query={query}
      cubejsApi={cubejsApi}
      render={({ resultSet, error }) =>
        (resultSet && renderChart(resultSet)) ||
          (error && error.toString()) || <span>Loading...</span>
      }
    />
</div>
);

const rootElement = document.getElementById("root");
ReactDOM.render(<App />, rootElement);
```

If everything works well, you should see a line chart similar to this one:

![](https://media.graphcms.com/h4SlFFYORT6Kb0u7ofCo)

## Benchmark
At this point we have an unoptimized data set of 100M records in our `payment_big` table. We’ll use serverless logs to see how long it takes to process queries on the Aurora MySQL side as Cube.js writes these processing logs by default. To enable log tail we’ll use:

```bash
$ serverless logs -t -f cubejsProcess
```

If we request one month of data we’ll get a processing delay of 176 seconds:
```bash
2019-02-27T12:47:45.384Z	794618d5-5eb3-40ce-88f3-cce6d75786f1	Performing query completed:
{
    "queueSize": 2,
    "duration": 175900,
    "queryKey": [
        "SELECT\n      DATE_FORMAT(CONVERT_TZ(payments.payment_date, @@session.time_zone, '+00:00'), '%Y-%m-%dT00:00:00.000Z') `payments.date_date`, count(payments.payment_id) `payments.count`\n    FROM\n      test.payment_big AS payments\n  WHERE (payments.payment_date >= TIMESTAMP(convert_tz(?, '+00:00', @@session.time_zone)) AND payments.payment_date <= TIMESTAMP(convert_tz(?, '+00:00', @@session.time_zone))) GROUP BY 1 ORDER BY 1 ASC LIMIT 10000",
        [
            "2005-09-01T00:00:00Z",
            "2005-09-30T23:59:59Z"
        ],
        []
    ]
}
```

Not too bad for an unoptimized table of 100M data points. Let’s add an index for `payment_date` to see if it can affect this time:
```sql
CREATE INDEX payment_big_date ON payment_big (payment_date)
```

The same query with index will be processed in 31 seconds which is great but still not fast enough to build a great user experience. What we can do is introduce pre-aggregations for this table. To do this let’s just add:

```javascript
preAggregations: {
 main: {
   type: `rollup`,
   measureReferences: [count],
   timeDimensionReference: date,
   granularity: `day`,
   partitionGranularity: `day`
 }
}
```
to `schema/Payments.js` so we can get:
```javascript
cube(`Payments`, {
 sql: `select * from test.payment_big`,

 joins: {
   Customers: {
     sql: `${CUBE}.customer_id = ${Customers}.customer_id`,
     relationship: `belongsTo`
   }
 },

 measures: {
   count: {
     type: `count`
   },

   totalAmount: {
     sql: `amount`,
     type: `sum`
   }
 },

 dimensions: {
   id: {
     sql: `payment_id`,
     type: `number`,
     primaryKey: true
   },

   date: {
     sql: `payment_date`,
     type: `time`
   }
 },

 preAggregations: {
   main: {
     type: `rollup`,
     measureReferences: [count],
     timeDimensionReference: date,
     granularity: `day`,
     partitionGranularity: `day`
   }
 }
});
```

It’ll create a rollup table for each day of data and will refresh it incrementally. Here `partitionGranularity` instructs Cube.js to create rollup table per day of data. Rollup table itself will contain `count` measure and `date` dimensions with `day` granularity. In fact every rollup table here will contain just 1 row which contains `count` for specific day calculated. Let’s deploy it and see how it changes query processing times:
```bash
$ serverless deploy -v
```

If we request the same interval of data, Cube.js will build pre-aggregations first for each day, which takes 1.5 seconds per one day:
```bash
2019-02-27T13:33:19.267Z	11477db2-f66e-4278-9103-eefbbc513be3	Performing query completed:
{
    "queueSize": 1,
    "duration": 1578,
    "queryKey": [
        [
            "CREATE TABLE stb_pre_aggregations.payments_main20050928 AS SELECT\n      DATE_FORMAT(CONVERT_TZ(payments.payment_date, @@session.time_zone, '+00:00'), '%Y-%m-%dT00:00:00.000Z') `payments.date_date`, count(payments.payment_id) `payments.count`\n    FROM\n      test.payment_big AS payments\n  WHERE (payments.payment_date >= TIMESTAMP(convert_tz(?, '+00:00', @@session.time_zone)) AND payments.payment_date <= TIMESTAMP(convert_tz(?, '+00:00', @@session.time_zone))) GROUP BY 1",
            [
                "2005-09-28T00:00:00Z",
                "2005-09-28T23:59:59Z"
            ]
        ],
        [
            [
                {
                    "current_hour": "2019-02-27T13:00:00.000Z"
                }
            ]
        ]
    ]
}
```

And then it will query the union of all pre-aggregations tables, which takes less than 200ms:
```bash
2019-02-27T13:33:23.647Z	a4162f29-570d-495f-8ca4-34600869d8e7	Performing query completed:
{
    "queueSize": 1,
    "duration": 134,
    "queryKey": [
        "SELECT `payments.date_date` `payments.date_date`, sum(`payments.count`) `payments.count` FROM (SELECT * FROM stb_pre_aggregations.payments_main20050901 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050902 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050903 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050904 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050905 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050906 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050907 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050908 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050909 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050910 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050911 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050912 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050913 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050914 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050915 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050916 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050917 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050918 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050919 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050920 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050921 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050922 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050923 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050924 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050925 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050926 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050927 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050928 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050929 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050930) as partition_union  WHERE (`payments.date_date` >= CONVERT_TZ(TIMESTAMP(convert_tz(?, '+00:00', @@session.time_zone)), @@session.time_zone, '+00:00') AND `payments.date_date` <= CONVERT_TZ(TIMESTAMP(convert_tz(?, '+00:00', @@session.time_zone)), @@session.time_zone, '+00:00')) GROUP BY 1 ORDER BY 1 ASC LIMIT 10000",
        [
            "2005-09-01T00:00:00Z",
            "2005-09-30T23:59:59Z"
        ],
       ...
}
```

This approach allows us to split the analytics workload into small reusable chunks to avoid DB burst with an incremental update strategy.

## Conclusion
We were playing with just a test dataset and the results are very well correlated with what we see in production workloads. MySQL is capable of handling several billions of data points per table with an analytics workload pretty well. In this case, sub second query times are achievable with several seconds time to insight delay using properly organized query orchestration. 

Although Serverless Aurora MySQL is capable of handling an enormous workload while stress testing, we discovered that workload routing algorithms aren’t smart enough yet to route queries to the least loaded nodes, which under certain circumstances can lead to partial database outages while analytics querying. For production environments we recommend you use provisioned Aurora MySQL with read replicas or the [Parallel Query feature](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora-mysql-parallel-query.html) in order to split your OLTP and OLAP workload by design.