Storage solution to run IoT analytics I have several hundred IoT devices pushing data to my server every minute. The data is structured and it has following columns:


Site ID (String)
Event Start time (Date Time)
Event End Time (Date Time)
Data (String)


Data once created, does not change or update. The primary purpose of this database is going to run Analytics.

I started working with MongoDB, but its performance degraded after a few hundred thousand of rows. Someone suggested that as my data is structured, I do not need NoSQL. I moved to RDBMS (Postgres). It performed better than the earlier solutions but it is getting to its knees after a few million rows. Now someone else suggested that as I am not planning to use any features of a transactional database, an RDBMS is not a suitable solution. I probably need an Analytical Database.

I researched a bit for a better solution and found that Elastic Search, Vertica, HBase etc. can be a solution to my problem. There are probably a dozen of other solutions as well and I have zero experience with any of these.

My question is, how should I proceed? The learn, experiment, repeat loop seems to be too long for me.
Someone suggested that as my data is structured, I do not need NoSQL. I moved to RDBMS (Postgres). It performed better than the earlier solutions but it is getting to its knees after a few million rows.


How is getting to it's knees? You can easily index a few billion rows, with a BRIN index. Merge your start and end times into a tsrange, and cluster on the pkid and range.

CREATE TABLE foo (
  id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  startts tsrange
);
CREATE INDEX ON foo USING brin (id,startts);


I would consider frequently clustering on id until your workload won't permit that, but at a few million rows you've got a LONG way before you have a real scale problem.