Section 4: Data Types

Counter Columns in Cassandra

Master distributed counters! Learn how to track views, likes, clicks, and more with atomic increment/decrement operations.

📖 How YouTube Tracks 5 Billion Views Per Day

YouTube needs to track view counts for billions of videos. Every second, thousands of people click "play". How do they count all those views without the database melting down?

❌ The Wrong Way (Regular Columns)

-- Step 1: Read current count
SELECT view_count FROM videos WHERE video_id = 'abc123';
-- Returns: 1,234,567

-- Step 2: Increment in application
new_count = 1,234,567 + 1

-- Step 3: Write back
UPDATE videos SET view_count = 1,234,568 WHERE video_id = 'abc123';

PROBLEMS:
❌ Race condition: Two viewers at the same time → lost update
❌ Read-modify-write: 3 operations instead of 1
❌ Network round trips: Slow!
❌ Under load: Counts become inaccurate

✅ The Cassandra Way (Counter Columns)

-- Single atomic operation!
UPDATE video_stats 
SET view_count = view_count + 1 
WHERE video_id = 'abc123';

BENEFITS:
✅ Atomic: No race conditions
✅ Single operation: No read required
✅ Fast: Immediate write
✅ Distributed: Works across nodes
✅ Accurate: Even under massive load

🎯 The Power of Counters

Counter columns are special data types that can only be incremented or decremented atomically!
Perfect for views, likes, clicks, votes, downloads, and any metric tracking

🔢 What Are Counter Columns?

Simple Definition

Counter columns are special 64-bit integer columns that support atomic increment and decrement operations. You don't set their value directly - you only add to or subtract from them.

Key Characteristics

✅ What Counters CAN Do

Increment: +1, +5, +100
Decrement: -1, -10, -50
Atomic operations: Thread-safe
Distributed: Work across nodes
No read required: Direct update

❌ What Counters CANNOT Do

Set value: No "= 100"
Use INSERT: Must use UPDATE
Mix with regular columns: Counter tables only
Be in primary key: Keys must be regular types
Perfect accuracy: Eventually consistent

Counter Table Rules

Important Constraints

Counter tables have special requirements:

ALL non-primary-key columns must be counters
Primary key columns CANNOT be counters
Cannot mix regular columns with counter columns
Must use UPDATE (INSERT not allowed for counter tables)

-- ✅ VALID: All non-key columns are counters
CREATE TABLE video_stats (
    video_id TEXT PRIMARY KEY,
    view_count COUNTER,
    like_count COUNTER,
    share_count COUNTER
);

-- ❌ INVALID: Mixing regular and counter columns
CREATE TABLE invalid_table (
    video_id TEXT PRIMARY KEY,
    title TEXT,              -- ERROR: Regular column!
    view_count COUNTER       -- ERROR: Can't mix!
);

-- ❌ INVALID: Counter in primary key
CREATE TABLE also_invalid (
    view_count COUNTER PRIMARY KEY  -- ERROR: Counter can't be key!
);

🎬 Live Console: Building a View Counter

Let's build a real-time analytics system step-by-step!

cassandra@cqlsh> -- Step 1: Create counter table

CREATE TABLE page_analytics (

  page_url TEXT PRIMARY KEY,

  total_views COUNTER,

  unique_visitors COUNTER,

  bounce_count COUNTER,

  conversion_count COUNTER

);
                            
✓ Counter table created | All non-key columns are counters
cassandra@cqlsh> -- Step 2: Record first page view

UPDATE page_analytics

SET total_views = total_views + 1,

    unique_visitors = unique_visitors + 1

WHERE page_url = '/products/laptop';
                            
✓ Counters initialized | total_views = 1, unique_visitors = 1
cassandra@cqlsh> -- Step 3: More views from same user

UPDATE page_analytics

SET total_views = total_views + 1

WHERE page_url = '/products/laptop';
                            
