系统设计之 time-series database

time-series database, 时序数据库。（其他写法: TSDB, time series database)
分享下自己收集的系统设计资料和经验。同时欢迎交流指正。


- Challenge and opportunity
    - scale of data
    - a****apend-only data(or insert-only)****
    - always timestamped and ordered by timestamp
- Use cases
    - Time-series data is primarily used for running analytics and deducing conclusions.
    - A time series database usually has two main functions -- ingest a lot of data in chronological order, and provide flexible queries on ranges of that data.
- When to NOT use it
    - if your data is not timestamped
    - if your data is not huge, then a SQL database can handle it as well
- The design of these systems with time as a key index is distinctly different from [relational databases](https://en.wikipedia.org/wiki/Relational_database) which reduce discrete relationships through referential models.
- The unique properties of time series datasets mean that time series databases can provide significant improvements in storage space and performance over general purpose databases.
    - For instance, due to the uniformity of time series data, specialized compression algorithms can provide improvements over regular compression algorithms designed to work on less uniform data.
- Time series databases can also be configured to regularly delete old data, unlike regular databases which are designed to store data indefinitely
    - ****downsampling****
    - TimescaleDB allows you to reduce the granularity of your data by aggregating data into coarser periods of time and dropping the finer-grained real-time data while maintaining data accuracy.
- Special [database indices](https://en.wikipedia.org/wiki/Database_index) can also provide boosts in query performance.
    - *TimescaleDB showed 200% to 5400% faster queries than MongoDB during our benchmark evaluation.*

下面是重点
- Functional Requirements:
    - Windowing functions
    - Sharding capabilities
    - TTL suporrt
    - Aggregate pipelines.
- Design philosophy
    - Leverage the aforementioned characteristics
        - insert only
        - timestamped
        - older data is less queried
    - Main ideas
        - Sharding
        - Right data structure
        - Other smaller tools/components
- A few design methodologies(there are multiple different ideas)
    - [Log-structured merge (LSM) trees](http://www.cs.umb.edu/~poneil/lsmtree.pdf), like Influx DB
        - shards will be created for each 7 day block of time
    - Build on top of SQL database with chunks, like timescale DB
    - others
- Some other considerations
  - compaction
  - retention
  - indexing

如果想深入理解细节，可以读下参考资料。
[Educative.io](http://Educative.io) - ****Time Series Database****

[https://www.educative.io/courses ... ure-101/7nVw0ZWxM9w](https://www.educative.io/courses ... ure-101/7nVw0ZWxM9w)

****Time-series data: Why (and how) to use a relational database instead of NoSQL****

[https://www.timescale.com/blog/t ... nosql-d0cd6975e87c/](https://www.timescale.com/blog/t ... nosql-d0cd6975e87c/)

TimescaleDB - ****Architecture & Concepts****

[https://legacy-docs.timescale.com/v1.7/introduction/architecture](https://legacy-docs.timescale.com/v1.7/introduction/architecture)

**In-memory indexing and the Time-Structured Merge Tree (TSM)**

[https://docs.influxdata.com/infl ... pts/storage_engine/](https://docs.influxdata.com/infl ... pts/storage_engine/)

[https://en.wikipedia.org/wiki/Time_series_database](https://en.wikipedia.org/wiki/Time_series_database)

Druid Architecture

[https://druid.apache.org/docs/latest/design/architecture.html](https://druid.apache.org/docs/latest/design/architecture.html)

这个我也有一点经验。

>     - a****apend-only data(or insert-only)****
>    - always timestamped and ordered by timestamp

Timeseries DB很多都是需要pre-aggregate(rollup)的。如果不做pre-aggregation，查询就会很慢，但是怎么aggregate就是个挺难的问题。因为你的目标是查询，要根据怎么查询来aggregate，比如哪些field是作为dimension（查询的时候可以filter的），哪些field是作为metric（查询的时候可以aggregation的，比如Sum，Average）。

如果build rollup，那么就很难让数据mutable。如果要让数据mutable，那么是synchronous还是async。Async性能好但是数据有delay，sync数据near real time但是性能差。

是否允许late-arriving data。如果不允许那有些需要backfill old data的scenario都不支持了；如果允许，late-arriving data，怎么处理rollup。因为数据室rollup的，late-arriving data往往远小于rollup data，是否需要merge进去，不merge进去的话，这小份的数据会造成unbalanced load。

是否支持joins。Timeseries DB的joins比RDBMS更复杂。尤其是SQL Query的planning部分。

还有，海量的historical data，怎么进行backfill。是用Spark或者Hadoop之类的呢，还是自己的引擎慢慢ingest（可能会很慢很慢）。

这是挺复杂的问题，建议参考一下Clickhouse和Apache Druid的design：
1. https://clickhouse.com/docs/en/d ... ted-query-execution （用的Merge Tree，不是LSM Tree）
2. https://druid.apache.org/docs/la ... html#storage-design

beer 发表于 2022-11-29 13:57
这个我也有一点经验。

>     - a****apend-only data(or insert-only)****

赞。请教大牛几个问题：
1. 为啥clickhouse 算time-series DB，我看很少地方提到算time-series DB。如果clickhosue算，那其他几个pinot之类的是不是也算？
2. Druid好像有提到。但我看它架构好像也不完全是为了time-series DB设计，所以也没认真看。Druid最常见的应用场景都有哪些？
3. 能展开聊聊influxDB和timescaleDB是怎么从设计上支持你说的pre-aggregation的吗？感觉非常有趣。