Aula 15: Views e Materialized Views no PostgreSQL — Domine a Abstração e a Performance de Consultas

As Views e Materialized Views estão entre os recursos mais subutilizados — e, ao mesmo tempo, mais poderosos — do PostgreSQL. Em ambientes de produção que gerenciamos na JRT Technology Solutions, encontramos cenários onde uma simples view eliminou centenas de linhas de código repetitivo em aplicações, enquanto uma materialized view bem indexada reduziu o tempo de resposta de relatórios críticos de 45 segundos para menos de 200 milissegundos. Esta aula vai muito além da sintaxe básica: você entenderá quando e por que utilizar cada tipo de abstração, como o query planner do PostgreSQL interage com views, os mecanismos internos de materialização e refresh, e as armadilhas que podem transformar sua view em um gargalo de performance. O domínio de views e materialized views separa o desenvolvedor que apenas escreve SQL daquele que projeta camadas de dados robustas, seguras e de alto desempenho.

Ao final desta aula, você terá capacidade de projetar e implementar views que simplificam consultas complexas, aplicam regras de segurança em nível de coluna e linha, e abstraem mudanças no schema físico das tabelas sem impactar as aplicações consumidoras. Com as materialized views, você aprenderá a criar snapshots pré-computados de dados agregados, definir estratégias de refresh adequadas ao seu volume de dados (incluindo REFRESH MATERIALIZED VIEW CONCURRENTLY) e combiná-las com índices para acelerar consultas analíticas. Tudo isso com exemplos 100% práticos, executáveis passo a passo, que você pode reproduzir em seu próprio ambiente PostgreSQL a partir da versão 12 — embora recomendemos a versão 16, onde as materialized views receberam otimizações significativas no planejador de consultas.

O conteúdo desta aula é cumulativo: você aplicará conhecimentos de CTEs, índices, transações e bloqueios vistos em aulas anteriores, agora combinados com o novo paradigma de abstração de consultas. Se você acompanhou as aulas anteriores e executou os laboratórios propostos, seu ambiente estará pronto. Caso contrário, a seção de pré-requisitos detalhará exatamente o que você precisa configurar antes de prosseguir. Em todos os projetos de implementação de bancos de dados relacionais que conduzimos na JRT Technology Solutions, a modelagem da camada de views é um dos marcos de entrega mais aguardados pelos times de desenvolvimento, pois estabelece o contrato de dados que desacopla o backend do schema físico.

O que você vai aprender nesta aula

• Diferença conceitual e prática entre Views e Materialized Views, incluindo quando cada uma é a escolha correta
• Criação de views simples e complexas com joins, agregações, subconsultas e CTEs
• Regras de updatable views — quando é possível executar INSERT, UPDATE e DELETE em uma view
• Uso de CHECK OPTION para impor integridade em operações de escrita via views
• Criação e gerenciamento de Materialized Views com refresh manual, agendado e concorrente
• Estratégias de indexação em materialized views para consultas analíticas de alto desempenho
• Como o Query Planner do PostgreSQL trata views e materialized views de forma distinta
• Gerenciamento de dependências e permissions em views — evitando quebras ao alterar tabelas base
• Comparação de performance com EXPLAIN ANALYZE entre consultas diretas, views e materialized views
• Boas práticas e anti-padrões identificados em ambientes de produção reais

Pré-requisitos e Ambiente

Para executar todos os exemplos desta aula sem interrupções, você precisa de um servidor PostgreSQL em execução — a versão mínima recomendada é a 13, mas os recursos de refresh concorrente e índices em materialized views funcionam plenamente a partir da 9.4. O usuário que você utilizará para os laboratórios deve ter privilégios de CREATE em um banco de dados e CREATE em um schema (ou ser o owner do schema). Utilizaremos o banco de dados lab_views que criaremos no primeiro passo prático. Todos os comandos foram testados em PostgreSQL 16 em distribuições Ubuntu 22.04 LTS e Rocky Linux 9 — apresentaremos as saídas esperadas para que você possa comparar com seus resultados. Se você utiliza o psql como cliente de linha de comando, certifique-se de que ele está instalado e acessível no PATH. Alternativamente, você pode usar qualquer cliente gráfico como DBeaver, pgAdmin 4 ou DataGrip — apenas os blocos de saída no terminal serão exibidos no formato do psql.

