Storage Schema

In one line: this is the first social chapter where "just use SQL" is a defensible answer, and the reason is worth understanding. It is also where the chapter's largest arithmetic error lives.

Relational or not?

Our data is inherently relational, requiring strict order (chronological posts) and durability. SQL databases are ideal for these requirements, allowing us to efficiently query relationships, such as fetching followers or retrieving images by user ID.

This answer is defensible here and would not have been in that building block — because the metadata is small

Compare how the three social chapters answered the same question.

Twitter built Manhattan, a custom key-value store, after trying and abandoning Cassandra. Newsfeed used relational for entities plus a property graph for relationships. Instagram says relational, full stop.

The difference is volume, and Lesson 1 explained why: when the media is the product, the metadata gets small. Add up this chapter's own table:

Users       111,000 MB   =  111 GB
Followers 1,000,000 MB   =    1 TB
Photos       23,640 MB   =   24 GB
Videos       13,790 MB   =   14 GB
                            --------
Total                    ~  1.15 TB

A little over a terabyte for a billion users' metadata and 125 billion follow relationships. That fits in a well-provisioned relational instance with replicas, and it is three orders of magnitude below the media tier.

So the reasoning is: the bytes are in blob storage, so the database only holds pointers and relationships, and that is small enough that you get transactions and joins for free.

The chapter's own note qualifies it honestly: "Instagram officially uses a combination of SQL (PostgreSQL) and NoSQL (Cassandra). The loosely structured data, like timeline generation, is usually stored in NoSQL."

That is the right split, and it matches Lesson 10: relational for entities and relationships, key-value for materialized timelines. Timelines are per-user lists with no join requirements and enormous write volume — precisely the shape relational databases handle worst.

Choose the store by what the data is shaped like, and note that a media platform's metadata is unusually well shaped for SQL.

The tables

Where should we store the photos and videos? We'll store them in a blob store (like S3) and save the path to each photo or video in the table, as it's more efficient to store larger data in distributed storage.

Separate Photo and Video tables duplicate a schema for no benefit

The two tables are nearly identical — ID, user ID, location, caption, creation time, path — and the design gives both a row size of 394 bytes.

That duplication costs something real. Every query that wants "this user's posts" must union two tables and sort the combined result, and a feed is exactly that query. Adding a third media type later means a third table and a three-way union.

The alternative is one Posts table with a media_type discriminator — which is what the API already uses, since postMedia takes a media_type parameter. The API models it as one entity; the schema models it as two.

The defensible case for splitting is when the types have genuinely different columns — video needs duration, resolution, transcoding status, and thumbnail paths that a photo does not. But then the tables would not be the same size, and here they are identical.

When two tables have the same columns and the same size, they should be one table with a type column.

The followers-table estimate is wrong by 500x — and the prose and table disagree

The design's prose says:

"The follower table requires about 2,000 MB when storing 250 followers per user."

The interactive table in the same lesson says 1,000,000 MB.

Work it out:

500,000,000 users x 250 followers = 125,000,000,000 rows
125 billion rows x 8 bytes         = 1,000,000,000,000 bytes
                                   = 1,000,000 MB = 1 TB

The table is right; the prose is off by a factor of 500.

Two things worth taking from it.

The followers table is the largest thing in the database. At 1 TB it is nine times the users table and forty times all post metadata combined. The relationship data dwarfs the entity data — which is generally true of social graphs and is exactly why the newsfeed chapter reached for a graph representation.

8 bytes per row is optimistic. The schema shows toUserID and fromUserID, both INT. Two 4-byte integers is 8 bytes of payload, but a real row carries a header, and this table needs two indexes — one per direction, as Lessons 8 and 9 require for push and pull. Indexes on 125 billion rows are themselves substantial, plausibly doubling or tripling the real footprint.

So the honest figure is 1 TB of rows plus a comparable amount of index, and the followers table alone is most of the database.

When a prose figure and a table figure disagree, recompute — and note that a relationship table in a social graph is usually the largest object you own.

Storing paths, not bytes — the decision that makes everything else work

"We'll store the photos and videos in a blob store and save the path in the table."

The schema shows it directly: photoPath VARCHAR(256) and videoPath VARCHAR(256).

Lesson 3's arithmetic makes the magnitude clear:

Media stored inline:   5,430 TB/day
Media stored as paths:    ~38 GB/day of metadata

Five orders of magnitude, and it is what makes the relational choice viable at all. A database holding the actual media would be an exabyte-scale system with none of the properties that make relational databases useful.

This is the same rule every chapter in this module has reached from a different direction — Yelp's 256-byte photo paths, Twitter's blob store, the newsfeed chapter's references-not-values. Separate the objects you serve from the metadata you query, because they have opposite access patterns: metadata is small, queried by many predicates, and updated transactionally; blobs are large, fetched by exact key, and never updated.

What the schema is missing

Three fields the design needs and does not have.

No like or comment counts. Lesson 11 handles viral engagement with sharded counters, which live outside this schema — correctly, since a counter under contention does not belong in a relational row. But nothing in the schema references them, so the join between a post and its engagement is unspecified.

No expiry timestamp. Lesson 10's Stories feature requires one, and Lesson 4's task scheduler exists to act on it. The schema has creationTime on photos but no TTL field, and no story table at all.

No creationTime on videos. The Photo table has it; the Video table, per the schema diagram, does not — despite both being given the same 394-byte row size. For a feed described as chronological, a post without a timestamp cannot be ordered.

That last one is a small inconsistency with a large consequence, and it is worth catching: a chronological feed requires a timestamp on every item in it.

Next: the upload, view, and search workflows.