Re: Question on Hot Standby in PostgreSQL

On Mon, Nov 23, 2015 at 3:24 PM, Scott Ribe <scott_ribe(at)elevated-dev(dot)com>
wrote:

> On Nov 20, 2015, at 12:50 PM, Williams, Alex <awilliams(at)teamdrg(dot)com>
> wrote:
> >
> > However, I thought with the use of MVCC,
> > an active query on the slave (long running, 30 seconds+) can run reading
> > from row xid versions/snapshot...
>
> The problem is that the master could well make changes that would remove
> rows in that snapshot because the master does not know anything about the
> rows that need preserving for the replica's query.
>

hot_standby_feedback addresses this problem.

Here is the detail from the PG docs:

"Specifies whether or not a hot standby will send feedback to the primary
or upstream standby about queries currently executing on the standby. This
parameter can be used to eliminate query *cancels caused by cleanup records*,
but *can cause database bloat* on the primary for some workloads. Feedback
messages will not be sent more frequently than once per
wal_receiver_status_interval. The default value is off. This parameter can
only be set in the postgresql.conf file or on the server command line."

http://www.postgresql.org/docs/9.3/static/runtime-config-replication.html

Note that this will only avoid the conflicts caused by cleanup records
(which normally is the majority of conflicts).

>
> That said, IIRC there may be something in 9.4 or 9.5 which provides for
> the replica to send that info to the server in order to coordinate things,
> but I haven't used that and so cannot offer more.
>
> --
> Scott Ribe
> scott_ribe(at)elevated-dev(dot)com
> http://www.elevated-dev.com/
> https://www.linkedin.com/in/scottribe/
> (303) 722-0567 voice
>
>
>
>
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>