Database design

For the media files (images and videos), blob store would be the most appropriate data store, due to the expected size (in petabytes) and the nature of the files (write once, read many times).

An interesting design question is whether to store media files in CDN. I lean toward no, because, the media files are viewed only by participants of a chat. I would imagine this would be usually between 10, instead of millions of users. As such, no media files will be needed by millions of users. Therefore, it does not suit CDN's caching capability well. Instead, we will cache in our datacenter.

Database Choice

For the text data, at 200 TB for 1 year. The characteristics of text messages are:

(1) the system needs to write a lot of small messages,

(2) once written, the data won't change often.

So this suits Cassandra.

The text data does not require strong consistency guarantees, so we do not have to rely on RDB's ACID properties.

I'll consider two types of DBs: Document DB (e.g. Mongo) and LSM based DB (e.g. Cassandra)

Pro of Document DB (e.g. Mongo):

- Supports relational query (e.g. list all messages by a user).

- Schema flexibility to allow further development of the product.

Con of Doc DB:

- As a B-tree based database, its write throughput and scalability wouldn't be as good as LSM based ones (like Cassandra). This would be problematic for storing text messages.

Pro of LSM-based DB (e.g. Cassandra):

- Higher write throughput.

- Scalability.

Con of LSM-based DB:

- No relational query.

- Inflexible schema.

There are three ways we can go:

1. Mongo to store all the data. Primary challenge will be write throughput.

2. Cassandra to store all the data. Primary challenge will be relational query.

3. Hybrid approach - text messages in Cassandra, relational data like Users, Groups in Mongo (or RDB).

I think approach (3) would be quite defensible. It takes the best of both technologies. (We need to be careful this wouldn't add too much complexity to the system.)

Important Data Models:

Message:

Message ID (primary key)
Sender (user_ID) : index
Timestamp : index
Message
Media Link (URLs)

Message Timestamp presents an interesting design choice. Should it be timestamped by client or by server? I lean toward relying solely in server's timestamp, as it would be much easier to keep servers time in sync at a reasonable level. We don't have a control over clients' clock (and clock skews).

One advantage of client side timestamp is that a user will be able to prove they have sent a message by the client side timestamp, even if the message transmission fails due to a network failure. But we are mainly building this for consumer use, instead of a legal use. Therefore, this advantage of client side timestamp can be deprioritized.

The data should be indexed by the message ID (primary key), sender, and the timestamp.

Users' geographic location can be used in data partition. I would assume (should be tested) most chat groups would have a strong geographic locality, in other words, they would happen in one geographic region. So it would make sense for the chat data (text and media) to be primarily stored in, and served by, one data center. But the data should be replicated, asynchronously, to other data centers in other regions for fault tolerance.

Submissions

Displaying Solutions with Scores Ranging from 1 to 10