0

I'm building a service (or rather a set of microservices) to serve as the backend for a social-network-like website. In short that means the following for my data:

  • millions of entities
  • with dozens of attributes
  • with potentially thousands of connections between the entities that change over time (like on Facebook, someone might have thousands of "friends").
    • (there is more than one type of connection, each potentially numbering in the thousands)
    • connection examples:
      • entity A knows entity B
      • entity A has blocked entity B
      • etc.
      • conceptually each entity maintains long lists of identifiers of other entities
  • and I need to be able to do searches where
    • I can search all entities based on a set of attributes to match
    • while filtering out already existing connections of the initiating entity

I'm trying to figure out what would be the best database solution to store this data. I'm not well-versed in database tech, so I need some suggestions to consider.

I know SQL / relational databases can easily scale for the first 2 criteria (number of entities and number of attributes), but I'm not sure how well suited they are for managing the connections.

I need a suitable database tech, which can also be set up in a distributed fashion - and ideally available in a cloud environment. If that is a SQL database, how would I store and manage the connections?

0

1 Answer 1

1

I'll try to spit some facts that are hopefully helpful.

I know SQL / relational databases can easily scale for the first 2 criteria (number of entities and number of attributes), but I'm not sure how well suited they are for managing the connections.

And

The "thousands of connections" are the actual number of records related to one another?

Yeah, pretty much. Like on Facebook, someone might have thousands of "friends".

In a relational database system (RDBMS) the relationship ("connections") between two records is more so a logical construct that exists implicitly or can be explicitly defined with a foreign key. Outside of that, in physical form, it is defined by the actual rows and attributes of those rows in the database.

An example implementation of this for a social network could look like a Users table with a UserId column (among other attribute columns) and a UserFriends linking table with the columns UserId and FriendUserId (since the relationship of friends is many-to-many, by nature). Both the columns in the UserFriends table would be foreign keys to the UserId column of the Users table. You can see how that's more of a logical construct. And the physical side of it is just the number of rows that exist in the UserFriends table. Each row in that table would represent one friend of a specific user.

So the "thousands of connections" you asked about, is now literally just rows in a table or instances of entities, as you already acknowledge you're aware that an RDBMS can handle fine. In fact, thousands is a tiny amount of rows (especially relative to a table in the millions+ overall) for a particular entity to relate to.

You would essentially model the other use cases in a similar fashion, e.g. BlockedUsers could be a linking table with the columns UserId and BlockedUserId, etc.

  • and I need to be able to do searches where

    • I can search all entities based on a set of attributes to match

From a performance perspective, in an RDBMS, that would just require proper indexing on the attributes that would be typically searched on.

  • while filtering out already existing connections of the initiating entity

You can use the UserFriends table to accomplish this via an anti-semijoin or NOT EXISTS type of query. Again, as long as things are properly indexed, this should be an efficient search.

I need a suitable database tech, which can also be set up in a distributed fashion - and ideally available in a cloud environment.

I'd say walk before you can run. Until you prove out that you need a distributed system, don't try to implement one out of the gate, because the cons outweigh the pros of one, when it's not actually needed. Most people are fine vertically scaling (especially in the cloud) their system, because of how well database systems scale when architected correctly. Also, most modern RDBMS do offer features to distribute the data and workload, so it's a moot point anyway, when deciding between database systems. Also, most modern RDBMS are available in the major cloud providers these days too.


This is a very rough cut explanation of how modern social media systems generally implement their database systems. At companies like Facebook, they have so many teams of developers and many more micro-use cases than the average developer will ever need to support, that typically they use a mix of database systems actually. Some of that is simply dictated by their developers' preference. But usually an RDBMS is their primary database system for modeling standard use cases, such as the ones you've mentioned.


An alternative database system that could be used to model the relationship between Friends is a Graph database system. The many-to-many nature of friends makes it more representative of a graph-like problem. But with social networks there's typically other data points that aren't necessarily graph-like (e.g. the Posts a User makes) which is more fitting in an RDBMS. Overall an RDBMS will likely be the best fitting for most standard use cases of a social network.

2
  • Thank you for the comprehensive answer! An RDBMS was my first thought as well, with the structure you outlined, but I wasn't sure that searches with those potentially huge joins would work. Thanks for confirming that it would; now I'll be more comfortable walking down this path, and only looking for alternatives if I run into an actual roadblock. :-)
    – Babszem
    Mar 28, 2023 at 0:08
  • @Babszem No problem, glad to be of help! All modern database systems basically follow the same algorithms when joining data together (the most efficient methodologies have been understood for a long time) under the hood. RDBMS are generally the best go-to for most common use cases, with other database systems focusing on optimizing for more edge case scenarios.
    – J.D.
    Mar 28, 2023 at 0:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.