多并发“刷新物化视图”：如何管理？

Question

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

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

不幸的是，在这种情况下，只有最新的查询是相关的；所有以前的查询都会浪费处理时间来刷新陈旧的数据。是否有一种方法可以在发出新调用时停止已在运行的呼叫以“刷新物化视图”？

请注意，“同时刷新物化视图”具有相同的行为，但会大大减慢刷新速度(从几分钟到一小时)，因此加重了性能问题。

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

Answer 1

看起来，每当表中的数据发生变化时，都需要刷新物化视图。如果您使用物化视图执行此操作，则需要很长时间，并且更新将相互阻塞和查询。

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

一个简单的例子：

而不是

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;