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?

Discussion (0)