Como usar visualizações materializadas no Hologres - Hologres: Central de Documentação do Alibaba Cloud

As visualizações materializadas em tempo real do Hologres pré-agregam os dados da tabela base e armazenam os resultados. As consultas leem diretamente da visualização em vez de recalcular a agregação dinamicamente, o que reduz a latência das consultas e a sobrecarga computacional.

Diferentemente das visualizações materializadas tradicionais, que exigem atualização manual, as visualizações materializadas do Hologres são atualizadas automaticamente. As alterações gravadas na tabela base refletem-se imediatamente na visualização, sem necessidade de jobs de atualização agendados.

Materialized view structure

A tabela que recebe gravações em tempo real é chamada de tabela base. Todas as operações INSERT, UPDATE e DELETE têm como alvo a tabela base. A visualização materializada é definida por regras de agregação na tabela base e reflete as alterações de INSERT em tempo real. O suporte à sincronização de alterações de UPDATE e DELETE será adicionado em uma versão futura.

Quando usar visualizações materializadas

As visualizações materializadas são adequadas quando:

Consultas agregam repetidamente grandes volumes de dados brutos (painéis, relatórios, análises)
O resultado da agregação é consultado com muito mais frequência do que a tabela base recebe gravações
A latência de consulta na tabela base representa um gargalo, mesmo com o uso de índices

Evite visualizações materializadas quando o resultado da agregação tiver aproximadamente o mesmo número de linhas da tabela base (baixa taxa de compressão) ou quando o padrão de consulta mudar frequentemente, tornando a chave GROUP BY fixa impraticável.

Funções de agregação suportadas

Função	Observações
`SUM`
`COUNT`
`AVG`
`MIN`
`MAX`
`RB_BUILD_CARDINALITY_AGG`	Apenas para o tipo de dado BIGINT. Requer a extensão `roaringbitmap`.

Pré-requisitos

Antes de começar, verifique se você possui:

Uma instância do Hologres com acesso de gravação
A extensão roaringbitmap criada (necessária apenas para RB_BUILD_CARDINALITY_AGG)

Criar uma visualização materializada em tempo real

Crie a tabela base e a visualização materializada juntas em uma única transação. A tabela base deve ter o tipo de mutação appendonly.

BEGIN;
CREATE TABLE base_sales(
  day  text        NOT NULL,
  hour int,
  ts   timestamptz,
  amount float,
  pk   text        NOT NULL PRIMARY KEY
);
CALL SET_TABLE_PROPERTY('base_sales', 'mutate_type', 'appendonly');

-- After dropping the materialized view, remove the appendonly property if needed:
-- CALL SET_TABLE_PROPERTY('base_sales', 'mutate_type', 'none');

CREATE MATERIALIZED VIEW mv_sales AS
  SELECT
    day,
    hour,
    avg(amount) AS amount_avg
  FROM base_sales
  GROUP BY day, hour;

COMMIT;

Insira dados na tabela base. A visualização reflete os novos dados imediatamente.

INSERT INTO base_sales VALUES (to_char(now(),'YYYYMMDD'), '12', now(), 100, 'pk1');
INSERT INTO base_sales VALUES (to_char(now(),'YYYYMMDD'), '12', now(), 200, 'pk2');
INSERT INTO base_sales VALUES (to_char(now(),'YYYYMMDD'), '12', now(), 300, 'pk3');

Consultar uma visualização materializada

Consulte a visualização diretamente usando SQL padrão.

SELECT * FROM mv_sales WHERE day = to_char(now(),'YYYYMMDD') AND hour = 12;

Alternativamente, consulte a tabela base e permita que o roteamento inteligente redirecione automaticamente para a visualização. Consulte Roteamento inteligente para visualizações materializadas.

Criar uma visualização materializada para uma tabela particionada

Quando a tabela base for particionada, a chave GROUP BY da visualização materializada deve incluir a coluna da chave de partição. Crie a visualização materializada na tabela pai, e não nas tabelas filhas individuais.

BEGIN;
CREATE TABLE base_sales_p(
  day    text NOT NULL,
  hour   int,
  ts     timestamptz,
  amount float,
  pk     text NOT NULL,
  PRIMARY KEY (day, pk)
) PARTITION BY LIST(day);
CALL SET_TABLE_PROPERTY('base_sales_p', 'mutate_type', 'appendonly');

-- day is the partition key and must appear in the GROUP BY clause
CREATE MATERIALIZED VIEW mv_sales_p AS
  SELECT
    day,
    hour,
    avg(amount) AS amount_avg
  FROM base_sales_p
  GROUP BY day, hour;
COMMIT;

