Re: PostgreSQL replication lag - Suggestions and questions

> My current architecture is: master (AZ1) --> read slave (AZ2) -->
hot-standby (AZ2) - They are all using streaming replication.

I am not sure of the difference between read replica and a hot standby. At
least with later versions hot standby allows read queries.
I mean unless you do not want queries to go to AZ2 node, both of them
should be able to server the queries.
with libpq 10 onwards, you can provide multiple nodes in the connection
string, which would "automatically" figure out next available node in to
query if one is not responding.
See
PostgreSQL: Documentation: 13: 33.1. Database Connection Control Functions
<https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-MULTIPLE-HOSTS>

> 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.

lags can be a result of multiple reasons like,
Intermittent connection issues,
network unable to keep up with WAL changes. A typical example would be any
action that performs a table rewrite of a huge table.
this can result in query getting cancelled on the replica and it may think
you might query stale data.
you can tune the replica with these params if you want to avoid query
cancellations but are ok with little stale data.
also if the application can initiate a retry for the cancelled query
exception if they can handle at app layer itself.
RUNTIME-CONFIG-REPLICATION-STANDBY
<https://www.postgresql.org/docs/current/runtime-config-replication.html#RUNTIME-CONFIG-REPLICATION-STANDBY>

If you cannot deal with stale data at all, then you have two options.
1) synchronous replication
synchronous replication in very simple terms would mean, if you have a
cluster like
Primary -> ( Replica R1, Replica R2) the primary would wait for
acknowledgement from at least one of the replicas. this may impact your
tps, but you get consistency.
but geo setups with synchronous replication would be problematic.
2) read from the primary alone.
this is always the safest option, as no intermediate network, but this
would also mean all the read load on the primary will impact the db
performance and the housekeeping work like
vacuuming. and incase the server goes down, till the time you failover, you
cannot perform any read or writes depending on the catch up time.
SYNCHRONOUS-REPLICATION
<https://www.postgresql.org/docs/current/warm-standby.html#SYNCHRONOUS-REPLICATION>

> Having the read slave in the same AZ as its master - for better network
throughput;
AZ spread is meant for dealing with availability. It may be ok to have some
read replica in some AZ, but also have some in another AZ in case of a
major outage.
if you can use a load balancer, you can provide more weight to local
replicas then the remote replicas etc and use a health check like
replication lag to ensure read request
are served by nodes with tolerable lag only.
scaling-postgresql-using-connection-poolers-and-load-balancers-for-an-enterprise-grade-environment/
<https://www.percona.com/blog/2018/10/02/scaling-postgresql-using-connection-poolers-and-load-balancers-for-an-enterprise-grade-environment/>

> Having the latest PostgreSQL version to get its best performance
mostly always. not just improvements, but a lot of new features like
partitioning, logical replication, stored procedures etc are now available
in newer versions.
PostgreSQL: Release Notes <https://www.postgresql.org/docs/release/>

> Having the latest Operational System behind PostgreSQL to get its best IO
performance
yes at the software layer, latest versions would be more performant
"mostly".
but still the hardware layer plays an important role.
If you want to really validate improvements, you can run pgbench/sysbench
stress tests on the interested versions and see the changes yourself.

upgrade is always needed. not just for performance reasons, but security
reasons as well.
Also, keeping the upgrade on hold for a time, may mean jumping across
multiple versions which although might work,
but you would need to read the change log across the versions to ensure
nothing would be broken,
Hence regular upgrades avoid the pain.

--
Thanks,
Vijay
Mumbai, India