Storage Schema

In one line: the row sizes here feed every storage estimate in the chapter, so an error propagates. Two of the four do not add up.

Note: The INT type in the schema refers to an 8-byte ID generated by the unique ID generator.

Place

Size = 8 + 256 + 1000 + 8 + 8 + 8 + 1 = 1,289 bytes

1,289 in the schema, 1,296 in every estimate

The design states 1,289 bytes here and then uses 1,296 bytes in the storage estimation, the incoming bandwidth calculation, the outgoing bandwidth calculation, and the segment index calculation.

Seven bytes. Across 500 million places that is 3.5 GB, which is 0.4% of the 835 GB total — immaterial to the conclusion and worth noticing anyway, because it means one of the two numbers has no derivation behind it.

The likely explanation is padding. 1,296 is divisible by 16 while 1,289 is not, and databases do pad rows to alignment boundaries. If that is the intent it is a reasonable adjustment — real row sizes exceed the sum of their columns because of alignment, null bitmaps, and per-row headers.

But it is never stated, which leaves you unable to tell a deliberate allowance from a typo. The useful habit: when a derived number and a used number disagree, say which one you are carrying forward and why. Here, carry 1,296 — it is the one every downstream figure depends on, and padding makes it the more realistic of the two.

Photos

Published:  Size = 8 + 8 + 8 + 256 = 280 bytes
Listed columns:      8 + 8 + 256   = 272 bytes

Four terms, three columns

The sum has four eight-byte terms where the schema lists three columns. Something eight bytes wide is in the arithmetic and not in the table.

The most plausible missing column is a User_ID — who uploaded the photo — which the design would want for attribution and moderation, and which the diagram's Photos entity does not show.

The consequence is small, 8 bytes over 500 million rows, or 4 GB. But unlike the Place discrepancy, this one is not explainable as padding: it is a term that corresponds to nothing.

If you meet this in an interview the right move is to say what the missing column probably is rather than to quietly accept 280. A schema whose row size exceeds its columns is telling you a column is missing, and identifying which one demonstrates you read the schema rather than the total.

Photo_path is why this database is 3 TB instead of 1.5 PB

The single most consequential decision in this schema is that the Photos table stores a 256-byte path, not the image.

500 million photos x 3 MB each   = 1,500,000 GB = 1.5 PB   <- if stored inline
500 million photos x 280 bytes   =         140 GB          <- as paths

A factor of ten thousand, and it is what makes everything else in the chapter possible: a metadata database small enough to index aggressively, replicate cheaply, and hold largely in memory.

The images live in blob storage and are served — as Lesson 3 argued they should be — directly to clients via a CDN rather than through the application tier.

This is object storage's argument applied concretely, and the general form is worth carrying: separate the objects you serve from the metadata you query. 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 at all.

Reviews and Users

Both of these add up exactly.

A 512-byte review is about 80 words — and there is no timestamp anywhere

Two things about the Reviews table are worth questioning.

512 bytes is short. Roughly 80 words, when real reviews routinely run to several paragraphs. Doubling it to 1 KB would add 0.5 GB per day, or under 4 TB over a decade — still trivial against the design's other numbers. The constraint is doing no work, so it should be relaxed.

Nothing in this schema has a timestamp. Not reviews, not places, not users. That is a more serious gap than it appears, because timestamps are load-bearing for:

• Ordering reviews — you cannot show newest first.
• Recency weighting in ratings — a place that improved last year should not be dragged down forever by old reviews.
• Detecting review bursts, which is the primary signal for fraudulent reviews.
• The incremental index updates in Lesson 13, which need to know what changed since the last rebuild.

The last one matters architecturally. The rating calculator runs once a day over reviews; without timestamps it cannot process only the new ones, so it must reprocess everything.

A schema with no time dimension can only ever be rebuilt, never updated incrementally. That is worth raising unprompted.

Rating appears in two tables, meaning two different things

Reviews.Rating is 1 byte — one user's score, an integer 1 to 5.

Place.Rating is 1 byte and described as "star rating 0 to 5, calculated based on user reviews" — an aggregate, and a derived value.

Two observations.

A derived column is a denormalization, and a deliberate one. You could compute a place's average rating by scanning its reviews on every read, but search results show ratings for 20 places at a time, so that would be 20 aggregate queries per search. Storing the aggregate turns it into a column read.

The price is that it must be kept current, which is precisely the rating calculator's job and why Lesson 13 runs it daily.

One byte cannot hold a useful average. An integer 0 to 5 loses the distinction between a 4.2-star place and a 4.8-star place, which is exactly the distinction ranking depends on. The entity diagram types it as DECIMAL, which is right — but then it is not 1 byte. Small, and it matters because the ranking in Lesson 13 sorts on this column, and sorting on a value rounded to whole stars puts thousands of places in ties.

Next: the components and how a search flows through them.