Distributed Counter

How YouTube counts millions of views per second without locking the database.

The idea

If you have a viral video, thousands of users might hit "Like" at the exact same millisecond. If your database runs UPDATE videos SET likes = likes + 1, it has to lock the row. Those thousands of queries will form a massive queue waiting for the lock, maxing out your connection pool and taking down the site. The solution is a Distributed Counter (or Sharded Counter). Instead of one row, you create N rows (shards). When a user likes the video, you pick a random shard and increment it. To get the total, you just sum the shards.

Step 1: A viral video gets 3 Likes at the exact same time.

How it works (Shard Summing)

By splitting the counter into 10 shards, you divide the lock contention by 10. A random number generator decides which shard to write to. The database handles 10 concurrent writes perfectly fine. When someone loads the page and needs to see the view count, a background job or quick query sums them up.

# The Schema
# table: video_likes (video_id, shard_id, count)

# To increment (Fast, concurrent)
shard = random.randint(1, 10)
execute("""
  UPDATE video_likes 
  SET count = count + 1 
  WHERE video_id = ? AND shard_id = ?
""", (vid, shard))

# To read the total (Fast, lock-free)
execute("""
  SELECT SUM(count) FROM video_likes 
  WHERE video_id = ?
""", (vid,))

Cost

Reads become slightly more expensive (you have to sum N rows instead of reading 1). You also have eventual consistency: if you cache the total count in Redis to save the database, users might see the Like count freeze for 5 seconds before jumping up. This is usually perfectly acceptable for metrics.

Watch out for