DEV Community

Pacharapol Withayasakpunt
Pacharapol Withayasakpunt

Posted on

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]
}
Enter fullscreen mode Exit fullscreen mode

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

GitHub logo zhquiz / zhquiz

Quiz for Chinese Hanzi, Vocab and Sentence daily!

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

Latest comments (2)

Collapse
 
rish_0_0 profile image
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.

Collapse
 
patarapolw profile image
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.