Step 2 of 16 · 1 min

Why one row is a bottleneck

Lesson 3.3 put a like_count column on posts, updated in the same transaction as each like:

BEGIN;
INSERT INTO likes (user_id, post_id) VALUES (:user, :post);
UPDATE posts SET like_count = like_count + 1 WHERE id = :post;
COMMIT;

And lesson 1.4 explained what UPDATE ... SET like_count = like_count + 1 does to stay correct: it locks the row until the transaction commits. That's what stopped likes being lost. It also means only one transaction at a time can be updating that row. Every other like for the same post waits in line for the lock.

How long is each turn? The lock is held from the update until the commit finishes, and the commit has to wait for the write-ahead log to reach disk (lesson 1.4), plus a network round trip back to the app. Call it one or two milliseconds. So no matter how big the database server is, one row can take about 500 to 1,000 updates a second. At 40,000 a second, the line grows by tens of thousands every second, and everything in it times out.

The same thing happens anywhere one item gets a huge share of the writes: a single counter, a single stock level for a product everyone wants, a single seat-availability row for a popular concert. It's called a hot row or hot key, and adding hardware doesn't help, because the limit isn't the machine. It's the rule that one row changes one transaction at a time.

Step 3 of 16 · 1 min

First, don't put the counter in the way

The quickest improvement didn't involve counters at all. The like itself is a row in likes, keyed by (user_id, post_id). Inserting different rows doesn't contend: 40,000 people liking the same post insert 40,000 different rows, and the database handles that happily. Only the UPDATE on posts makes everyone queue.

So the first change was to take the counter out of the like transaction:

INSERT INTO likes (user_id, post_id) VALUES (:user, :post)
ON CONFLICT DO NOTHING;

That's all the request does now. The ON CONFLICT DO NOTHING also makes likes naturally idempotent (lesson 4.1): a retried like, or a double tap, inserts nothing the second time. Unlikes delete the row.

The counter then has to be kept up to date some other way. The rest of this lesson is the menu of ways to do that, and they trade exactness and freshness for throughput.

Step 4 of 16 · 1 min

Option 1: sharded counters

Instead of one counter row per post, keep several, and spread the increments across them:

CREATE TABLE post_like_counts (
    post_id  bigint,
    shard    smallint,
    count    bigint NOT NULL DEFAULT 0,
    PRIMARY KEY (post_id, shard)
);

-- each like picks a random shard, 0 to 15:
UPDATE post_like_counts SET count = count + 1
WHERE post_id = :post AND shard = :random_shard;

-- reading the total adds them up:
SELECT sum(count) FROM post_like_counts WHERE post_id = :post;

With 16 shards, 16 transactions can update the same post's count at once, each on a different row, so throughput goes up about 16 times. Reads get slightly more expensive (summing 16 rows instead of reading one), which is why you'd cache the total for a few seconds (lesson 2.4).

The count is still exact, and still updated in real time. The cost is a little complexity, and choosing the number of shards. Most posts never need more than one, so many systems start each post with one shard and add more only when a post gets hot. Google App Engine's documentation popularized this pattern years ago as "sharding counters."

Step 5 of 16 · 1 min

Option 2: batch the increments

Most of those 40,000 updates a second are the same thing: "add one." So add them up first, and write the total.

Each app server (or a dedicated counter worker) keeps a small in-memory tally: post 9001, +312 since the last flush. Every second, it writes all its tallies at once:

UPDATE posts SET like_count = like_count + 312 WHERE id = 9001;

Forty app servers each flushing once a second means about 40 updates a second on the hot row, instead of 40,000. Easily within its limit.

The trade-offs:

• The count lags by up to the flush interval, a second or so. For likes, nobody can tell.
• A crash loses the unflushed tally. If an app server dies, the likes it hadn't flushed are missing from the count, though the like rows themselves are safe in likes. A nightly recount (lesson 3.3's reconciliation) fixes the difference.

A tidier version uses lesson 4.1's log instead of app server memory: each like publishes a like_added event (through an outbox, lesson 4.2), and a counter consumer reads the events, adds up the increments for each post over a second, and writes the totals. Nothing is lost on a crash, because the events are still in the log. This is what Chirp built.

Step 6 of 16 · 1 min

Option 3: count in Redis

Redis can increment a number atomically:

INCR likes:post:9001

Redis is fast, and it does everything for one key on one thread with no locks or disk waits in the way, so a single key can take far more increments a second than a database row. Many apps keep live counters in Redis and write them to the database periodically.

