Been a Backend Engineer Has made me take interest in techniques for scaling databases to meet high demands in terms of performance and reliability. sometimes we get a lot of pressure when it comes to designing databases because you don't know if you should design for a million users while you only have a thousand it can be a waste of resources, However, I wanted to know the progression that will lead me to such a point so that I can plan with foresight.
After taking a Udemy course on Database Engineering by Hussein Nasser I’ve learned some of the common techniques used to scale databases. This is relevance as one’s database load grows.
Indexing
Indexing in simple terms means organizing data in a way that makes it faster to retrieve. Relational databases allow you to create indexes on one or more columns in your tables. This can result in huge performance gains with little or no effort and as such is highly recommended as a way to optimize your database. The link below shows how indexes should be based on the kind of queries being run against a given database.
Steps to Building Useful Database Indexes
Sharding
Sharding is the process of horizontally partitioning data into multiple databases. Instead of splitting a database by groups of tables, The table’s rows are split across multiple instances. In order to save or retrieve a piece of data, certain models are employed in determining the shard to use the data resides. This adds a fair amount of complexity to your database setup and your application as well. Operations that might otherwise be straightforward can become a challenge under this configuration. As such sharding is usually reserved for last and carried out by admins who have a solid grasp of the database engine they’re dealing with.
Primary-replica architecture
This involves scaling a database horizontally by running two or more instances. One is designated the primary database and handles writes while the others are replica databases which handle reads. The database engine uses a replication protocol to keep all the instances in sync by copying the data from the primary database to the replicas. This setup comprises a database cluster. The pros of the architecture include traffic load balancing and automatic data backups which result in improved performance and reliability of a database.
Query optimization
This is the good old technique of finding poorly written queries or queries that could be improved upon and making them more efficient. To be able to write optimal queries, you need to have a good handle of the query language SQL, as well as the database engine MySQL or PostgreSQL. Simple tips like selecting specific fields as opposed to selecting all fields SELECT * when not required can go a long way in giving your application the performance boost it needs.
Visual inspection of queries might not be sufficient as some queries only break when confronted with large amounts of data or heavy traffic. Application Performance Monitoring tools can be instrumental in finding these bottlenecks as they allow you to view the performance metrics of all your queries and figure out which ones are degrading your database.