Postgres Hot Standby and Long Running Queries On Slave

Question

Question: 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?

Case Implementation: 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.

We have adjusted these two params to allow long queries on the hot standby:

max_standby_archive_delay = -1  # max delay before canceling queries
max_standby_streaming_delay = -1  # max delay before canceling queries

And looking at an archived mail question similar to ours in a postgres mailing list:

http://www.postgresql.org/message-id/[email protected]

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 https://devcenter.heroku.com/articles/postgresql-concurrency, 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?

Summary: 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.

Any help would be greatly appreciated.

Thank you!

Solution

Thanks Guillaume for your answer, but luckily, starting in PostgreSQL 9.1, PostgreSQL has the hot_standby_feedback option (you set this on the standby server in postgresql.conf) which wont kill long running queries and will allow for WAL updates to the standby servers. Credit for this answer goes to three people on the PostgreSQL mail list (Raja/Albe/Scott) who helped me on this in that mailing thread. Hopefully this could be helpful to someone searching for this answer on stackoverflow. The email thread can be found here:

http://www.postgresql.org/message-id/D274E3C1.1113E8%[email protected]

http://www.postgresql.org/docs/9.1/static/hot-standby.html

Excerpt:

Remedial possibilities exist if the number of standby-query cancellations is found to be unacceptable. The first option is to set the parameter hot_standby_feedback, which prevents VACUUM from removing recently-dead rows and so cleanup conflicts do not occur. If you do this, you should note that this will delay cleanup of dead rows on the primary, which may result in undesirable table bloat. However, the cleanup situation will be no worse than if the standby queries were running directly on the primary server, and you are still getting the benefit of off-loading execution onto the standby. max_standby_archive_delay must be kept large in this case, because delayed WAL files might already contain entries that conflict with the desired standby queries.

Solution Implementation:

Here's what your postgresql.conf should be configured to on the standby server:

max_standby_archive_delay = -1
max_standby_streaming_delay = -1
hot_standby_feedback = on

Comments

[Volte]

Spot on! Hopefully this helps others; don't forget to restart your database/server! (For example if you're running RDS)

[Kivan]

hot_standby_feedback is dangerous, since it forces autovacuum to wait on master and you can easily start to loose space fast on some workloads. Use it with caution.

[Khushbu]

We have a similar use-case, streaming replication between a Master and a Replica. With Replica being used heavily for read queries. We tried the mentioned three properties and are now not seeing the conflict errors. However, we are seeing lag go upto as high as 2000 seconds and on an avg around 500 secs. Our use-case cannot tolerate such high lag. What else can we do to bring the lag down AND not cause the conflict errors. Thanks!