PostgreSQL MATERIALIZED VIEW non-blocking, yet updated frequently

• It needs to be REFRESH'd after update.
• REFRESHing locks VIEW's read, and may subsequently make server and UI unresponsive.
• REFRESH MATERIALIZED VIEW CONCURRENTLY makes it non-read blocking; however
  • Sending multiple repeated REFRESH's still blocks reading
  • MUST be fixed with throttling
  • Also, CONCURRENTLY needs UNIQUE INDEX on MATERIALIZED VIEW

The solution??? - throttling

import sql from '@databases/sql'

const isPending: Record<string, Promise<any[]> | undefined> = {}

export async function refresh(view: string) {
  if (!isPending[view]) {
    isPending[view] = new Promise((resolve, reject) => {
      db.query(
        sql`REFRESH MATERIALIZED VIEW CONCURRENTLY ${sql.__dangerous__rawValue(
          view
        )}`
      )
        .then(resolve)
        .catch(reject)
    })
  }
  await isPending[view]
}

I suspect that my answer is still wrong. I should debounce, rather than throttle; to ensure eventual update.

My materialized views can take around 30 seconds to refresh; because of so many entries, with PL/pgSQL functions and indexing.

So, I am new to PostgreSQL

But, I made quite a full project, with plugins, PL/pgSQL and stuff, as seen in this project - https://github.com/zhquiz/zhquiz/tree/master/packages/www/db/initdb.d

zhquiz / zhquiz

Quiz for Chinese Hanzi, Vocab and Sentence daily!

Please advise me anything you can help, including where to host?

Comments

[Rishabh Anand]

Why not create a REST API with a rate limit of say: 1 request every 10 minutes, and make something call the REST API periodically.

[Pacharapol Withayasakpunt]

I still don't know the answer, other than that PostgreSQL driver also support listening for changes.

About set interval, it is not really real time, and it can be quite heavy if the traffic is low; and too light, if the traffic is high.