✓ Incremented | total_views = 2, unique_visitors = 1 (unchanged)
cassandra@cqlsh> -- Step 4: Track conversions and bounces

UPDATE page_analytics

SET conversion_count = conversion_count + 1

WHERE page_url = '/products/laptop';

UPDATE page_analytics

SET bounce_count = bounce_count + 1

WHERE page_url = '/products/laptop';
                            
✓ Updated | conversion_count = 1, bounce_count = 1
cassandra@cqlsh> -- Step 5: Simulate high traffic (1000 views)

UPDATE page_analytics

SET total_views = total_views + 1000,

    unique_visitors = unique_visitors + 850

WHERE page_url = '/products/laptop';
                            
✓ Bulk increment | total_views = 1002, unique_visitors = 851
cassandra@cqlsh> -- Step 6: Query analytics

SELECT * FROM page_analytics

WHERE page_url = '/products/laptop';
                            
page_url          | total_views | unique_visitors | bounce_count | conversion_count
------------------+-------------+-----------------+--------------+-----------------
/products/laptop  | 1002        | 851             | 1            | 1
cassandra@cqlsh> -- Step 7: Calculate metrics

-- Bounce rate: (1/1002) * 100 = 0.1%

-- Conversion rate: (1/851) * 100 = 0.12%

-- Pages per visit: 1002/851 = 1.18 pages
                            
✓ Real-time analytics ready! No complex aggregations needed.

Why This Is Powerful

No locks: Thousands of concurrent updates work fine
No reads: Direct increment - no SELECT before UPDATE
Fast writes: Single operation, minimal latency
Scales linearly: Add nodes = more capacity
Real-time: Counts available immediately

⚙️ How Counter Columns Work Internally

📊 Distributed Counter Architecture (Animated)

How It Actually Works

Cassandra uses "context-based counters":

Each node tracks local increments: Node stores its own "context" of changes
No coordination during writes: Node accepts increment immediately
Replication is async: Updates propagate in background
Read sums all contexts: Query adds up all node contributions
Merges resolve conflicts: All increments eventually counted

Storage Format:

-- Physical storage (simplified):
Counter: view_count
Node1 context: (node1_id, clock1, +5)
Node2 context: (node2_id, clock2, +3)
Node3 context: (node3_id, clock3, +2)

-- When you query:
Total = sum of all contexts = 5 + 3 + 2 = 10

-- This is why counters are eventually consistent
-- All nodes eventually agree on the total

Eventual Consistency Impact

Counters are NOT immediately consistent:

Write on Node1, immediately read from Node2 → might see old value
Network partitions can delay propagation
Counter repairs needed for accuracy over time
Good for trends, not exact point-in-time values

Example: YouTube views might show 1,234 on one page load and 1,230 on the next (during repairs) - this is normal!

💡 Real-World Counter Examples with Full Data

📺 Netflix: Content Analytics

CREATE TABLE content_stats (
  content_id TEXT PRIMARY KEY,
  total_plays COUNTER,
  completed_plays COUNTER,
  total_watch_time_seconds COUNTER,
  likes COUNTER,
  shares COUNTER,
  downloads COUNTER
);

-- Sample Data for "Stranger Things"
content_id: 'stranger-things-s4'
total_plays: 89,234,567
completed_plays: 67,891,234
total_watch_time_seconds: 4,521,890,123
likes: 12,456,789
shares: 3,456,789
downloads: 8,234,567

-- Usage:
-- User starts watching
UPDATE content_stats 
SET total_plays = total_plays + 1
WHERE content_id = 'stranger-things-s4';

-- User completes episode
UPDATE content_stats
SET completed_plays = completed_plays + 1,
    total_watch_time_seconds = total_watch_time_seconds + 3420
WHERE content_id = 'stranger-things-s4';

-- Calculate completion rate
-- 67,891,234 / 89,234,567 = 76% completion

🛒 Amazon: Product Metrics

