Cassandra schema for vehicle gps tracking

Question

I have a requirement where I am getting gps data from devices every interval.

So for a given device id, I have the latitude, longitude and timestamp fields.

I need to find the latest latitude longitude for a given vehicle id.

My approach is to have primary key as (device_id, timestamp) and order it by timestamp.

Is choosing device id as partition key a good idea?

In my case, device is a virtual machine. A new one is created for every new job. The device will send its gps location till it has finished its job (typically 3-4 days) and then expire. For the next job, a new device with new device id will be created.

So, I'll have data for a given device id for 3-4 days and then nothing. Also, there are lacks of new devices created every week.

So, the only concern I have is that my number of partitions will keep increasing with time. Is that a good design for cassandra?

Answer 1

Yes, It is a Good design.

The partition key determines which node stores the data. It is responsible for data distribution across the nodes. The more number of partitions, the more data distribution, the more scale able and the more load balanced.

Answer 2

Is choosing device id as partition key a good idea?

Yes, as per cassandra you need to have a partition key such that those key should have very good distribution, So more number of keys you have more number of distribution you get, so that when multiple queries are executed from client each query will execute in different node so that hot spot can be reduced. So having the device_id as partition key is the exact use case for the cassandra

And one more thing that you need to bear in mind is, from the cassandra documentation here,

The maximum number of cells (rows x columns) in a single partition is 2 billion.

Which means from your schema, you cannot have more than 2 billion timestamp for a given device_id. I believe your data wont touch that limit, if that is the case then you need to reconsider your data model. Otherwise your data model is cent percent fine.