Database Reference
In-Depth Information
How to do it...
Queries that run on the Standby node see a version of the database that is slightly behind the
primary node. We describe this as eventually consistent. How long is "eventually"? That time
is exactly the replication delay plus the apply delay, as discussed in the recipe on replication
concepts. You can set an upper limit on the acceptable apply delay by controlling two similar
parameters:
max_Standby_streaming_delay
and
max_Standby_archive_delay
.
To understand how to set these parameters, we must understand the forces that act to
increase the apply delay: query conflicts. There are four main types of conflicts that can occur
between the Master and queries on the Standby, which are as follows:
F
Resources—CPU, I/O, and so on
F
Locks—AccessExclusiveLocks
F
Cleanup records
F
Other special cases
Resource conflicts are the easiest to understand: if the server is busy applying changes from
the Master, then you will have fewer resources to use for queries. That means if there are no
changes arriving, then you'll get more query throughput. If there are predictable changes in
the write workload on the Master, then you may need to throttle back your query workload on
the Standby when that occurs.
Resource conflicts can slow down queries on the Standby, and can be thought of as soft
conflicts. Other forms of conflict are hard conflicts, causing queries on the Standby to be
canceled or disconnected.
Lock conflicts are also easy to understand: if you wish to run a command on the Master, such
as
ALTER
TABLE
…
DROP
COLUMN
, then you must first lock the table to prevent all access.
The lock request is sent through to the Standby server as well, which will then cancel Standby
queries that are currently accessing that table after a configurable delay.
On high-availability systems, making DDL changes to tables that cause long periods of locking
on the Master can be difficult. You may want the tables on the Standby to stay available
for read during the period while the changes on the Master are being made. To do that,
temporarily set
max_Standby_streaming
delay
=
-1
and
max_Standby_archive_
delay
=
-1
, and then reload the server. As soon as the first lock record is seen on the
Standby, all further changes will be held. Once the locks are released on the Master, you can
then reset the original parameter values on the Standby, which then will allow the changes to
be made there.