CREATE TABLE product_metrics (
  product_id TEXT PRIMARY KEY,
  page_views COUNTER,
  add_to_cart COUNTER,
  purchases COUNTER,
  returns COUNTER,
  reviews COUNTER,
  questions COUNTER
);

-- Sample: iPhone 15 Pro
product_id: 'iphone-15-pro-256gb'
page_views: 2,345,678
add_to_cart: 234,567
purchases: 156,789
returns: 3,456
reviews: 45,678
questions: 12,345

-- Metrics:
-- Conversion: 156,789/2,345,678 = 6.7%
-- Cart→Purchase: 156,789/234,567 = 66.8%
-- Return rate: 3,456/156,789 = 2.2%

-- Track events:
UPDATE product_metrics
SET page_views = page_views + 1
WHERE product_id = 'iphone-15-pro-256gb';

UPDATE product_metrics
SET add_to_cart = add_to_cart + 1
WHERE product_id = 'iphone-15-pro-256gb';

UPDATE product_metrics
SET purchases = purchases + 1
WHERE product_id = 'iphone-15-pro-256gb';

💬 Reddit: Post Engagement

CREATE TABLE post_stats (
  post_id TEXT PRIMARY KEY,
  upvotes COUNTER,
  downvotes COUNTER,
  comment_count COUNTER,
  share_count COUNTER,
  award_count COUNTER,
  report_count COUNTER
);

-- Viral post example
post_id: 'post_abc123'
upvotes: 145,678
downvotes: 3,456
comment_count: 8,234
share_count: 12,345
award_count: 567
report_count: 23

-- Calculate score
score = upvotes - downvotes
      = 145,678 - 3,456
      = 142,222 (to /r/all!)

-- Events:
-- User upvotes
UPDATE post_stats
SET upvotes = upvotes + 1
WHERE post_id = 'post_abc123';

-- User changes to downvote
UPDATE post_stats
SET upvotes = upvotes - 1,
    downvotes = downvotes + 1
WHERE post_id = 'post_abc123';

-- User comments
UPDATE post_stats
SET comment_count = comment_count + 1
WHERE post_id = 'post_abc123';

🎮 Twitch: Stream Analytics

CREATE TABLE stream_stats (
  stream_id TEXT PRIMARY KEY,
  current_viewers COUNTER,
  peak_viewers COUNTER,
  total_views COUNTER,
  chat_messages COUNTER,
  subscriptions COUNTER,
  donations COUNTER,
  follows COUNTER
);

-- Popular stream
stream_id: 'stream_xyz789'
current_viewers: 45,678
peak_viewers: 67,890
total_views: 234,567
chat_messages: 1,234,567
subscriptions: 1,234
donations: 5,678
follows: 12,345

-- Real-time updates:
-- Viewer joins
UPDATE stream_stats
SET current_viewers = current_viewers + 1,
    total_views = total_views + 1
WHERE stream_id = 'stream_xyz789';

-- Viewer leaves
UPDATE stream_stats
SET current_viewers = current_viewers - 1
WHERE stream_id = 'stream_xyz789';

-- Chat message
UPDATE stream_stats
SET chat_messages = chat_messages + 1
WHERE stream_id = 'stream_xyz789';

-- New subscription
UPDATE stream_stats
SET subscriptions = subscriptions + 1
WHERE stream_id = 'stream_xyz789';

📱 Twitter/X: Tweet Metrics

CREATE TABLE tweet_stats (
  tweet_id TEXT PRIMARY KEY,
  impressions COUNTER,
  engagements COUNTER,
  likes COUNTER,
  retweets COUNTER,
  replies COUNTER,
  profile_clicks COUNTER,
  link_clicks COUNTER,
  bookmarks COUNTER
);

-- Viral tweet
tweet_id: 'tweet_viral_001'
impressions: 5,678,901
engagements: 456,789
likes: 234,567
retweets: 89,012
replies: 45,678
profile_clicks: 34,567
link_clicks: 23,456
bookmarks: 12,345

