1

This is a question that I've not been able to find any good documentation for on the internet.

I think it's a common industry standard that for tables with heavy writes, you add an index on the read replica and not on the master. This way, you're ensuring the writes on master don't slow down, while also ensuring the reads on read replica are faster.

But doesn't the read replica also have writes, in the form of binlog replication? If I'm not wrong, the replication is simply a form of copy-pasting the commands from the binlog as-is. But the index on the read replica would still have to be updated, right? Since the binlog file won't have any "index updates" commands (because the master doesn't even have the index), I'm assuming that the "index update" on the read replica happens when an INSERT or an UPDATE commands comes from the binlog. So, is it still not a "WRITE" operation that happens? Will it still not slow down the write latency?

I understand that not all DBs have this capability of indexing a read replica separately, so this question is restricted to only those DBs that do, such as MySQL.

3
  • Indeed replicating data from master to read only slave performs write operation. So based on the requirement whether we need realtime replication or batched replication we decide replication frequency to minimize write load on RO replica. For better read performance from slave we go with batched replication. Jun 1, 2023 at 9:06
  • This is going to depend heavily on the database system, version, edition, and feature you're referring to. You should add that information to your Post. For example, in many cases, a read-only replica is not allowed to be indexed differently than the primary replica.
    – J.D.
    Jun 1, 2023 at 12:02
  • Thanks @J.D. I've added the MySQL tag. Jun 2, 2023 at 10:48

1 Answer 1

0
  • Since all writes occur on both the Primary and the Replica, it is unclear whether you will be saving anything.
  • The Replica should normally be "readonly", but you should be able to turn it off to add an index.
  • Index updates are not sent in the binlog, even when the Primary has indexes.
  • Replication is implanted in one of two ways: STATEMENT, in which each INSERT is re-parsed and run on the Repicas, or, ROW, in which the row is sent without needing to re-parse the statement. [I don't know for a fact that the index will be updated in the ROW case.] (MIXED is a 3rd option, but it has some types of statements being Statement versus Row.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.