Course contentsShow
← System Design
Lesson 1642 of 1,91940. Real-World System: Social Media FeedFree lesson

Post Metadata and Schema Design

Define schema for posts including author, timestamp, content, media references, and metadata for efficient queries.

Post Metadata and Schema Design

What You'll Learn

You'll learn how to structure post data with the right fields and indexes to support efficient feed queries at scale.


The Core Idea

A social media post isn't just content—it's a rich object with metadata that drives everything from feed ranking to search to moderation. Your schema must balance query performance, storage efficiency, and future extensibility while supporting billions of posts.

Think of a post like a library catalog card: the content is the book itself, but the card contains all the critical information (author, date, category) that helps you find, sort, and manage it efficiently.

Essential Post Fields

Your post schema should include:

Identity & Ownership

  • post_id (primary key, UUID or snowflake ID)
  • author_id (indexed, foreign key to users)

Temporal Data

  • created_at (timestamp, indexed for chronological queries)
  • updated_at (tracks edits)

Content References

  • text_content (short text or reference to blob storage)
  • media_urls (array or JSON of image/video references)
  • media_types (enum: photo, video, link)

Engagement Metadata

  • like_count, comment_count, share_count (denormalized for fast ranking)
  • engagement_score (computed metric, indexed for feed ranking)

Feed & Ranking Signals

  • visibility (public/private/friends)
  • is_deleted (soft delete flag)
  • tags or hashtags (searchable)

Index Strategy

Critical indexes for feed queries:

  • (author_id, created_at DESC) — user profile timeline
  • (created_at DESC) — global recent posts
  • (engagement_score DESC, created_at DESC) — ranked feeds

Key Takeaway: A well-designed post schema with strategic indexes on author, timestamp, and engagement metrics enables sub-second feed queries even at billions of posts scale.