Hacker News Reader

Comment by thegreatpeter | Hacker News Reader

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

Here's a Medium post on Time Series Databases: 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.

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.

> 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?

"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.

Ah yes, I should have grouped by the non-time key fields.

I still don't understand how that would give the right result.

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.

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.

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.

"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.

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...

If you're comparing performance against a transaction-oriented DBMS like postgres, rather than an analytics-oriented columnar DBMS like Actian Vector, MonetDB, HP Vertica etc - then of course you'll get bad perfromance. The former kind are typically up to 1000x slower than the latter kind on analytic queries.

> 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

plug, but our system provides very fast access to price, fundamentals, estimates, etc: https://factset.quantopian.com

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)

Reply on news.ycombinator.com