-- Engagement rate
= 456,789 / 5,678,901 = 8.0% (very good!)

-- User interactions:
-- View tweet
UPDATE tweet_stats
SET impressions = impressions + 1
WHERE tweet_id = 'tweet_viral_001';

-- Like tweet
UPDATE tweet_stats
SET engagements = engagements + 1,
    likes = likes + 1
WHERE tweet_id = 'tweet_viral_001';

-- Retweet
UPDATE tweet_stats
SET engagements = engagements + 1,
    retweets = retweets + 1
WHERE tweet_id = 'tweet_viral_001';

🏦 Banking: Transaction Counts

CREATE TABLE account_activity (
  account_id TEXT PRIMARY KEY,
  total_deposits COUNTER,
  total_withdrawals COUNTER,
  total_transfers_in COUNTER,
  total_transfers_out COUNTER,
  failed_transactions COUNTER,
  successful_transactions COUNTER
);

-- Active account
account_id: 'acct_12345'
total_deposits: 156
total_withdrawals: 234
total_transfers_in: 89
total_transfers_out: 145
failed_transactions: 3
successful_transactions: 621

-- Note: Use counters for COUNT only
-- NOT for monetary amounts!
-- (Amounts need exact precision)

-- Track activity counts:
UPDATE account_activity
SET total_deposits = total_deposits + 1,
    successful_transactions = successful_transactions + 1
WHERE account_id = 'acct_12345';

UPDATE account_activity
SET failed_transactions = failed_transactions + 1
WHERE account_id = 'acct_12345';

⚠️ Counter Limitations & Gotchas

Critical Limitations

Cannot set absolute values: No "SET counter = 100"
Cannot use INSERT: Must use UPDATE only
Cannot mix column types: All non-key columns must be counters
Cannot be in primary key: Keys must be regular types
No TTL support: Counters don't expire automatically
Cannot be part of collections: No LIST or MAP

Common Mistakes

❌ Mistake 1: Trying to INSERT

-- ❌ WRONG: INSERT doesn't work
INSERT INTO page_stats (page_id, views)
VALUES ('page1', 0);
-- ERROR: Cannot use INSERT on counter table

-- ✅ CORRECT: Use UPDATE
UPDATE page_stats
SET views = views + 0
WHERE page_id = 'page1';

❌ Mistake 2: Setting Absolute Value

-- ❌ WRONG: Can't set counter to value
UPDATE page_stats
SET views = 1000
WHERE page_id = 'page1';
-- ERROR: Can only increment/decrement

-- ✅ WORKAROUND: Delete and re-increment
DELETE FROM page_stats WHERE page_id = 'page1';
UPDATE page_stats SET views = views + 1000
WHERE page_id = 'page1';

❌ Mistake 3: Mixing Column Types

-- ❌ WRONG: Can't mix regular and counter
CREATE TABLE invalid (
  id TEXT PRIMARY KEY,
  name TEXT,           -- Regular column
  views COUNTER        -- Counter column
);
-- ERROR: Can't mix types!

-- ✅ CORRECT: Separate tables
CREATE TABLE items (
  id TEXT PRIMARY KEY,
  name TEXT
);

CREATE TABLE item_stats (
  id TEXT PRIMARY KEY,
  views COUNTER
);

❌ Mistake 4: Using for Money

-- ❌ WRONG: Counters for account balance
CREATE TABLE accounts (
  account_id TEXT PRIMARY KEY,
  balance COUNTER       -- NO! Not precise!
);

-- ✅ CORRECT: Use DECIMAL for money
CREATE TABLE accounts (
  account_id TEXT PRIMARY KEY,
  balance DECIMAL,
  transaction_count COUNTER  -- OK for count
);

Idempotency Issues

Counter updates are NOT idempotent!

