I have a production RDS and Document DB hosted and managed in AWS. The data they contain is analyzed and researched outside of the application critical path. The analyze work involve running complex (and potential 'expensive') read queries on both DBs.
I don't want the analyze and research work done on them to effect the performance of the application that uses them in production. As an example, I don't want a query run on the DB as part of a research job to effect (in terms of latency / errors) on a query run as part of the application run.
I'm looking at 2 solutions to this problem and not sure which way is considered a better practice.
First method: Use Snapshots to Provision new DBs Clusters Daily
Use the backup snapshots to provision new clusters of both the RDS and the Document DB in a separate AWS account, let's call it 'research-account'.
Do the 'research' job only on the new DBs clusters in the 'research-account'.
Have a process that daily takes the latest snapshot and provision a new DBs clusters in 'research-account' using these snapshots.
Disadvantage is that restoring from snapshot is an overhead that takes time, and there might be additional complexity since the data in encrypted using the default KMS key and I need to share it with a different account (regarding Document DB). Moreover, I need to build the process that does it daily.
Another disadvantage is higher cost - creating new cluster costs more than adding a replica.
Second method: Use read-replica for both the RDS and the Document DB
Add a read-replica for both DBs
When performing the research job, make sure I connect directly to the read-replica (not the primary) thus any queries the application is running won't be affected(?)
Potential disadvantage is that I'm not sure that running read queries against a dedicated read-replica really doesn't cause any performance penalty to other queries running in the same time.
Another disadvantage is that it's harder to manage cost - if I want to know to additional costs that are caused by the research job - it isn't trivial to get this info (I think).
Are there other methods of achieving what I want? are there best practices I'm missing? Are the purposed methods making sense?
I tend to go with option #2 as it is cheaper, easier to setup and to maintain, but I'm not sure my assumptions and thoughts are valid and wanted to consult with the community.
The DBs size is around 10 GB for the RDS and 200 GB for Document DB (and they shouldn't change much over time).
In ideal world, the research and analysis queries should be run outside of the main application. You may use a data lake to run your research and analysis queries. This will give you the following benefit :
Reporting requirements can be managed separately without impacting the main application
Easy to configure role based access if required
Complex queries can be run outside of the main application
https://docs.aws.amazon.com/glue/latest/dg/aws-glue-programming-etl-connect-snowflake-home.html
If your preference is to stick to one of your options, then I would suggest to go for option 2. Having read replicas setup in multi AZs will not only reduce the load on the primary instance but will be helpful incase of failovers as well.