Social network database schema
A schema for a social product with users, posts, follows, likes, comments, and a feed materialisation strategy. Designed to scale from 10k to 10M users without a rewrite.
Database schema previewSocial network database schema
Recreate this in Raltey
Copy this prompt
Paste this into Raltey's AI agent to generate the same diagram, then tweak it on the canvas.
Prompt
Design a PostgreSQL database schema for: Social network database schema. A schema for a social product with users, posts, follows, likes, comments, and a feed materialisation strategy. Designed to scale from 10k to 10M users without a rewrite. Include these tables: user_account, follow, post, post_like, comment, feed_entry. Use proper primary keys, foreign keys, and indexes. Export SQL DDL for PostgreSQL.
Context
- - Read-heavy: ~95% reads, mostly the home feed.
- - Feed is materialised on write (fan-out on write) for users with <5k followers; computed on read for high-follower accounts.
- - Soft delete on posts and comments to retain references.
Patterns
- - Fan-out on write for normal accounts (write to N feed_entry rows when a post is created).
- - Fan-out on read for celebrity accounts (compute feed by querying recent posts from followees).
- - Composite PK on feed_entry keyed by created_at for fast pagination.
Tables
The schema, table by table
user_account
DBML - user_account
Table user_account {
id uuid pk
handle text unique not null
display_name text not null
bio text
created_at timestamptz default `now()`
}follow
Directional follow graph.
DBML - follow
Table follow {
follower_id uuid [ref: > user_account.id]
followee_id uuid [ref: > user_account.id]
created_at timestamptz default `now()`
pk (follower_id, followee_id)
}post
DBML - post
Table post {
id uuid pk
author_id uuid [ref: > user_account.id, not null]
body text not null
media_url text
created_at timestamptz default `now()`
deleted_at timestamptz
}post_like
DBML - post_like
Table post_like {
user_id uuid [ref: > user_account.id]
post_id uuid [ref: > post.id]
created_at timestamptz default `now()`
pk (user_id, post_id)
}comment
DBML - comment
Table comment {
id uuid pk
post_id uuid [ref: > post.id, not null]
author_id uuid [ref: > user_account.id, not null]
parent_id uuid [ref: > comment.id] // threading
body text not null
created_at timestamptz default `now()`
deleted_at timestamptz
}feed_entry
Pre-computed home-feed entry per user.
DBML - feed_entry
Table feed_entry {
user_id uuid [ref: > user_account.id]
post_id uuid [ref: > post.id]
created_at timestamptz not null
pk (user_id, created_at, post_id)
}Relationships
| From | To | Type |
|---|---|---|
| user_account | follow | 1:N |
| user_account | post | 1:N |
| post | post_like | 1:N |
| post | comment | 1:N |
| user_account | feed_entry | 1:N |
Notes from the field
- Cap fan-out-on-write to followers with last-active in the past 30 days to keep write amplification bounded.
- Index post(author_id, created_at DESC) for profile pages.
Frequently asked
How do I handle the celebrity-account fan-out problem?
Set a follower-count threshold. Above it, skip writes to feed_entry and instead read recent posts from those celebrity followees at feed-render time, merging with the materialised entries.
Should comments and likes share a table?
No. They have different access patterns and growth rates. Likes are read in aggregate (counts), comments are read in full. Keep them separate.
More templates
FlowchartSaaS onboarding email flowFlowchartCustomer support runbook flowFlowchartPayment webhook retry and error flowFlowchartGitHub PR review and merge flowFlowchartLogin with magic link flowArchitectureThree-tier SaaS architecture on AWSArchitectureMicroservices architecture on KubernetesArchitectureReal-time data pipeline on GCP
Open this template in Raltey
Edit it on the canvas, ask the AI agent to extend it, then share or export.
Start for free