Módulo 11 · Engenharia de Software · Aula 7

Otimização de
Data Warehouses

Laboratório comparativo de consultas OLAP com ClickHouse e PostgreSQL no DBeaver.

Computação 2 · Prof. Afonso Brandão · 24/08/2026

MergeTree Partition Pruning Skip Index Materialized View

Continuidade · das Aulas 2-6 para a Aula 7

Da modelagem dimensional à otimização da execução

A arquitetura dimensional exige que as consultas respondam dentro do SLA analítico.

Aulas 2-4Modelagem dimensional

Aula 5Arquitetura de dados

Aula 6Barramento corporativo

Aula 7Otimização física

Partições, índices e agregações pré-processadas preservam o modelo lógico e podem reduzir a leitura, o I/O e o tempo de resposta.

Roteiro · 120 minutos

Método experimental aplicado a ClickHouse e PostgreSQL

Cada alteração é isolada, medida e interpretada antes da próxima.

Bloco 01

10 min

Preparação

Conectar ClickHouse e PostgreSQL no DBeaver e criar a baseline.

Bloco 02

50 min

ClickHouse

MergeTree, PARTITION BY, skip index e materialized view incremental.

Bloco 03

50 min

PostgreSQL

Particionamento declarativo, BRIN, B-tree, ANALYZE e VACUUM.

Bloco 04

10 min

Comparação

Consolidar evidências e registrar a recomendação técnica.

Objetivo: comparar, com evidências mensuráveis, as estratégias de otimização aplicáveis a cada cenário sem alterar os resultados.

Método experimental

Uma alteração por rodada, três execuções por cenário

A interpretação combina tempo, linhas, bytes, buffers e cardinalidade para explicar a variação observada.

Protocolo

Isolamento de variáveis e atribuição causal

Consulta, dados e conexão permanecem constantes. Registra-se a baseline, aplica-se uma alteração estrutural e repete-se a medição. Cada consulta é executada ao menos três vezes e registra-se a mediana.

✓

Execute cada consulta ao menos três vezes e registre a mediana.

✓

Compare resultados antes de comparar desempenho.

✓

Altere apenas uma estrutura por rodada.

✓

Capture o plano de execução junto com o tempo medido.

✓

Distinga redução de leitura de mero efeito de cache.

Comparar consultas diferentes ou registrar somente a execução mais rápida invalida a conclusão.

Bloco 2 · ClickHouse

MergeTree, ordenação física e partições temporais

No ClickHouse, ORDER BY define a ordenação física e, na ausência de outra declaração, também define a chave primária esparsa.

PARTITION BY

Ciclo de vida dos dados

Organiza partes por mês (toYYYYMM). Permite partition pruning e gestão granular de retenção. Não substitui uma chave de ordenação coerente com os filtros frequentes.

PRIMARY KEY + ORDER BY

Chave primária esparsa

Índice esparso que aponta para granules. Mantenha a chave primária como prefixo de ORDER BY. Particionar por identificadores de alta cardinalidade produz excesso de partes.

SKIP INDEX

Filtros seletivos fora da PK

Bloom filter descarta granules quando a chave principal não atende a predicado seletivo. Teste o predicado antes e depois de materializar o índice.

MATERIALIZED VIEW

Agregação incremental

Desloca agregações da leitura para a ingestão. Exige tabela de destino (SummingMergeTree no laboratório) e backfill explícito para dados históricos.

Bloco 3 · PostgreSQL

Particionamento declarativo, BRIN e B-tree

BRIN resume faixas físicas; B-tree atende predicados seletivos e ordenados, com maior custo de armazenamento.

PARTITIONING

Eliminação de partições

PARTITION BY RANGE permite eliminar partições fora do intervalo da consulta. Confirme no plano quantas partições foram acessadas.

BRIN

Block Range INdex

Resume faixas físicas e favorece tabelas extensas correlacionadas com a ordem de armazenamento. Aplica-se a intervalos temporais fisicamente ordenados.

B-TREE

Índice composto seletivo

