Hologres:Comprehensive guide to materialized views

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;

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');

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

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');

Vérifier la taille de stockage

Vérifiez la taille de stockage d'une seule vue matérialisée :

SELECT pg_relation_size('mv_sales');

Listez toutes les vues matérialisées triées par taille de stockage :

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;

Supprimer une vue matérialisée

DROP MATERIALIZED VIEW mv_sales;

-- 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;

Pourquoi AVG standard échoue entre les dimensions

Une vue matérialisée stocke des résultats préagrégés. Si vous définissez mv_sales avec avg(amount) groupé par (day, hour), l'interrogation portant uniquement sur la dimension day produit des résultats incorrects : la moyenne des moyennes n'est pas égale à la moyenne totale.

Compte tenu de ces données de base :

Une requête directe sur la vue renvoie :

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

La réagrégation par day donne un résultat incorrect :

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

Utiliser l'état d'agrégation intermédiaire

Au lieu de stocker la moyenne finale, stockez l'état d'agrégation intermédiaire à l'aide de avg_partial. Lors de l'interrogation selon une dimension différente, finalisez le résultat avec 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;

Interrogez selon une autre dimension d'agrégation à l'aide de 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

Référence des fonctions partielles et finales

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

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

Contraintes de création

• Les vues matérialisées doivent être créées dans la même transaction que la table de base. La création asynchrone n'est pas prise en charge.

• Une vue matérialisée ne peut faire référence qu'à une seule table. Les expressions de table communes (CTE), les jointures multitable, les sous-requêtes ainsi que les clauses WHERE, ORDER BY, LIMIT et HAVING ne sont pas prises en charge.

• La clé GROUP BY et les valeurs d'agrégat ne prennent pas en charge les expressions. Par exemple, SUM(CASE WHEN cond THEN a ELSE b END), SUM(col1 + col2) et GROUP BY date_trunc('hour', ts) ne sont pas pris en charge.

• Maximum de 10 vues matérialisées par table de base. La consommation de ressources augmente proportionnellement au nombre de vues.

• Pour les tables partitionnées : la clé GROUP BY doit inclure la colonne de clé de partition ; la vue doit être créée sur la table parente.

• Pour les tables partitionnées : ATTACH PARTITION n'est pas pris en charge pour l'ajout de partitions ; utilisez plutôt CREATE TABLE PARTITION OF.

Contraintes d'exploitation

• Les opérations DELETE et UPDATE sur une table de base ne sont pas prises en charge. Définissez la propriété appendonly sur la table de base. Toute tentative de DELETE ou UPDATE renvoie l'erreur Table XXX is append-only.

• Lors de l'écriture en temps réel dans la table de base à l'aide de Flink, définissez mutateType sur InsertOrIgnore.

• DROP COLUMN sur une table de base possédant une vue matérialisée n'est pas pris en charge.

• Ne définissez pas manuellement de TTL sur une vue matérialisée. La vue hérite du TTL de la table de base. La définition d'un TTL différent entraîne une incohérence des données.