Re: Slow queries in hot standby

The main side affect is what those settings you mentioned control. If the query you're running would cause a conflict with replaying replication from the master, replication will be put on hold until the query completes or the time given for the delay settings is reached. Once that time is reached, the query will be forcefully cancelled and replication will continue. If replication is held up, that also means all other queries on your slave will not be seeing any new data either, so you could cause consistency issues depending if your app needs current data on the slave.

How much of an affect on your slave this will have also depends how much write traffic you're getting. If it's a very write heavy cluster, you could run into the slave taking a long time to catch up again. Also, this causes WAL files to be held up on the master as well since they have to be kept around for the slave to replay them. So disk usage on your master will go up while the query is running. Also if you have hot_standby_feedback turned on, you can cause excessive bloat on the master as well (see http://www.postgresql.org/docs/9.4/static/runtime-config-replication.html#GUC-HOT-STANDBY-FEEDBACK).

The other thing to consider when you use the delay settings on a slave is that you should not consider that slave as a legitimate failover target. If your master suddenly goes away and replication is held up, your slave just lost all that data that hadn't been replicated yet. You can mitigate this by also using the archive_command on the master or pg_receivexlogs to keep an additional backup of your WAL files somewhere else. But you still potentially lose that last WAL file's worth of data that hadn't completed yet (I believe 9.5 adds support for partial WAL files for pg_recievexlogs, but that's not released yet). So if you have a slave being used for read-only queries and you need the delay, it's recommended to have a separate slave for failover without the delay (if you need failover that is).

So, several things to consider when you run a slave for read-only queries.

--
Keith Fiske
Database Administrator
OmniTI Computer Consulting, Inc.
http://www.keithf4.com