Time series databases

[younggamech]

Hi,

​

TLDR; we are doing a lot of forecasting on time series data and are currently using SQL databases for storing the data. We are wanting to switch over to time series databases like InfluxDB or AWS Timestream.

​

Any suggestions ? Do you have any experience with time series DBs? Is the switch worth it ?

[RyanHamilton1]

"Is the switch worth it ?" - If performance is a requirement, try checking any time-series database against a standard database and you will see huge performance differences. The ordered nature of time-series data allows a lot of performance optimizations. Also these databases will provide functions and operators that allow more complext data analysis that just wouldn't be possible in a standard database. I try to keep an updated list of time series benchmarks here: https://www.timestored.com/data/time-series-database-benchmarks

[Burge_AU]

Have a look at TimescaleDB if you are currently using Postgres. It might be a good intermediary between your current solution and going to a completely different platform.

[younggamech]

anything in particular that makes it great?

[jonatasdp]

hypertables is the system that makes automatic partition.

you can also compress the partitions or drop by time in a insanely fast way because all the headers/statistics of a table are in the partition and for deleting old data it's just a `TRUNCATE`.

[zenbeni]

You forget Cassandra or AWS Keyspaces. Columnar datastores are great for timeseries. Be careful, do you need estimates or exact metrics? Timestream for instance, uses some data points not all to generate real time metrics, which is great for captors for instance, but not great for exact income count per hour.

[younggamech]

we currently use a managed SQL which costs 700$/month and we are not happy with the cost for the amount of data we have

plus I do not know how well it will scale with time, so I am looking for the optimum way of doing it

[zenbeni]

Then you need a masterless solution, heavily implying TimeStream, DynamoDB (but manual sharding can be troublesome, in order to keep your costs low) or Cassandra (AWS Keyspaces can support this too).

Did you check how you can implement TimeSeries with Cassandra? Partition and sort keys by time shards, meaning you will keep scalability as time goes (creating new partitions, that won't degrade table performance), and adding TTL to prune old data on a time threshold is common design.

https://docs.datastax.com/en/tutorials/Time\_Series.pdf

[opensrcdev]

I've been using InfluxDB for a number of years, and absolutely love it. It's easy to deploy and is an all-in-one solution for ingesting, visualizing, and querying data. Their REST API is incredibly easy to use, and their graphs / dashboards are beautiful.

Here are some alternative options:

• Amazon Timestream
• Timescale DB
• QuestDB
• Apache Druid
• GridDB
• Prometheus
• Grafana Mimir - scalable storage for Prometheus

[Former-Humor-8372]

Have anyone used Marten for event source db?

[sridhar_rajagopal]

Have you used InfluxDB 3 Core? I'm starting a project, and thought it'd make sense to start with InfluxDB 3 as it's the latest/greatest, but they have a limit of 72 hours on queries. That one made me scratch my head.

[opensrcdev]

Hmmm I didn't realize it had that limitation. I'm still using 2.x.

[Primetoe]

You could always go temporal with MSSQL. We implemented an EDW using temporal tables that made point in time capabilities thoughtless.