5 ms·

Am I the only one that's like "wtf is a time-series database compared to a normal one?"

Maro 6y ago
In general, imagine a problem space where you have millions (or much much more) of timeseries, each is potentially millions long (usually it's purely floats), and you want to perform time-series specific operations, like interpolate, extrapolate, moving avg, forecast, alert if ususual, plot, etc. Like, imagine AWS has 100s-1000s (or more) of metrics per "thing", and there's a very large number of things (EC2 instances, subnets, SageMaker instances, network switches, etc). Very specific data model, usually append-only, very specific read operations.
Ixiaus 6y ago
Yes. Google it.
- airstrike 6y ago
  https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html Please don't post shallow dismissals, especially of other people's work. A good critical comment teaches us something.
bluestreak 6y ago
"normal" database does not preserve the order of data as it comes in. To get the data out you have to constantly rely on indexes or "order by" constantly to get chronological order back. Time series database should maintain the order of data and not rely on indexes to have data make sense again.
santafen 6y ago
Here's a Medium post on Time Series Databases: https://medium.com/datadriveninvestor/what-are-time-series-databases-a3e847608f91 https://medium.com/datadriveninvestor/what-are-time-series-d... But basically, they are databases where the time dimension is a critical aspect of the data. They handle never-ending streams of incoming time-stamped data. Think of streaming stock prices, or streaming temperature data from a sensor. You want to be able to query your data specifically in a time dimension -- let's see the temperature fluctuations over the past 24 hours, including the mean. Stuff like that. A 'normal one' is typically used for things like transactional data which adds, deletes, and updates data among linked tables. While these transactions happen in time, the time component isn't necessarily a critical dimension of the data.
viraptor 6y ago
It organises storage so that operations like "drop all entries from before X", "get entries between X and Y with tags A or B" are cheap and so that storing ~linearly increasing values is super efficient with stupid ratios like 1:800+ for DoubleDelta.
avthar 6y ago
This is actually an underrated question. Time-series databases offer better performance and usability for dealing with time-series data (think DevOps metrics, data from IoT devices, stock prices etc, anything where you're monitoring and analyzing how things change over time) They allow you answer questions where time is the main component of interest much more quickly and easily: eg 1: IoT Sensors) Show me the average of temperature over all my devices over the past 3 days in 15 minute intervals eg 2: Financial data) What's the price of stock X over the past 5 years eg 3: DevOps data) What's the average memory and CPU used by all my servers of the past 5 mins A normal database could be a purely relational database (e.g Postgres) or a non-relational database (e.g MongoDB). In both these cases, while you could use these databases for time-series data, they tend to offer worse performance at scale and a worse experience for doing common things (e.g real-time aggregations of data, data retention policies etc) For more on time-series data and when you'd need a time-series database, check out: 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...
- airstrike 6y ago
  > eg 2: Financial data) What's the price of stock X over the past 5 years This is so incredibly frustratingly slow to pull on FactSet and Capital IQ, it makes me want to pull my hair every time I have to build line charts over time for a period greater than 2 years
  - fawce 6y ago
    plug, but our system provides very fast access to price, fundamentals, estimates, etc: https://factset.quantopian.com https://factset.quantopian.com
  - avthar 6y ago
    Sounds like you need a time-seres database for those sorts of narrow and deep queries :) What's difficult is to find a database that has good performance on both narrow and deep queries (e.g Price of stock X for past 5 years) as well as shallow and wide queries (e.g Price of all stocks in past 15mins)
