Materializované pohledy (materialized views) jsou jedním z nejefektivnějších nástrojů pro optimalisaci výkonu databasových dotazů. Zatímco běžné pohledy (views) jsou pouze uložené dotazy, materializované pohledy fysicky ukládají výsledky do database.

Co je to materializovaný pohled?

Materializovaný pohled je databasový objekt, který obsahuje fysicky uložené výsledky dotazu. Na rozdíl od běžného pohledu, který se pokaždé znovu vyhodnocuje při každém přístupu, materializovaný pohled ukládá data na disk.

Představte si to jako cache pro databasové dotazy.

Rozdíl mezi view a materialized view

Vlastnost	View (Pohled)	Materialized view
Ukládání dat	Neukládá, jen definice dotazu	Fysicky ukládá výsledky
Výkon čtení	Pomalý (spouští dotaz vždy)	Rychlý (čte uložená data)
Aktuálnost dat	Vždy aktuální	Může být zastaralé
Diskový prostor	Minimální	Může být značný

Jak funguje materializovaný pohled?

Princip je následující:

1. Vytvoření: Při vytvoření se spustí definovaný dotaz a výsledky se uloží
2. Čtení: Dotazy na materializovaný pohled čtou přímo uložená data (velmi rychle)
3. Refresh: Data je potřeba pravidelně aktualisovat, aby odpovídala zdrojovým tabulkám

Vytvoření materializovaného pohledu

Syntaxe se liší podle databasového systému. Zde jsou příklady pro nejpoužívanější database:

PostgreSQL

CREATE MATERIALIZED VIEW orders_summary AS
SELECT
    customer_id,
    COUNT(*) as order_count,
    SUM(total_amount) as total_spent,
    MAX(order_date) as last_order_date
FROM orders
GROUP BY customer_id;

-- Vytvoření indexu pro rychlejší dotazy
CREATE INDEX idx_orders_summary_customer
ON orders_summary(customer_id);

MySQL

MySQL nemá nativní podporu pro materializované pohledy, ale lze je simulovat pomocí tabulek a triggerů nebo pravidelných úloh:

-- Vytvoření tabulky jako materializovaného pohledu
CREATE TABLE orders_summary AS
SELECT
    customer_id,
    COUNT(*) as order_count,
    SUM(total_amount) as total_spent,
    MAX(order_date) as last_order_date
FROM orders
GROUP BY customer_id;

-- Pravidelná aktualisace pomocí EVENT
CREATE EVENT refresh_orders_summary
ON SCHEDULE EVERY 1 HOUR
DO
  REPLACE INTO orders_summary
  SELECT
      customer_id,
      COUNT(*) as order_count,
      SUM(total_amount) as total_spent,
      MAX(order_date) as last_order_date
  FROM orders
  GROUP BY customer_id;

Aktualisace dat (Refresh)

Klíčovou otázkou u materializovaných pohledů je, kdy a jak aktualisovat data.

Strategie aktualisace

1. Manuální refresh

-- PostgreSQL
REFRESH MATERIALIZED VIEW orders_summary;

2. Automatický refresh pomocí cron nebo scheduleru

PostgreSQL nemá vestavěný scheduler, ale můžete použít:

-- PostgreSQL: Pomocí pg_cron rozšíření
SELECT cron.schedule('refresh-orders-summary', '0 2 * * *',
  'REFRESH MATERIALIZED VIEW orders_summary');

-- Nebo klasický cron na serveru
# crontab -e
0 2 * * * psql -d mydb -c "REFRESH MATERIALIZED VIEW orders_summary;"

MySQL: Použijte EVENT scheduler (viz výše).

Kdy použít materializované pohledy?

Materializované pohledy jsou ideální v následujících situacích:

1. Analytické a reportovací dotazy

Složité agregace přes miliony záznamů:

CREATE MATERIALIZED VIEW sales_analytics AS
SELECT
    DATE_TRUNC('month', order_date) as month,
    product_category,
    region,
    COUNT(*) as order_count,
    SUM(amount) as total_revenue,
    AVG(amount) as avg_order_value,
    COUNT(DISTINCT customer_id) as unique_customers
FROM orders
JOIN products ON orders.product_id = products.id
JOIN customers ON orders.customer_id = customers.id
WHERE order_date >= DATE_TRUNC('year', CURRENT_DATE – INTERVAL '2 years')
GROUP BY DATE_TRUNC('month', order_date), product_category, region;

