Transaction apply speed on the standby

On Thu, Jan 26, 2017 at 8:34 AM, Rakesh Kumar <rakeshkumar464@outlook.com>
wrote:

Ver 9.6.1

In a streaming replication can it be assumed that if both primary and
standby are of the same hardware, then the rate at which transactions are
applied on the standby will be same as that on primary. Or standbys are
always slower than primary in applying transactions because of the way
replication works.

It could go either way. The standby only has to apply the changes, not
compute them, so if the primary does something like:

UPDATE foobar set col1 = (<slow select query returning one row and one
column>) where col2=? ;

then the standby will replay it much faster than the primary needed to
execute it.

On the other hand, replay is done single-threaded. If the primary has a
lot of active concurrent connections, replaying them serially could be much
slower than it took to produce them in the first place. This might be true
of both CPU and of IO. If your IO is a big RAID system, the primary could
keep multiple spindles active simultaneously by having multiple connections
waiting on different pieces of data independently, while replay will wait
on them serially. There is currently not a prefetch mechanism for replay.

Cheers,

Jeff

On Fri, Jan 27, 2017 at 3:34 AM, Rakesh Kumar <rakeshkumar464@outlook.com>
wrote:

Ver 9.6.1

In a streaming replication can it be assumed that if both primary and
standby are of the same hardware, then the rate at which transactions are
applied on the standby will be same as that on primary. Or standbys are
always slower than primary in applying transactions because of the way
replication works.

The reason why I am asking is that I am trying to find what are the
circumstances when there can be a large gap between primary and standby in
async replication mode.

As already said, the replication sync between master and standby depends on
lot of factors CPUs, Disks, Network bandwidth etc.

If it is the application demand that data has to be in complete sync on
Standby at all times, then, the best way to go is with synchronous
replication. In this mode PostgreSQL ensures all the changes on master are
replicated to standby and master waits until changes are committed on
standby. This is even more better in 9.6 with "remote_apply" to
synchronous_commit parameter, which ensures data from master is actually
replayed at synchronous standby. However, this type of replication mode can
be a risk when there is a failure (Example : Network break down) and
standby is not connected to master and during this time transactions on
master wait until standby responds back.

Regards,

Venkata B N
Database Consultant