I want to integrate CNPG operator to my k8s cluster, but have several questions, I tried to find answers on the web, but couldn't, so decided to ask them here.
My team needs HA Postgres cluster with auto-failover, as CNPG could provide. But, moreover, team of developers needs some consistency guarantee: reads after writes. That means, that when response on some write requests returns, all next reads must see this write (or later one). When the PG is running in single instance manner, it is guaranteed. When it is running in primary-replica mode, guarantee could be achieved by remote_apply and synchronous_standby_names settings. But if I choose to use all names in synchronous_standby_names setting, cluster will lose it's HA status, because fail of single pod will cause refusing write queries of entire system.
So, Postgres configuration allows, and CNPG configuration allows to use quorum replication for these purposes: we can wait only for k of n replicas to answer to primary. But, than, the question is: can we load balance read queries on standby replicas, as CNPG does? CNPG creates 3 k8s services (-rw for read and writes leading to primary, -ro for read only leading to replicas, -r for read leads to replicas and primary because all of them can handle reads).
So, what if service send my query to replica, that wasn't in quorum on previous write? Is it possible, or CNPG makes something to avoid this behavior, and my application can use all replicas for reads using -r or -ro services? Or maybe connection pooler may balance queries on right replicas? (I tried to find out it on web, but couldn't)
I need to answer these questions to choose architecture to build in our platform. If you have some advice on my situation, will be glad to listen to you!
Tried to find this info on web, or in documentation, but all of the links were talking about work of synchronous commit without needed info about quorum corner cases.
Just to be clear, this is more generally related to Postgres rather than CloudNativePG.
Beware that synchronous commit in Postgres can be changed at transaction level, which might invalidate your assumptions. Anyway, suppose you are interested in "remote_apply" for all your transactions. What you are thinking of would require the operator to continuously update the service based on the actual application of redo logs by the instances in the cluster that are part of the quorum and the primary. This would be too invasive and expensive.
However, I suggest you look at how CloudNativePG implements synchronous replication support. The formula, defined in the documentation, is the following:
1 <= minSyncReplicas <= q <= maxSyncReplicas <= readyReplicas
This means that if an instance is unhealthy, it is automatically excluded from the quorum by CloudNativePG - as it prioritizes self-healing (at the moment we do not have a knob that transforms the current "preference" of synchronicity with actual requirement, but it can be easily implemented if needed).
Thanks to this self-healing mechanism, I believe you could use the "-r" service to randomly point to any of the available instances in the cluster and have a consistent view for your read-operations.
I have never tried it, but it should theoretically work. Keep us posted.