Course Overview
ClickHouse Database Objects

Materialised Views

Lesson #4

In this lesson we will:

  • Learn about the ClickHouses powerful materialised view features;
  • Demonstrate how they are used in practice.

Materialised Views

In ClickHouse, a materialized view is a database object that stores the result of a precomputed query, which is periodically refreshed based on the data from one or more source tables.

Materialized views are designed to improve query performance by reducing the computational overhead of complex and frequently used queries. They are particularly useful for speeding up analytical workloads in cases where you have expensive or resource-intensive calculations.

CREATE MATERIALIZED VIEW my_materialized_view
ENGINE = MergeTree()
    sum(sales) AS total_sales

The upside of this is that the materialised views are faster to query, because the query is already pre-calculated.

However, there are two downsides.

Firstly, they take more storage because the results. This could imply duplication such as in the example below where the high value orders will be stored in both the original table and the materialised view.

Secondly, manaing the materialised views will add delays when we insert and update data, and require database resources to accomplish this tasks.

Next Lesson:


In this lesson we will learn about the various ways of creating and integrating with user defined functions.

0h 5m

Work With The Experts In Real-Time Analytics & AI

we help enterprise organisations deploy powerful real-time Data, Analytics and AI solutions based on ClickHouse, the worlds fastest open-source database.

Join our mailing list for regular insights:

We help enterprise organisations deploy advanced data, analytics and AI enabled systems based on modern cloud-native technology.

© 2024 Ensemble. All Rights Reserved.