3 ms·

If the author ends up reading this... as someone who is ignorant but curious about time series databases, it was frustrating to see an example of what relationa

If the author ends up reading this... as someone who is ignorant but curious about time series databases, it was frustrating to see an example of what relational data looks like, but then no example of what time series data looks like. It might take a bit more context or setup, but this article is an opportunity to educate folks who aren't deep in the same trench. Edit: in fairness, this article doesn't describe itself as "a Postgres developer's guide to getting started with time series databases" and maybe that's what I was unfairly hoping I would find.

osigurdson 2y ago
The naive relational time series table looks like: propertyid, timestamp, value Then add a covering index so your reads only ever hit the index. This works completely fine for low billions of rows. After that suggest using clickhouse. It is less general but at large enough scale you need to make some tradeoffs. Completely fine to start with a relational DB in many cases though.
- candiddevmike 2y ago
  But then I have to use standard SQL instead of a vendor's half assed, poorly documented query language.
- peteforde 2y ago
  Thanks for this. Can we keep going? The article mentioned stock prices, so let's use your schema: LLY.NYSE, 1726377148, 924.38 SHOP.NYSE, 1726377216, 72.45 SHOP.NYSE, 1726377245, 72.41 LLY.NYSE, 1726377284, 924.39 LLY.NYSE, 1726377310, 924.36 Okay, so you're appending values that capture a moment in time for a given index, with the understanding that you're almost never going to revise a tuple. So far as we're concerned, time only flows in one direction. Periodicity is determined by the client application but could be throttled due to a bottleneck, resulting in fewer writes. I can imagine how storing in blocks based on time ranges and the fact that time number goes up makes this very easy to index. With even this simple example, it's clear what this type of datastore could be useful for, even though I'm not convinced you couldn't do pretty exciting things in Redis or sqlite3. That said, I'm still significantly confused why the hell you can only store numeric values. It just seems like a very arbitrary constraint.
  - Too 2y ago
    The constraint is not inherent to TSDB generally. Influxdb supports string values for example, as mentioned in the article. You also have log databases, like Loki, that are designed like a TSDB, except they only store strings. My guess is that constraining to numbers greatly simplifies the implementation, especially so for the query language and aggregation functions.
    - osigurdson 2y ago
      There are some compression algos (e.g. gorilla) which do a good job at lossless compression. However the key is not to try to store arbitrary data types in the same table (don't use "OO" ideas here).
  - atombender 2y ago
    Non-TSDB databases can do an adequate job — up to a certain point. However, getting good performance comes down to query access paths. For enormous amounts of data, you want your data to be sequential (a few batch reads are generally faster than lots of random ones) and sorted. Databases like Postgres don't store rows in index order, and their indexes are geared towards small fetches as opposed to big ranges of sequential data. (Postgres does have BRIN indexes, but they're not that great for this.) Other databases do support clustered tables, but they're still page-based underneath and still not suboptimal for other reasons. It's a good learning exercise to compare traditional databases with something like ClickHouse, which was s designed from the ground up to store data in this optimized way. (It's a general-purpose OLAP database, so columns can be any kind of data, not just numbers, and data doesn't need to be time-based; so it's useful for much more than "pure" TSDBs.) As for numbers: For metric data you're storing numeric measurements. The whole point is applying aggregations to the data, like calculating an average or a percentile over a certain time period. Storing numbers also has the advantage of allowing downsampling. For many purposes you don't need 1s granularity for all your data. So as you "roll" data over into old time periods, you can downsample by storing the data points aggregated as mean/min/max/etc. New data can be exact down to the second, while old data can be exact down to the hour, for example.
    - 2y ago

osigurdson 2y ago

The naive relational time series table looks like: propertyid, timestamp, value Then add a covering index so your reads only ever hit the index. This works completely fine for low billions of rows. After that suggest using clickhouse. It is less general but at large enough scale you need to make some tradeoffs. Completely fine to start with a relational DB in many cases though.

candiddevmike 2y ago
But then I have to use standard SQL instead of a vendor's half assed, poorly documented query language.
peteforde 2y ago
Thanks for this. Can we keep going? The article mentioned stock prices, so let's use your schema: LLY.NYSE, 1726377148, 924.38 SHOP.NYSE, 1726377216, 72.45 SHOP.NYSE, 1726377245, 72.41 LLY.NYSE, 1726377284, 924.39 LLY.NYSE, 1726377310, 924.36 Okay, so you're appending values that capture a moment in time for a given index, with the understanding that you're almost never going to revise a tuple. So far as we're concerned, time only flows in one direction. Periodicity is determined by the client application but could be throttled due to a bottleneck, resulting in fewer writes. I can imagine how storing in blocks based on time ranges and the fact that time number goes up makes this very easy to index. With even this simple example, it's clear what this type of datastore could be useful for, even though I'm not convinced you couldn't do pretty exciting things in Redis or sqlite3. That said, I'm still significantly confused why the hell you can only store numeric values. It just seems like a very arbitrary constraint.
- Too 2y ago
  The constraint is not inherent to TSDB generally. Influxdb supports string values for example, as mentioned in the article. You also have log databases, like Loki, that are designed like a TSDB, except they only store strings. My guess is that constraining to numbers greatly simplifies the implementation, especially so for the query language and aggregation functions.
  - osigurdson 2y ago
    There are some compression algos (e.g. gorilla) which do a good job at lossless compression. However the key is not to try to store arbitrary data types in the same table (don't use "OO" ideas here).
- atombender 2y ago
  Non-TSDB databases can do an adequate job — up to a certain point. However, getting good performance comes down to query access paths. For enormous amounts of data, you want your data to be sequential (a few batch reads are generally faster than lots of random ones) and sorted. Databases like Postgres don't store rows in index order, and their indexes are geared towards small fetches as opposed to big ranges of sequential data. (Postgres does have BRIN indexes, but they're not that great for this.) Other databases do support clustered tables, but they're still page-based underneath and still not suboptimal for other reasons. It's a good learning exercise to compare traditional databases with something like ClickHouse, which was s designed from the ground up to store data in this optimized way. (It's a general-purpose OLAP database, so columns can be any kind of data, not just numbers, and data doesn't need to be time-based; so it's useful for much more than "pure" TSDBs.) As for numbers: For metric data you're storing numeric measurements. The whole point is applying aggregations to the data, like calculating an average or a percentile over a certain time period. Storing numbers also has the advantage of allowing downsampling. For many purposes you don't need 1s granularity for all your data. So as you "roll" data over into old time periods, you can downsample by storing the data points aggregated as mean/min/max/etc. New data can be exact down to the second, while old data can be exact down to the hour, for example.
  - 2y ago