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

1. Read existing reaction

2. Decrement old counter

3. Increment new counter

4. Update reaction row

5. 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

1. User reacts

2. Persist reaction

3. Emit event (reaction_added / reaction_changed)

4. 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.