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()
AS
SELECT
date,
sum(sales) AS total_sales
FROM
my_source_table
GROUP BY
date;
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.