Leveraging AWS Read Replica for minimal downtime
It is really a very big problem to apply indexing or executing any query on a table containing millions of records. There is a large probability of query taking locks on the table. A few people just wait for the query to execute while some just end up killing important background queries. Both the above-mentioned scenarios are not fruitful as the query taking lock might slow or turn down the entire database. It is not either a better choice to kill background queries as it may affect the running database. The same situation occurred to me and rather than choosing one of the above ways, I decided to use a significant feature of Amazon Web Services – AWS Promoting Read Replica. By executing queries or indexing on AWS Read Replica and promoting it to the database, you will be on a much safer side.
AWS Read Replica is wrongly perceived by many people that it is just used for executing read queries on a database, instead we can also apply indexing directly to read replica. One major disadvantage of such indexing is that it will not be applied directly to the main DB instance. This is where the AWS Read Replica Promotion comes into action. We can promote the Read Replica to the main DB instance.
The same feature can also be used for altering the schema or modifying large tables in a production environment. There will be minimal downtime. The downtime will be mainly of 10-20 seconds when we have to apply read locks to the main database instance. The read lock is applied so that no write query is able to execute on a database instance. Thus, Read Replica will be in sync to main DB instance.
The following are the steps to apply indexing on AWS RDS having millions of records:
- Create a Read Replica of existing RDS.
Click on Instance Actions and then click on Create Read Replica. The following window will appear:
Always check Yes on Enable Automatic Backups. If you choose No, then you will not be able to create Read Replica of the promoted RDS.
- Create read locks on main RDS.
- Execute indexing or alter tables on the Read Replica.
The indexing can be applied to the AWS Read Replica. The indexed data will not be reflected in the original database instance. The indexed data will only be served through Read Replica.
- Promote Read Replica to the Database instance.
Promoted read replica will now behave as main RDS and not as read replica. If required, new read replicas can be created. After clicking on Promote Read Replica, a new RDS will be created with indexed data.
The new RDS can now be replaced with the original database instance.