(regiao_id, data_venda) INCLUDE (valor) atende filtro combinado de região e tempo. Maior custo de armazenamento e escrita que BRIN.

MAINTENANCE

ANALYZE e VACUUM

ANALYZE atualiza estatísticas do planejador. VACUUM recupera espaço reutilizável. Execute ANALYZE após carga inicial e mudanças relevantes de distribuição.

ClickHouse · Passos 1 e 2

Preparar tabelas e gerar dois milhões de vendas

Recria o ambiente e estabelece baseline sem ordenação útil para filtros analíticos.

🔥

ClickHouse — Passo 1

1 Criar fato_vendas_base

ORDER BY tuple() — sem chave útil para pruning.

2 Criar fato_vendas_otimizada

PARTITION BY toYYYYMM(data_venda), PRIMARY KEY (regiao_id, data_venda), ORDER BY (regiao_id, data_venda, produto_id).

🔥

ClickHouse — Passo 2

INSERT INTO aula_dw.fato_vendas_base
SELECT
    number + 1 AS venda_id,
    toDateTime('2025-01-01 00:00:00')
        + toIntervalSecond(intDiv(number * 31536000, 2000000)) AS data_venda,
    toUInt8(number % 20 + 1) AS regiao_id,
    toUInt32(intDiv(number, 20) % 100000 + 1) AS cliente_id,
    toUInt16(number % 1000 + 1) AS produto_id,
    toDecimal64(1000 + number % 50000, 2) / 100 AS valor
FROM numbers(2000000);

-- Copiar para tabela otimizada
INSERT INTO aula_dw.fato_vendas_otimizada
SELECT * FROM aula_dw.fato_vendas_base;

Observe: As duas tabelas devem possuir 2.000.000 de linhas. A tabela otimizada deve apresentar partições mensais.

ClickHouse · Passos 3 e 4

Partition pruning e skip index bloom filter

Comparar full scan com partition pruning e avaliar predicado seletivo fora da chave primária.

Passo 3

Partition pruning vs full scan

EXPLAIN indexes = 1
SELECT count(), sum(valor)
FROM aula_dw.fato_vendas_otimizada
WHERE regiao_id = 7
  AND data_venda >= toDateTime('2025-07-01 00:00:00')
  AND data_venda <  toDateTime('2025-07-08 00:00:00');

Observe: O plano otimizado deve mostrar Partition e PrimaryKey com fração menor de parts e granules.

Passo 4

Skip index bloom_filter

ALTER TABLE aula_dw.fato_vendas_otimizada
ADD INDEX idx_cliente cliente_id
TYPE bloom_filter(0.01) GRANULARITY 1;

ALTER TABLE aula_dw.fato_vendas_otimizada
MATERIALIZE INDEX idx_cliente
SETTINGS mutations_sync = 1;

Observe: O segundo plano deve incluir Skip com idx_cliente. O índice só se justifica se reduzir granules para o predicado observado.

ClickHouse · Passo 5

Definir a agregação incremental

A tabela de destino acumula estados parciais; a view processa cada novo bloco inserido na fato.

Tabela de destino

SummingMergeTree

CREATE TABLE aula_dw.vendas_dia
(
    dia Date,
    regiao_id UInt8,
    quantidade UInt64,
    receita Decimal(18, 2)
)
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(dia)
ORDER BY (dia, regiao_id);

Processamento na ingestão

Materialized view incremental

CREATE MATERIALIZED VIEW
    aula_dw.mv_vendas_dia
TO aula_dw.vendas_dia AS
SELECT
    toDate(data_venda) AS dia,
    regiao_id,
    count() AS quantidade,
    CAST(sum(valor), 'Decimal(18, 2)') AS receita
FROM aula_dw.fato_vendas_otimizada
GROUP BY dia, regiao_id;

Observe: GROUP BY e ORDER BY usam as mesmas chaves. Como as somas podem permanecer em partes distintas até a mesclagem, a consulta da tabela de destino agrega novamente por dia e região.

ClickHouse · Passo 5

Executar o backfill e verificar a consistência

O histórico existente é carregado explicitamente; as novas linhas passam a ser processadas pela view.

