How to create auto increment IDs in Cassandra

Question

We know that it is easy to create auto increment IDs in SQL databases, is there a good solution for it in Cassandra? The IDs should be for key or column name.

Answer 1

How about the following, using Cassandra's Lightweight transactions

1 - Create IDs table:

CREATE TABLE ids (   id_name varchar,   next_id int,   PRIMARY KEY (id_name) ) 

2 - Insert every id you'd like to use a global sequence with

For example:

INSERT INTO ids (id_name, next_id) VALUES ('person_id', 1) 

3 - Then, when inserting to a table where you'd like to use an auto-incremented key, do the following:

3.1 - Get the next_id from the ids table:

SELECT next_id FROM ids WHERE id_name = 'person_id' 

Let's say the result is next_id = 1

3.2 - Increment next_id, the following way:

UPDATE ids SET next_id = 2 WHERE id_name = 'person_id' IF next_id = 1 

The result should look like this:

[{[applied]: True}] 

If it was updated successfully, OR

[{[applied]: False, next_id: 2}] 

If someone else has already updated it.

So, if you got True, use id '1' - it is yours. Otherwise, increment next_id (or just use the returned next_id) and repeat the process.

Answer 2

Creating a global sequential sequence of number does not really make any sense in a distributed system. Use UUIDs.

(Because you would have to make all participants agree and accept the evolution of the sequence -- under a naive implementation)

Answer 3

There is no good solution.

1. Create a column with a number, increase the number and save it to all replicas together with a temporary id, read all replicas and check if the temporary id is "yours", if not do it again.. not a great solution and will not scale.

or

1. Build your own id service where you fetch your next id. This service will only be run in a single instance and will be a non scaling scary factor.

As soon as anything goes beyond a single instance the sequencing of id's gets complicated, at least if you want it to scale. That includes relational databases.