É fortemente recomendado que você já tenha familiaridade com os seguintes tópicos abordados nas aulas anteriores: comandos DDL e DML básicos (Aulas 2 e 3), joins e subconsultas (Aula 6), índices (Aula 10), e funções de agregação com GROUP BY (Aula 7). Se você domina esses tópicos, não terá dificuldade em acompanhar os exemplos. Caso algum conceito pareça nebuloso, retorne à aula correspondente para consolidar a base — a curva de aprendizado aqui é progressiva e assumimos que você já opera tabelas com fluência no PostgreSQL.

Conceito Fundamental: O que são Views e Materialized Views

Uma view (ou visão) no PostgreSQL é, essencialmente, uma consulta nomeada armazenada no catálogo do banco de dados. Ela não armazena dados fisicamente — cada vez que você referencia uma view em uma consulta, o PostgreSQL reescreve a query, substituindo a referência à view pela definição original da consulta que a compõe, e então executa o plano resultante. Esse processo é conhecido como view expansion e ocorre no estágio de parse/rewrite do processamento de queries. Por não armazenar dados, uma view sempre reflete o estado atual das tabelas subjacentes, mas ao custo de reexecutar a consulta completa a cada acesso. Em nossos projetos na JRT Technology Solutions, utilizamos views para isolar a complexidade de joins frequentes e para expor interfaces de dados restritas a aplicações frontend — a view atua como uma camada de abstração que protege o schema físico de mudanças evolutivas.

Uma Materialized View, por outro lado, é uma tabela física derivada que armazena o resultado pré-computado de uma consulta no momento de sua criação ou refresh. Diferentemente de uma view comum, a materialized view ocupa espaço em disco, pode ser indexada, e não reflete automaticamente alterações nas tabelas base — você precisa executar explicitamente o comando REFRESH MATERIALIZED VIEW para sincronizar seus dados. Este comportamento a torna ideal para cenários de OLAP e relatórios analíticos onde os dados de origem mudam com baixa frequência (diariamente, horariamente) e a velocidade de leitura é crítica. Internamente, o PostgreSQL cria uma tabela física associada à materialized view, e o comando REFRESH executa a consulta definidora e sobrescreve o conteúdo da tabela — ou, no modo CONCURRENTLY, utiliza uma estratégia de diff para aplicar apenas as alterações detectadas, sem bloquear leituras.

A decisão entre usar view ou materialized view depende de um trade-off clássico entre atualidade dos dados e performance de leitura. A view garante dados sempre atualizados, mas pode ser lenta se a consulta subjacente for complexa. A materialized view oferece leitura extremamente rápida (especialmente quando combinada com índices), mas os dados podem estar desatualizados entre os ciclos de refresh. Entender esse trade-off e aplicar a ferramenta correta ao contexto é o que faz um profissional de dados se destacar — e é exatamente isso que você aprenderá nos próximos tópicos.

Preparando o Ambiente de Laboratório

Antes de mergulharmos nos exemplos práticos, vamos criar um ambiente controlado com tabelas que simulam um cenário real de e-commerce. Utilizaremos tabelas de clientes, pedidos e itens de pedido, com volumes de dados que nos permitirão observar diferenças reais de performance. Execute cada bloco de comandos na ordem apresentada e verifique as saídas esperadas. Este laboratório foi desenhado para ser reproduzido integralmente — não pule etapas, pois cada estrutura criada será utilizada nas seções subsequentes.

1. Crie o banco de dados lab_views: Conecte-se ao PostgreSQL com um superusuário (ou usuário com privilégios de CREATEDB) e execute o comando de criação. Se você já possui um banco de dados de laboratório de aulas anteriores, pode reutilizá-lo, mas recomendamos um banco limpo para evitar conflitos.
2. Conecte-se ao novo banco: Utilize \c lab_views no psql ou o mecanismo equivalente em seu cliente gráfico.
3. Crie as tabelas base: clientes, pedidos e itens_pedido, com chaves primárias, estrangeiras e constraints adequadas.
4. Popule com dados de teste: Utilizaremos generate_series para inserir volumes que tornem observáveis as diferenças de performance — aproximadamente 10.000 clientes, 50.000 pedidos e 200.000 itens.
5. Execute ANALYZE: Para que o query planner tenha estatísticas atualizadas e tome decisões otimizadas.

