5 ms·

Can someone give me a real-world example of a scenario where they actually need a time series database, like an example query with the business use case / justi

Can someone give me a real-world example of a scenario where they actually need a time series database, like an example query with the business use case / justification? Just super curious.

detailyang 5y ago
I build TSDB (10,000,000 samples per seconds and average query time is almost 200ms) over clickhouse which support multi insert protocol (influxdb line protocol|prometheus remote write|opentsdb json format) and opentsdb query protocol and incomplete PromQL. Clickhouse is good but something you need to tunning. using memory table to speedup insert,high performance zookeeper to improve replicated table, data shard via timeseries id (distributed_group_by_no_merge=1) to reduce bootstrap node load
joshxyz 5y ago
- https://clickhouse.com/docs/en/faq/use-cases/time-series/ https://clickhouse.com/docs/en/faq/use-cases/time-series/ - https://clickhouse.com/docs/en/faq/general/olap/ https://clickhouse.com/docs/en/faq/general/olap/
LoriP 5y ago
Here's some posts from users on the Timescale blog https://blog.timescale.com/tag/dev-q-a/ https://blog.timescale.com/tag/dev-q-a/
ryanbooz 5y ago
(Post author) This is a great post to give you some talking points: https://blog.timescale.com/blog/what-the-heck-is-time-series-data-and-why-do-i-need-a-time-series-database-dcf3b1b18563/ https://blog.timescale.com/blog/what-the-heck-is-time-series... I also love this recent one we did with some non-standard time-series data that the NFL provided! Really fun working on that data set. https://blog.timescale.com/blog/hacking-nfl-data-with-postgresql-timescaledb-and-sql/ https://blog.timescale.com/blog/hacking-nfl-data-with-postgr...
GordonS 5y ago
We're using TimescaleDB to store log events - so each row has a timestamp, some properties, and a log message. And a lot of that is actually in a JSONB column. Not the archetypal time series use case, but TimescaleDB is still really useful. TSDB's compression means we can store a huge volume of data in a fraction of the space of a standard Postgres table. You can achieve even better compression ratios and performance if you spend time designing your schema carefully, but honestly we didn't see the need, as just throwing data in gets us something like 10:1 compression and great performance. TSDB's chunked storage engine means that queries along chunking dimensions (e.g. timestamp) are super-fast, as it knows exactly which files to read. Chunking also means that data retention policies execute nearly instantaneously, as it's literally just deleting files from disk, rather than deleting rows one-by-one - millions of rows are gone in an instant! And best of all, this all works in Postgres, and we can query TSDB data just the same as regular data. All that combined easily justified the decision to use TSDB - and if you're familiar with Postgres, it's actually really simple to get started with. Really, we'd of needed a business justification not to use it! Much love for the TimescaleDB team!
- sam0x17 5y ago
  So services like Sentry / Honeybadger / etc probably use this architecture?
  - GordonS 5y ago
    I think most online logging SaaS services actually use ElasticSearch.
    - RhodesianHunter 5y ago
      I know that at least two of the top ten use ClickHouse.
    - akulkarni 5y ago
      StackGres by Ongres, which achieved 1.0.0 status today, is another example of someone storing logs in TimescaleDB: https://twitter.com/ongresinc/status/1451194886795849742 https://twitter.com/ongresinc/status/1451194886795849742 (Also, thank you GordonS for the kind words!)
