When a project grows, one of the most common bottlenecks is on the database side. It could come because the program’s complexity has been increased due to new requirements, or more often, due to an increase in the traffic that the application should handle.

Previous considerations

Like in many other cases, you should escalate your database base on metrics. As a general rule, I won’t recommend applying any database escalation without studying first the database, application, and infrastructure metrics. The only exception to this rule is when you are suffering a malfunction in your application, with constant outreaches, performance and/or latency issues, or even overloads and lockdowns in the database.

Furthermore, my advice is not to apply any of these technics until they are necessary. If you don’t expect or are not facing issues with your database, you should not over-dimension your system. You must be aware of the tradeoffs of the different escalation options and what they entail, like the increase in the complexity of the system, issues in maintenance, making debugging harder, etc.

Add an index

Adding additional indexes to your tables for the searches needed by your application will lower the latency of the queries.

The tradeoff of this solution is that you will consume more space hard disk of your DB. When you create a new index, you create a new table with the same number of records that the original table.

Furthermore, be aware that write actions on the database will be slower with this solution because you need to perform additional search and write operations in the index table. Generally, this will not be a problem unless your IOPS operations are heavily unbalanced towards writes (especially in huge tables, with several millions of records) or/and you abuse this solution, adding several indexes in the same table.

#software-development #database #software-architecture #data-science