0

Consider a case where you have a single PostgreSQL database. Then you add a replica (hot standby) and you send all the reads to that replica. This is what does Rails for example.

Does this gives any performance benefit? For my understanding the answer is no.

Let me explain:

  1. when you have only a single database it gets N writes and M reads
  2. when you add the replica, the replica has to perform N writes (replicate them) and M reads... so there isn't any advantage.

Basically adding replicas does not add any benefit for writes (i.e. 2 x number of machines => 2 x number of writes).

The only advantage would be to split the reads: but in that case you need to split the reads between the master and the replica (or between multiple replicas), otherwise it is useless. So the strategy adopted by Rails (i.e. moving all reads to the single replica) seems not effective to me.

Am I missing something? Is the write work performed by a replica less then the work of the master (considering that each update must be applied to both the machines)?

Improve this question
1
  • pgPool can distribute reads between multiple replicas. Which has a clear benefit when you have a workload that does more reads than writes (which is quite common). And a replicat isn't primarily intended for performance reasons but for high-availability
    – user1822
    Feb 6, 2021 at 14:16

2 Answers 2

Reset to default
1

Am I missing something? Is the write work performed by a replica less then the work of the master (considering that each update must be applied to both the machines)?

Probably yes, you are missing something, and write work done on the replica is less than the (total) work of the master. It is rare for every write on a database to be of the form update foo set bar=592 where pk=127. What if it were instead update foo set bar=592 where bar is distinct from 592 and pk in (select foo_id from baz where t ilike '%one fish%' and sin(foo.z)<sqrt(baz.y)/7) and tzr @> :something. How many reads (and CPU time, and memory) would need to happen to figure out which writes end up happening? Replication, however, just needs to replay the end result.

But if you were setting up read replication just for performance, it would be pretty weird to set up just one of them. Unless that one was on your LAN, while the master was someplace with more latency.

Improve this answer
1
  • Usually when you use an ORM like Rails ActiveRecord most updates are actually in the form of update foo set bar=592 where pk=127... So basically your answer confirms that there isn't a significant performance gain when you add a single replica (and yes, if you have many replicas you get better read performance)
    – collimarco
    Feb 9, 2021 at 13:18
0

Using an ORM, The write on the replica are way faster because there is no need for constraint check : primary key, foreign key, etc...

Be aware that there is lag between both server. You must take that in consideration for the design of your application. ie: if you read a value on slave, immediately increase it then write back to the master. One time will be ok, but several iterations of this will fail (it is ugly design).

Before setting up replication be sure your master is a proper server with enough CPU and RAM and its own ssd disks (not a remote drive on a SAN). If you still max it out and have plenty of read work you can think of adding another slave.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.