overwhelmed database problem

[Bronislav Bronek]

I have the database where many connections is serviced in the same time (reading, writing)
My idea to unload the connections is to create a replica of my DB. So that, I have 2 DBs one for writing, second for reading.-

- could you tell me the way how to synchronize these DB ?

- maybe you know a better idea to solved that problem with  overwhelmed database problem ?

[Claude Moore]

Scale up your database.  My first advice is to avoid handmade solutions, any serious DBMS offer a way to set up clusters,replicas, high availability and so on.
Also, review your application scenario.How many apps connect to the DBMS? If you split it into a read only and a write only DBMS, you should also modify your applications to use the right datasource.
Moreover, read data may be live data, i.e fresh,actual data an application needs to process to fulfill a business task, so that Woking on a copy taken with a given frequency may not be a viable option.

[Tim Holloway]

It really depends on how your database is being ovewhelmed. Identify what resources are being used heavily and tune them. That may involve setting database options, upgrading hardware, using connection pools and caches, and a lot of other tricks. But without seeing anything I cannot offer a one-size-fits-all solution.

[Christopher Webster]

If this is a serious issue and a project where you are prepared to invest significant resources to re-architect your approach, you might want to consider finding out about CQRS - Command Query Responsibility Segregation.

The basic idea is that you have a separate data model for Read (Query) operations and for Write (Command) operations, so you can optimise each model specifically for queries or writes respectively.  This obviously means you need some mechanism to maintain the Read model when your Write model is updated, and this can introduce complexity and - if you get it wrong - fragility to your application.  But it also recognises the reality that your data model is trying to do many different things at once, and sometimes it is better to optimise for different things separately.

If you want to find out more about CQRS, Microsoft published a free CQRS book and other online resources about CQRS several years ago.  The CQRS pattern can be applied to any DB and using any programming language i.e. there is nothing Microsoft-specific about all this.

Just be prepared for some serious work if you decide to take this approach. Meanwhile, start by finding out where and why your database is being overwhelmed - it might be there is a much simpler solution!

[Claude Moore]

Christopher Webster wrote:If this is a serious issue and a project where you are prepared to invest significant resources to re-architect your approach, you might want to consider finding out about CQRS - Command Query Responsibility Segregation.

Good advice. I have seen Cqrs mainly applied with microservices but nothing forbids to apply it in any scenario. Be careful that  using two different data models also implies some workload for your database, so at least a first, basic analysis aimed at finding bottlenecks and performing some tuning should be your very first step.