Not sure if I missed something here :

1. When we say that "Data is replicated to two replicas. " are we saying sync or async way. If its async how can we guarantee that during we are not providing the stale data

After all the biggest problem is read repair latency.

Expand full comment

I think it can be both sync or async and its upto the developer to decide which one they want. If the reads MUST be consistent with writes (like a banking system), then we may need to handle insert in primary server + replicate data to read server as one atomic operation. Else if it is acceptable to have read replicas out of primary server for sometime (social media), then we can go for async replication (eventual consistency)

Expand full comment
May 17, 2023·edited May 17, 2023

Are there any middleware for postgresql? It will be nice if the middleware can do the heavy lifting of routing "read your own write" by understanding the client's DAO call i.e

1. If the client say inserts and reads what it inserted in a transaction, the middleware should know this complete transaction request and call only the primary

2. If it is just read it should call replicas (even better will be if it calls replicas that has caught up with the primary at least for that log in Primary)

Expand full comment