Cassandra Counters

A counter is a special column type in Cassandra that stores a 64-bit integer you can only increment or decrement. Counters are designed for tallying values that change frequently — page views, likes, downloads, login counts, and inventory quantities. Because Cassandra is distributed, counter operations work differently from regular column updates and require dedicated counter tables.

The Stadium Turnstile Analogy

A stadium has multiple entrance turnstiles, each clicking up a count as fans enter. At the end of the day, the total attendance is the sum of all turnstile clicks. In a distributed system, multiple nodes may receive +1 increments for the same counter simultaneously. Cassandra's counter mechanism coordinates these distributed increments and produces the correct final total.

Counter Table Rules

Counter tables follow strict rules that make them different from regular tables.

Rule                                   Explanation
──────────────────────────────────────────────────────────────────
All non-PK columns must be COUNTER     A table cannot mix COUNTER
                                       and non-COUNTER data columns
Cannot use INSERT for counters         Use UPDATE instead (always)
Cannot set a counter to a fixed value  You can only add or subtract
Cannot use TTL on counter columns      Counters do not expire
Cannot use conditional updates (IF)    No compare-and-set on counters

Creating a Counter Table

CREATE TABLE page_views (
  page_id  TEXT,
  day      DATE,
  views    COUNTER,
  PRIMARY KEY (page_id, day)
);

CREATE TABLE product_stats (
  product_id UUID PRIMARY KEY,
  views      COUNTER,
  purchases  COUNTER,
  returns    COUNTER
);

Incrementing a Counter

-- Add 1 to page views:
UPDATE page_views SET views = views + 1
WHERE page_id = '/home' AND day = '2024-06-15';

-- Add 5 to a counter (bulk increment):
UPDATE page_views SET views = views + 5
WHERE page_id = '/products' AND day = '2024-06-15';

Decrementing a Counter

-- Subtract from a counter (e.g., inventory):
UPDATE product_inventory SET stock_count = stock_count - 1
WHERE product_id = [uuid];

Reading a Counter

SELECT page_id, day, views FROM page_views
WHERE page_id = '/home' AND day = '2024-06-15';

 page_id | day        | views
─────────────────────────────────
 /home   | 2024-06-15 | 4823

How Distributed Counter Increments Work

In a distributed cluster with RF=3, a counter update goes to all replica nodes that hold the counter's partition. Each replica independently stores the increment. When you read the counter, Cassandra sums the local counts from each replica to produce the final value. This approach avoids a global lock but means you cannot atomically compare and set a counter value.

Increment: page_id='/home' views + 1

  Coordinator → Node A: views + 1
  Coordinator → Node B: views + 1
  Coordinator → Node C: views + 1

Each node stores its share of the increment.

Read: Cassandra combines all replica increments → correct total.

Deleting a Counter

Deleting a counter row resets it to zero (from the application's perspective). However, deleting and then immediately re-incrementing a counter can produce incorrect values due to distributed timing. Best practice is to avoid deleting counter rows and instead design counter tables so rows are never deleted.

DELETE FROM page_views WHERE page_id = '/old-page' AND day = '2024-01-01';

Counter Table Separate from Data Table

Because all non-primary-key columns in a counter table must be COUNTER type, you cannot store descriptive data (like a page name or URL) in the same table. Keep one counter table for the counts and a separate regular table for descriptive information.

-- Regular table: page metadata
CREATE TABLE pages (
  page_id  TEXT PRIMARY KEY,
  title    TEXT,
  url      TEXT,
  created  TIMESTAMP
);

-- Counter table: page metrics
CREATE TABLE page_metrics (
  page_id  TEXT PRIMARY KEY,
  views    COUNTER,
  shares   COUNTER,
  comments COUNTER
);

Practical Counter Use Cases

Use Case                     Counter Table Design
──────────────────────────────────────────────────────────────────
Website page views           PRIMARY KEY (page_id, day)
Product like count           PRIMARY KEY (product_id)
User login attempts          PRIMARY KEY (user_id, day)
Inventory stock level        PRIMARY KEY (product_id, warehouse_id)
API rate limiting            PRIMARY KEY (api_key, minute_bucket)
Download counts              PRIMARY KEY (asset_id)

Counter Accuracy and Idempotency

Cassandra counters are not idempotent. If a client retries a counter update after a network timeout, the counter may be incremented twice. For use cases where exact counts are critical (billing, inventory), consider using lightweight transactions on a regular INT column instead of a counter, accepting the lower performance that comes with compare-and-set operations.

Counter (not idempotent):
  Retry after timeout → views incremented twice ⚠

LWT on regular INT (idempotent with version check):
  UPDATE stock SET quantity = 49
  WHERE product_id = [uuid] IF quantity = 50;
  → Second attempt fails safely (quantity is already 49)

Summary

Cassandra counters provide a distributed, lock-free mechanism for counting events and tallying quantities. Use UPDATE to increment or decrement — never INSERT. Counter tables must have only COUNTER columns in the non-primary-key positions. Keep counter tables separate from metadata tables. Counters are not idempotent, so avoid them in exact-accuracy billing or inventory scenarios where retry safety matters. For everything else — page views, likes, downloads, and engagement metrics — counters are the right tool.