Databases Optimisation for Distributed Systems
Database scaling as a problem comes much later in distributed system unlike the scaling of the application specially when user scale explodes resulting in delayed query results from the DB. Following are the options we can opt when DB gets overwhelmed.
1. Indexing
This is one of the more common methodology opted for optimising the DB queries, let take an example of Football Clubs Table
| FC_ID | FC_NAME | FC_SYMBOL |
|---|---|---|
| 1 | Arsenal FC | AFC |
| 2 | SSC Napoli | SSC |
| 3 | FC Barcelona | BAR |
| 4 | Paris Saint-Germain | PSG |
| 5 | FC Bayern Munich | FCB |
Now in real case when there a lot of football clubs, getting an particular club by their symbol will lead to complete DB traversal in case of no indexing. This will cause bottling up and can be solved using indexing.
Indexing is can be done on any column, in this example lets add indexing on FC_Symbol, now when querying any record based on symbol, instead of complete DB traversal it can look on index get the results quicker. Indexing is done by storing columns in special datastructure (B-Trees) .
Only the column which get widely queried on must be indexed instead of indexing all columns otherwise this will lead to the problem which indexing solved at first place - slower DB query
2. Partitioning
It consist of partitioning (breaking down) a large table in small set of tables, lets take the same example where we would break down same table in three separate tables. This makes querying much easier combined with indexing, again this is done when records increase at a scale.
This one of the easier methods to scale DB when DB has to be scaled using same server. Each table can have their own index which can be queried on.
3.Master Slave Architecture
It consists of saving copies of DB on different servers (nodes), hence the load is distributed among the servers, the only caveat is all the read operations are done on slave nodes, but the write operation are done only on master node, in this we ensure consistency across the DB copies on the server. Again this scaling is done when our application is read-heavy.