2. Časté JOINy přes více tabulek

Pokud máte dotaz, který JOINuje 5+ tabulek a spouští se často:

CREATE MATERIALIZED VIEW customer_360_view AS
SELECT
    c.customer_id,
    c.name,
    c.email,
    a.address,
    COUNT(DISTINCT o.order_id) as lifetime_orders,
    SUM(o.total) as lifetime_value,
    MAX(o.order_date) as last_order,
    AVG(r.rating) as avg_rating,
    s.subscription_status
FROM customers c
LEFT JOIN addresses a ON c.customer_id = a.customer_id
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN reviews r ON c.customer_id = r.customer_id
LEFT JOIN subscriptions s ON c.customer_id = s.customer_id
GROUP BY c.customer_id, c.name, c.email, a.address, s.subscription_status;

3. Dashboardy a metriky

Pro dashboardy, které se načítají často, ale data se mění pomaleji:

CREATE MATERIALIZED VIEW dashboard_metrics AS
SELECT
    'today' as period,
    COUNT(*) as orders,
    SUM(total) as revenue,
    COUNT(DISTINCT customer_id) as customers
FROM orders
WHERE order_date >= CURRENT_DATE

UNION ALL

SELECT
    'this_month' as period,
    COUNT(*) as orders,
    SUM(total) as revenue,
    COUNT(DISTINCT customer_id) as customers
FROM orders
WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE)

UNION ALL

SELECT
    'this_year' as period,
    COUNT(*) as orders,
    SUM(total) as revenue,
    COUNT(DISTINCT customer_id) as customers
FROM orders
WHERE order_date >= DATE_TRUNC('year', CURRENT_DATE);

4. Data Warehouse a ETL procesy

Materializované pohledy jsou jedním ze základních stavebních kamenů datových skladů.

Data Warehouse (datový sklad) je centrální úložiště dat z různých zdrojů, optimalisované pro analytické dotazy a reportování.

ETL je proces, kterým se data dostávají do Data Warehouse. Zkratka znamená:

• Extract (Extrakce) – získání dat ze zdrojů
• Transform (Transformace) – čištění a úprava dat
• Load (Načtení) – uložení do datového skladu

Materializované pohledy se používají ve fázi Transform a jako výstup Load fáze:

Výhody a nevýhody

Výhody

• Dramatické zrychlení dotazů – i 100× a více pro složité agregace
• Snížení zátěže database – méně výpočtů při každém dotazu
• Možnost indexování – na materializovaný pohled lze vytvořit indexy
• Prediktabilní výkon – dotazy mají konzistentní rychlost

Nevýhody

• Zastaralá data – data nemusí být aktuální
• Diskový prostor – zabírají místo na disku
• Režie při aktualisaci – refresh může být náročný
• Komplexita údržby – je třeba řídit aktualisaci

Best Practices

1. Vytvářejte indexy

Materializované pohledy jsou fysicky uložené tabulky, takže můžete na ně vytvořit indexy pro ještě rychlejší dotazy.

Proč přidávat indexy?

Materializovaný pohled už data předpočítal, ale stále je potřeba je vyhledat!

Představte si tento scénář:

-- Materializovaný pohled s milionem zákazníků
CREATE MATERIALIZED VIEW orders_summary AS
SELECT
    customer_id,
    COUNT(*) as order_count,
    SUM(total_amount) as total_spent
FROM orders
GROUP BY customer_id;

Když teď chcete najít konkrétního zákazníka:

-- BEZ indexu: musí projít všech milion řádků!
SELECT * FROM orders_summary WHERE customer_id = 12345;
-- Trvá: 500ms (full table scan)

S indexem je to okamžité:

-- Vytvoříme index
CREATE INDEX idx_mv_customer ON orders_summary(customer_id);

-- S indexem: najde řádek přímo
SELECT * FROM orders_summary WHERE customer_id = 12345;
-- Trvá: 2ms (index seek)

Kdy vytvořit indexy?

• WHERE podmínky: Indexujte sloupce, které používáte ve WHERE
• JOIN operace: Indexujte sloupce pro joinování
• ORDER BY: Index může urychlit řazení
• GROUP BY: V některých případech pomůže i s grupováním

Příklady užitečných indexů:

-- 1. Index pro vyhledávání podle zákazníka
CREATE INDEX idx_mv_customer ON orders_summary(customer_id);

-- 2. Index pro časové dotazy
CREATE INDEX idx_mv_date ON sales_analytics(month);