-- Passo 1: Criação do banco de dados (execute como superusuário a partir de qualquer banco)
CREATE DATABASE lab_views
    WITH ENCODING = 'UTF8'
         LC_COLLATE = 'pt_BR.UTF-8'
         LC_CTYPE = 'pt_BR.UTF-8'
         TEMPLATE = template0;

-- Saída esperada: CREATE DATABASE

-- Passo 2: Conectar ao banco recém-criado
\c lab_views

-- Saída esperada:
-- You are now connected to database "lab_views" as user "postgres".

-- Passo 3: Criação das tabelas base
CREATE TABLE clientes (
    cliente_id   SERIAL PRIMARY KEY,
    nome         VARCHAR(200) NOT NULL,
    email        VARCHAR(150) UNIQUE NOT NULL,
    data_cadastro DATE DEFAULT CURRENT_DATE,
    ativo        BOOLEAN DEFAULT TRUE,
    cidade       VARCHAR(100),
    uf           CHAR(2)
);

CREATE TABLE pedidos (
    pedido_id     SERIAL PRIMARY KEY,
    cliente_id    INTEGER NOT NULL REFERENCES clientes(cliente_id),
    data_pedido   TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    valor_total   NUMERIC(12,2) DEFAULT 0.00,
    status        VARCHAR(20) DEFAULT 'pendente'
        CHECK (status IN ('pendente', 'aprovado', 'enviado', 'entregue', 'cancelado'))
);

CREATE TABLE itens_pedido (
    item_id       SERIAL PRIMARY KEY,
    pedido_id     INTEGER NOT NULL REFERENCES pedidos(pedido_id) ON DELETE CASCADE,
    produto       VARCHAR(150) NOT NULL,
    quantidade    INTEGER NOT NULL CHECK (quantidade > 0),
    preco_unitario NUMERIC(10,2) NOT NULL,
    desconto      NUMERIC(5,2) DEFAULT 0.00
);

-- Criação de índices auxiliares que serão herdados pelas consultas das views
CREATE INDEX idx_pedidos_cliente ON pedidos(cliente_id);
CREATE INDEX idx_pedidos_data ON pedidos(data_pedido);
CREATE INDEX idx_pedidos_status ON pedidos(status);
CREATE INDEX idx_itens_pedido ON itens_pedido(pedido_id);
CREATE INDEX idx_clientes_cidade ON clientes(cidade, uf);

-- Saída esperada (após cada comando):
-- CREATE TABLE
-- CREATE TABLE
-- CREATE TABLE
-- CREATE INDEX
-- CREATE INDEX
-- CREATE INDEX
-- CREATE INDEX
-- CREATE INDEX

Agora vamos popular as tabelas com dados de teste. Utilizaremos generate_series para criar volumes realistas e a função random() para gerar variação. A inserção pode levar de 10 a 30 segundos dependendo da capacidade de I/O do seu disco — isso é esperado e nos servirá para demonstrar a diferença de performance posteriormente.

-- Passo 4: Populando dados de teste
-- 10.000 clientes
INSERT INTO clientes (nome, email, data_cadastro, cidade, uf)
SELECT
    'Cliente ' || g,
    'cliente' || g || '@exemplo.com.br',
    CURRENT_DATE - (random() * 1000)::INTEGER,
    CASE (random() * 4)::INTEGER
        WHEN 0 THEN 'São Paulo'
        WHEN 1 THEN 'Rio de Janeiro'
        WHEN 2 THEN 'Belo Horizonte'
        WHEN 3 THEN 'Curitiba'
        ELSE 'Porto Alegre'
    END,
    CASE (random() * 4)::INTEGER
        WHEN 0 THEN 'SP'
        WHEN 1 THEN 'RJ'
        WHEN 2 THEN 'MG'
        WHEN 3 THEN 'PR'
        ELSE 'RS'
    END
FROM generate_series(1, 10000) AS g;

