title: refresh only part of a materialized view?
body: <p>My core problem is that I have a jsonb database column where the data is shaped like this:</p>
  
  <pre><code>{"ops": [
    {"insert": "yaaaah "},
    {"insert": {"atmention": {"id": "183"}}}, 
    {"insert": "  "},
    {"insert": {"hashtag": "potato"}},
    {"insert": " \n"}
  ]}
  </code></pre>
  
  <p>(it is the output of a <a href="https://quilljs.com" rel="nofollow noreferrer">quill</a> field on the front end and I'd rather not change that structure). Currently, my API notices that the field has a <code>hashtag</code> entry and manually refreshes the tags column in the database by extracting the hashtag entries ('potato' in this case) and updating a text[] column where the tags are stored. I can then query matches by doing <code>@&gt; tags</code>, or get a list of all tags by <code>select distinct unnest(tags) from documents</code>. </p>
  
  <p>This is functional, but somewhat unsatisfying as it denormalizes the tag data - the rich_text column is the authoritative value, but the tags column needs to be computed and updated for queries.</p>
  
  <p>What I was thinking I'd like to do is make a materialized view of document - tag pairs, like:</p>
  
  <pre><code>create materialized view hashtags
  with taglist as (
    select documents.id, 
    jsonb_array_elements(rich_text-&gt;'ops') as ops from documents
  ) 
  select 
    taglist.id,
    ops-&gt;'insert'-&gt;'hashtag'
  from taglist 
  where ops-&gt;'insert'-&gt;'hashtag' is not null;
  </code></pre>
  
  <p>This works, but I now have to refresh the materialized view every time a document is updated, deleted, or inserted, and I don't think this will scale very well - since it needs to basically seq scan every document.</p>
  
  <p>what I was wondering is if there's some way to say, "hey, refresh the materialized view by deleting elements where id = 1, and then re-running the query where documents.id = 1". I'll know which entries in the view can change, and only want to change them.</p>
  
  <p>Other suggestions, like ways to efficiently index this column (which is a jsonb element with a single top-level key, "ops", whose value is always an array of objects with "insert" as a top level key, which may have a text or further object value, and may also have an "attributes" top level key sibling to "insert"), are welcome.</p>
  
  <p>My goal is to move the computation of "What hashtags are in this document" and "What hashtags are there in general" and "What documents are tagged with potato" queries into the SQL, instead of using the denormalized technique I currently use (which again: functions, but is somewhat unsatisfying as it means I have to maintain this extra column, and it is conceivable that the data gets into undefined states, as if the tag column is edited directly somehow, or the rich_text field is updated without recomputing the tag column).</p>