Backfill histórico

Preencher a tabela de destino

INSERT INTO aula_dw.vendas_dia
SELECT
    toDate(data_venda) AS dia,
    regiao_id,
    count() AS quantidade,
    CAST(sum(valor), 'Decimal(18, 2)') AS receita
FROM aula_dw.fato_vendas_otimizada
GROUP BY dia, regiao_id;

Consulta de verificação

Consolidar partes ainda não mescladas

SELECT
    dia,
    sum(quantidade) AS quantidade,
    sum(receita) AS receita
FROM aula_dw.vendas_dia
WHERE regiao_id = 7
  AND dia >= toDate('2025-07-01')
  AND dia <  toDate('2025-08-01')
GROUP BY dia
ORDER BY dia;

Critério: antes da inserção adicional, a fato e a agregada devem retornar totais idênticos. Depois, a nova venda deve aparecer automaticamente na agregada.

A materialized view incremental processa somente novos blocos. Em produção, o backfill exige corte temporal ou pausa controlada da ingestão para evitar lacunas e duplicações.

ClickHouse · Passo 6

Consultar métricas executadas via system.query_log

Obter duração, linhas, bytes e memória para as consultas recentes do laboratório.

system.query_log

Métricas de execução

SYSTEM FLUSH LOGS;

SELECT
    event_time,
    query_duration_ms,
    read_rows,
    formatReadableSize(read_bytes) AS bytes_lidos,
    formatReadableSize(memory_usage) AS memoria,
    left(replaceAll(query, '\n', ' '), 120) AS consulta
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time >= now() - INTERVAL 15 MINUTE
  AND has(databases, 'aula_dw')
  AND query NOT ILIKE '%system.query_log%'
ORDER BY event_time DESC
LIMIT 30;

Observe: Registre a mediana de três execuções e compare read_rows e read_bytes. Caso não possa executar SYSTEM FLUSH LOGS, aguarde a gravação assíncrona.

PostgreSQL · Passos 1 e 2

Particionamento declarativo e geração de dados

Doze partições mensais para 2025 e dados ordenados temporalmente.

🐘

PostgreSQL — Passo 1

1 Criar schema aula_dw

DROP SCHEMA IF EXISTS aula_dw CASCADE; CREATE SCHEMA aula_dw;

2 Criar fato_vendas_part

PARTITION BY RANGE (data_venda) com 12 partições mensais (2025_01 a 2025_12).

🐘

PostgreSQL — Passo 2

INSERT INTO aula_dw.fato_vendas_base
SELECT
    g AS venda_id,
    timestamp '2025-01-01 00:00:00'
        + (
            (CAST(g AS bigint) - 1)
            * 31536000 / 2000000
          ) * interval '1 second'
        AS data_venda,
    CAST(((g - 1) % 20) + 1 AS smallint) AS regiao_id,
    CAST((((g - 1) / 20) % 100000) + 1 AS integer) AS cliente_id,
    CAST(((g - 1) % 1000) + 1 AS integer) AS produto_id,
    CAST(10 + ((g - 1) % 50000) / 100.0 AS numeric(12, 2)) AS valor
FROM generate_series(1, 2000000) AS g;

INSERT INTO aula_dw.fato_vendas_part
SELECT * FROM aula_dw.fato_vendas_base;

ANALYZE aula_dw.fato_vendas_base;
ANALYZE aula_dw.fato_vendas_part;

Observe: As partições devem totalizar 2.000.000 de linhas. ANALYZE fornece estatísticas da tabela base, das folhas e da hierarquia particionada.

PostgreSQL · Passos 3 e 4

Eliminação de partições, BRIN e B-tree

Comparar varredura sequencial, partição eliminada e tipos de índice.

Passo 3

Partition elimination

EXPLAIN (ANALYZE, BUFFERS, SUMMARY)
SELECT count(*), sum(valor)
FROM aula_dw.fato_vendas_part
WHERE data_venda >= timestamp '2025-07-01 00:00:00'
  AND data_venda <  timestamp '2025-07-08 00:00:00';

Observe: A consulta particionada deve acessar somente julho. Compare shared hit/read blocks.