Two things to watch:

• Durability. Lesson 2.4: Redis used as a cache can lose data. If counts live in Redis, configure it to persist (or treat Redis as the fast copy and the database, or the log, as the source of truth).
• It's still one key on one machine. At a big enough event, even Redis has a limit per key, and the network card of the server holding it becomes the bottleneck. Sharding the key (likes:post:9001:0 to :15) works here too.

Step 7 of 16 · 1 min

Option 4: don't count exactly

It's worth asking what the number is for. A post showing "40.2K likes" doesn't need to know whether the true count is 40,213 or 40,219. Lots of large apps show rounded counts on popular items for exactly this reason: rounding makes delays and approximations invisible.

So decide, per counter, how exact it needs to be:

Counting unique things

Some counts are about distinct things: how many different people viewed a post, how many unique visitors today. Counting exactly means remembering every ID you've seen (millions of them per popular post) just to answer one number.

There's a clever alternative called HyperLogLog. It estimates how many distinct items it has seen, using a tiny fixed amount of memory, with a small, predictable error. Redis has it built in: PFADD views:post:9001 <user_id> to record a view, and PFCOUNT views:post:9001 to get the estimate. Each key uses at most about 12 KB, and the typical error is under 1%, whether the true number is a thousand or a billion. For a "seen by 2.1M people" label, that's exactly right. The idea behind it, roughly: in random-looking hashes, long runs of leading zeros are rare, and the longest run you've seen hints at how many different hashes you've looked at.

Step 8 of 16 · 1 min

Hot keys when reading

The same night had a read-side hot spot too. The cricket board's posts were being read hundreds of thousands of times a second, by every feed that included them (lesson 4.4 pulled celebrity posts at read time).

Everything in Part 2 that spreads load assumes the load is spread across many keys. A single hot key defeats it:

• Caches. One cache key lives on one cache node (lesson 2.4). That node's network was saturated while the others were idle.
• Database replicas help (lesson 2.5), but each one still serves its share of the hot row.
• Partitions in a log (lesson 4.1). Every like event for one post went to the same Kafka partition, because the post ID was the key. One consumer handled that partition, and it fell behind while the others had nothing to do.

The fixes are versions of the same few ideas:

• Cache it closer. A tiny in-memory cache on each app server, holding the hottest few hundred keys for a second or two (lesson 2.4 suggested this). Each server asks the shared cache once a second instead of thousands of times. A second of staleness on a post's like count is invisible.
• Make copies. Store the hot key under several names (post:9001#0 to #7) on different cache nodes, and have readers pick one at random. Writes update all copies.
• Detect it automatically. Some systems track which keys are hot right now and switch on these protections only for them, since most keys never need them.
• Choose partition keys with hot spots in mind. For like events, Chirp changed the partition key from the post ID to the user ID of the person liking. That spreads one viral post's likes across every partition. The counter consumer then sums partial counts per post from all partitions, which batching already did anyway. The trade-off is that likes for one post are no longer in a single order, which didn't matter for counting.

Step 9 of 16 · 1 min

Write-heavy events, in general

What happened during that final over is a general shape: a huge number of writes aimed at one thing, in a short burst. It shows up as likes on a viral post, votes in a live poll, bids in the last seconds of an auction, sign-ups the moment tickets go on sale, or a flash sale on one product.

The playbook is the same each time:

1. Separate the record from the total. Store each action as its own row or event (no contention), and compute the total separately.
2. Decide how exact and how fresh the total must be. Most totals can lag by a second and be shown rounded.
3. Spread the total across shards, or batch the increments, or both.
4. Protect the read side with short-lived local caches and copies of hot keys.
5. Choose partition keys that don't concentrate the hot thing on one machine.

Step 10 of 16 · 1 min

From the real world: the video that outgrew its counter

On 1 December 2014, YouTube posted that PSY's "Gangnam Style" had been viewed more than 2,147,483,647 times. That's the largest number a signed 32-bit integer can hold (2³¹ − 1). YouTube joked that it had never expected a video to be watched that many times, "but that was before we met PSY."

The punchline was that YouTube had already moved the counter to a 64-bit integer, which holds up to about 9.2 quintillion, so nothing broke. But the same overflow has broken plenty of systems that hadn't prepared. Lesson 4.3's warning about JavaScript and large IDs is a cousin of it.

What to take for Chirp:

• Choose counter types for the extreme, not the typical. A like count, a view count, a sequence, a byte counter: use 64 bits unless there's a strong reason not to. The space saved by 32 bits is almost never worth it.
• Test the boundary. Set a counter in a test environment to just below its limit and increment it. Most overflow bugs are found this way in minutes.
• Overflow can be silent. In many languages, a 32-bit counter doesn't throw an error; it wraps to −2,147,483,648. A view count that goes negative is embarrassing. An ID or a balance that wraps is much worse.

Step 11 of 16 · 1 min

Going deeper: counting in several places at once

In Part 5, Chirp runs in several regions, and likes happen everywhere. You'd like each region to accept a like locally, without a round trip to one central counter. How do the regions agree on the total?

The naive approach replicates "the like count" as a value. Mumbai's copy goes from 100 to 105, Frankfurt's from 100 to 103, and replication uses last-write-wins (lesson 5.3): whichever write arrives last overwrites the other. You end up with 103 or 105, and the other region's likes are gone.

A G-counter (grow-only counter) is the classic fix. It's one of the simplest CRDTs, conflict-free replicated data types: data structures designed so that replicas can be updated independently and always merge to the same result.

• Each region has its own slot in a small vector: [Mumbai, Frankfurt, Virginia].
• A region only ever increments its own slot.
• To merge two copies, take the maximum of each slot.
• The count is the sum of the slots.

Mumbai has [105, 0, 0], Frankfurt has [100, 3, 0]. Merge: [105, 3, 0], so 108. It doesn't matter in what order merges happen, or how many times: the result is the same. To support unlikes, keep a second G-counter for removals and subtract (a PN-counter).

Redis Enterprise's active-active databases, and Riak's counters, are built on these ideas. For Chirp, the useful insight even without a CRDT library: store increments, or per-source totals, never just the total. That's also what makes the sharded counters in this lesson safe.

Step 12 of 16 · 1 min

What Chirp changed

• Liking a post now just inserts a row (ON CONFLICT DO NOTHING). Typical time: 3 ms, whatever the post.
• Like counts are updated by a consumer of like_added events, which batches increments per post every second.
• Posts that cross 1,000 likes a minute automatically get a sharded counter in Redis for live display, written back to Postgres by the batcher.
• Counts above 10,000 are shown rounded ("40.2K").
• "Seen by" counts use HyperLogLog.
• App servers keep a 2-second local cache of the 500 hottest posts.
• Like events are partitioned by the liker's user ID, not the post ID.
• The nightly reconciliation from lesson 3.3 now covers like counts too.

The next big match, the cricket board's posts peaked at 55,000 likes a second. The like button took 3 milliseconds throughout.

Step 13 of 16 · 1 min

In the simulation

Stage 3's viral post sends a huge like burst at one row. Your choice for decision D6 (one row per like count, async counting, or sharded counters) decides whether the like button keeps working. Then watch the read side: if your cache has no protection for hot keys, one cache node lights up red while the others idle.

Open the simulation →

Step 14 of 16 · 1 min

If you're here for an interview

Counters come up in almost every social, e-commerce or live-event design ("how do you show the like count?"). Strong answers:

• Spot the hot row, and explain why it limits throughput no matter the hardware: one transaction at a time per row.
• Separate the action from the count: a likes table with a unique (user, post) key, plus a count maintained separately.
• Offer a menu: sharded counters (exact, more writes), batching through a queue (lagged, few writes), Redis (fast, mind durability), approximate counts where acceptable.
• Mention HyperLogLog for unique counts.
• Don't forget reads: hot cache keys and hot partitions.

Step 15 of 16 · 2 min

Try these yourself

Step 16 of 16 · 1 min

Further reading

• Google App Engine documentation, "Sharding counters." Short and clear, and the source of the pattern's name.
• Redis documentation on INCR, and on HyperLogLog (PFADD, PFCOUNT).
• Philippe Flajolet et al., "HyperLogLog: the analysis of a near-optimal cardinality estimation algorithm," 2007.
• Martin Kleppmann, Designing Data-Intensive Applications, Chapter 6, on skewed workloads and relieving hot spots.
• YouTube's Google+ post of 1 December 2014 on "Gangnam Style" and 64-bit view counts (widely reported, e.g. by the BBC, "Gangnam Style music video 'broke' YouTube view limit").
• Marc Shapiro et al., "A comprehensive study of Convergent and Commutative Replicated Data Types," INRIA, 2011.