3

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.

Ibn-e-Batuta
  • 31
  • 1
  • 1
    How are you querying the data? Did you create any indexes? – Alen Jun 18 '18 at 19:49
  • @Alen: Most of queries involve aggregation. e.g. average event duration in last one month for each site or average time delta of repeating data. I use shell for that and using index only on Site ID. – Ibn-e-Batuta Jun 18 '18 at 19:53
  • It sounds to me like you need a SAN and a bunch of RAM. I'd suggest taking a look at your hardware before migrating to a new database vendor. – James Jun 18 '18 at 19:55
  • You really don't need a a SQL type database for this. In theory you can have some text files sitting somewhere and just query them with Python or R. I'm not an R master, but in a class I took we had a project similar to this. If your devices each write to a separate file it's like one line of code to have R loop through every file to do your calculations. Haven't used Python but it should be very similar. But both will have thousands of plugins for different analytics you can run to make coding easier. – Alen Jun 18 '18 at 20:05
  • 1
    after a few million rows... I've queried tables with more complex rows and orders of magnitude (100's of millions) more of them using a laptop and got sub-second performance with PostgreSQL. You'll have to expand on the nature of your queries... – Vérace Jun 18 '18 at 21:55
  • Look at time series databases: https://en.wikipedia.org/wiki/Time_series_database – Michael Green Jun 19 '18 at 11:33

1 Answers1

0

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.

Evan Carroll
  • 63,051
  • 46
  • 242
  • 479