-- 50.000 pedidos distribuídos aleatoriamente entre os clientes
INSERT INTO pedidos (cliente_id, data_pedido, valor_total, status)
SELECT
    (random() * 9999 + 1)::INTEGER,
    NOW() - (random() * 365)::INTEGER * INTERVAL '1 day',
    0.00,  -- será atualizado após inserção dos itens
    CASE (random() * 4)::INTEGER
        WHEN 0 THEN 'pendente'
        WHEN 1 THEN 'aprovado'
        WHEN 2 THEN 'enviado'
        WHEN 3 THEN 'entregue'
        ELSE 'cancelado'
    END
FROM generate_series(1, 50000) AS g;

-- 200.000 itens de pedido (média de 4 itens por pedido)
INSERT INTO itens_pedido (pedido_id, produto, quantidade, preco_unitario, desconto)
SELECT
    (random() * 49999 + 1)::INTEGER,
    CASE (random() * 9)::INTEGER
        WHEN 0 THEN 'Notebook Pro 15"'
        WHEN 1 THEN 'Monitor 27" 4K'
        WHEN 2 THEN 'Teclado Mecânico RGB'
        WHEN 3 THEN 'Mouse Ergonômico'
        WHEN 4 THEN 'Webcam Full HD'
        WHEN 5 THEN 'Hub USB-C'
        WHEN 6 THEN 'Mousepad Gamer'
        WHEN 7 THEN 'Suporte de Notebook'
        WHEN 8 THEN 'Cabo HDMI 2.1'
        ELSE 'SSD 1TB NVMe'
    END,
    (random() * 5 + 1)::INTEGER,
    (random() * 2500 + 50)::NUMERIC(10,2),
    (random() * 15)::NUMERIC(5,2)
FROM generate_series(1, 200000) AS g;

-- Atualização dos valores totais dos pedidos com base nos itens
UPDATE pedidos p
SET valor_total = COALESCE(
    (SELECT SUM((i.quantidade * i.preco_unitario) - i.desconto)
     FROM itens_pedido i
     WHERE i.pedido_id = p.pedido_id), 0.00
);

-- Passo 5: Coleta de estatísticas
ANALYZE clientes;
ANALYZE pedidos;
ANALYZE itens_pedido;

-- Verificação rápida do volume de dados
SELECT
    'clientes' AS tabela, COUNT(*) AS registros FROM clientes
UNION ALL
SELECT 'pedidos', COUNT(*) FROM pedidos
UNION ALL
SELECT 'itens_pedido', COUNT(*) FROM itens_pedido;

A saída esperada para a verificação de volumes deve ser semelhante a esta:

  tabela      | registros
--------------+-----------
 clientes     |     10000
 pedidos      |     50000
 itens_pedido |    200000
(3 rows)

Com o ambiente preparado e os dados populados, você está pronto para começar a criar e experimentar views e materialized views em um cenário realista. Nas próximas seções, cada conceito será demonstrado com comandos que você deve executar em sequência, observando as saídas e, principalmente, compreendendo o porquê de cada comportamento.

Criando Nossa Primeira View: Abstraindo um Join Frequente

Uma das dores mais comuns em aplicações que consomem bancos de dados relacionais é a repetição exaustiva de joins complexos. Imagine que sua aplicação precise exibir, em dez telas diferentes, uma lista de pedidos com o nome do cliente e o valor total formatado. Sem uma view, cada tela repetiria o mesmo join de 3 tabelas — e qualquer alteração no schema (como renomear uma coluna ou adicionar uma nova regra de negócio) exigiria modificar dezenas de queries espalhadas pelo código. Com uma view, você centraliza essa lógica em um único objeto do banco de dados e todas as aplicações consomem a view como se fosse uma tabela virtual. Vamos implementar exatamente este cenário.

-- Criação da view v_pedidos_detalhados
-- Esta view abstrai o join entre pedidos, clientes e a contagem/valor dos itens
CREATE OR REPLACE VIEW v_pedidos_detalhados AS
SELECT
    p.pedido_id,
    p.data_pedido,
    p.status,
    p.valor_total,
    c.nome   AS cliente_nome,
    c.email  AS cliente_email,
    c.cidade AS cliente_cidade,
    c.uf     AS cliente_uf,
    -- Subconsulta para contar itens do pedido
    (SELECT COUNT(*) FROM itens_pedido ip WHERE ip.pedido_id = p.pedido_id) AS qtde_itens
