Most databases scale much more than we think.

Sat, Dec 30, 2023

One should know the integrities not just to scale things, but to take full advantage of utilizing existing resources. And necessarily avoid hopping into the dilemma of choosing the best database or scaling strategy.

I’ll bifurcate the post based on pre and post-development strategies.

Pre-development:

• Fetch only the required data, and if the data is still large -> paginate (this will lay the foundation for schema design)
• Different queries on the same view should avoid searching the same data multiple times (multiple queries on the same table should be avoided)
• It’s not necessary to fetch all the data for the view in a single query -> breakdown into components

Essentially, the UI and DB view can be different

Post-development

• An optimized query can be scaled at least 1000x compared to an un-optimized one
• There will be a lot of gotchas while creating indexes depending upon the type of DB (there will be more and more surprises as you dig in)
• Going through the codebase to determine the un-optimized parts is not feasible
• The observability can be broken down into metrics and logs (on the DB level), along with tracing
• 95% of the time, the profiler slow log is enough for improvements
• Metrics are required to reduce the search space for inefficient aspects (e.g is the b-tree the reason for the occasional spike instead of the query)

Till what point improvements are feasible - until there are spikes in the resource usage.

One needs to accommodate the above factors based on the database, again everything might not hold for every scenario, e.g. OLAP databases.

And yes, you don’t need horizontal scaling - and it’s less common than one might think.