-- If this runs twice due to retry:
UPDATE views SET count = count + 1 WHERE page = 'home';

-- Result: count increased by 2 instead of 1
-- Problem: Same increment applied multiple times

-- Compare to regular UPDATE (idempotent):
UPDATE users SET last_login = '2025-01-15' WHERE id = 123;
-- Running twice gives same result

Solution: Application-level deduplication or accept approximate counts

✅ Counter Best Practices

1️⃣ Use for Aggregates Only

Counters are perfect for totals, not details

-- ✅ GOOD: High-level metrics
total_views COUNTER
page_views COUNTER
unique_visitors COUNTER

-- ❌ BAD: Trying to track details
-- Don't use counters to track
-- which users viewed, when, etc.

2️⃣ Accept Eventual Consistency

Counters prioritize availability over consistency

-- Counter reads may be slightly off
-- During network partitions
-- After node failures
-- During repairs

-- This is OK for:
-- View counts, likes, votes
-- Trending metrics
-- Analytics dashboards

3️⃣ Run Counter Repairs Regularly

Keep counters accurate over time

-- Run nodetool repair periodically
nodetool repair keyspace_name table_name

-- Recommended: Weekly for active counters
-- Fixes inconsistencies
-- Merges contexts across nodes
-- Improves accuracy

4️⃣ Separate Tables for Counters

Keep counters isolated from entity data

-- ✅ GOOD: Separate tables
CREATE TABLE videos (...);
CREATE TABLE video_stats (...);

-- ✅ Join at application level
video = SELECT * FROM videos 
        WHERE id = 'abc';
stats = SELECT * FROM video_stats 
        WHERE id = 'abc';

5️⃣ Use Consistency Level ONE

Fast writes for counters

-- Counter writes with CL=ONE
-- Fastest performance
-- Still eventually consistent
-- Good for high-traffic scenarios

UPDATE video_stats
SET views = views + 1
WHERE video_id = 'abc';
-- Using CONSISTENCY ONE

6️⃣ Batch Counter Updates Wisely

Group updates to same partition

-- ✅ GOOD: Batch to same partition
BEGIN COUNTER BATCH
  UPDATE stats SET views = views + 1
    WHERE id = 'page1';
  UPDATE stats SET clicks = clicks + 1
    WHERE id = 'page1';
APPLY BATCH;

-- ❌ AVOID: Cross-partition batches

💼 Top 10 Counter Interview Questions

What are counter columns in Cassandra and how do they differ from regular columns?

Answer:

Counter columns are special 64-bit integer columns that support atomic increment and decrement operations.

Feature	Regular Column	Counter Column
Set Value	Yes: SET col = 100	No: Only +/-
Operations	Any CRUD	Increment/Decrement only
INSERT	Supported	Not allowed
Idempotent	Yes	No (retries add up)
Consistency	Tunable	Eventually consistent

Use counters for: Views, likes, clicks, votes - any monotonically increasing/decreasing count

Why can't you use INSERT statements with counter tables?

Answer:

Counter tables don't support INSERT because counters work by accumulating increments, not setting values.

Technical Reason:

Counters store "contexts" - each node tracks its own increments
INSERT would imply setting an absolute value
This conflicts with the distributed counter architecture
Would break the merge semantics during repairs

Instead:

-- First update initializes counter to 0 + increment
UPDATE page_stats 
SET views = views + 1 
WHERE page_id = 'home';

-- Counter starts at 0, becomes 1
-- No INSERT needed!

Can you mix counter columns with regular columns in the same table?

Answer: No, you cannot mix counter and regular columns (except in primary key).

-- ❌ INVALID: Mixing types
CREATE TABLE invalid (
  id TEXT PRIMARY KEY,    -- OK: Key can be regular
  name TEXT,              -- ERROR: Regular column
  views COUNTER           -- ERROR: Counter column
);

