incubator-cassandra-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Laing, Michael" <>
Subject Re: Best data structure for tracking most recent updates.
Date Sat, 09 Nov 2013 00:03:57 GMT
Here are a couple ideas:

1. You can rotate tables and truncate to avoid deleting.
2. You can shard your tables (partition key) to mitigate hotspots.
3. You can use a column key to store rows in timeuuid sequence.

create table recent_updates_00 (shard text, uuid timeuuid, message text,
primary key (shard, uuid));
create table recent_updates_01 (shard text, uuid timeuuid, message text,
primary key (shard, uuid)));

You can determine 'shard' randomly within a range, e.g. 1 of 24 shards,
when you write. Sharding spreads the load as each shard is a row.

You determine which table to write to by current datetime, e.g. hour of
day, day of week, etc. and use the modulus based upon, e.g. every 5 hours,
every 3 days, etc. So you are only writing to 1 table at a time. Usually I
derive the datetime from the timeuuid so all is consistent. Within your
modulus range, you can truncate currently unused tables so they are ready
for reuse - truncation is overall much cheaper than deletion.

You can retrieve 'the latest' updates by doing a query like this - the
table is determined by current time, but possibly you will want to append
results from the 'prior' table if you do not satisfy your limit:

select uuid, message from recent_updates_xx where shard in ('00', '01',
...) order by uuid desc limit 10; -- get the latest 10

This is a very efficient query. You can improve efficiency somewhat by
altering the storage order in the table creates.


On Fri, Nov 8, 2013 at 6:02 PM, Jacob Rhoden <> wrote:

> I need to be able to show the most recent changes that have occurred in a
> system, I understand inserting every update into a tracking table and
> deleting old updates may not be great, as I may end up creating millions of
> tombstones. i.e. don't do this:
> create table recent_updates(uuid timeuuid primary key, message text);
> insert into recent_updates(now(), 'the message');
> insert into recent_updates(now(), 'the message');
> ....
> insert into recent_updates(now(), 'the message');
> // delete all but the most recent ten messages.
> So how do people solve it? The following option occurs to me, but I am not
> sure if its the best option:
> create table recent_updates(record int primary key, message text, uuid
> timeuuid);
> insert into recent_updates(1, 'the message', now());
> insert into recent_updates(2, 'the message', now());
> ....
> insert into recent_updates(10, 'the message', now());
> // rotate back to 1
> insert into recent_updates(1, 'the message', now());
> Doing it this way would require a query to find out what number in the
> sequence we are up to.
> Best regards,
> Jacob

View raw message