All possible ways to Scale a Database

Question

I recently began studying Database Scaling and have a few questions.

Please correct me if I’m mistaken on the following points:

1. A database (db) is a collection of tables (in relational databases) or collections (in non-relational databases).
2. A software system that manages databases (and performs various other tasks) is called a Database Management System.
3. A database server is a machine that stores the actual data (the "database") and runs an instance of a DBMS.
4. Types of database include: relational, document-based, graph-based, etc. Types of dbms includes: MySQL, Postgres, MongoDB, DynamoDB, etc

Assuming the above are correct, I believe the following are the primary ways to scale a database server horizontally:

A. Monolithic Architecture

One MySQL (or Postgres, MongoDB, etc.) instance managing 5 databases. Each of these 5 databases contains 3 tables of its own. All 5 databases are part of the same application (for example, Uber).

B. Database Replication

5 MySQL instances on 5 different servers. Each instance contains all 5 databases, and each database has 3 tables. This can involve either master-slave or master-master replication setups.

C. Microservices Architecture

5 MySQL instances on 5 servers, with each instance managing only one of the 5 databases. Each database contains its own 3 tables, and each instance serves a specific microservice.

D. Sharding

5 MySQL instances on 5 servers. Each instance has all 5 databases, and each database contains 3 tables. However, each instance’s user.db's "user" table only holds a subset of the total user data.

E. Sharding + Partitioning

In the scenario described above, each "user.db" (spread across different instances) implements database partitioning (for example, based on user IDs).

F. Sharding + Replication

Each MySQL instance has a master-slave or master-master replication setup while also handling its own shard of the overall data.

G. Sharding + Partitioning + Replication

Questions:

1. What are your thoughts on the scalability methods described above?
2. In a monolithic application (such as Uber on its first day), would it make sense to "avoid" placing all tables in a single database? Instead, should separate databases be created (e.g., "user.db" for user-related tables and "vehicle.db" for vehicle-related tables) while still storing them on the same database server under a single DBMS instance?
3. Can a single DBMS instance manage two databases located on different servers?
4. Is it possible to run two DBMS instances on the same server?

Answer 1

• Q1- What are your thoughts on the scalability methods described above?

In order to answer that, there would have to be a (long) discussion of the pros and cons of the various methods. Such discussions are off-topic as they are too broad. Check out the help centre or look at my profile for assistance on question asking and what's on topic!

• Q2- In a monolithic application (such as Uber on its first day), would it make sense to "avoid" placing all tables in a single database? Instead, should separate databases be created (e.g., "user.db" for user-related tables and "vehicle.db" for vehicle-related tables) while still storing them on the same database server under a single DBMS instance?

I would consider separate schemas for this use-case but not necessarily separate databases, but who knows? The system's architect(s) would have to balance a range of factors, and different architects could have differing opinions. Again, a bit too broad for this forum!

• Q3 - Can a single DBMS instance manage two databases located on different servers?

Yes, I believe they can using a clustered file system (not an expert in this area). Oracle RAC might be a good example?

• Q4 - Is it possible to run two DBMS instances on the same server?

Yes, very much so! PostgreSQL can have many instances running many different databases each, provided the instances use different ports. MySQL is the same, as I'm sure many (most?) other systems can also.

Confusingly, PostgreSQL's name for an instance is a cluster!

In future, please keep your questions here confined to one topic that can be answered in a reasonably short space - as alluded to above, long discursive questions and answers are discouraged!

p.s. welcome to dba.se!

Answer 2

"Scale for incoming traffic". If that is web traffic, the first thing to do is to have multiple web servers. It may suffice to have a single database server. If not...

I would set up a Galera cluster with 3 nodes. This provides a good deal of failure recovery. Also have a proxy to redirect traffic from the web servers to any of the database servers.

That will easily handle hundreds of queries per second go multiple gigabytes of data. If you need more, tell us about your application.

• Replication -- Galera; also replicas provide unlimited read scaling.
• Partitioning -- Probably not useful.
• Sharting -- You need to carefully think how to split up the data and write a bunch of code to handle it. Sharding is for write scaling.
• Multiple DB instances on a single server -- generally no useful because of being throttled by I/O and/or Networking.
• Lots of CPU cores -- rarely needed.
• RAID -- use hardware controller; used to be good for HDD; not so necessary now for SSDs.

In other words, I vote for F, but using a clustering solution rather than the fragile Primary-Replica pair or Dual-Primary.

Advice: Completely redesign your system every 6 months. Because:

• This lets you start small and figure out what works or doesn't.
• $$$