Scylla Counters

Counters are useful for any application where you need to increment a count, such as keeping track of:

  • the number of web page views on a website;

  • the number of apps/updates downloaded;

  • the number of clicks on an ad or pixel;

  • the number of points earned in a game;

A counter is a special data type (column) that only allows its value to be incremented, decremented, read or deleted. As a type, counters are a 64-bit signed integer. Updates to counters are atomic, making them perfect for counting.

A table with a counter column is created empty and gets populated and initialized by updates, rather than inserts. A new record with a counter set to null is created on the first update statement with particular PK value and then immediately incremented by a given number (treating null as a 0). For example:

cqlsh:mykeyspace> CREATE TABLE cf (pk int PRIMARY KEY, my_counter counter);
cqlsh:mykeyspace> SELECT * FROM cf;
pk | my_counter
----+------------
cqlsh:mykeyspace> UPDATE cf SET my_counter = my_counter + 0 WHERE pk = 20;
cqlsh:cdc_test> SELECT * FROM cf;
pk | my_counter
----+------------
20 | 0
cqlsh:mykeyspace> UPDATE cf SET my_counter = my_counter + 6 WHERE pk = 0;
cqlsh:mykeyspace> SELECT * FROM cf;
cqlsh:cdc_test> SELECT * FROM cf;
pk | my_counter
---+------------
 0 | 6
20 | 0

However, counters have limitations not present in other column types:

  • The only other columns in a table with a counter column can be columns of the primary key (which cannot be updated). No other kinds of column can be included. This limitation safeguards correct handling of counter and non-counter updates by not allowing them in the same operation.

  • All non-counters columns in the table must be part of the primary key.

  • Counter columns, on the other hand, may not be part of the primary key.

  • Counters may not be indexed.

  • Counters may not be part of a materialized view.

  • One cannot use TIMESTAMP or set a TTL (time to live) when updating a counter.

  • Once deleted, counter column values cannot be used again.

  • Counters cannot be set to a specific value, other than when incrementing from 0 using the UPDATE command at initialization.

  • Updates are not idempotent. In the case of a write failure, the client cannot safely retry the request.

Example:

  1. Create a table. The table can only contain the primary key and the counter column(s):

    cqlsh:mykeyspace> CREATE TABLE cf (pk int PRIMARY KEY, my_counter counter);
    
  2. Increment the count:

    cqlsh:mykeyspace> UPDATE cf SET my_counter = my_counter + 3 WHERE pk = 0;
    cqlsh:mykeyspace> SELECT * FROM cf;
    
    pk | my_counter
    ---+-------------
    0  | 3
    
  3. Decrement the count:

    cqlsh:mykeyspace> UPDATE cf SET my_counter = my_counter - 1 WHERE pk = 0;
    cqlsh:mykeyspace> SELECT * FROM cf;
    
    pk | my_counter
    ---+-----------
    0  | 2
    
  4. Delete the row:

    cqlsh:mykeyspace> delete from cf where pk = 0;
    cqlsh:mykeyspace> select * from cf;
    
    pk | my_counter
    ---+-----------
    

Remember that once deleted, counter column values cannot be used again. Read our blog on counters, or see the data type description.