-- 3. Kompozitní index pro složitější dotazy
CREATE INDEX idx_mv_date_region ON sales_analytics(month, region);

-- 4. Index pro řazení (DESC = sestupně)
CREATE INDEX idx_mv_revenue ON top_products(total_revenue DESC);

-- 5. Částečný index (jen aktivní zákazníci)
CREATE INDEX idx_mv_active ON orders_summary(customer_id)
WHERE order_count > 0;

Kdy NEVYTVÁŘET indexy?

• Pokud je materializovaný pohled malý (< 1000 řádků) – full scan je rychlejší
• Pokud nikdy nefiltrujete data – čtete vždy všechno
• Index zabírá místo a zpomaluje refresh – nepřehánějte to

2. Monitorujte velikost a výkon

-- PostgreSQL: velikost materializovaného pohledu
SELECT
    schemaname,
    matviewname,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||matviewname)) as size
FROM pg_matviews
ORDER BY pg_total_relation_size(schemaname||'.'||matviewname) DESC;

3. Volte správnou refresh strategii

• Real-time data: Materializované pohledy nejsou vhodné – použijte běžné views nebo cache
• Near real-time: Refresh každých 5-15 minut
• Reporty: Refresh jednou denně (v noci)
• Historická data: Refresh týdně nebo měsíčně

4. Použijte CONCURRENTLY refresh (PostgreSQL)

Běžný refresh zamkne materializovaný pohled. S CONCURRENTLY zůstává pohled dostupný:

REFRESH MATERIALIZED VIEW CONCURRENTLY orders_summary;

Podmínky pro CONCURRENTLY:

• Vyžaduje unique index na materializovaném pohledu
• Index musí pokrývat všechny řádky (nesmí být partial)
• Bez unique indexu refresh selže s chybou

-- NEJPRVE vytvořte unique index
CREATE UNIQUE INDEX idx_orders_summary_customer_unique
ON orders_summary(customer_id);

-- TEĎ můžete použít CONCURRENTLY
REFRESH MATERIALIZED VIEW CONCURRENTLY orders_summary;

Kompromis: CONCURRENTLY refresh je pomalejší než běžný refresh, ale pohled zůstává dostupný během aktualisace.

5. Zvažte částečné materializované pohledy

Nemusíte materializovat všechna data, jen ta nejpoužívanější:

-- Pouze data za poslední rok
CREATE MATERIALIZED VIEW recent_orders_summary AS
SELECT
    customer_id,
    COUNT(*) as order_count,
    SUM(total_amount) as total_spent
FROM orders
WHERE order_date >= CURRENT_DATE – INTERVAL '1 year'
GROUP BY customer_id;

Praktická ukázka: Zrychlení reportu

Představme si e-shop s reportem zobrazujícím top produkty za poslední měsíc.

Před – běžný pohled

CREATE VIEW top_products AS
SELECT
    p.product_name,
    p.category,
    COUNT(oi.order_item_id) as items_sold,
    SUM(oi.quantity) as total_quantity,
    SUM(oi.price * oi.quantity) as total_revenue
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_date >= CURRENT_DATE – INTERVAL '30 days'
GROUP BY p.product_id, p.product_name, p.category
ORDER BY total_revenue DESC
LIMIT 100;

-- Dotaz trvá: 2.3 sekundy

Po – materializovaný pohled

CREATE MATERIALIZED VIEW top_products AS
SELECT
    p.product_name,
    p.category,
    COUNT(oi.order_item_id) as items_sold,
    SUM(oi.quantity) as total_quantity,
    SUM(oi.price * oi.quantity) as total_revenue
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_date >= CURRENT_DATE – INTERVAL '30 days'
GROUP BY p.product_id, p.product_name, p.category
ORDER BY total_revenue DESC
LIMIT 100;

CREATE INDEX idx_top_products_revenue ON top_products(total_revenue DESC);

-- Refresh každou hodinu
-- (konfigurace závisí na databasi)

-- Dotaz trvá: 0.02 sekundy (100× rychleji!)

Upozornění: Toto je ideální scénář. Ve skutečnosti:

• Zrychlení závisí na velikosti dat, složitosti dotazu a hardwaru
• Realističtější je očekávat 10-50× zrychlení
• Musíte počítat s náklady na refresh (může trvat minuty až hodiny)
• Musíte řešit zastaralá data mezi refresh cykly

Moderní databasové alternativy

Kromě PostgreSQL a MySQL existují moderní database s pokročilou podporou pro materializované pohledy:

