Subchapter 4.22

Query Mv Refreshable

rules/query-mv-refreshable.mdMarkdown2 KBView on GitHub

Use Refreshable MVs for Complex Joins and Batch Workflows

Impact: HIGH

Refreshable MVs execute queries periodically on a schedule. The full query re-executes and overwrites (or appends to) the target table.

Query Mv Refreshable · Clickhouse Best Practices · ClickHouse/agent-skills · Skills Docs

-- Complex join executed on every request
SELECT
    o.order_id, o.total,
    c.name as customer_name,
    p.name as product_name
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products p ON o.product_id = p.id
WHERE o.created_at >= now() - INTERVAL 1 DAY;

-- Create refreshable MV that runs every 5 minutes
CREATE MATERIALIZED VIEW orders_denormalized
REFRESH EVERY 5 MINUTE
ENGINE = MergeTree()
ORDER BY (created_at, order_id)
AS SELECT
    o.order_id, o.created_at, o.total,
    c.name as customer_name, c.segment,
    p.name as product_name
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products p ON o.product_id = p.id
WHERE o.created_at >= now() - INTERVAL 1 DAY;

-- Query the pre-joined data (sub-millisecond)
SELECT * FROM orders_denormalized WHERE segment = 'enterprise';

Mode	Behavior	Use Case
`REPLACE` (default)	Overwrites previous contents	Current state, lookup tables
`APPEND`	Adds new rows to existing data	Periodic snapshots, historical accumulation