Modern platforms (Reddit, LinkedIn, YouTube) support multiple reaction types per entity.

Core Requirements

✅ One user → one reaction per entity
✅ Reaction can change over time
✅ Extremely write-heavy
✅ Reads need fast aggregated counts
✅ Prevent duplicate reactions

This is not a simple “likes table” problem.

Solutions

❌ 1. Naive Design (Why It Fails)

Code given in github repo.

Problems

❌ No uniqueness → duplicate reactions
❌ COUNT(*) on every read
❌ Full table scans under load
❌ Read amplification explodes

Works at 10 users. Dies at 10 million.

✅ 2. Optimized Data Model (Correct Foundation)

We separate concerns:

Truth (who reacted)
Speed (aggregated counts)

2.1 Core Reaction Table (Uniqueness + Truth)

Code given in github repo.

Why This Works

✅ Enforces 1 reaction per user per entity
✅ Reaction change = UPDATE, not new row
✅ Write cost is predictable

2.2 Pre-Aggregated Counters (Read Optimization)

Code given in github repo.

Why This Exists

Reads are O(1)
No GROUP BY
No scanning reaction table

🔄 3. Handling Reaction Updates (Critical Part)

Scenario

User changes reaction:

LIKE → LOVE

Correct Flow

Read existing reaction
Decrement old counter
Increment new counter
Update reaction row
All inside one transaction

Why Atomicity Matters

Prevents counter drift
Guarantees consistency

🔁 Alternative Solutions

1️⃣ Event-Driven Counters (Async Aggregation)

Idea

Writes go only to the Reaction table
Counters updated asynchronously via events

Flow

User reacts
Persist reaction
Emit event (reaction_added / reaction_changed)
Consumer updates counters

Why Use This

✅ Absorbs write spikes
✅ Decouples write path
✅ Great for huge scale

Tradeoffs

❌ Eventual consistency
❌ Needs reconciliation

2️⃣ Log-Structured / Append-Only Reactions

Idea

Never update reactions
Append every change
Compute latest reaction via compaction

(user=1, post=7, LIKE)
(user=1, post=7, LOVE)

Why Use This

✅ Perfect audit trail
✅ High write throughput
✅ Works well with Kafka

Tradeoffs

❌ Read complexity
❌ Needs background jobs

3️⃣ Redis-Only Atomic Counters + Sets

Idea

Use Redis as primary reaction store.

SET post:7:users
HINCRBY post:7:counts LIKE 1

Flow

SADD enforces uniqueness
HINCRBY updates counters atomically

Why Use This

✅ Extremely fast
✅ Simple logic
✅ Atomic operations

Tradeoffs

❌ Memory expensive
❌ Persistence risk
❌ Not ideal as source of truth

4️⃣ Bitmaps for Binary Reactions (Like / Unlike)

Idea

If reaction is binary, use bitmap

BITSET post:7 12345 1

Why Use This

✅ Ultra memory efficient
✅ Fast counts

Tradeoffs

❌ Not for multiple reaction types
❌ User ID must be dense

5️⃣ Fanout-on-Write (Precompute Everything)

Idea

Write path updates every read model
Reads become trivial

Why Use This

✅ Zero read computation
✅ Best for read-heavy apps

Tradeoffs

❌ Massive write amplification
❌ Complex rollback

6️⃣ Materialized Views (DB-Native)

Idea

Let DB maintain aggregates.

CREATE MATERIALIZED VIEW reaction_counts AS
SELECT entity_id, reaction_type, COUNT(*)
FROM Reaction
GROUP BY entity_id, reaction_type;

Why Use This

✅ Simple mental model
✅ DB-managed

Tradeoffs

❌ Refresh cost
❌ Limited scalability

7️⃣ CRDT-Based Counters (Distributed Systems)

Idea

Use conflict-free replicated data types

Each node increments locally
Eventually converge

Code given in github repo.

Why Use This

✅ Multi-region
✅ Offline writes

Tradeoffs

❌ Approximate
❌ Complex reasoning

🧠 How to Choose (Decision Table)

Traffic Pattern	Best Solution
Moderate scale	DB + Counter table
Massive writes	Event-driven
Ultra-low latency	Redis
Binary reactions	Bitmap
Audit required	Append-only log
Multi-region	CRDT
Read-heavy feeds	Fanout-on-write

All code given here - github repo.

Designing a Like / Reaction System (Facebook / LinkedIn) - Database Modelling