title: Postgres Hot Standby and Long Running Queries On Slave
body: <p><strong>Question</strong>: Can you have long running queries (30s+) running while having WAL updates applied on the slave (the slave role is as a Reporting DB Server) in a Hot Standby mode? The way it's working now is, either you set the params below to kill long running queries so WAL updates can be applied, or delay the WAL updates indefinitely until no queries are running to apply them. Can we have both? Long running queries and WAL updates being applied at the same time?</p>
  <p><strong>Case Implementation</strong>: We are currently using Hot Standby mode to sync any changes from one master to one slave. The slave role is as a reporting db server with queries constantly and concurrently running (some in ms, some in seconds,
  some in minutes.) It would be very rare to have a gap of no active queries running on the slave.</p>
  <p>We have adjusted these two params to allow long queries on the hot standby:</p>
  <pre><code>max_standby_archive_delay = -1  # max delay before canceling queries
  max_standby_streaming_delay = -1  # max delay before canceling queries
  </code></pre>
  <p>And looking at an archived mail question similar to ours in a postgres mailing list:</p>
  <p><a href="http://www.postgresql.org/message-id/AANLkTinLg+bpzcjzdndsnGGNFC=D1OsVh+hKb85A-s=n@mail.gmail.com" rel="nofollow noreferrer">http://www.postgresql.org/message-id/AANLkTinLg+bpzcjzdndsnGGNFC=D1OsVh+hKb85A-s=n@mail.gmail.com</a></p>
  <p>I understand the concept of preventing WAL updates being applied to
  slaves while queries are running. However, I thought with the use of MVCC,
  an active query on the slave (long running, 30 seconds+) can run reading
  from one version/snapshot, while the WAL update is being applied, so
  subsequent queries will get the WAL updates when that WAL transaction is
  committed. I haven't fully digested the MVCC model used in PostgreSQL yet
  <a href="https://devcenter.heroku.com/articles/postgresql-concurrency" rel="nofollow noreferrer">https://devcenter.heroku.com/articles/postgresql-concurrency</a>, so this is
  just my assumption -- that even if a table is dropped / truncated during a
  WAL update, the current running query should still work as it's using a
  version/snapshot of the table(s) it's querying?</p>
  <p><strong>Summary</strong>: Is there anyway (even with a third party extension) we can sync slaves
  from a master and have those updates from the master be applied to the
  slave right away while letting queries of any execution time continue to
  run till they complete on the standby/slave? If Hot Standby can't do that,
  what would you recommend for this situation? Our scenario is that we are
  hitting postgres with queries constantly and concurrently running (some in
  ms, some in seconds, some in minutes,) leaving almost no time for a WAL
  update to be applied. We have used Bucardo, but that wouldn't be a good
  choice in this scenario, as we over 200+ tables that would need to be
  synced, including views as well 40+ other databases aside from our main
  database.</p>
  <p>Any help would be greatly appreciated.</p>
  <p>Thank you!</p>