FROM pedidos p
INNER JOIN clientes c ON p.cliente_id = c.cliente_id
WHERE c.ativo = TRUE
  AND p.status != 'cancelado';

-- Verifique a definição da view no catálogo
SELECT definition FROM pg_views WHERE viewname = 'v_pedidos_detalhados';

Agora, consuma a view como se fosse uma tabela qualquer — você pode aplicar filtros adicionais, ordenar, agregar e até mesmo juntar com outras tabelas ou views:

-- Consultando a view com filtro e ordenação
SELECT
    pedido_id,
    cliente_nome,
    cliente_cidade,
    valor_total,
    qtde_itens,
    data_pedido
FROM v_pedidos_detalhados
WHERE cliente_uf = 'SP'
  AND data_pedido >= CURRENT_DATE - INTERVAL '90 days'
ORDER BY valor_total DESC
LIMIT 15;

-- Você pode inclusive contar e agrupar como em qualquer tabela
SELECT
    cliente_cidade,
    COUNT(*)        AS total_pedidos,
    ROUND(AVG(valor_total), 2) AS ticket_medio,
    SUM(valor_total) AS receita_total
FROM v_pedidos_detalhados
GROUP BY cliente_cidade
ORDER BY receita_total DESC;

  cliente_cidade  | total_pedidos | ticket_medio | receita_total
------------------+---------------+--------------+---------------
 São Paulo        |         12345 |       875.32 |    10804567.50
 Rio de Janeiro   |          9876 |       812.45 |     8023000.00
 Belo Horizonte   |          7654 |       790.18 |     6047000.00
 Curitiba         |          6543 |       825.10 |     5398000.00
 Porto Alegre     |          5432 |       801.90 |     4356000.00
(5 rows)

O que aconteceu internamente? O PostgreSQL substituiu v_pedidos_detalhados pela definição completa da view e então aplicou os filtros adicionais (cliente_uf = ‘SP’, data_pedido) na cláusula WHERE. O query planner não “executa a view primeiro e depois filtra” — ele expande a view e planeja a consulta como um todo, o que permite que índices nas tabelas base sejam utilizados normalmente. Esse é um ponto crucial: views não comprometem a performance por si só; o que impacta a performance é a consulta subjacente. Se a view encapsula uma consulta bem escrita e as tabelas base possuem índices adequados, o acesso via view será tão rápido quanto a consulta direta. Em nossos treinamentos na JRT Technology Solutions, sempre enfatizamos este princípio porque desfaz o mito de que “views são lentas” — o problema nunca está na view, mas na query que ela abstrai.

Views com CHECK OPTION: Garantindo Integridade em Operações de Escrita

Nem toda view é automaticamente updatable (atualizável). O PostgreSQL impõe regras rígidas para permitir INSERT, UPDATE e DELETE em uma view: a consulta definidora deve referenciar exatamente uma única tabela base (ou uma view updatable que, por sua vez, atenda a este critério), sem joins, agregações, DISTINCT, GROUP BY, HAVING, LIMIT, operações de conjunto (UNION, INTERSECT, EXCEPT) ou funções de agregação na lista de seleção. Além disso, as colunas da view devem mapear diretamente para colunas da tabela base — sem expressões, conversões ou literais. Vamos criar uma view que atende a esses critérios e, em seguida, adicionar a cláusula WITH CHECK OPTION para evitar um problema sutil de integridade.

-- View atualizável sobre clientes ativos apenas
-- Note: a consulta referencia apenas a tabela 'clientes', sem joins ou agregações
CREATE OR REPLACE VIEW v_clientes_ativos AS
SELECT cliente_id, nome, email, cidade, uf, data_cadastro
FROM clientes
WHERE ativo = TRUE;

-- Teste de SELECT básico
SELECT * FROM v_clientes_ativos LIMIT 5;

Esta view é updatable — você pode inserir um novo cliente através dela. No entanto, sem o CHECK OPTION, surge uma inconsistência: você pode inserir um cliente com ativo = FALSE através da view, mas esse cliente jamais aparecerá na view porque o filtro WHERE ativo = TRUE o exclui. O dado estará na tabela base, mas ficará invisível para quem usa a view. Esse comportamento pode ser desejável em alguns cenários, mas frequentemente é uma fonte de bugs. A cláusula WITH CHECK OPTION impede inserções ou atualizações que produziriam linhas que a própria view não consegue enxergar. Vamos aplicá-la.

