4 ms·

A bit of a side question, but how slow would it be if we were to query the data without using a time-based approach? I.e. "select * from obj where parent_id = <

A bit of a side question, but how slow would it be if we were to query the data without using a time-based approach? I.e. "select * from obj where parent_id = <x>" --> Imagine we had billions of obj and a few thousands matching "parent_id" added randomly during the last 5-10 years. Would there be an index on "parent_id" or would it need to read every hypertable for such a query? Basically, I'm trying to understand if Timescale is /only/ useful for timeseries or if it has good-enough performance for other use-cases.

mfreed 6y ago
Internally, a hypertable "partitions" data into 1+ dimensions, typically always by time, but also by other dimensions (esp. for multi-node). Each of these partitions are called a "chunk" (and are actually Postgres tables within the DB). You can also define arbitrary indexes on a hypertable; practically, these index definitions get "pushed down" to all chunks, so an index is built on each chunk. Queries have two-stages: 1. Using "constraint exclusion" by looking at the SQL query and constaints on each chunk (e.g., their time interval), exclude certain chunks for the query. 2. "Push down" the query (often in parallel) to all non-excluded chunks, and collect/aggregate those results. Various features in TimescaleDB will help even in your case: 1. You get parallelism to scan across chunks. If employing multi-node (TimescaleDB 2.x), you can parallelize these aggregations across servers. 2. You can employ native columnar compression, which gets like 94-97% compression rates, and allows you to organize your data based on selected keys (the "segmentby" parameter). So if you "segmentby" parent_id, it really collocates that data together, and enables much faster query. Thanks for quesiton! https://docs.timescale.com/ https://docs.timescale.com/ https://docs.timescale.com/latest/using-timescaledb/compression https://docs.timescale.com/latest/using-timescaledb/compress... https://slack.timescale.com// https://slack.timescale.com//
- yamrzou 6y ago
  Another side question, please. How does TimescaleDB compare to Clickhouse? Since you mentioned compression, I've been using Clickhouse to store bitemporal data, and have been amazed by its speed and compression levels. Unfortunately it's lacking in terms of relational modeling. Would I get the best of two worlds with TimescaleDB? What are the tradeoffs?
  - lima 6y ago
    Compression and aggregation performance in TimescaleDB is much worse than ClickHouse - it represents a different tradeoff, you trade relational features for raw speed. TimescaleDB is basically (very) fancy PostgreSQL sharding and row-oriented, while ClickHouse is a column store. Depending on what you need to do, ClickHouse dictionaries and JOINs might be good enough.
    - akulkarni 6y ago
      > Compression and aggregation performance in TimescaleDB is much worse than ClickHouse You are likely basing this off on an old version of TimescaleDB. For the last year TimescaleDB has included native compression (in part by storing data in a columnar format): https://blog.timescale.com/blog/building-columnar-compression-in-a-row-oriented-database/ https://blog.timescale.com/blog/building-columnar-compressio... TimescaleDB now implements delta-delta, Gorilla, and other best-in-class compression algorithms: https://blog.timescale.com/blog/time-series-compression-algorithms-explained/ https://blog.timescale.com/blog/time-series-compression-algo... This has yielded 94%+ compression, which should make TimescaleDB and Clickhouse fairly similar in storage compression.
    - ants_a 6y ago
      There is no technical tradeoff here. PostgreSQL could add a batched, vectorized and JITed execution engine, and ClickHouse could add "relational features". Either one would of course be a significant engineering project, but there is no fundamental breakthrough required. A small matter of programming as they say.
- xyzzy_plugh 6y ago
  I haven't read all your docs exhaustively, but how do you solve joining your parallelized query results without running into spill problems? Or rather, how do you solve for spill? Once your hypertables balloon to large sizes, you may be able to store handily across many Postgres instances, but collating the sub-queries will be costly. Spilling to e.g. S3 is a potential solution, but brings with it a new bag of problems, not to mention cost.

mfreed 6y ago

Internally, a hypertable "partitions" data into 1+ dimensions, typically always by time, but also by other dimensions (esp. for multi-node). Each of these partitions are called a "chunk" (and are actually Postgres tables within the DB). You can also define arbitrary indexes on a hypertable; practically, these index definitions get "pushed down" to all chunks, so an index is built on each chunk. Queries have two-stages: 1. Using "constraint exclusion" by looking at the SQL query and constaints on each chunk (e.g., their time interval), exclude certain chunks for the query. 2. "Push down" the query (often in parallel) to all non-excluded chunks, and collect/aggregate those results. Various features in TimescaleDB will help even in your case: 1. You get parallelism to scan across chunks. If employing multi-node (TimescaleDB 2.x), you can parallelize these aggregations across servers. 2. You can employ native columnar compression, which gets like 94-97% compression rates, and allows you to organize your data based on selected keys (the "segmentby" parameter). So if you "segmentby" parent_id, it really collocates that data together, and enables much faster query. Thanks for quesiton! https://docs.timescale.com/ https://docs.timescale.com/ https://docs.timescale.com/latest/using-timescaledb/compression https://docs.timescale.com/latest/using-timescaledb/compress... https://slack.timescale.com// https://slack.timescale.com//

yamrzou 6y ago
Another side question, please. How does TimescaleDB compare to Clickhouse? Since you mentioned compression, I've been using Clickhouse to store bitemporal data, and have been amazed by its speed and compression levels. Unfortunately it's lacking in terms of relational modeling. Would I get the best of two worlds with TimescaleDB? What are the tradeoffs?
- lima 6y ago
  Compression and aggregation performance in TimescaleDB is much worse than ClickHouse - it represents a different tradeoff, you trade relational features for raw speed. TimescaleDB is basically (very) fancy PostgreSQL sharding and row-oriented, while ClickHouse is a column store. Depending on what you need to do, ClickHouse dictionaries and JOINs might be good enough.
  - akulkarni 6y ago
    > Compression and aggregation performance in TimescaleDB is much worse than ClickHouse You are likely basing this off on an old version of TimescaleDB. For the last year TimescaleDB has included native compression (in part by storing data in a columnar format): https://blog.timescale.com/blog/building-columnar-compression-in-a-row-oriented-database/ https://blog.timescale.com/blog/building-columnar-compressio... TimescaleDB now implements delta-delta, Gorilla, and other best-in-class compression algorithms: https://blog.timescale.com/blog/time-series-compression-algorithms-explained/ https://blog.timescale.com/blog/time-series-compression-algo... This has yielded 94%+ compression, which should make TimescaleDB and Clickhouse fairly similar in storage compression.
  - ants_a 6y ago
    There is no technical tradeoff here. PostgreSQL could add a batched, vectorized and JITed execution engine, and ClickHouse could add "relational features". Either one would of course be a significant engineering project, but there is no fundamental breakthrough required. A small matter of programming as they say.
xyzzy_plugh 6y ago
I haven't read all your docs exhaustively, but how do you solve joining your parallelized query results without running into spill problems? Or rather, how do you solve for spill? Once your hypertables balloon to large sizes, you may be able to store handily across many Postgres instances, but collating the sub-queries will be costly. Spilling to e.g. S3 is a potential solution, but brings with it a new bag of problems, not to mention cost.