Skip to content

PATTERN Cited by 1 source

Slotted counter pattern

Problem

Per-entity counters (downloads, page views, likes) in a relational database typically map to UPDATE counters SET count = count + 1 WHERE id = ? on a single row. Under bursty parallel writes against the same entity, all concurrent transactions target one InnoDB row, serialise on its lock_mode X locks rec but not gap lock, and experience:

  • Row-level lock contention — transactions wait for the X record lock, surfacing in SHOW ENGINE INNODB STATUS\G as TRX HAS BEEN WAITING N SEC FOR THIS LOCK TO BE GRANTED.
  • Dramatic latency spikes — what should be a sub-ms write becomes N × lock-wait seconds.
  • Deadlock risk — mixed transaction workloads that touch the same row from different angles can form dependency cycles.

This is the canonical hot-row problem for counter-shaped workloads. (Source: sources/2025-02-04-planetscale-the-slotted-counter-pattern.)

Solution

Replace one counter row with N slot rows. Schema:

CREATE TABLE slotted_counters (
  id          INT NOT NULL AUTO_INCREMENT,
  record_type INT NOT NULL,   -- what kind of counter
  record_id   INT NOT NULL,   -- which entity
  slot        INT NOT NULL DEFAULT 0,
  count       INT,
  PRIMARY KEY (id),
  UNIQUE KEY records_and_slots (record_type, record_id, slot)
) ENGINE=InnoDB;

Write path — pick a random slot (RAND() * N, typical N = 100):

INSERT INTO slotted_counters (record_type, record_id, slot, count)
VALUES (123, 456, RAND() * 100, 1)
ON DUPLICATE KEY UPDATE count = count + 1;

Read path — sum across slots:

SELECT SUM(count) AS count
FROM slotted_counters
WHERE record_type = 123 AND record_id = 456;

Why it works

  • Lock surface multiplies by N. Under uniform slot choice, the probability that two concurrent writes to the same (record_type, record_id) collide on the same row is 1/N. With N = 100, 99% of concurrent increments touch different rows and don't contend.
  • Mean lock wait drops roughly linearly with N for the single-entity workload.
  • Reads scan a bounded number of rows — at most N rows per counter; the records_and_slots unique key gives sequential index access.
  • MySQL's ON DUPLICATE KEY UPDATE gives upsert semantics in one statement — first write to a slot inserts, subsequent writes increment.

Canonical deployment: GitHub

The pattern originated at GitHub for per-repository downloads + page-view counters — the diagnostic snippet in the PlanetScale post shows RECORD LOCKS ... index PRIMARY of table github.downloads. PlanetScale's founders carried the pattern from GitHub.

Caveats

  • Reads pay O(N) cost. For counters that are read often and written rarely, the slotted design is worse than the single-row design. The break-even depends on read / write ratio + N.
  • N = 100 is a default, not a derivation. No tuning guidance in the canonical article. Higher N reduces contention but widens the read scan; lower N narrows reads but re-introduces contention.
  • Random slot choice discards locality. Using thread_id % N or a client-sticky hash would also work and avoid per-row RAND() cost. Not discussed in the canonical source.
  • Still a single-DB solution. The pattern scales a single row onto one table on one database; it does not scale across shards, replicas, or regions. When single-DB contention is no longer sufficient, the architectural successor is a dedicated event-log-based counter service like Netflix's Distributed Counter — event log + background rollup + explicit idempotency + multi-region.
  • ON DUPLICATE KEY UPDATE is MySQL syntax. The pattern generalises to Postgres (INSERT ... ON CONFLICT DO UPDATE) and SQLite (INSERT ... ON CONFLICT DO UPDATE) but the exact SQL differs.
  • No per-slot fairness guarantees — under skewed distributions (e.g. a bug in slot selection), a single slot can still become hot. Monitor slot-row-count variance.
  • Roll-up to a single column is a follow-on. For counters displayed frequently, periodically summing slots into a denormalised total on the parent row is a common extension; the canonical article names this as one option and stops.
  • Decrement is possible but not discussed. Slot counts can be negative locally as long as the SUM is what matters; write-path code must tolerate that.

Comparison

Design Write contention Read cost Scale ceiling
Single-row counter High on hot entity O(1) One DB, few concurrent writers
Slotted counter (N = 100) 1/N of single-row O(N) per entity One DB, moderate concurrency
Event-log counter + rollup Append-only (no contention) O(1) rollup cache Distributed, multi-region
Redis INCR Single writer (Redis) O(1) Redis-cluster scale; different durability profile

Seen in

  • sources/2025-02-04-planetscale-the-slotted-counter-pattern — canonical articulation by PlanetScale's Sam Lambert (originally 2022); identifies the InnoDB lock-wait signature, presents the schema + upsert query + read query, attributes the origin to GitHub's github.downloads counter workload.
Last updated · 319 distilled / 1,201 read