-- ✅ VALID: All non-key columns are counters
CREATE TABLE valid (
  id TEXT PRIMARY KEY,    -- Regular key: OK
  views COUNTER,          -- Counter: OK
  likes COUNTER,          -- Counter: OK
  shares COUNTER          -- Counter: OK
);

Solution: Use two tables

CREATE TABLE videos (
  id TEXT PRIMARY KEY,
  title TEXT,
  description TEXT
);

CREATE TABLE video_stats (
  id TEXT PRIMARY KEY,
  views COUNTER,
  likes COUNTER
);

-- Join in application code

How does Cassandra implement counters internally?

Answer: Cassandra uses "context-based counters" with per-node tracking.

Architecture:

Each node maintains a context: Tracks its own increments with (node_id, clock, value)
Writes are local: Node records increment immediately without coordination
Replication is async: Context propagates to replicas in background
Reads sum contexts: Query adds up contributions from all nodes
Repair merges contexts: Periodically consolidates to prevent unbounded growth

Example:

-- Physical storage:
Counter: view_count
  Node_A: (nodeA_id, clock1, +5)
  Node_B: (nodeB_id, clock2, +3)  
  Node_C: (nodeC_id, clock3, +7)

-- When you SELECT:
view_count = 5 + 3 + 7 = 15

Why this design?

No coordination needed during writes (high performance)
Works during network partitions (high availability)
Eventually consistent (all nodes converge)

Are counter updates idempotent? Why is this important?

Answer: No, counter updates are NOT idempotent!

What this means:

-- If this UPDATE runs twice (due to retry):
UPDATE stats SET views = views + 1 WHERE id = 'page1';

-- Result: Counter increased by 2, not 1
-- Each execution adds to the count

-- Compare to regular UPDATE (idempotent):
UPDATE users SET status = 'active' WHERE id = 123;
-- Running twice gives same result

Why it matters:

Network retries: Application retry → double count
At-least-once delivery: Kafka/message queues can deliver twice
Client crashes: Unclear if write succeeded → retry → double count

Solutions:

Accept approximation: For analytics, exact count not critical
Application-level deduplication: Track processed message IDs
Idempotent wrappers: Use UUIDs to detect duplicates

Can you reset a counter to zero or set it to a specific value?

Answer: Not directly - you must delete and re-create.

-- ❌ DOESN'T WORK: Can't set counter value
UPDATE stats SET views = 0 WHERE id = 'page1';
UPDATE stats SET views = 1000 WHERE id = 'page1';
-- ERROR: Can only increment/decrement

-- ✅ WORKAROUND: Delete then increment
DELETE FROM stats WHERE id = 'page1';
UPDATE stats SET views = views + 1000 WHERE id = 'page1';

-- Result: Counter now at 1000

Why this limitation?

Setting absolute value conflicts with distributed nature
Would require coordinating across all replicas
Could create inconsistencies during partitions

Best practice:

If you need to reset counters regularly (e.g., daily stats), use time-based partition keys:

CREATE TABLE daily_stats (
  page_id TEXT,
  date DATE,
  views COUNTER,
  PRIMARY KEY (page_id, date)
);

-- Each day starts fresh automatically!

What happens to counters during network partitions?

Answer: Counters remain available and eventually converge after partition heals.

During partition:

Each partition accepts writes independently
Nodes track increments in local contexts
No coordination between sides of partition
All writes succeed (high availability!)

After partition heals:

Contexts from both sides are merged
All increments from both sides are counted
Final value = sum of all increments
No data loss!

Example scenario:

Initial: counter = 100

Network partition occurs:
  Side A: +10 increments → sees 110
  Side B: +15 increments → sees 115

During partition:
  - Queries to Side A return 110
  - Queries to Side B return 115
  - Both are "correct" from their perspective

After heal:
  - Contexts merged
  - Final value: 100 + 10 + 15 = 125
  - Both sides now agree: 125

