Support Database read and write replicas

[quirinziessler]

Is your feature request related to a problem? Please describe
I am currently running a DD instance with 200k active findings. The findings DB table currently has nearly 7.5m rows. Day for day, my DD setup is getting slower and slower. That's why I am looking for some improvements for a more performant database setup & queries.

Describe the solution you'd like
It would be great, if DD supports database replicas and separate read and write actions to those different DBs. Furthermore it may be helpful to cache the Dashboard counts in the DB and not update them on every page refresh. This could maybe also applied to some more SQL queries.

What are your thoughts on this? Is there maybe anything already planned within the new version?

[Gunjan-Rajput]

I want to work on this issue. Please assign me this issue.

[valentijnscholten]

Not sure if this is something that should be done from inside DD. Real world cases for other software I have seen are using ProxySQL, pg pool or pg proxy.

[quirinziessler]

@valentijnscholten I agree with you, this is probably possible. But as most user don't host there own DB and instead are running on e.g. AWS Aurora putting another possible bottleneck and point-of-failure in the middle may not be the right way. Hence I think it should be integrated to DD. Django has the ability to integrate it very easy so why don't use this?

[valentijnscholten]

I didn't know Django had support for it. At first glance it looks easy to setup, but also less flexible. And I wonder if it works well with "all" plugins.
ProxySQL can be made HA as well, but is more work than using cloudnative stuff indeed.

[quirinziessler]

It should. So far from my understanding we need to go through all functions and define if they are read only or not and then define the required DB conn or put a router in the middle that knows how to handle them. If we do this one by one and let plugins/queries which are not yet edited run against the default db there should be no issue. And if there is one, we could run it against the default db ongoing.

I know that will be a lot of work but it will be worth it.

[manuel-sommer]

I would love it if you would provide a PR @quirinziessler