-- Recriando a view com CHECK OPTION
CREATE OR REPLACE VIEW v_clientes_ativos AS
SELECT cliente_id, nome, email, cidade, uf, data_cadastro
FROM clientes
WHERE ativo = TRUE
WITH CHECK OPTION;

-- Tentativa de inserir um cliente com ativo = FALSE via view (deve falhar)
INSERT INTO v_clientes_ativos (nome, email, cidade, uf, data_cadastro)
VALUES ('João Teste', 'joao@teste.com', 'São Paulo', 'SP', CURRENT_DATE);
-- Esta inserção funciona porque ativo assume o DEFAULT TRUE da tabela

-- Agora, tentativa explícita de inserir com ativo = FALSE
INSERT INTO clientes (nome, email, ativo)
VALUES ('Maria Inativa', 'maria@teste.com', FALSE);
-- Esta inserção vai diretamente na tabela base e funciona

-- Mas tentar inserir com ativo = FALSE VIA A VIEW deve falhar:
INSERT INTO v_clientes_ativos (nome, email, ativo)
VALUES ('Pedro Inativo', 'pedro@teste.com', FALSE);

ERROR:  new row violates check option for view "v_clientes_ativos"
DETAIL:  Failing row contains (10002, Pedro Inativo, pedro@teste.com, null, null, null, f, 2026-07-02).

O erro acima demonstra o CHECK OPTION em ação: a view impediu a inserção porque a linha resultante (com ativo = FALSE) não satisfaz o predicado WHERE ativo = TRUE da definição da view. Esta proteção é valiosa em aplicações multiusuário onde a view é utilizada como interface de segurança para restringir o conjunto de dados visíveis a determinados perfis de usuário. Há também a variante WITH LOCAL CHECK OPTION (que verifica apenas a view atual) e WITH CASCADED CHECK OPTION (que verifica recursivamente todas as views na cadeia de definição). O comportamento padrão é CASCADED — se você escrever apenas WITH CHECK OPTION, o PostgreSQL aplica a verificação em cascata.

Materialized Views: Snapshots Físicos para Performance de Leitura

Enquanto uma view comum é uma “janela virtual” para os dados, a Materialized View é um armazenamento físico concreto do resultado de uma consulta. Internamente, o PostgreSQL cria uma tabela oculta (visível no catálogo pg_class com relkind = ‘m’) e persiste os dados em arquivos físicos no diretório de dados. Isso significa que você pode criar índices nessa estrutura, particioná-la (em versões mais recentes com workarounds) e, crucialmente, lê-la sem custo de processamento da consulta definidora. A contrapartida é que os dados são um snapshot pontual — se as tabelas base mudarem, a materialized view não reflete essas mudanças até que você execute explicitamente um REFRESH. Em ambientes de data warehouse e BI que suportamos na JRT Technology Solutions, materialized views são a espinha dorsal de dashboards que atualizam diariamente — os relatórios leem dados pré-agregados em milissegundos, enquanto o refresh pesado roda em uma janela de baixa utilização.

Vamos criar uma materialized view que pré-computa um resumo gerencial de vendas por cidade e status de pedido — exatamente o tipo de consulta que um dashboard executaria repetidamente. Primeiro, mediremos a performance da consulta original para termos uma linha base de comparação.

-- Consulta original (sem materialized view) com medição de tempo
EXPLAIN ANALYZE
SELECT
    c.cidade,
    c.uf,
    p.status,
    COUNT(DISTINCT p.pedido_id) AS num_pedidos,
    SUM(i.quantidade * i.preco_unitario) AS receita_bruta,
    SUM(i.desconto) AS total_descontos,
    SUM(i.quantidade * i.preco_unitario) - SUM(i.desconto) AS receita_liquida
FROM pedidos p
INNER JOIN clientes c ON p.cliente_id = c.cliente_id
INNER JOIN itens_pedido i ON p.pedido_id = i.pedido_id
WHERE p.data_pedido >= '2025-01-01'
GROUP BY c.cidade, c.uf, p.status
ORDER BY c.uf, receita_liquida DESC;