AlloyDB (Google Cloud)

AlloyDB je plně spravovaná PostgreSQL-kompatibilní database od Google. Oproti standardnímu PostgreSQL nabízí:

• Až 4× rychlejší transakce oproti standardnímu PostgreSQL
• Až 100× rychlejší analytické dotazy díky column-store enginu
• Nativní integrace s Google Cloud ekosystémem
• Automatické škálování a vysoká dostupnost
• Kompatibilita s PostgreSQL – včetně materializovaných pohledů

Materializované pohledy v AlloyDB fungují stejně jako v PostgreSQL, ale s lepším výkonem díky optimalisovanému storage.

ClickHouse

ClickHouse je open-source column-oriented database optimalisovaná pro analytické dotazy (OLAP – Online Analytical Processing).

• Incremental materialized views – automatická aktualisace při vložení dat
• Extrémně rychlé – zpracování miliard řádků za sekundy
• Agregující enginy – vestavěná podpora pro agregace

-- ClickHouse: Materialized view s automatickou aktualisací
CREATE MATERIALIZED VIEW orders_summary_mv
ENGINE = SummingMergeTree()
ORDER BY customer_id
AS SELECT
    customer_id,
    count() as order_count,
    sum(total_amount) as total_spent,
    max(order_date) as last_order_date
FROM orders
GROUP BY customer_id;

-- Data se aktualisují AUTOMATICKY při INSERT do orders!

Výhoda: Nepotřebujete refresh – view se aktualisuje inkrementálně při vkládání dat.

TimescaleDB

TimescaleDB je rozšíření PostgreSQL pro time-series data (časové řady).

• Continuous Aggregates – chytřejší materializované pohledy
• Automatická aktualisace na pozadí
• Refresh policy – nastavitelná strategie aktualisace

-- TimescaleDB: Continuous aggregate
CREATE MATERIALIZED VIEW orders_daily
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 day', order_date) as day,
    customer_id,
    COUNT(*) as order_count,
    SUM(total_amount) as daily_total
FROM orders
GROUP BY day, customer_id;

