title: Way to handle autoincrement ID with counter on Cassandra?
body: <p>This is not a question about using an autincrement integer for primary key instead of UUIDs on Cassandra, in this case I want to generate an autoincrement effect like PostgreSQL on Cassandra that doesn't need to be necessarily scalable. I'm using UUID as primary key for entries in a table, but I need to generate a shortid like bitly for those entries. So I came up trying to make an application that grabs an index for a specific entry and generates a shortid based on that index and then set the shortid to the entry.</p>
  
  <p>So I'm trying to do something like this on Cassandra:</p>
  
  <pre><code>CREATE TABLE photo (
     id uuid,
     shortid text,
     title text,
     PRIMARY KEY (id)
  );
  
  CREATE TABLE shortid (
    shortid text,
    family text,
    longid uuid,
    index bigint,
    created_at timestamp,
    PRIMARY KEY ((shortid, family))
  ) WITH COMPACT STORAGE;
  
  CREATE TABLE shortid_reverse (
    longid uuid,
    family text,
    shortid text
    PRIMARY KEY ((longid, family))
  ) WITH COMPACT STORAGE;
  
  CREATE TABLE shortid_last_index (
    family text,
    last_index counter,
    last_long_id uuid,
    PRIMARY KEY (family)
  );
  </code></pre>
  
  <p>So in this application that will handle the shortid, when the application initiates It'll get the last index for that family, and then it'll increase the value on the application itself, as this application will run on Nodejs and Nodejs can scale that.</p>
  
  <p>Application.js</p>
  
  <pre><code>var index = lastIndexFromCassandra++ //5
    , hashids = new Hashids("this is my salt")
    , shortid = hashids.encrypt(index); //dDae3KDDj4Q
  </code></pre>
  
  <p>After the application increase the index and generate the shortid, It'll persist on Cassandra:</p>
  
  <pre><code>UPDATE shortid_last_index SET last_index = last_index+1, last_long_id = fabac1f0-7f88-11e3-baa7-0800200c9a66 WHERE family = 'photo';
  
  INSERT INTO shortid (shortid, family, longid, index, created_at) VALUES ('dDae3KDDj4Q', 'photo', fabac1f0-7f88-11e3-baa7-0800200c9a66, 5, NOW());
  
  INSERT INTO shortid_reverse (longid, family, shortid) VALUES (fabac1f0-7f88-11e3-baa7-0800200c9a66, 'photo', 'dDae3KDDj4Q');
  
  UPDATE photo SET shortid = 'dDae3KDDj4Q' WHERE id = fabac1f0-7f88-11e3-baa7-0800200c9a66;
  </code></pre>
  
  <p>So, it really there isn't a better way to do this in Cassandra without creating an application that will just do that? Couldn't I just do something like PostgreSQL on Cassandra:</p>
  
  <pre><code>UPDATE shortid_last_index SET last_index = last_index+1, last_long_id = ? WHERE family = 'photo' RETURNING last_index;
  </code></pre>
  
  <p>In comparison, if the statement above worked it would probably lock the row, but increasing and grabbing the index in the application itself and then safely increase the counter in Cassandra wouldn't lock the row too? How scale would be the application?</p>