-- Use CREATE TABLE PARTITION OF to add partitions (ATTACH PARTITION is not supported)
CREATE TABLE base_sales_20220101 PARTITION OF base_sales_p FOR VALUES IN ('20220101');

Mais operações

Verificar o tamanho de armazenamento

Verifique o tamanho de armazenamento de uma única visualização materializada:

SELECT pg_relation_size('mv_sales');

Liste todas as visualizações materializadas ordenadas por tamanho de armazenamento:

SELECT schemaname || '.' || matviewname AS mv_full_name,
       pg_size_pretty(pg_relation_size('"' || schemaname || '"."' || matviewname || '"')) AS mv_size,
       pg_relation_size('"' || schemaname || '"."' || matviewname || '"') AS order_size
FROM pg_matviews
ORDER BY order_size DESC;

Excluir uma visualização materializada

DROP MATERIALIZED VIEW mv_sales;

Cálculo preciso de UV com RoaringBitmap

O cálculo preciso de visitantes únicos (UV) — contagem de IDs de usuário distintos — exige muitos recursos computacionais e é um gargalo de desempenho comum. A função RB_BUILD_CARDINALITY_AGG pré-agrega campos de ID de negócio BIGINT em um RoaringBitmap na visualização materializada, permitindo a deduplicação em tempo real. Apenas campos BIGINT são suportados para essa agregação.

-- The roaringbitmap extension must be created before use
CREATE EXTENSION IF NOT EXISTS roaringbitmap;

BEGIN;
CREATE TABLE base_sales_r(
  day    text        NOT NULL,
  hour   int,
  ts     timestamptz,
  amount float,
  userid bigint,
  pk     text        NOT NULL PRIMARY KEY
);
CALL SET_TABLE_PROPERTY('base_sales_r', 'mutate_type', 'appendonly');

CREATE MATERIALIZED VIEW mv_sales_r AS
  SELECT
    day,
    hour,
    avg(amount)                      AS amount_avg,
    rb_build_cardinality_agg(userid) AS user_count
  FROM base_sales_r
  GROUP BY day, hour;

COMMIT;

INSERT INTO base_sales_r VALUES (to_char(now(),'YYYYMMDD'), '12', now(), 100, 1, 'pk1');
INSERT INTO base_sales_r VALUES (to_char(now(),'YYYYMMDD'), '12', now(), 200, 2, 'pk2');
INSERT INTO base_sales_r VALUES (to_char(now(),'YYYYMMDD'), '12', now(), 300, 3, 'pk3');

-- user_count holds the count of distinct userid values for that day+hour
SELECT user_count AS UV FROM mv_sales_r WHERE day = to_char(now(),'YYYYMMDD') AND hour = 12;

Agregação multidimensional com funções parciais

Por que o AVG padrão falha entre dimensões

Uma visualização materializada armazena resultados pré-agregados. Se você definir mv_sales com avg(amount) agrupado por (day, hour), consultar apenas pela dimensão day produzirá resultados incorretos, pois a média das médias não corresponde à média total.

Considerando estes dados base:

Dia	Hora	Valor	PK
20210101	12	2	pk1
20210101	12	4	pk2
20210101	13	6	pk3

Uma consulta direta na visualização retorna:

postgres=> SELECT * FROM mv_sales;
    day    | hour | amount_avg
-----------+------+------------
  20210101 |   12 |     3
  20210101 |   13 |     6

Reagregar por day gera um resultado incorreto:

postgres=> SELECT day, avg(amount_avg) FROM mv_sales GROUP BY day;
    day    |  avg
-----------+------
  20210101 |  4.5    -- incorrect: true average is 4

Usar estado de agregação intermediário

Em vez de armazenar a média final, armazene o estado de agregação intermediário usando avg_partial. Ao consultar por uma dimensão diferente, finalize o resultado com avg_final.

BEGIN;
CREATE TABLE base_sales(
  day    text        NOT NULL,
  hour   int,
  ts     timestamptz,
  amount float,
  pk     text        NOT NULL PRIMARY KEY
);
CALL SET_TABLE_PROPERTY('base_sales', 'mutate_type', 'appendonly');

CREATE MATERIALIZED VIEW mv_sales_partial AS
  SELECT
    day,
    hour,
    avg(amount)         AS avg,
    avg_partial(amount) AS amt_avg_partial   -- stores intermediate state
  FROM base_sales
  GROUP BY day, hour;

COMMIT;

Consulte por uma dimensão de agregação diferente usando avg_final:

postgres=> SELECT day, avg(avg) AS avg_avg, avg_final(amt_avg_partial) AS real_avg
           FROM mv_sales_partial
           GROUP BY day;
    day    | avg_avg | real_avg