Passo 4

BRIN vs B-tree

-- BRIN para tempo
CREATE INDEX fato_vendas_data_brin
ON aula_dw.fato_vendas_part USING brin (data_venda)
WITH (pages_per_range = 32);

-- B-tree composto
CREATE INDEX fato_vendas_regiao_data_btree
ON aula_dw.fato_vendas_part USING btree (regiao_id, data_venda)
INCLUDE (valor);

ANALYZE aula_dw.fato_vendas_part;

Observe: O plano temporal tende a utilizar BRIN; o filtro de região e tempo tende a utilizar B-tree. Compare também o espaço total ocupado.

PostgreSQL · Passos 5 e 6

Materialized view e manutenção operacional

Persistir agregação, refresh concorrente, ANALYZE e VACUUM.

Passo 5

Materialized view com refresh concorrente

CREATE MATERIALIZED VIEW aula_dw.mv_vendas_dia AS
SELECT
    CAST(data_venda AS date) AS dia,
    regiao_id,
    count(*) AS quantidade,
    CAST(sum(valor) AS numeric(18, 2)) AS receita
FROM aula_dw.fato_vendas_part
GROUP BY CAST(data_venda AS date), regiao_id
WITH DATA;

-- Índice UNIQUE para refresh concorrente
CREATE UNIQUE INDEX mv_vendas_dia_pk
ON aula_dw.mv_vendas_dia (dia, regiao_id);

REFRESH MATERIALIZED VIEW CONCURRENTLY aula_dw.mv_vendas_dia;

Observe: A materialized view reduz linhas lidas, mas permanece defasada entre refreshes. O índice UNIQUE permite atualizar sem bloquear leituras.

Passo 6

VACUUM e ANALYZE

-- Executar com auto-commit
VACUUM (ANALYZE, VERBOSE) aula_dw.fato_vendas_2025_07;
ANALYZE VERBOSE aula_dw.fato_vendas_part;

-- Consultar estatísticas
SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE schemaname = 'aula_dw'
ORDER BY relname;

Observe: Execute VACUUM com auto-commit. Compare rows estimadas e reais, shared hit/read blocks, tipo de scan e Execution Time.

Guia de benchmark

Seis cenários comparativos com evidência mensurável

Para cada cenário: execute três vezes, registre mediana, capture plano e confira resultado.

Mecanismo	Consulta	Estratégia	Tempo mediano	Leitura	Plano	Resultado confere?
ClickHouse	Região + 7 dias	Base / MergeTree otimizada	ms	read_rows e read_bytes	parts e granules	sim/não
ClickHouse	Cliente seletivo	Sem / com skip index	ms	granules	Skip	sim/não
ClickHouse	Receita diária	Fato / agregada	ms	read_rows	Aggregation	sim/não
PostgreSQL	Intervalo de 7 dias	Heap / partições	ms	shared hit/read	Seq Scan / Append	sim/não
PostgreSQL	Região + 7 dias	BRIN / B-tree	ms	buffers	Bitmap / Index Scan	sim/não
PostgreSQL	Receita diária	Fato / materialized view	ms	buffers	Aggregate / Index Scan	sim/não

Primeira execução pode refletir cache frio; as seguintes caracterizam o caminho aquecido.

Configuração do DBeaver

Duas conexões separadas, dialetos isolados

Não misture os dialetos: cada roteiro deve permanecer associado à respectiva conexão.

🔥

ClickHouse

Informe host, porta HTTP 8123 ou HTTPS 8443, database fornecido, usuário e senha. Teste a conexão antes de abrir o editor.

🐘

PostgreSQL

Informe host, porta 5432 ou a porta fornecida, database, usuário e senha. Ative SSL quando o provedor exigir.

Regras: Use auto-commit no bloco de VACUUM e não execute o refresh concorrente em uma transação longa. Não inclua host, usuário ou senha nos scripts compartilhados. Execute um bloco por vez com Ctrl+Enter.

Ponte com a Aula 1 · Spec-Driven Development

A otimização deve ser expressa como especificação verificável

