0

I have a materialized view which takes around 57 second to be created and I'm using PostgreSQL 9.4.

When I do an insert into a table, a trigger will call a trigger function which will do a REFRESH MATERIALIZED VIEW CONCURRENTLY view the trigger fires after each statement (insert,update,delete)

What happens is, to do a insert it will take around 57 seconds.

How can I fix this and still use a materialized view?

I had this implemented for a long time, and never had this problem, recently I've added pgbouncer, can it be the cause of this?

  • 1
    A side question, why do you use materialized views if you refresh it on every insert? Isn't a separate table that you manually update with your triggers enough? You will need update and delete triggers, but if you've already gone down the trigger way.. – Trygve Laugstøl Mar 13 '15 at 21:12
5

Generally, refreshing the MV immediately only seems reasonable if write access to underlying tables is a rare event. A statement-level trigger is better than a row-level trigger, but may still prove too much for big tables. I would consider a solution that polls the database every n minutes checking for updates. You could have a trigger write to a table with a single row.
Or you could use LISTEN / NOTIFY.

As long as you want to propagate every change (almost) immediately, you may be better off with row-level triggers on insert / update / delete writing to a hand-knit "materialized view" (a table), like @trygvis suggested.

Since pgbouncer keeps sessions open, there may be locking issues with uncommitted transactions slowing the process down, but I am merely speculating here. You could run tests in a copy of your database without pgbouncer. Or check for zombi transactions by looking up pg_stat_activity.

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.