0

I have a large Postgres database with many tables, some with tens of millions of rows. Several working processes update the database concurrently. For faster searches, relevant data is compiled into a materialized view.

There are possibly multiple parallel processes writing to the database, then refreshing the materialized view afterwards. However, as the "REFRESH MATERIALIZED VIEW" query takes at least several minutes, quite often such queries pile up in a queue, and they all execute one after the other.

Unfortunately in such cases, only the latest query is of any relevance; all the previous queries consume processing time in vain to refresh stale data. Is there a way for stopping already running calls to "REFRESH MATERIALIZED VIEW" when a new call is issued?

Please note that "REFRESH MATERIALIZED VIEW CONCURRENTLY" has the same behaviour, but considerably slows the refresh (from a few minutes up to an hour), therefore aggravating the performance problem.

Of course it's possible to test for an existing lock on the view for each new query, so it's easy to cancel new queries; the problem is that I'd rather cancel the old queries and keep only the latest one...

  • Have a separate process to run REFRESH? – mustaccio Dec 18 '19 at 14:53
  • @mustaccio and how would I do that? I want the view updated as much as necessary, but not more. – wazoox Dec 18 '19 at 15:14
  • 2
    How do you prevent a never ending stream of cancellations, where a new refresh attempt cancels the old one, but then immediately becomes the old one and gets cancelled, leading to never having a refreshed view finish? – jjanes Dec 18 '19 at 15:18
1

It looks like you want to refresh the materialized views whenever the data in the tables change. If you do that with materialized views, it will take a long time, and updates will block each other and queries.

Maybe you can build your own “on commit refresh” materialized views as tables.

A simple example:

Instead of

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

you could do this:

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;
|improve this answer|||||
  • That looks like a good idea. I'll have to benchmark this compared to a materialized view. But why set the trigger in a transaction? – wazoox Dec 18 '19 at 17:59
  • So that concurrent access to the table is blocked and the data are consistent, even if there is concurrent DML activity. – Laurenz Albe Dec 19 '19 at 6:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.