Postgres materialized views with hot standby

Question

We are using Postgres (9.3) Hot Standby to build a read-only copy of a database. We have a UI which reads from a materialized view.

When we try to read from the materialized view in the standby database, the query hangs.

The materialized view takes ~10 seconds to rebuild in the master database. We've waited over 30 minutes for the query in the standby database and it seems to never complete.

Notably, the materialized view does exist in the standby database. We can't refresh it of course (since the DB is read only)

We can't find anything in the documentation which indicates that materialized views can't be used in standby databases, but that appears to be the case.

Has anyone got this to work, and/or what is the recommended work-around?

Works fine for me in 9.5. What does pg_stat_activity.waiting say for your standby query? Where/when/how are you refreshing your view on the primary? Does your query involve other tables? — Nick Barnes
Could be a locking problem. Look in pg_locks if the session is waiting for a lock held by somebody else. — Laurenz Albe
@LaurenzAlbe yes. There's an exclusive lock held by the recovery process. This would seem to be a problem, since that process is always running. Not sure what to do? — Nick Lothian
Here's the locks: postgres 14064 0.0 1.1 290108 48324 ? Ss 05:22 0:03 _ postgres: startup process waiting for 00000001000000060000000E postgres 23242 0.0 0.0 4508 808 ? S 22:02 0:00 | _ sh -c /usr/lib/postgresql/9.3/bin/pg_standby /var/lib/postgresql/master_archive 00000001000000060000000E pg_xlog/RECOVERYXLOG 00000001000000060000000C postgres 23243 0.0 0.0 4372 796 ? S 22:02 0:00 | _ /usr/lib/postgresql/9.3/bin/pg_standby /var/lib/postgresql/master_archive 00000001000000060000000E pg_xlog/RECOVERYXLOG 00000001000000060000000C — Nick Lothian
It is ok for the recovery process to lock the view, but the lock should be released again. Is there a lock on the materialized view on the primary? — Laurenz Albe

Stathis Andronikos Stathis Andronikos · Accepted Answer · 2017-08-11T09:14:57

According to PostgreSQL Documentation - Hot Standby there is a way to handle Query Conflicts by assigning proper values to

max_standby_archive_delay & max_standby_streaming_delay

that define the maximum allowed delay in WAL application. In your case a high value may be preferable.

Postgres materialized views with hot standby

1 Answers