1

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

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();
  • I'm considering a similar approach, I'm just curious how this worked out, or if you had any comments I should consider. – Coding Gorilla Apr 10 '18 at 18:08
  • 1
    @CodingGorilla I wrote up the details at nathanmlong.com/2018/01/… – Nathan Long Apr 10 '18 at 19:34
1

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.

  • 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. – Nathan Long Nov 9 '17 at 18:58
  • I'm not convinced anyway, can I see your query \d on all tables with a tsvector? – Evan Carroll Nov 9 '17 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? – Nathan Long Nov 9 '17 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. – Evan Carroll Nov 9 '17 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 ` – Nathan Long Nov 9 '17 at 19:05

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.