beagle3 6y ago
One differentiating feature is "as of" join. You have records of the form (time, value), and you ask "what's the most recent value as of $time?"; On a non-TS oriented DBMS, this query is usually slow and hard to write. Window extensions to SQL can make it a little better, but - you can assume that a proper TSDB answers this query x10 to x10,000 times faster on the same hardware, especially when done in bulk (e.g.: I have one million (time,bid_price) records, and one million (time,transaction_price) records; For each transaction record, I want to know what the most-recent bid price was at that time. That's something kdb+ and ClickHouse do in milliseconds; and I assume QuestDB can to, though I didn't check.
- einpoklum 6y ago
  > On a non-TS oriented DBMS, this query is usually slow and hard to write. I don't see why this is hard to write: SELECT max(time) as most_recent_time, transaction_price INTO newtable FROM oldtable WHERE time > $my_datetime GROUP BY time; As for being slow - can you provide some references about this being slow in non-time-series columnar DBMSes? MonetDB, Vectorwise, Vertica?
  - beagle3 6y ago
    "most recent" is min(time) where time>=$my_datetime ; I don't understand your query - max(time) .. GROUP BY time means that every record is its own group, so max(time)==time; also, you will have a result for every single point in time after the requested time. There is no way in standard SQL (without window extensions) to do this with just one clause; you need the price associated with the min(time) associated with time>=$my_datetime ; You need either a subquery, a join, or some other correlated query. e.g. SELECT time, price FROM trades WHERE time IN (SELECT min(time) FROM trades WHERE time>=$my_datetime) And that's for one record; It gets more complicated when you try to do a batch. > As for being slow - can you provide some references about this being slow in non-time-series columnar DBMSes? MonetDB, Vectorwise, Vertica? I've timed kdb2 against Vertica a decade or so ago, and kdb2 was about x10 faster, I didn't do a proper benchmark but monetdb was in the x10-x100 slower than kdb2 at the time.
    - 6y ago
JonoBB 6y ago
Broadly timeseries DB's are optimized around the following: - ingesting large amounts of data - aggregating data around a "time" field (i.e. per minute, per hour, etc) - updates are rare - deletes are rare By sacrificing update and delete functionality, timeseries DB's can optimize creating and reading records.

Maro 6y ago

In general, imagine a problem space where you have millions (or much much more) of timeseries, each is potentially millions long (usually it's purely floats), and you want to perform time-series specific operations, like interpolate, extrapolate, moving avg, forecast, alert if ususual, plot, etc. Like, imagine AWS has 100s-1000s (or more) of metrics per "thing", and there's a very large number of things (EC2 instances, subnets, SageMaker instances, network switches, etc). Very specific data model, usually append-only, very specific read operations.

Ixiaus 6y ago

Yes. Google it.

airstrike 6y ago
https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html Please don't post shallow dismissals, especially of other people's work. A good critical comment teaches us something.

bluestreak 6y ago

"normal" database does not preserve the order of data as it comes in. To get the data out you have to constantly rely on indexes or "order by" constantly to get chronological order back. Time series database should maintain the order of data and not rely on indexes to have data make sense again.

santafen 6y ago

Here's a Medium post on Time Series Databases: https://medium.com/datadriveninvestor/what-are-time-series-databases-a3e847608f91 https://medium.com/datadriveninvestor/what-are-time-series-d... But basically, they are databases where the time dimension is a critical aspect of the data. They handle never-ending streams of incoming time-stamped data. Think of streaming stock prices, or streaming temperature data from a sensor. You want to be able to query your data specifically in a time dimension -- let's see the temperature fluctuations over the past 24 hours, including the mean. Stuff like that. A 'normal one' is typically used for things like transactional data which adds, deletes, and updates data among linked tables. While these transactions happen in time, the time component isn't necessarily a critical dimension of the data.

viraptor 6y ago

It organises storage so that operations like "drop all entries from before X", "get entries between X and Y with tags A or B" are cheap and so that storing ~linearly increasing values is super efficient with stupid ratios like 1:800+ for DoubleDelta.

avthar 6y ago

This is actually an underrated question. Time-series databases offer better performance and usability for dealing with time-series data (think DevOps metrics, data from IoT devices, stock prices etc, anything where you're monitoring and analyzing how things change over time) They allow you answer questions where time is the main component of interest much more quickly and easily: eg 1: IoT Sensors) Show me the average of temperature over all my devices over the past 3 days in 15 minute intervals eg 2: Financial data) What's the price of stock X over the past 5 years eg 3: DevOps data) What's the average memory and CPU used by all my servers of the past 5 mins A normal database could be a purely relational database (e.g Postgres) or a non-relational database (e.g MongoDB). In both these cases, while you could use these databases for time-series data, they tend to offer worse performance at scale and a worse experience for doing common things (e.g real-time aggregations of data, data retention policies etc) For more on time-series data and when you'd need a time-series database, check out: 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...

airstrike 6y ago
> eg 2: Financial data) What's the price of stock X over the past 5 years This is so incredibly frustratingly slow to pull on FactSet and Capital IQ, it makes me want to pull my hair every time I have to build line charts over time for a period greater than 2 years
- fawce 6y ago
  plug, but our system provides very fast access to price, fundamentals, estimates, etc: https://factset.quantopian.com https://factset.quantopian.com
- avthar 6y ago
  Sounds like you need a time-seres database for those sorts of narrow and deep queries :) What's difficult is to find a database that has good performance on both narrow and deep queries (e.g Price of stock X for past 5 years) as well as shallow and wide queries (e.g Price of all stocks in past 15mins)

beagle3 6y ago

One differentiating feature is "as of" join. You have records of the form (time, value), and you ask "what's the most recent value as of $time?"; On a non-TS oriented DBMS, this query is usually slow and hard to write. Window extensions to SQL can make it a little better, but - you can assume that a proper TSDB answers this query x10 to x10,000 times faster on the same hardware, especially when done in bulk (e.g.: I have one million (time,bid_price) records, and one million (time,transaction_price) records; For each transaction record, I want to know what the most-recent bid price was at that time. That's something kdb+ and ClickHouse do in milliseconds; and I assume QuestDB can to, though I didn't check.

einpoklum 6y ago
> On a non-TS oriented DBMS, this query is usually slow and hard to write. I don't see why this is hard to write: SELECT max(time) as most_recent_time, transaction_price INTO newtable FROM oldtable WHERE time > $my_datetime GROUP BY time; As for being slow - can you provide some references about this being slow in non-time-series columnar DBMSes? MonetDB, Vectorwise, Vertica?
- beagle3 6y ago
  "most recent" is min(time) where time>=$my_datetime ; I don't understand your query - max(time) .. GROUP BY time means that every record is its own group, so max(time)==time; also, you will have a result for every single point in time after the requested time. There is no way in standard SQL (without window extensions) to do this with just one clause; you need the price associated with the min(time) associated with time>=$my_datetime ; You need either a subquery, a join, or some other correlated query. e.g. SELECT time, price FROM trades WHERE time IN (SELECT min(time) FROM trades WHERE time>=$my_datetime) And that's for one record; It gets more complicated when you try to do a batch. > As for being slow - can you provide some references about this being slow in non-time-series columnar DBMSes? MonetDB, Vectorwise, Vertica? I've timed kdb2 against Vertica a decade or so ago, and kdb2 was about x10 faster, I didn't do a proper benchmark but monetdb was in the x10-x100 slower than kdb2 at the time.
  - 6y ago

JonoBB 6y ago

Broadly timeseries DB's are optimized around the following: - ingesting large amounts of data - aggregating data around a "time" field (i.e. per minute, per hour, etc) - updates are rare - deletes are rare By sacrificing update and delete functionality, timeseries DB's can optimize creating and reading records.