How to make a large database hold the load?

Question

MySQL

Good day to all!

I am launching my cloud platform, the code and architecture are created from scratch.
The essence of my DBaaS: shared-server, users create databases with the prefix "username_"
Provided DBMS: MySQL, PostgreSQL.

One question arose: how to shard / replicate database servers so that for the user there are no differences from a server without scaling tools?
For example: a user writes some kind of request and he does not need to specify which server to send or where to read from, for him this is one server.

Thanks in advance)

Answer 1

Oooh .... Are you serious now?
If you are running a DBaaS platform and don't know what to do with the databases, then you don't have

I am launching my cloud platform, the code and architecture are created from scratch.

You have absolutely nothing at all, and architecture in particular. In addition to the "idea", which many people have been struggling with for a long time, while having the resources for their own DBA teams.

For example: a user writes some kind of request and he does not need to specify which server to send or where to read from, for him this is one server.

Means to this user it is enough to go only on the master.
For a spherical application in a vacuum, there is no means to automatically distribute requests across replicas. And this is completely impossible to do without imposing restrictions on use. For example,
select somefunc();
What does this function do? Does it only read data and therefore can be sent to the replica or does it write and therefore must be on the master? Want the right answer from the postgresql contributor? Even the database itself does not know this for sure until it executes the function.
Another example:

begin isolation level repeatable read;
select ...
update ...
commit;

As a maximum on the select request, you should already know whether you can send this transaction to the reading replica. How would you know in advance?
And other fundamentally unresolved issues.
DBaaS is not about large databases. No matter how much marketing wants to convince you otherwise. This is about small or start-up projects, where it is easier to overpay the service for a relatively sane setup and maintenance of a typical configuration.

Answer 2

Use connection pullers and load balancers. For example haproxy and pgbouncer for pgsql.
Study materials, articles, conduct research on suitable options.
Here is an article with an example from percona, maybe a good starting point https://www.percona.com/blog/2018/10/02/scaling-po...