主页 / dba / 问题 / 255869
Accepted
wazoox
Asked: 2019-12-19 06:44:27 +0800 CST

多个并发“REFRESH MATERIALIZED VIEW”：如何管理？

  • 772

我有一个包含许多表的大型 Postgres 数据库，其中一些具有数千万行。几个工作进程同时更新数据库。为了更快地搜索，相关数据被编译成物化视图。

可能有多个并行进程写入数据库，然后刷新物化视图。但是，由于“REFRESH MATERIALIZED VIEW”查询至少需要几分钟时间，因此此类查询通常会堆积在队列中，并且它们都一个接一个地执行。

不幸的是，在这种情况下，只有最新的查询具有任何相关性；所有先前的查询都徒劳地消耗处理时间来刷新陈旧的数据。有没有办法在发出新呼叫时停止已经在运行的对“REFRESH MATERIALIZED VIEW”的呼叫？

请注意，“REFRESH MATERIALIZED VIEW CONCURRENTLY”具有相同的行为，但会大大减慢刷新速度（从几分钟到一个小时），因此会加剧性能问题。

当然，可以为每个新查询测试视图上的现有锁，因此很容易取消新查询；问题是我宁愿取消旧的查询，只保留最新的……

postgresql postgresql-9.6

1 个回答

  1. Best Answer

    每当表中的数据发生变化时，您似乎都想刷新物化视图。如果你用物化视图来做这件事，会花费很长时间，并且更新会互相阻塞和查询。

    也许您可以将自己的“提交刷新”物化视图构建为表格。

    一个简单的例子：

    代替

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

    你可以这样做：

    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

相关问题