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.