Key insight: Cassandra prioritizes availability (AP in CAP) for counters.

When should you NOT use counter columns?

Answer: Don't use counters when you need exact precision or detailed tracking.

❌ Don't use counters for:

Financial amounts: Account balances, transaction totals (need exact precision)
Inventory counts: When exact stock levels are critical
Legal/compliance data: When audit trail is required
Detailed tracking: When you need to know WHO incremented, WHEN, WHY
Mutable counts: When count might need to be recalculated or corrected

Examples:

-- ❌ BAD: Money (needs precision)
CREATE TABLE accounts (
  account_id TEXT PRIMARY KEY,
  balance COUNTER  -- NO! Use DECIMAL
);

-- ✅ GOOD: Transaction count (approximate OK)
CREATE TABLE account_activity (
  account_id TEXT PRIMARY KEY,
  transaction_count COUNTER  -- OK for analytics
);

-- ❌ BAD: Inventory (needs exact count)
CREATE TABLE inventory (
  product_id TEXT PRIMARY KEY,
  in_stock COUNTER  -- NO! Use INT
);

-- ✅ GOOD: Page views (approximate OK)
CREATE TABLE page_stats (
  page_id TEXT PRIMARY KEY,
  views COUNTER  -- Perfect use case
);

Rule of thumb: If "close enough" is good enough → use counters. If you need exact values → use regular columns.

How do counter repairs work and why are they needed?

Answer: Counter repairs merge contexts and improve accuracy over time.

Why repairs are needed:

Context accumulation: Each increment adds a new context entry
Storage growth: Unbounded contexts take more space
Query performance: More contexts = slower reads (must sum all)
Accuracy drift: Temporary inconsistencies during failures

What repair does:

Merkle tree comparison: Identifies differences between replicas
Context merging: Combines contexts from all replicas
Context compaction: Reduces multiple contexts to fewer entries
Synchronization: Ensures all replicas have same contexts

How to run repairs:

-- Repair specific table
nodetool repair keyspace_name table_name

-- Repair entire keyspace
nodetool repair keyspace_name

-- Recommended schedule:
-- Daily: For critical high-traffic counters
-- Weekly: For normal counters
-- Monthly: For low-traffic counters

Performance impact:

Repair is resource-intensive (CPU, network, disk I/O)
Run during off-peak hours
Use `-pr` flag to repair only primary ranges
Monitor cluster performance during repair

What's the difference between using counters vs aggregating from detail tables?

Answer: Counters provide real-time aggregates without querying detail tables.

Aspect	Counter Approach	Aggregation Approach
Read Performance	O(1) - single row	O(n) - scan all rows
Write Cost	One UPDATE per event	INSERT detail row
Storage	Minimal (just count)	High (all details)
Accuracy	Eventually consistent	Strongly consistent
Detail Access	Not possible	Full details available
Recomputation	Difficult	Easy - just re-aggregate

Example comparison:

-- APPROACH 1: Counters (fast aggregates)
CREATE TABLE video_stats (
  video_id TEXT PRIMARY KEY,
  views COUNTER
);

UPDATE video_stats SET views = views + 1 
WHERE video_id = 'abc';

SELECT views FROM video_stats 
WHERE video_id = 'abc';
-- Returns: 1,234,567 instantly!

-- APPROACH 2: Detail table (flexible but slower)
CREATE TABLE video_views (
  video_id TEXT,
  user_id TEXT,
  viewed_at TIMESTAMP,
  PRIMARY KEY (video_id, viewed_at, user_id)
);

-- Get count requires aggregation
SELECT COUNT(*) FROM video_views 
WHERE video_id = 'abc';
-- Scans millions of rows → slow!
-- But you can see: WHO viewed, WHEN, from WHERE

Best practice: Use both!

Counters for dashboard/metrics (fast)
Detail tables for analysis (flexible)
Periodically reconcile for accuracy

Responsive Ad