Quatro artefatos tornam a performance mensurável e reproduzível.

RF-002

Requisito funcional

Disponibilizar a receita diária por região para relatórios e painéis analíticos.

RNF-002

Contrato mensurável

Consulta de sete dias atende p95 ≤ 2 s e reduz leitura ≥ 80% em relação à baseline.

ADR-DW-02

Decisão arquitetural

ClickHouse: ordenar por região+data, particionar por mês. PostgreSQL: particionar por mês, B-tree em região+data.

Cenário de aceite

Comportamento esperado

Dado o mesmo conjunto de vendas nos dois mecanismos, Quando a consulta otimizada é executada três vezes, Então o resultado é idêntico ao da baseline.

Entregável da aula

Relatório comparativo com evidências mensuráveis

O relatório deve sustentar a recomendação com resultados, planos de execução e custos introduzidos.

Artefato

Relatório de benchmark

Inclui as seis linhas do benchmark preenchidas, planos de execução antes e depois, interpretação do gargalo e recomendação técnica para o Data Warehouse do projeto.

Os dois roteiros SQL executados no DBeaver sem misturar conexões ou dialetos.

Registro com ao menos três execuções por cenário apresentando mediana.

Cada comparação inclui plano, métrica de leitura e conferência do resultado.

Recomendação distingue ClickHouse e PostgreSQL e explicita custo introduzido.

Materialização possui estratégia de backfill/refresh e defasagem declarada.

Fechamento

Uma estratégia de otimização é aceitável quando comprova redução mensurável do trabalho executado e preserva o resultado da baseline.

ClickHouse Docs — MergeTree table engine ClickHouse Docs — Data skipping indexes ClickHouse Docs — Incremental materialized views ClickHouse Docs — Refreshable materialized views PostgreSQL 18 — Table Partitioning PostgreSQL 18 — Using EXPLAIN PostgreSQL 18 — Materialized Views PostgreSQL 18 — Routine Vacuuming

Pergunta final: qual estratégia apresenta evidência suficiente para integrar o Data Warehouse do projeto?

Sobre este encontro

Otimização de Data Warehouses · 24/08/2026 · Prof. Afonso

Objetivo de aprendizagem

Ao final do encontro, o estudante deve ser capaz de executar e avaliar técnicas de otimização de consultas OLAP no ClickHouse e no PostgreSQL, registrando evidências obtidas antes e depois de cada alteração.

Estratégia do encontro

Laboratório guiado no DBeaver, com o mesmo conjunto sintético de vendas nos dois mecanismos. Cada grupo executa os blocos SQL em ordem, interpreta os planos de execução e registra as diferenças de latência, leitura e cardinalidade.

Estrutura do encontro

Preparação do DBeaver e construção da baseline — 10 min.
ClickHouse: MergeTree, PARTITION BY, PRIMARY KEY e ORDER BY — 30 min.
ClickHouse: skip index, materialized view e query_log — 20 min.
PostgreSQL: particionamento, BRIN, B-tree e EXPLAIN — 30 min.
PostgreSQL: materialized view, ANALYZE e VACUUM — 20 min.
Comparação dos resultados e registro da recomendação — 10 min.

Otimização deData Warehouses

Preparação

ClickHouse

PostgreSQL

Comparação

Isolamento de variáveis e atribuição causal

Ciclo de vida dos dados

Chave primária esparsa

Filtros seletivos fora da PK

Agregação incremental

Eliminação de partições

Block Range INdex

Índice composto seletivo

ANALYZE e VACUUM

Partition pruning vs full scan

Skip index bloom_filter

SummingMergeTree

Materialized view incremental

Preencher a tabela de destino

Consolidar partes ainda não mescladas

Métricas de execução

Partition elimination

BRIN vs B-tree

Materialized view com refresh concorrente

VACUUM e ANALYZE

Requisito funcional

Contrato mensurável

Decisão arquitetural

Comportamento esperado

Relatório de benchmark

Sobre este encontro

Objetivo de aprendizagem

Estratégia do encontro

Estrutura do encontro

Otimização de
Data Warehouses