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)tagsorhashtags(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.