Optimizing a database for a social media platform

Optimizing a database for a social media platform is something to be carefully considered. Many factors come into play when designing a database scheme that can carry the heavy load of a lot of users. Talkr.app doesn't have many users like other social media platforms, but that might change one day. Are we really overengineering here? Perhaps, but you might never know if one day traffic increases to the point when we need to redo everything. We don't want that scenario.

When we created the MySQL database layout for Talkr, we ran into one immediate engineering question: how many users are going to use it? and what if it suddenly grows beyond what a single INT (2,147,483,647) can handle? Wait a minute, almost 2 billion? yes, that seems like a lot, but when dealing with a massive amounts of statistics, likes, shares, timelines, and user interactions that requires a giant table, then 2BN adds up quickly. We thought: better choose something else, as we don't like to adjust or re-engineer the database later on, or in the heat of the moment when things get really tight.

In comes BIG INT.

We went for BIGINT on most of our MySQL database tables where there are integers involved. A BIGINT has a maximum value of 9,223,372,036,854,775,807 and UNSIGNED even greater: a maximum value of 18,446,744,073,709,551,615.

That suffices for sure.

One of our other tips is to keep tables small and descriptive. Make sure that each table uses keys and a indexes properly when those indexes are queried a lot.

Another tip is to use pre computed feeds. With precomputed feeds we basically limit a lot of JOIN and LEFT JOIN queries, by setting up a table for timelines that can be queried directly without the need for JOINS. This small adjustment takes the load of MySQL and makes things snappy and fast.

We also considered table partioning, but haven't done so yet. Maybe when the site grows, table partioning comes into view. So we can do without it for the moment.

I hope this helps to understand how we engineered the database for Talkr.app so that it can handle traffic without slowing down too much.