-----------+---------+----------
  20210101 |     4.5 |        4   -- real_avg is correct

Referência de funções parciais e finais

Função padrão	Função parcial	Função final
AVG	AVG_PARTIAL	AVG_FINAL
RB_BUILD_CARDINALITY_AGG	RB_BUILD_AGG	RB_OR_CARDINALITY_AGG

Roteamento inteligente para visualizações materializadas

Consulte a tabela base diretamente — o otimizador reescreve automaticamente a consulta para usar uma visualização materializada correspondente. Não é necessário alterar a sintaxe da consulta.

O otimizador seleciona uma visualização quando todas as condições a seguir forem verdadeiras:

A visualização contém todas as colunas referenciadas na consulta (ou colunas das quais os valores consultados podem ser derivados).
As colunas GROUP BY da visualização incluem todas as colunas GROUP BY da consulta original.
Se várias visualizações se qualificarem, o otimizador escolhe aquela com o menor número de colunas GROUP BY.

Funções de agregação suportadas para roteamento inteligente: SUM, COUNT, MIN, MAX.

As funções AVG e RB_BUILD_CARDINALITY_AGG não suportam roteamento inteligente.

Considerações sobre TTL

Os dados subjacentes de uma visualização materializada compartilham o mesmo tempo de vida (TTL) da tabela base. Não defina um TTL separado na visualização materializada, pois valores inconsistentes causam inconsistência de dados entre a visualização e a tabela base.

Quando o TTL da tabela base expira e os dados são recuperados, uma consulta na tabela base e uma consulta na visualização podem retornar resultados diferentes. Por exemplo:

Consulta na tabela base após a expiração do TTL:

postgres=> SELECT day, hour, avg(amount) AS amount_avg FROM base_sales GROUP BY day, hour;
    day    | hour | amount_avg
-----------+------+------------
  20210101 |   12 |     4
  20210101 |   13 |     6

Consulta na visualização — os dados recuperados já haviam sido materializados e permanecem na visualização:

postgres=> SELECT * FROM mv_sales;
    day    | hour | amount_avg
-----------+------+------------
  20210101 |   12 |     3       -- stale; reflects pre-reclamation state
  20210101 |   13 |     6

Para evitar essa inconsistência, use uma das seguintes abordagens:

Não defina um TTL na tabela base.
Caso um TTL seja necessário, inclua um campo baseado em tempo na chave GROUP BY da visualização. Ao consultar, evite intervalos de tempo próximos ao limite de expiração do TTL.
Utilize uma tabela particionada sem TTL. Recupere dados antigos excluindo partições filhas.

Limitações

Restrições de criação

As visualizações materializadas devem ser criadas na mesma transação da tabela base. A criação assíncrona não é suportada.
Uma visualização materializada pode referenciar apenas uma única tabela. Expressões de tabela comuns (CTEs), JOINs de múltiplas tabelas, subconsultas e cláusulas WHERE, ORDER BY, LIMIT e HAVING não são suportadas.
A chave GROUP BY e os valores agregados não suportam expressões. Por exemplo, SUM(CASE WHEN cond THEN a ELSE b END), SUM(col1 + col2) e GROUP BY date_trunc('hour', ts) não são suportados.
Limite máximo de 10 visualizações materializadas por tabela base. O consumo de recursos cresce proporcionalmente ao número de visualizações.
Para tabelas particionadas: a chave GROUP BY deve incluir a coluna da chave de partição; a visualização deve ser criada na tabela pai.
Para tabelas particionadas: ATTACH PARTITION não é suportado para adicionar partições; use CREATE TABLE PARTITION OF em seu lugar.

Restrições operacionais

Operações DELETE e UPDATE em uma tabela base não são suportadas. Defina a propriedade appendonly na tabela base. Tentar executar DELETE ou UPDATE retorna o erro Table XXX is append-only.
Ao gravar na tabela base em tempo real usando Flink, defina mutateType como InsertOrIgnore.
A operação DROP COLUMN em uma tabela base que possui uma visualização materializada não é suportada.
Não defina manualmente um TTL em uma visualização materializada. A visualização herda o TTL da tabela base. Definir um TTL diferente causa inconsistência de dados.

Melhores práticas

Alinhe a chave GROUP BY com a chave de distribuição. Configure a chave GROUP BY da visualização materializada para corresponder à chave de distribuição da tabela base. Isso melhora tanto a taxa de compressão de dados quanto o desempenho da consulta.
Posicione as colunas de filtro primeiro na chave GROUP BY. Coloque as colunas frequentemente usadas em filtros WHERE no início da chave GROUP BY. Isso segue o princípio de correspondência mais à esquerda da chave de clustering e permite uma poda mais eficiente.