-- Automatická refresh policy
SELECT add_continuous_aggregate_policy('orders_daily',
    start_offset => INTERVAL '1 month',
    end_offset => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour');

Výhoda: Optimalisováno pro časové řady, automatické inkrementální aktualisace.

CockroachDB

CockroachDB je distribuovaná SQL database kompatibilní s PostgreSQL.

• Podpora materializovaných pohledů (od verse 23.1)
• Distribuované zpracování – view může být rozložen přes více nodů
• Vysoká dostupnost – automatické replikace

Srovnání databasí

Database	Typ	Refresh	Výhoda
PostgreSQL	OLTP	Manuální/Cron	Všestranná, stabilní
MySQL	OLTP	Simulace tabulkou	Široce podporovaná
AlloyDB	OLTP/OLAP	Manuální/Cron	Managed, rychlejší PG
ClickHouse	OLAP	Automatický	Extrémní rychlost
TimescaleDB	Time-series	Automatický	Optimalisace pro čas
CockroachDB	OLTP	Manuální	Distribuovaná

OLTP = Online Transaction Processing (online zpracování transakcí) – běžné database pro aplikace.

Kdy použít moderní alternativy?

• AlloyDB: Pokud používáte Google Cloud a potřebujete lepší výkon než PostgreSQL
• ClickHouse: Pro analytické aplikace s obrovskými objemy dat (miliardy řádků)
• TimescaleDB: Pro IoT, monitoring, metriky a jiná time-series data
• CockroachDB: Když potřebujete globální distribuci a vysokou dostupnost

Úskalí a časté problémy

Materializované pohledy nejsou všespásné řešení. Zde jsou reálné problémy, se kterými se můžete setkat:

1. Výkonové nároky nejsou vždy tak velké

Články často uvádějí „100× rychlejší dotazy“. Realita je složitější:

• 10–50× zrychlení je realistické pro složité agregace přes miliony řádků
• 2–5× zrychlení u dotazů se správnými indexy
• Žádné zrychlení pokud database má dobrý query planner a správné indexy na zdrojových tabulkách
• Zpomalení pokud refresh trvá příliš dlouho a blokuje jiné operace

Zlaté pravidlo: Měřte a testujte na reálných datech. Co funguje na 1000 řádcích, nemusí fungovat na 100 milionech.

2. Refresh může být velmi náročný

Refresh není zadarmo:

-- Refresh velkého materializovaného pohledu může trvat hodiny
REFRESH MATERIALIZED VIEW huge_analytics; -- Trvá: 4 hodiny!

Problémy v praxi:

• Dead tuples: PostgreSQL vytváří při obnovování (refresh) mrtvé řádky, které zabírají místo dokud neproběhne VACUUM
• Blokování: Běžný refresh zamkne view pro čtení (použijte CONCURRENTLY, ale je pomalejší)
• Kaskádové refreshe: Pokud máte materializovaný pohled z materializovaného pohledu, musíte refreshovat oba
• Časové okno: Refresh musí doběhnout před dalším použitím – co když trvá déle než plánovaný interval?

3. Zastaralá data mohou způsobit problémy

Materializovaný pohled ukazuje data z času posledního refreshe:

-- Refresh v 2:00 ráno
REFRESH MATERIALIZED VIEW daily_sales;

-- Uživatel v 15:00 vidí data ze 2:00
-- Všechny objednávky od 2:00 do 15:00 CHYBÍ!
SELECT * FROM daily_sales WHERE date = CURRENT_DATE;
-- Výsledek je neúplný a může vést k špatným rozhodnutím

Reálné příklady problémů:

• Dashboard ukazuje nižší prodeje, než je realita
• Zákazník je označen jako „neaktivní“, i když právě nakoupil
• Reporty pro management obsahují zastaralá čísla

4. Refresh strategie nejsou universální

• Závisí na objemu změn: 1000 změn/den vs. 1 milion změn/den je obrovský rozdíl
• Závisí na velikosti view: Malý view můžete refreshovat každou minutu, obří view jednou týdně
• Závisí na SLA: Kolik minut zastaralých dat je přijatelných?
• Závisí na databasovém systému: PostgreSQL, MySQL a ClickHouse se chovají zcela jinak

5. Diskový prostor může být problém

-- Zdrojová tabulka: 500 GB
SELECT pg_size_pretty(pg_total_relation_size('orders'));
-- 500 GB

-- Materializovaný pohled: dalších 200 GB!
SELECT pg_size_pretty(pg_total_relation_size('orders_analytics_mv'));
-- 200 GB

-- Indexy na MV: dalších 50 GB
-- Celkem: 750 GB místo původních 500 GB

Materializované pohledy duplikují data. To znamená:

• Vyšší náklady na storage (zejména v cloudu)
• Pomalejší backupy (je třeba zálohovat i MV)
• Delší časy pro restore

6. Údržba může být složitá

Čím více materializovaných pohledů máte, tím složitější je údržba:

• Musíte monitorovat časy refresh (co když začnou trvat déle?)
• Musíte řešit selhání refresh (co když refresh selže v noci?)
• Musíte koordinovat refresh více pohledů (v jakém pořadí?)
• Změna schématu zdrojové tabulky vyžaduje změnu MV
• Musíte dokumentovat, které metriky jsou v jakém MV

Kdy materializované pohledy NEPOUŽÍVAT

• Real-time data: Pokud potřebujete aktuální data (trading, monitoring, alerting)
• Málo používané dotazy: Pokud dotaz spouštíte jednou měsíčně, refresh každý den je zbytečný
• Rychle se měnící data: Pokud se data mění každou vteřinu, refresh nestíháte
• Malé tabulky: Pro tisíce řádků je MV overhead, ne optimalisace
• Jednoduché dotazy: Pokud stačí správný index, je to lepší řešení

Alternativy k materializovaným pohledům

V některých situacích mohou být lepší jiná řešení:

• Indexy: Pro jednoduché dotazy může stačit správný index
• Partitioning: Rozdělení velkých tabulek na menší části
• Query cache: Aplikační cache (Redis, Memcached)
• Denormalizace: Přidání redundantních sloupců do tabulek
• Incremental views: Některé database podporují inkrementální aktualisaci

Závěr

Materializované pohledy jsou mocný nástroj pro optimalisaci výkonu database:

• Výrazně zrychlují složité a opakující se dotazy
• Snižují zátěž databasového serveru
• Ideální pro analytické dotazy, reporty a dashboardy
• Je třeba vyvážit rychlost čtení vs. aktuálnost dat
• Vyžadují správnou strategii aktualisace a údržby

Pokud máte v aplikaci pomalé dotazy, které se často opakují, ale data se mění pomaleji, materializované pohledy mohou být přesně to, co hledáte.