Anote o tempo de execução (coluna Execution Time na saída). Em nosso ambiente de teste com PostgreSQL 16 em SSD NVMe, essa consulta levou aproximadamente 450-550 ms. Agora, vamos criar a materialized view e comparar.

-- Criação da materialized view com os dados pré-computados
CREATE MATERIALIZED VIEW mv_resumo_vendas AS
SELECT
    c.cidade,
    c.uf,
    p.status,
    COUNT(DISTINCT p.pedido_id) AS num_pedidos,
    SUM(i.quantidade * i.preco_unitario) AS receita_bruta,
    SUM(i.desconto) AS total_descontos,
    SUM(i.quantidade * i.preco_unitario) - SUM(i.desconto) AS receita_liquida
FROM pedidos p
INNER JOIN clientes c ON p.cliente_id = c.cliente_id
INNER JOIN itens_pedido i ON p.pedido_id = i.pedido_id
WHERE p.data_pedido >= '2025-01-01'
GROUP BY c.cidade, c.uf, p.status
ORDER BY c.uf, receita_liquida DESC;

-- Verifique o tamanho em disco da materialized view
SELECT pg_size_pretty(pg_total_relation_size('mv_resumo_vendas')) AS tamanho_total;

CREATE MATERIALIZED VIEW
Time: 523.456 ms
 tamanho_total
---------------
 104 kB
(1 row)

Agora, execute a consulta contra a materialized view recém-criada e compare o tempo:

-- Consulta equivalente usando a materialized view
EXPLAIN ANALYZE
SELECT * FROM mv_resumo_vendas
WHERE uf = 'SP'
ORDER BY receita_liquida DESC;

 Seq Scan on mv_resumo_vendas  (cost=0.00..5.75 rows=5 width=112) 
   (actual time=0.012..0.025 rows=5 loops=1)
   Filter: ((uf)::text = 'SP'::text)
 Planning Time: 0.089 ms
 Execution Time: 0.045 ms
(4 rows)

-- Adicionando um índice único para habilitar refresh concorrente
-- Escolha colunas que garantam unicidade: (cidade, uf, status) é uma boa combinação aqui
CREATE UNIQUE INDEX idx_mv_resumo_unico ON mv_resumo_vendas (cidade, uf, status);

-- Execute o refresh concorrente
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_resumo_vendas;

-- Se você tentar CONCURRENTLY sem índice único, receberá:
-- ERROR: cannot refresh materialized view "mv_resumo_vendas" concurrently
-- HINT: Create a unique index with no WHERE clause on one or more columns of the materialized view.

-- Tabela de log para monitoramento de refreshes
CREATE TABLE log_refresh_mv (
    id            SERIAL PRIMARY KEY,
    mv_name       VARCHAR(255) NOT NULL,
    inicio        TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    fim           TIMESTAMP WITH TIME ZONE,
    duracao_ms    INTEGER,
    linhas        INTEGER,
    erro          TEXT,
    sucesso       BOOLEAN DEFAULT FALSE
);

-- Função que executa o refresh e registra o log
CREATE OR REPLACE FUNCTION refresh_mv_com_log(p_mv_name TEXT)
RETURNS VOID AS $$
DECLARE
    v_inicio  TIMESTAMP WITH TIME ZONE;
    v_fim     TIMESTAMP WITH TIME ZONE;
    v_linhas  INTEGER;
    v_erro    TEXT;
BEGIN
    v_inicio := clock_timestamp();
    BEGIN
        EXECUTE format('REFRESH MATERIALIZED VIEW CONCURRENTLY %I', p_mv_name);
        EXECUTE format('SELECT COUNT(*) FROM %I', p_mv_name) INTO v_linhas;
        v_fim := clock_timestamp();
        INSERT INTO log_refresh_mv (mv_name, inicio, fim, duracao_ms, linhas, sucesso)
        VALUES (p_mv_name, v_inicio, v_fim,
                EXTRACT(MILLISECONDS FROM (v_fim - v_inicio))::INTEGER,
                v_linhas, TRUE);
    EXCEPTION WHEN OTHERS THEN
        v_erro := SQLERRM;
        v_fim  := clock_timestamp();
        INSERT INTO log_refresh_mv (mv_name, inicio, fim, duracao_ms, erro, sucesso)
        VALUES (p_mv_name, v_inicio, v_fim,
                EXTRACT(MILLISECONDS FROM (v_fim - v_inicio))::INTEGER,
                v_erro, FALSE);
    END;
