I think we've got (1) covered elsewhere in the comment tree.

For (2), semi-synchronous replication is a MySQL term which we realize in Postgres is by using synchronous replication with ANY one of the available replicas acknowledging the write. This allows us to guarantee durability in two availability zones before acknowledging writes to clients.

In MySQL the _semi_ part of semi-synchronous replication refers to the write only needing to be written to the binary log on the replica and not (necessarily) applied to InnoDB. This is why a MySQL database might be both acknowledging semi-synchronous writes and reporting non-zero replication lag.

▲

n_u 4 days ago | parent [-]

> write only needing to be written to the binary log on the replica and not (necessarily) applied to InnoDB.

Ah. I wonder are writes in the log but not yet in InnoDB are available for reads? Then your write may succeed but a subsequent read from a replica would not see it so you lose read-after-write consistency. Perhaps that's another tradeoff.

I'll have to research a bit more but the MySQL docs [1] say "requires only an acknowledgement from the replicas, not that the events have been fully executed and committed on the replica side" which implies that it can't be read yet.

Thanks!

[1] https://dev.mysql.com/doc/refman/8.4/en/replication-semisync...

▲

rcrowley 4 days ago | parent [-]

A lagging replica, even one that just acknowledged a semi-sync write, will return stale results if you route a `SELECT` to it.

First and foremost, the extra copies of the data are for fault tolerance. In specific circumstances they may offer some slack capacity that you can use to serve (potentially stale) reads but they're never going to offer read-your-writes consistency.

The docs you quote are a bit obtuse but the "acknowledgement" is the row event being written to the binary log. "Fully executed and committed" is when it makes its way into InnoDB and becomes available for future reads.

	▲	n_u 4 days ago \| parent [-]
		Ah yeah that makes sense. I guess if you are only using replica for fault tolerance and not as a read-replica then it seems semi-synchronous replication is strictly better than synchronous replication. I suppose in semi-sync the failover will take a little longer but it's probably inconsequential. Thanks!