tnolet 5y ago
We just migrated to Clickhouse. We collect monitoring data. So response times from 20+ different location. We are not super duper big but at least 100M+ individual metrics per month. We want to give our users a snappy, interactive dashboard that lets them explore aggregates of that data over time: averages, p99 etc.. That is where a time series DB is very handy
claytonjy 5y ago
I introduced Timescale at an IIOT company, we had thousands of sensors regularly sending data up and wanted to efficiently display such metrics to users on a per-sensor basis, and one "tick" of data had a lot of metrics. Timescale let us go from Postgres for metadata and OpenTSDB (awful, stay far away) for time series to just one Timescale instance for everything. Huge win for us. We had enough data that doing the same with vanilla Postgres would have performed much worse (billions of rows). We wrote more about this for an earlier Timescale blog post: https://blog.timescale.com/blog/how-everactive-powers-a-dense-sensor-network-with-virtually-no-power-at-all/ https://blog.timescale.com/blog/how-everactive-powers-a-dens...
bradstewart 5y ago
We capture and store energy readings at ~5second intervals, then display total energy at various time granularity by aggregating the values over minutes, hours, days, months, etc.
ur-whale 5y ago
> Can someone give me a real-world example of a scenario where they actually need a time series database Large scale infrastructure monitoring? If you run a data center with 10K machines in it, whitebox monitoring of these machines and what runs on it generates tons of timestamped data. These time series can be used to inform an automated alerting system (eg using trends to forecast bad things before they happen). They can also be analyzed in batch mode to figure out how to optimize many things (power / cooling / workload assignment / etc ...)
jedberg 5y ago
At Netflix all of our monitoring was in a time series database so we could get real time insights into pretty much anything we were monitoring (which was most everything).
pgwhalen 5y ago
Most useful economic or financial markets data is some kind of time series. For example, what was the maximum price of a stock in the last year, and how does it compare to the current price? FRED (https://fred.stlouisfed.org/ https://fred.stlouisfed.org/) is a great resource for publicly available economic time series data.
sarlalian 5y ago
I use TimescaleDB to store data from logs (syslog, http logs, render logs), timeseries host metrics (CPU, Disk, Memory, Network), system inventory data, FDW to LDAP, network flows, etc. I do analysis across multiple dimensions for a variety of business uses. 1. Render job logs, combined with host metrics to determine if any systems are being under utilized. 2. Intranet network usage for monitoring purposes. 3. General monitoring of log events, system metrics, etc with alerting based on specific query criteria. 4. Temperature / Humidity monitoring and trend analysis. Basically if there are things that happen somewhat often (http logs, system logs, host metrics, power usage, temperature change, etc) and you want to analyze that data, and one of the factors in your analysis is time, then time series databases are for you. In the end each TSDB has different trade offs, and there are a number of considerations to make, and how you weigh them will change a lot depending on scale. If you are writing 1-20GB/Day vs 1-20TB/Day, you will have different considerations. If you have relatively simple queries vs complex queries with complex joins on other data you will have different considerations. In the end you need to have some idea what your data is, how much of it there is, what the growth looks like and what your write/query usage patterns are going to look like. Insert / Query performance isn't everything, unless for your business it is.

Can someone give me a real-world example of a scenario where they actually need a time series database, like an example query with the business use case / justification? Just super curious.

detailyang 5y ago

I build TSDB (10,000,000 samples per seconds and average query time is almost 200ms) over clickhouse which support multi insert protocol (influxdb line protocol|prometheus remote write|opentsdb json format) and opentsdb query protocol and incomplete PromQL. Clickhouse is good but something you need to tunning. using memory table to speedup insert,high performance zookeeper to improve replicated table, data shard via timeseries id (distributed_group_by_no_merge=1) to reduce bootstrap node load

joshxyz 5y ago

- https://clickhouse.com/docs/en/faq/use-cases/time-series/ https://clickhouse.com/docs/en/faq/use-cases/time-series/ - https://clickhouse.com/docs/en/faq/general/olap/ https://clickhouse.com/docs/en/faq/general/olap/

LoriP 5y ago

Here's some posts from users on the Timescale blog https://blog.timescale.com/tag/dev-q-a/ https://blog.timescale.com/tag/dev-q-a/

ryanbooz 5y ago

(Post author) This is a great post to give you some talking points: https://blog.timescale.com/blog/what-the-heck-is-time-series-data-and-why-do-i-need-a-time-series-database-dcf3b1b18563/ https://blog.timescale.com/blog/what-the-heck-is-time-series... I also love this recent one we did with some non-standard time-series data that the NFL provided! Really fun working on that data set. https://blog.timescale.com/blog/hacking-nfl-data-with-postgresql-timescaledb-and-sql/ https://blog.timescale.com/blog/hacking-nfl-data-with-postgr...

GordonS 5y ago

We're using TimescaleDB to store log events - so each row has a timestamp, some properties, and a log message. And a lot of that is actually in a JSONB column. Not the archetypal time series use case, but TimescaleDB is still really useful. TSDB's compression means we can store a huge volume of data in a fraction of the space of a standard Postgres table. You can achieve even better compression ratios and performance if you spend time designing your schema carefully, but honestly we didn't see the need, as just throwing data in gets us something like 10:1 compression and great performance. TSDB's chunked storage engine means that queries along chunking dimensions (e.g. timestamp) are super-fast, as it knows exactly which files to read. Chunking also means that data retention policies execute nearly instantaneously, as it's literally just deleting files from disk, rather than deleting rows one-by-one - millions of rows are gone in an instant! And best of all, this all works in Postgres, and we can query TSDB data just the same as regular data. All that combined easily justified the decision to use TSDB - and if you're familiar with Postgres, it's actually really simple to get started with. Really, we'd of needed a business justification not to use it! Much love for the TimescaleDB team!

sam0x17 5y ago
So services like Sentry / Honeybadger / etc probably use this architecture?
- GordonS 5y ago
  I think most online logging SaaS services actually use ElasticSearch.
  - RhodesianHunter 5y ago
    I know that at least two of the top ten use ClickHouse.
  - akulkarni 5y ago
    StackGres by Ongres, which achieved 1.0.0 status today, is another example of someone storing logs in TimescaleDB: https://twitter.com/ongresinc/status/1451194886795849742 https://twitter.com/ongresinc/status/1451194886795849742 (Also, thank you GordonS for the kind words!)

tnolet 5y ago

We just migrated to Clickhouse. We collect monitoring data. So response times from 20+ different location. We are not super duper big but at least 100M+ individual metrics per month. We want to give our users a snappy, interactive dashboard that lets them explore aggregates of that data over time: averages, p99 etc.. That is where a time series DB is very handy

claytonjy 5y ago

I introduced Timescale at an IIOT company, we had thousands of sensors regularly sending data up and wanted to efficiently display such metrics to users on a per-sensor basis, and one "tick" of data had a lot of metrics. Timescale let us go from Postgres for metadata and OpenTSDB (awful, stay far away) for time series to just one Timescale instance for everything. Huge win for us. We had enough data that doing the same with vanilla Postgres would have performed much worse (billions of rows). We wrote more about this for an earlier Timescale blog post: https://blog.timescale.com/blog/how-everactive-powers-a-dense-sensor-network-with-virtually-no-power-at-all/ https://blog.timescale.com/blog/how-everactive-powers-a-dens...

bradstewart 5y ago

We capture and store energy readings at ~5second intervals, then display total energy at various time granularity by aggregating the values over minutes, hours, days, months, etc.

ur-whale 5y ago

> Can someone give me a real-world example of a scenario where they actually need a time series database Large scale infrastructure monitoring? If you run a data center with 10K machines in it, whitebox monitoring of these machines and what runs on it generates tons of timestamped data. These time series can be used to inform an automated alerting system (eg using trends to forecast bad things before they happen). They can also be analyzed in batch mode to figure out how to optimize many things (power / cooling / workload assignment / etc ...)

jedberg 5y ago

At Netflix all of our monitoring was in a time series database so we could get real time insights into pretty much anything we were monitoring (which was most everything).

pgwhalen 5y ago

Most useful economic or financial markets data is some kind of time series. For example, what was the maximum price of a stock in the last year, and how does it compare to the current price? FRED (https://fred.stlouisfed.org/ https://fred.stlouisfed.org/) is a great resource for publicly available economic time series data.

sarlalian 5y ago

I use TimescaleDB to store data from logs (syslog, http logs, render logs), timeseries host metrics (CPU, Disk, Memory, Network), system inventory data, FDW to LDAP, network flows, etc. I do analysis across multiple dimensions for a variety of business uses. 1. Render job logs, combined with host metrics to determine if any systems are being under utilized. 2. Intranet network usage for monitoring purposes. 3. General monitoring of log events, system metrics, etc with alerting based on specific query criteria. 4. Temperature / Humidity monitoring and trend analysis. Basically if there are things that happen somewhat often (http logs, system logs, host metrics, power usage, temperature change, etc) and you want to analyze that data, and one of the factors in your analysis is time, then time series databases are for you. In the end each TSDB has different trade offs, and there are a number of considerations to make, and how you weigh them will change a lot depending on scale. If you are writing 1-20GB/Day vs 1-20TB/Day, you will have different considerations. If you have relatively simple queries vs complex queries with complex joins on other data you will have different considerations. In the end you need to have some idea what your data is, how much of it there is, what the growth looks like and what your write/query usage patterns are going to look like. Insert / Query performance isn't everything, unless for your business it is.