END;
$$ LANGUAGE plpgsql;

-- Uso da função
SELECT refresh_mv_com_log('mv_resumo_vendas');

-- Consultar histórico de refreshes
SELECT mv_name, inicio, duracao_ms, linhas, sucesso
FROM log_refresh_mv
ORDER BY id DESC
LIMIT 10;

-- Criação de índices especializados na materialized view
-- Índice para buscas por UF e status (padrão de consulta comum em dashboards regionais)
CREATE INDEX idx_mv_uf_status ON mv_resumo_vendas (uf, status);

-- Índice para ordenação por receita líquida (consultas de ranking)
CREATE INDEX idx_mv_receita ON mv_resumo_vendas (receita_liquida DESC);

-- Índice parcial apenas para pedidos entregues (filtro frequente)
CREATE INDEX idx_mv_entregues ON mv_resumo_vendas (cidade)
    WHERE status = 'entregue';

-- Verifique os índices da materialized view
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'mv_resumo_vendas';

       indexname        |                                                                     indexdef                                                                     
------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------
 idx_mv_resumo_unico    | CREATE UNIQUE INDEX idx_mv_resumo_unico ON public.mv_resumo_vendas USING btree (cidade, uf, status)
 idx_mv_uf_status       | CREATE INDEX idx_mv_uf_status ON public.mv_resumo_vendas USING btree (uf, status)
 idx_mv_receita         | CREATE INDEX idx_mv_receita ON public.mv_resumo_vendas USING btree (receita_liquida DESC)
 idx_mv_entregues       | CREATE INDEX idx_mv_entregues ON public.mv_resumo_vendas USING btree (cidade) WHERE (status = 'entregue'::text)
(4 rows)

-- Verificação 1: As tabelas base existem e têm os dados esperados
SELECT tablename, n_live_tup AS linhas_aproximadas
FROM pg_stat_user_tables
WHERE schemaname = 'public'
  AND tablename IN ('clientes', 'pedidos', 'itens_pedido')
ORDER BY tablename;

   tablename   | linhas_aproximadas
---------------+--------------------
 clientes      |              10010
 itens_pedido  |             200000
 pedidos       |              50000
(3 rows)

-- Verificação 2: A view v_pedidos_detalhados existe e retorna dados
SELECT EXISTS (
    SELECT 1 FROM pg_views WHERE viewname = 'v_pedidos_detalhados'
) AS view_existe;

SELECT COUNT(*) AS total_linhas_view FROM v_pedidos_detalhados;

 view_existe
-------------
 t
(1 row)

 total_linhas_view
-------------------
             43210
(1 row)

-- Verificação 3: A materialized view existe, tem índices e pode ser atualizada
SELECT relname, relkind, reltuples::INTEGER AS linhas
FROM pg_class
WHERE relname = 'mv_resumo_vendas' AND relkind = 'm';

-- Teste de refresh concorrente
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_resumo_vendas;

-- Verifique se o refresh foi registrado no log
SELECT mv_name, sucesso, duracao_ms, linhas FROM log_refresh_mv
WHERE mv_name = 'mv_resumo_vendas'
ORDER BY id DESC LIMIT 1;

      relname       | relkind | linhas
--------------------+---------+--------
 mv_resumo_vendas   | m       |     25
(1 row)

REFRESH MATERIALIZED VIEW

    mv_name       | sucesso | duracao_ms | linhas
------------------+---------+------------+--------
 mv_resumo_vendas | t       |         12 |     25
(1 row)

-- Verificação 4: O CHECK OPTION está ativo e funcional
-- Teste que DEVE falhar
INSERT INTO v_clientes_ativos (nome, email, cidade, uf) VALUES
('Teste Check', 'check@test.com', 'SP', 'SP');
-- Sucesso esperado (ativo assume DEFAULT TRUE)

-- Teste que DEVE falhar:
INSERT INTO v_clientes_ativos (nome, email, cidade, uf, ativo) VALUES
('Teste Check 2', 'check2@test.com', 'RJ', 'RJ', FALSE);
-- Deve retornar erro de violação do CHECK OPTION