· SWE Editorial · System Design · 4 min read
Design a News Feed: Data Model and APIs
Schema and API design for the news feed system design interview: feed table, friendship graph, REST vs GraphQL, and cursor pagination vs offset, updated July 2026.
Once the architecture is on the whiteboard, interviewers will push into the data model and API surface. This is where candidates lose points for hand-waving — a vague “we’ll store posts in a database” answer is not sufficient at mid-level and above. This article covers the core tables, the friendship/follow graph, API design choices, and pagination mechanics.
Core Tables
Users table
| Field | Type | Notes |
|---|---|---|
| user_id | bigint (PK) | Snowflake or UUID |
| username | varchar | Unique, indexed |
| created_at | timestamp | |
| profile_metadata | JSON/blob | Avatar, bio, etc. |
Posts table
| Field | Type | Notes |
|---|---|---|
| post_id | bigint (PK) | Snowflake ID — encodes timestamp for natural sort |
| author_id | bigint (FK) | Indexed for author-timeline lookups |
| content | text | Or reference to blob storage for large content |
| media_urls | array/JSON | Pointers to CDN-hosted assets |
| created_at | timestamp | |
| visibility | enum | public, followers-only, private |
Feed table (precomputed timeline)
| Field | Type | Notes |
|---|---|---|
| user_id | bigint (PK, partition key) | The viewer whose feed this is |
| post_id | bigint (sort key) | Sorted by timestamp descending |
| score | float | Ranking score, updated by re-ranking job |
| inserted_at | timestamp | For TTL-based eviction |
The Feed table is deliberately denormalized — it stores (user_id, post_id) pairs rather than joining against the Posts table at read time. This is the entire point of fanout-on-write: pay the storage and write cost up front so reads are a single indexed lookup, not a join across the follow graph and post store.
Friendship / Follow Graph
Model follows as a directed edge, since “A follows B” is not symmetric (unlike mutual friendship on some platforms):
| Field | Type | Notes |
|---|---|---|
| follower_id | bigint | Indexed |
| followee_id | bigint | Indexed |
| created_at | timestamp |
Store this table twice, logically: one index optimized for “who does user X follow” (queried when building X’s feed) and one optimized for “who follows user Y” (queried by the fanout service when Y posts). In practice this means two indexes on the same underlying edge table, or two denormalized tables if write amplification on a single table becomes a bottleneck — call this tradeoff out explicitly, since it’s a natural follow-up question.
For celebrity accounts, this follow table can have tens of millions of rows pointing to a single followee_id. Mention that this is precisely why the fanout service special-cases high-follower accounts — a naive “fetch all followers, write to each cache” loop would fail to complete in reasonable time for a single post from a top-tier celebrity.
REST vs GraphQL for the Feed API
| Dimension | REST | GraphQL |
|---|---|---|
| Client over-fetching | Common (fixed response shape) | Avoided (client specifies fields) |
| Caching | Simple (HTTP cache, CDN-friendly) | Harder (single endpoint, POST-based) |
| Versioning | Explicit (/v2/feed) | Implicit via schema evolution |
| Mobile client fit | Requires multiple endpoints for varying screen needs | One query adapts to screen/data needs |
| Interview default | Safer, more universally understood | Signals awareness of client flexibility tradeoffs |
For a feed endpoint specifically, REST with a well-designed cursor-based contract is the safer default to propose first — it’s cacheable at the CDN edge for anonymous or semi-personalized views and easier to reason about under load. Mentioning GraphQL as a viable alternative for clients that need to vary field selection by device (e.g., omitting media URLs on a low-bandwidth mobile view) shows range without over-committing the whiteboard to a heavier stack.
Example REST contract:
GET /v1/feed?cursor={opaque_cursor}&limit=20
Response:
{
"posts": [ { "post_id": ..., "author_id": ..., "content": ..., "score": ... }, ... ],
"next_cursor": "eyJ0cyI6MTc..."
}Cursor Pagination vs Offset Pagination
This deserves its own comparison because it’s a frequent point of confusion and a common follow-up question.
| Aspect | Offset pagination (OFFSET/LIMIT) | Cursor pagination |
|---|---|---|
| Behavior under inserts | Items shift, causing duplicates/skips | Stable — cursor anchors to a specific item |
| Query cost at large offsets | Increases linearly (DB must scan/skip rows) | Constant — indexed seek from cursor |
| Implementation complexity | Trivial | Requires encoding/decoding opaque tokens |
| Supports “jump to page N” | Yes | No (sequential access only) |
| Fit for infinite-scroll feed | Poor | Strong — this is the standard choice |
The cursor itself is typically a base64-encoded combination of (timestamp, post_id) — the tuple guarantees a total order even when two posts share the same timestamp to the second. The server decodes the cursor, performs an indexed range query (WHERE (created_at, post_id) < (cursor_ts, cursor_id) ORDER BY created_at DESC LIMIT 20), and encodes the last row’s tuple as the next_cursor for the client’s subsequent request.
API Surface Beyond the Feed Read
A complete answer should also sketch:
POST /v1/posts— create a post (triggers the async fanout pipeline described in the architecture deep dive).POST /v1/follow/{user_id}— create a follow edge (may trigger a backfill of the new followee’s recent posts into the follower’s feed cache).DELETE /v1/posts/{post_id}— soft-delete, which must also invalidate the post from every follower’s feed cache — a detail worth naming since naive fanout systems forget the deletion path entirely.
For a structured reference covering data modeling and API design patterns across every major system design question, not just feed, The 0-to-1 SWE Interview Playbook (Amazon: https://www.amazon.com/dp/B0H256Z1MF?tag=sirjohnnymai-20) includes worked schema examples you can adapt live in an interview.
Summary
A strong data model answer denormalizes the feed table for read performance, models follows as a directed graph with dual indexing, defaults to REST with cursor pagination for the read-heavy feed endpoint, and explicitly handles the deletion/invalidation edge case that most candidates forget.