Hi,
I have a cluster of PostgreSQL 9.2.21, where there is one master and one slave with streaming replication.
I have few points and questions about the replication, and was hopping you guys could share your opinions, suggestions and experiences.
Before I start; Yes! I know... PG 9.2? Really? Well... we're working on a migration project.. We're considering either EnterpriseDB or RDS (we're already in EC2 instances in AWS).
My current architecture is: master (AZ1) --> read slave (AZ2) --> hot-standby (AZ2) - They are all using streaming replication.
All read-only queries are sent to the read slave. Sometimes the replication lag between the master and the slaves reaches up to 10 minutes. I understand that the lag is expected in any replication scenario, and below you can find some suggestions that I think would help to minimize the lag time.
- Having the read slave in the same AZ as its master - for better network throughput;
- Having the latest PostgreSQL version to get its best performance
- For the replication, we use Streaming Replication. A native PostgreSQL solution that was first introduced in PostgreSQL 9.0 version.
- So, that means that we are using its very very early version. Many improvements have been introduced since 9.x which we’re not taking advantage of.
- Having the latest Operational System behind PostgreSQL to get its best IO performance
- We’re still on Ubuntu 16.04.2 for both Master and Slaves. Again, a lot of performance improvements were introduced in the new Ubuntu version 20.x, which we’re not taking advantage of.
- Consider changing the read slave to be synchronous and not asynchronous
- Consider having multiple slaves and not just one big instance
- Consider spreading the load between the master and the slaves with a Pooling software (PGPOOL)
- Currently this is done at the application level (PHP)
- The master should also do read-only queries. Why not?
Do you agree?
Do you have any other suggestions?
Is there anything I could do now to minimize the replication lag, or since we're working on a migration there is no point wasting our time?
---
Regards,
Lucas
This message is encrypted. Both the Public Key and the GPG encrypted message are included in this email so that you can verify its origin.