6

I'm using PostgreSQL 9.6.

I have a materialized view to support full-text search across two tables, which I'll call posts and tags. The tables are infrequently updated and frequently searched.

I'm trying to find a good strategy to run REFRESH MATERIALIZED VIEW post_search.

The ideal solution would be a trigger like this: at the end of (or after) any transaction that modifies posts, tags and/or posts_tags (join table), refresh the materialized view exactly once.

How could I accomplish this?

2 Answers 2

12

An OK solution using triggers

This is what I'm doing now. It's not exactly what I wanted because the trigger fires once per statement instead of once per transaction. But it works for now.

(In the future, we're thinking of using an actual table for search instead of a materialized view, and updating individual relevant rows via triggers instead of refreshing an entire matview.)

Create a function to refresh the materialized view concurrently:

  CREATE OR REPLACE FUNCTION refresh_post_search()
  RETURNS TRIGGER LANGUAGE plpgsql
  AS $$
  BEGIN
  REFRESH MATERIALIZED VIEW CONCURRENTLY post_search;
  RETURN NULL;
  END $$;

And create a trigger for each of the underlying tables:

  CREATE TRIGGER refresh_post_search
  AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE
  ON posts
  FOR EACH STATEMENT
  EXECUTE PROCEDURE refresh_post_search();

  CREATE TRIGGER refresh_post_search
  AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE
  ON posts_tags
  FOR EACH STATEMENT
  EXECUTE PROCEDURE refresh_post_search();

  CREATE TRIGGER refresh_post_search
  AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE
  ON tags
  FOR EACH STATEMENT
  EXECUTE PROCEDURE refresh_post_search();
5
  • I'm considering a similar approach, I'm just curious how this worked out, or if you had any comments I should consider. Apr 10, 2018 at 18:08
  • 2
    @CodingGorilla I wrote up the details at nathanmlong.com/2018/01/… Apr 10, 2018 at 19:34
  • we're thinking of using an actual table for search instead of a materialized view This still would not solve your nitpick of it firing by once per statement, not per transaction, would it? In other words, what do you think is wrong with the materialized view approach?
    – phil294
    May 24, 2021 at 17:41
  • Nevermind, I found the answer to my own question in your blogpost: The remaining downside is that recalculating the entire materialized view when only one row needs to change is a bit wasteful.
    – phil294
    May 24, 2021 at 17:44
  • what is the difference between running for each row and for each statement for insert and update and delete queries? i know that truncate only works with for each statement
    – PirateApp
    Nov 18, 2022 at 7:07
4

Well, likely you're doing it wrong. While the verb REFRESH may connote otherwise, you're actually rewriting the table when you run it. That kind of workload is heavy to run in a trigger (though you could).

That doesn't make much sense. You either

  1. Don't want a MATERIALIZED VIEW;
  2. Want a strategy to REFRESH during down time, or off time, or periodically.

The second one is usually done with

  1. a simple cron, pg_cron or the like
  2. at the end of import

Normally, you either need

  • Always-right: use a plain view.
  • Periodically right: use a materialized view.
  • Fluid and right: you may check into NOTIFY/EVENT or time-series databases

It's quite possible that a MATERIALIZED VIEW simply isn't what you want.

8
  • I'm fairly sure that I do want a materialized view. I want to run full-text queries against the combined, weighted ts_vectors of posts.title and string_agg(tags.title). Using a materialized view instead of a subquery sped this up 10x. Not using one means building the same vectors on every query. Nov 9, 2017 at 18:58
  • I'm not convinced anyway, can I see your query \d on all tables with a tsvector? Nov 9, 2017 at 19:02
  • As for running REFRESH on a cron job, that could work. But it seems a bit silly to be checking (eg) every hour for changes when I'm in charge of the system that produces them, doesn't it? Nov 9, 2017 at 19:02
  • Sure, again, it's somewhat likely that a trigger would do exactly what you want if you need event driven-recalculation of a table -- that goes back to a materialized view not being what you want. But, I'm not convinced you need a view either. If the query is slow, show it and let's take a look at it. I need to see more information. Paste the query the MATERIALIZED VIEW runs, an explain analyze plan, and the schema for the tables. Nov 9, 2017 at 19:05
  • 1
    The actual data is recipes rather than posts. The materialized view is ` CREATE MATERIALIZED VIEW recipe_search AS SELECT recipes.id AS id, recipes.title AS title, ( setweight(to_tsvector(recipes.title), 'A') || setweight(to_tsvector(coalesce(string_agg(tags.value_slug, ' '), ' ')), 'B') ) AS document FROM recipes LEFT JOIN recipes_tags ON recipes_tags.recipe_id = recipes.id LEFT JOIN tags ON tags.id = recipes_tags.tag_id GROUP BY recipes.id ` Nov 9, 2017 at 19:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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