2

I'm receiving a lot of a data entries from different (IOT) devices. I need to temporarily and persistently store this data until it is polled by the matching clients, after which it will be deleted.

The data looks something like the following:

id=1 device=1 key1=value1 key2=value2
id=2 device=1 ...
id=3 device=2 ...
id=4 device=2 ...
id=5 device=1 ...
id=6 device=2 ...

Now a client can request the oldest X records for one or more devices. So for example if a client requests 2 records for the device 1, it'll receive the records with the id 1 and 2 and they'll be removed from the list.

Right now I'm doing this with a MySQL database table where the records are inserted, selected when fetched by a client and deleted afterwards. However as databases aren't really meant to be used as queues, this has a few major disadvantages, namely:

  • growing table sizes: Most database engines don't actually free space when stuff is deleted
  • poor indexes: Index selection works very poorly on small, always changing tables, causing the database to not use the best index in many cases
  • no good monitoring: There is no built-in overview of queue (table) sizes, you either have to track every insert/delete or do a count() over them periodically

I've also considered using a message queue like RabbitMQ, however they generally work with a first in, first out principle and are not searchable, which means I can't select the latest entries per device.

Which type of database / data structure would you suggest for implementing such a queue?

2
  • 4
    The question is already flagged for closing as it's asking for tool recommendations (which can probably be fixed by re-wording) but it is also unclear what your actual issues with a database are. "Poor indexes" and "no good monitoring" don't read like factual problems but highly opinionated. It is also unclear what your real search criteria will be, and if they're actually more than simple fifo access then a database is likely the best fit. May 26 at 16:09
  • @Hans-MartinMosner I've added more detail regarding the disadvantages of databases. The search criteria is basically "poll the last N where the device id is X".
    – Florian
    May 27 at 6:21
1

I would use a message queue, but pre-apply the search.

So in your example you could have queue per device, this allows the worker to pull only from selected devices.

I found this article on the problem

https://derickbailey.com/2015/07/22/airport-baggage-claims-selective-consumers-and-rabbitmq-anti-patterns/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.