Working with Materialized View

Question

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?

Answer 1

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.