3 ms·

I'm still confused why time-series databases are even a thing. It seems to me that time-series just means you have a date/time column plus an index on it. Which

I'm still confused why time-series databases are even a thing. It seems to me that time-series just means you have a date/time column plus an index on it. Which is something typical databases already do well, and like the referenced post mentioned, you could use a column store for better performance. But I just don't see anything that makes creating an entire database design for one specific index type worthwhile... I index many tables on my site by num_upvotes so I can find the top ranked items to show. Does this mean that I need an UpvoteDB? I don't think so. A previous time I argued this point, it was mentioned that you rarely need to update or delete old rows. This allows you to tailor the storage solution better. However, this basically means a compressed column store, which again, doesn't really have much to do with time.

hetspookjee 5y ago
If I recall correctly TimescaleDB is mostly some extension functions for Postgres, with indeed some specific indices that vastly increase some often used insert & lookup query's. You can also just extend it with PostGIS for those really fancy smancy geographical oriented time series query's. Pretty neat stuff running out of the box. Here's the docker implementation: https://hub.docker.com/r/timescale/timescaledb-postgis/ https://hub.docker.com/r/timescale/timescaledb-postgis/
cookguyruffles 5y ago
The internals are completely different. Given the collection of software technologies we posses today, you can't assemble them around a database using a row-oriented encoding and come up with something that can outperform (in space, time and cost) the kinds of query styles that column-oriented encodings absolutely murder. Logically they're the same thing, but engineering is about details, details in this case that could easily be a 2x to 20x budget difference given an appropriate project A column store can take 100 years worth of samples occurring every 10ms that yield a constant result and using technology we actually have, represent those ~87 million data points on disk and in CPU using somewhere under 10 bytes.
- whimsicalism 5y ago
  But there are plenty of non-"time series DB" that are column oriented, maria, monet, etc.
- gautamcgoel 5y ago
  That's way more than 87M samples, more like 300B.
  - cookguyruffles 5y ago
    Oops :) You're right, fat fingered some quick calc
ironman1478 5y ago
Certain time series databases tend to me be optimized towards making the most recent data readily available and quick to fetch. There are also certain filtering / compression algorithms that are run on these time series databases that only make sense in a time domain. Also, some of these time series databases have very specific use cases and you have to also think about the client tools associated with the database. Many of these databases sit in power plants, factories, etc. and they stream data to tools that are built to visualize or analyze the last few minutes of data and then trigger alerts based on patterns. Also, these database are very "device" aware and integrates with other systems that represent their data in a timeseries fashion already (like a sensor). A lot of customers who needed this type of database care only about this index because their concern is record keeping and monitoring. Not necessarily number crunching (this is changing though). There are drawbacks to storing your data this way. If your primary index is time, it can be hard to merge that with some based on a coordinate system. So doing certain types of analysis is really difficult unless you replicate your data into some other database with a different index.
jandrewrogers 5y ago
Everything works reasonably well in a relational database if your data is small. As you scale up, the performance will fall off a cliff for any data model that the internals of the database kernel were not specifically designed for. No relational database kernel is optimized for time-series data models, so poor performance is just a matter of scale.
pgwhalen 5y ago
This is a thought exercise I've done myself, and your questions will mostly be answered by looking at the features (https://docs.timescale.com/api/latest/ https://docs.timescale.com/api/latest/) that TimescaleDB provides. > However, this basically means a compressed column store, which again, doesn't really have much to do with time. It does though: which data do you compress? The old data. Why not let the database figure that out for you, so you don't specifically have to tell it. Other features include: - Continuous Aggregates: a materialized view aggregating data over time is doable, but why not let the database materialize it for you, and automatically fall back to an un-materialized query for the newest data? - Retention: deleting (or downsampling) old data is easy to do on your own, but why not let the database do it for you according to a policy?

hetspookjee 5y ago

If I recall correctly TimescaleDB is mostly some extension functions for Postgres, with indeed some specific indices that vastly increase some often used insert & lookup query's. You can also just extend it with PostGIS for those really fancy smancy geographical oriented time series query's. Pretty neat stuff running out of the box. Here's the docker implementation: https://hub.docker.com/r/timescale/timescaledb-postgis/ https://hub.docker.com/r/timescale/timescaledb-postgis/

cookguyruffles 5y ago

The internals are completely different. Given the collection of software technologies we posses today, you can't assemble them around a database using a row-oriented encoding and come up with something that can outperform (in space, time and cost) the kinds of query styles that column-oriented encodings absolutely murder. Logically they're the same thing, but engineering is about details, details in this case that could easily be a 2x to 20x budget difference given an appropriate project A column store can take 100 years worth of samples occurring every 10ms that yield a constant result and using technology we actually have, represent those ~87 million data points on disk and in CPU using somewhere under 10 bytes.

whimsicalism 5y ago
But there are plenty of non-"time series DB" that are column oriented, maria, monet, etc.
gautamcgoel 5y ago
That's way more than 87M samples, more like 300B.
- cookguyruffles 5y ago
  Oops :) You're right, fat fingered some quick calc

ironman1478 5y ago

Certain time series databases tend to me be optimized towards making the most recent data readily available and quick to fetch. There are also certain filtering / compression algorithms that are run on these time series databases that only make sense in a time domain. Also, some of these time series databases have very specific use cases and you have to also think about the client tools associated with the database. Many of these databases sit in power plants, factories, etc. and they stream data to tools that are built to visualize or analyze the last few minutes of data and then trigger alerts based on patterns. Also, these database are very "device" aware and integrates with other systems that represent their data in a timeseries fashion already (like a sensor). A lot of customers who needed this type of database care only about this index because their concern is record keeping and monitoring. Not necessarily number crunching (this is changing though). There are drawbacks to storing your data this way. If your primary index is time, it can be hard to merge that with some based on a coordinate system. So doing certain types of analysis is really difficult unless you replicate your data into some other database with a different index.

jandrewrogers 5y ago

Everything works reasonably well in a relational database if your data is small. As you scale up, the performance will fall off a cliff for any data model that the internals of the database kernel were not specifically designed for. No relational database kernel is optimized for time-series data models, so poor performance is just a matter of scale.

pgwhalen 5y ago

This is a thought exercise I've done myself, and your questions will mostly be answered by looking at the features (https://docs.timescale.com/api/latest/ https://docs.timescale.com/api/latest/) that TimescaleDB provides. > However, this basically means a compressed column store, which again, doesn't really have much to do with time. It does though: which data do you compress? The old data. Why not let the database figure that out for you, so you don't specifically have to tell it. Other features include: - Continuous Aggregates: a materialized view aggregating data over time is doable, but why not let the database materialize it for you, and automatically fall back to an un-materialized query for the newest data? - Retention: deleting (or downsampling) old data is easy to do on your own, but why not let the database do it for you according to a policy?