I've only glanced at the discussion, but I think our setup is different. We use AWS RDS Aurora, which is not using physical nor logical replication, but instead uses a single logical volume for all instances in the Aurora cluster. Which means the transaction IDs are necessarily the same: it's reading the same data (at least logically). In other words, the replication is handled at a lower level (AWS' logical storage) so there's no need to use the higher-level PG replication functionalities. Comes with trade-offs obviously (Aurora lock-in, price, blablah). I don't know if physical replication solves your problem: if you have a multi-master setup it's yet another layer of complexity that I'm not very familiar with

This picked my interest and looked into Aurora's docs. At first glance Aurora is still subject to the same problem: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.Replication.html It suggests that replication is asynchronous to Aurora replicas, which would be problematic for SpiceDB when you care about the new enemy problem or read-your-own-writes scenarios. > As a result, all Aurora Replicas return the same data for query results with minimal replica lag. This lag is usually much less than 100 milliseconds after the primary instance has written an update. Replica lag varies depending on the rate of database change. That is, during periods where a large amount of write operations occur for the database, you might see an increase in replica lag.

I'd need to refresh my memory on the new enemy problem, but we are able to handle read-your-own-write. We use zedtokens and if the replica doesn't know of the revision, we fallback on the master

I see, so you are handling the problem at the application layer

More details there: https://github.com/authzed/spicedb/issues/1320#issuecomment-1561626079

I could imagine providing native support using this approach. A datastore middleware could route always to the read URL, and if an revision missing error is detected, it would fall back to the primaries. This of course has the side of effect of increased replica lag adding more pressure to the primary, which would eventually fall over as it's unable to handle the load representing the sum of all replicas' compute.

Yeah, and increased replica lag usually means the master is busy with writes so sending more traffic to the master might not be the best idea. Could be mitigated by waiting for the revision to be sync'd rather than falling back on the master.

right. I've seen that done in the past with

MySQL

, it has some native support for "waiting for tx to replicate", and so I worked on the assumption that's how we'd handle it the Postgres datastore as well, just not sure it has an equivalent functionality. We could add a configurable naive "wait and retry" on the datastore side.

I'd be surprised if that was built into postgres, but PG surprised me before!

Yeah I just did a quick search and couldn't find anything. Alternatively it could be implemented with a "retry+backoff if not

txid

visible".