3

We have an application producing 5k-10k datapoints per second. Each datapoint has more than one metric, alongside its time of creation.

We are looking for an efficient, scalable way to store this huge volume, such that aggregations can be queried by business logic shortly after storage, and such that they can be aggregated into dashboards as well.

We realize that basing business logic on metrics is not typical. Usually, when faced with data that needs to be both written and read by an application, we would choose a typical database such as Postgres or MongoDB. However, the volume we want to deal with has made us think twice about our choice. Here are some options we've considered, which may or may not be a good fit:

  • Time-series databases such as Graphite or InfluxDB, which is great for write-heavy workloads even at this volume, but might not be so good when retrieving data;
  • Redis, which can handle very fast reads and writes, but may struggle with the amount of swaps between storage and memory which would occur with this volume;
  • Mongo DB, which is a little expensive and would create one document per measurement, making the database grow into the billions of documents range in a few days;
  • Redshift (we are on AWS).

Is any of these options objectively better than the others (and why/why not)? Or are there any other options out there which would allow for this sort of efficient reads and writes over billions of datapoints?

Remark: There is a similar question here. But it's from 2015 and seems outdated and not comprehensive enough.

7
  • 5
    Trying to find a single database that both handles this write load and later queries might not be optimal – could make sense to have one service to just persist the data stream, and other services to turn that data into a queryable form. Which approaches are suitable also depends on your usage characteristics – is 10k events per second an average or peak rate? Will this be sustained forever, or just for a few hours? What kind of queries would be desirable? Might it be better to use event processing languages like Esper instead of making queries in the future?
    – amon
    Feb 15, 2022 at 13:55
  • 1
    What limitations did the options you proposed have in your tests? You wrote they "may" not work or "might" not keep up, but the real question is whether they actually did or not with your specific loads in tests. If you're discarding viable options because of some unproven possible limitation, there's not much we, or anyone, can do...
    – mmathis
    Feb 15, 2022 at 14:04
  • @amon Hadn't heard of Esper before, it's pretty cool! I don't think we want to introduce a new language to the company, but we'll keep it in mind as we grow - thanks for the suggestion. The average rate ia 5k and we're probably looking at max 10k peaks for now. This rate will likely be sustained forever, or at least until it becomes too unwieldy and tanks performance. We'd like to query by time period, and by some of the dimensions present on each datapoint.
    – Paul Benn
    Feb 15, 2022 at 14:05
  • 3
    @PaulBenn: at that rate, you will probably produce several dozens of GB of data per day (depending on the number and size of the datapoints, which you did not mention). Do you really need to produce and store all that data over years continually and online? Or can you archive the data after a reasonable time period and just store some heavily reduced aggregates for queries over longer time periods? Before even thinking about which storage to pick, please clarify your requirements first.
    – Doc Brown
    Feb 16, 2022 at 4:40
  • 1
    @DocBrown - yes, we will likely need to archive the data eventually. But that wasn't really part of my question - we are interested in solutions which can deal with huge influx rates, that can also deal with production queries. For now, it seems like amon's solution is the best: splitting the data into read and write stores which are optimised for their respective workload. My apologies for not posting any more requirements - it is probable that we will want to heavily aggregate things older than a few months, yes, but we aren't after that info at the moment! Thanks for your help
    – Paul Benn
    Feb 17, 2022 at 17:59

1 Answer 1

1

With this kind of load I suggest you roll your own solution using a compiled language and you use the operating system's file system directly as a storage medium.

Your requirements seem pretty simple (although you are not very specific). You could structure your data in folders that map to the calendar and the clock, putting 2022 data in one folder, February's data in it's own subfolder, data of the 17th in another subfolder. Then start a new file every minute.

By doing so you may leave out the timestamp if points are coming in at a fixed rate (the time may be derived from the point data's index). This may not be the case though, it is just one idea to optimize the use of space.

Depending on the meta data you need to store with your points you can design a record layout that makes best use of a series of bits.

Searching and reading would be trivial, you could use the same classes and structures used for storing. That is, if searching based on time would do. If not and you want to be able to search based on some of that other undisclosed meta data, you would need to produce indexes which would complicate matters considerably.

Do you need to hot-swap disks or could you fit several sessions on a single disk? There are still a lot of questions to be answered.

How bad are 10K data points per second really? Say you need 100 bytes per point (seems generous), that would be 1 MB/s, just under 100 GB a day. One 10 TB drive should give you 100 days of storage.

Compression could extend this to several years but would complicate searching and reading a bit.

2
  • 1
    I don't see how this strategy would be better than using a database engine. While the filesystem can be used as a kind of database, it will rarely offer advantages. In particular, writing crash-resilient code yourself is really tricky, whereas a database engine will provide some durability guarantees.
    – amon
    Mar 15, 2022 at 9:41
  • @amon I would expect this to be faster, cheaper, more flexible, more controlled and use less resources. I acknowledge there will be more factors into play once you have the data and want to make it available to users. The focus was on efficiency though and the concern about being able to deal with the volume. Mar 15, 2022 at 10:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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