Início / dba / Perguntas / 255869
Accepted
wazoox
Asked: 2019-12-19 06:44:27 +0800 CST

Múltiplas "REFRESH MATERIALIZED VIEW" simultâneas: como gerenciar?

  • 772

Eu tenho um grande banco de dados Postgres com muitas tabelas, algumas com dezenas de milhões de linhas. Vários processos de trabalho atualizam o banco de dados simultaneamente. Para pesquisas mais rápidas, os dados relevantes são compilados em uma visualização materializada.

Existem possivelmente vários processos paralelos gravando no banco de dados e, em seguida, atualizando a visualização materializada posteriormente. No entanto, como a consulta "REFRESH MATERIALIZED VIEW" leva pelo menos vários minutos, muitas vezes essas consultas se acumulam em uma fila e todas são executadas uma após a outra.

Infelizmente, nesses casos, apenas a consulta mais recente é relevante; todas as consultas anteriores consomem tempo de processamento em vão para atualizar dados obsoletos. Existe uma maneira de interromper as chamadas já em execução para "REFRESH MATERIALIZED VIEW" quando uma nova chamada é emitida?

Observe que "REFRESH MATERIALIZED VIEW CONCURRENTLY" tem o mesmo comportamento, mas retarda consideravelmente a atualização (de alguns minutos até uma hora), agravando assim o problema de desempenho.

É claro que é possível testar um bloqueio existente na visualização para cada nova consulta, por isso é fácil cancelar novas consultas; o problema é que prefiro cancelar as consultas antigas e manter apenas a mais recente...

postgresql postgresql-9.6

1 respostas

  1. Best Answer

    Parece que você deseja atualizar as visualizações materializadas sempre que os dados nas tabelas forem alterados. Se você fizer isso com visualizações materializadas, levará muito tempo e as atualizações bloquearão umas às outras e às consultas.

    Talvez você possa construir suas próprias visualizações materializadas “on commit refresh” como tabelas.

    Um exemplo simples:

    Ao invés de

    CREATE MATERIALIZED VIEW sum_eumel AS
SELECT eumel_category,
       sum(eumel_data) AS eumel_sum
FROM eumel
GROUP BY eumel_category;

    você poderia fazer isso:

    BEGIN;

CREATE TABLE sum_eumel (
   eumel_category text NOT NULL PRIMARY KEY,
   eumel_sum bigint NOT NULL DEFAULT 0
);

CREATE FUNCTION eumel_trigger() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   IF TG_OP IN ('UPDATE', 'DELETE') THEN
      /*
       * Will leave rows with value 0 after the last
       * row for a category has been deleted.
       */
      UPDATE sum_eumel
      SET eumel_sum = eumel_sum - OLD.eumel_data
      WHERE eumel_category = OLD.eumel_category;
   END IF;

   IF TG_OP IN ('INSERT', 'UPDATE') THEN
      INSERT INTO sum_eumel (eumel_category, eumel_sum)
      VALUES (NEW.eumel_category, NEW.eumel_data)
      ON CONFLICT TO UPDATE
      SET eumel_sum = sum_eumel.eumel_sum + EXCLUDED.eumel_data
   END IF;

   IF TG_OP = 'TRUNCATE' THEN
      TRUNCATE sum_eumel;
      RETURN NULL;
   END IF;

   IF TG_OP = 'DELETE' THEN
      RETURN OLD;
   ELSE
      RETURN NEW;
   END IF;
END;$$;

CREATE TRIGGER eumel_dml_trig
   AFTER INSERT OR UPDATE OR DELETE ON eumel
   FOR EACH ROW EXECUTE PROCEDURE eumel_trigger();

CREATE TRIGGER eumel_truncate_trig
   AFTER TRUNCATE ON eumel
   FOR EACH STATEMENT EXECUTE PROCEDURE eumel_trigger();

INSERT INTO sum_eumel
SELECT eumel_category,
       sum(eumel_data) AS eumel_sum
FROM eumel
GROUP BY eumel_category;

COMMIT;
    • 2

relate perguntas