Sharded Counter

Concepts Covered

• Hot counter rows
• Partial counters
• Shard selection
• Compacted totals
• Counter drift
• Read/write tradeoffs
• Counter repair
• Eventual consistency

1. Intent

The Sharded Counter pattern spreads updates to one logical counter across multiple physical records.

It is used when a single counter row becomes a write bottleneck.

The pattern trades one simple hot row for many smaller counter shards plus a read and repair strategy.

2. The Problem Without This Pattern

A simple like counter might look like:

post_id -> like_count

For a viral post, every like and unlike updates the same row:

UPDATE post_counts
SET like_count = like_count + 1
WHERE post_id = 'post_42';

That can create lock contention, high write latency, replication pressure, and database load even if the rest of the system is healthy.

The total cluster may have spare capacity, but the hot counter concentrates work into one physical place.

3. How The Pattern Works

Instead of one counter row:

post_id -> count

store multiple partial rows:

post_id, shard_id -> partial_count

Example:

post_42, shard_0 -> 2300
post_42, shard_1 -> 2601
post_42, shard_2 -> 2550
post_42, shard_3 -> 2550

Each write chooses a shard, commonly by hashing a stable value such as user_id, event_id, or request ID.

Reads can either:

• sum all shards at request time
• read a periodically compacted total
• use cached totals with bounded staleness

The source of truth should usually be the underlying relationship or event log, not the counter itself.

4. When To Use It

Use sharded counters when:

• many writes target the same logical counter
• a single row or partition becomes hot
• approximate or slightly stale reads are acceptable
• the counter is a projection, not the only source of truth
• hot objects are common enough to justify extra complexity
• repair from source truth is possible

Good examples:

• likes on viral posts
• views on popular videos
• downloads of a hot file
• reactions in a large chat
• high-volume page counters

5. When Not To Use It

Avoid this pattern when:

• write volume is low
• exact instant reads are required
• the counter is financial or safety-critical
• operational simplicity matters more than write distribution
• there is no source of truth for repair
• summing shards would be too expensive for the read path

If a normal single-row counter works and the row is not hot, sharding it may only add failure modes.

6. Data And Operational Model

Example table:

CREATE TABLE post_like_count_shards (
  post_id BIGINT NOT NULL,
  shard_id INT NOT NULL,
  count BIGINT NOT NULL,
  updated_at TIMESTAMP NOT NULL,
  PRIMARY KEY (post_id, shard_id)
);

A compacted total may be stored separately:

post_like_counts
- post_id
- compacted_count
- updated_at

Operators should monitor:

• write distribution across shards
• hot shard latency
• compacted total freshness
• counter drift
• repair job results
• duplicate event rate
• shard-count migration progress

7. Failure Modes

• Poor shard selection still creates hotspots.
• Duplicate events inflate shard counts.
• Missed events undercount.
• Compacted totals lag behind shards.
• Increasing shard count requires migration logic.
• Reads become expensive because too many shards must be summed.
• Repair jobs overwrite fresh data with stale recomputation.

8. Tradeoffs

Benefit	Cost
Higher write throughput	More complex reads
Reduces hot row pressure	Needs compaction or summing
Supports viral objects	Eventual consistency is common
Works well for projections	Needs reconciliation
Spreads lock contention	Requires shard selection strategy

Sharded counters are most useful when the count is a read-optimized projection that can tolerate bounded lag and be repaired from truth.

9. Related Systems And Concepts

• Instagram Likes System
• Distributed Counters
• Hot Key Mitigation
• Eventual Consistency
• Projection Drift
• Reconciliation Job