6 ms·

Reading this post is frustrating. What they are describing is where column store databases were 20 years ago. Perhaps at some point the folks at TimescaleDB wil

Reading this post is frustrating. What they are describing is where column store databases were 20 years ago. Perhaps at some point the folks at TimescaleDB will read Daniel Abadi’s 2008 paper, which describes the key elements of how all modern column stores work: http://db.csail.mit.edu/pubs/abadi-column-stores.pdf http://db.csail.mit.edu/pubs/abadi-column-stores.pdf The key takeaway is that columnar compression only accounts for a small minority of the speed up that you get for scan-oriented workloads; the real big win comes when you implement a block-oriented query processor and pipelined execution. Of course you can’t do this by building inside the Postgres codebase, which is why every good column store is built more or less from scratch. Anyone considering a “time series database” should first set up a modern commercial column store, partition their tables on the time column, and time their workload. For any scan-oriented workload, it will crush a row store like Timescale.

grumpydba 7y ago
> Anyone considering a “time series database” should first set up a modern commercial column store, partition their tables on the time column, and time their workload. For any scan-oriented workload, it will crush a row store like Timescale. Or you can set up a clickhouse instance. It's a seriously promising and underrated product.
- manigandham 7y ago
  Clickhouse is a distributed relational columnar database. It competes with MemSQL, Vertica, Actian, Greenplum, and hosted options like Redshift, Bigquery, Snowflake, etc.
  - IanCal 7y ago
    I assume they were pointing out you don't need to go to a commercial offering.
- atombender 7y ago
  Clickhouse is good, but it's definitely made for a very limited purpose; it's not a general purpose SQL database. Which is fine, but the attraction with something like TimescaleDB is that your time series data can coexist with normal data.
  - IanCal 7y ago
    I don't know if I'd say very limited, I've used it to do a lot of standard SQL stuff where my workload wasn't time series at all but was aggregations and analytical workloads.
  - PeterZaitsev 7y ago
    Many Time-Series applications do not need super complicated SQL. This is why there are many timeseries focused databases even without SQL support. There is also PostgreSQL Foreign Data Wrapper for ClickHouse which allows you to run all SQL PostgreSQL support and often with great performance
    - buremba 7y ago
      If you use Postgresql's query engine on the Clickhouse data, you lose all the benefits of the columnar query engine of Clickhouse so that's not correct.
      - grumpydba 7y ago
        No you don't lose them. Fdw supports push down of where clauses, only selects the required columns. You can also create views in clickhouse to make sur the joins are processed there.
        buremba 7y ago
        You're right but if the syntax that you're using is not supported in Clickhouse, aggregate and predicate pushdowns won't work and this FDW (https://github.com/adjust/clickhouse_fdw https://github.com/adjust/clickhouse_fdw) needs to map all the Postgresql functions / produces to Clickhouse in order to take advantage of push-down so the only use-case here is that you may want to join the data in Clickhouse with the data in Postgresql (or other FDW sources).
mamcx 7y ago
I think this is fair. The whole selling point is to have time series abilities inside of PG. It will not be as fast as a tailored engine? Sure. But the advantage of stay inside of PG is very great (not only ecosystem, but have sql, OLTP + OLAP, etc). Is like how a document store could be better than add JSON with PG... In practique? I think this hybrid approach is very good. P.D: And what about the opposite? Which columnar store can I use for OLTP?
- GordonS 7y ago
  I was going to say something similar. We have TimescaleDB in production with hundreds of millions of time series data sat alongside "regular" data - having to deploy a single database instead of Postgres and a specialised time series database is a real boon. For us, TimescaleDB queries run way faster than stock Postgres. They might well run faster in something specialised, but querying/aggregating hundreds of millions of rows in 10s or 100s of milliseconds is plenty fast enough for many use cases.
manigandham 7y ago
True, but this is a major step up from what Timescale offered before. This was always my biggest contention since performance lagged behind many other datastores but they have done well in taking advantage of the Postgres integration and ecosystem. There are plenty of scenarios that benefit from having automatic time-based partitioning and querying within the same operational PG datastore instead of running a separate analytical system. The improved performance only helps this case.
mfreed 7y ago
Hi @georgewfraser: We understand this point, and you aren't the first to mention it on HN =) But our users, and the users of most so-called "time series databases", do not typically generate the classic scan-oriented workloads of OLAP systems that motivated C-Store and classic columnar data warehouses. I talk about this in the post: sure, there are deep and narrow queries, but there are also a /lot/ of shallow and wide queries. Most real-time dashboarding, which is a huge use-case for IT/devops monitoring and IoT sensor monitoring, is about "what happened very recently". But you do occasionally want to look back, so you want to keep old data sitting around, just in a cost-effective manner. And there is a lot of data: time-series and monitoring applications are typically write (not read) heavy, and ingest high volumes of data continuously. Enter compression and data tiering, both of which appeared in today's v1.5 release. As such, the primary motivation behind our work on compression was actually saving storage overhead, not improving query performance. Although we'll never pass up on performance improvements when we get them! =) (And we are familiar with the citation -- I've known Daniel and Sam for many years, and one of the engineers who led this work at Timescale was actually Daniel's former PhD advisee Josh Lockerman.)
- georgewfraser 7y ago
  Thank you for responding. The “shallow and wide” query you describe is not a challenge for any recently built column store, which will partition the data into blocks of 10-100 MB. So when you run a query like: select * from time_series where ingest_time > ? You will only scan the most recent block, and it will be extremely fast. I have no doubt that there are individual queries where a time-series database will “win”, but I earnestly question whether there’s a real-world use case, with a mix of queries, where a special purpose time-series db is preferable to a good column store with time-based partitioning.
m0zg 7y ago
Um, no. Columnar compression can be huge if your data can be sorted favorably. You basically get to filter (both compare and skip) and aggregate without fully decompressing. If you have to scan a column (or a few), this can be huge. It's great if you can skip blocks within the column or entire shards, but that's unlikely to happen unless you're doing a highly selective query. OLAP queries aggregate a ton of data. Judiciously chosen column compression scheme coupled with a good sort order can be a huge, huge advantage.

grumpydba 7y ago

> Anyone considering a “time series database” should first set up a modern commercial column store, partition their tables on the time column, and time their workload. For any scan-oriented workload, it will crush a row store like Timescale. Or you can set up a clickhouse instance. It's a seriously promising and underrated product.

manigandham 7y ago
Clickhouse is a distributed relational columnar database. It competes with MemSQL, Vertica, Actian, Greenplum, and hosted options like Redshift, Bigquery, Snowflake, etc.
- IanCal 7y ago
  I assume they were pointing out you don't need to go to a commercial offering.
atombender 7y ago
Clickhouse is good, but it's definitely made for a very limited purpose; it's not a general purpose SQL database. Which is fine, but the attraction with something like TimescaleDB is that your time series data can coexist with normal data.
- IanCal 7y ago
  I don't know if I'd say very limited, I've used it to do a lot of standard SQL stuff where my workload wasn't time series at all but was aggregations and analytical workloads.
- PeterZaitsev 7y ago
  Many Time-Series applications do not need super complicated SQL. This is why there are many timeseries focused databases even without SQL support. There is also PostgreSQL Foreign Data Wrapper for ClickHouse which allows you to run all SQL PostgreSQL support and often with great performance
  - buremba 7y ago
    If you use Postgresql's query engine on the Clickhouse data, you lose all the benefits of the columnar query engine of Clickhouse so that's not correct.
    - grumpydba 7y ago
      No you don't lose them. Fdw supports push down of where clauses, only selects the required columns. You can also create views in clickhouse to make sur the joins are processed there.
      - buremba 7y ago
        You're right but if the syntax that you're using is not supported in Clickhouse, aggregate and predicate pushdowns won't work and this FDW (https://github.com/adjust/clickhouse_fdw https://github.com/adjust/clickhouse_fdw) needs to map all the Postgresql functions / produces to Clickhouse in order to take advantage of push-down so the only use-case here is that you may want to join the data in Clickhouse with the data in Postgresql (or other FDW sources).

mamcx 7y ago

I think this is fair. The whole selling point is to have time series abilities inside of PG. It will not be as fast as a tailored engine? Sure. But the advantage of stay inside of PG is very great (not only ecosystem, but have sql, OLTP + OLAP, etc). Is like how a document store could be better than add JSON with PG... In practique? I think this hybrid approach is very good. P.D: And what about the opposite? Which columnar store can I use for OLTP?

GordonS 7y ago
I was going to say something similar. We have TimescaleDB in production with hundreds of millions of time series data sat alongside "regular" data - having to deploy a single database instead of Postgres and a specialised time series database is a real boon. For us, TimescaleDB queries run way faster than stock Postgres. They might well run faster in something specialised, but querying/aggregating hundreds of millions of rows in 10s or 100s of milliseconds is plenty fast enough for many use cases.

manigandham 7y ago

True, but this is a major step up from what Timescale offered before. This was always my biggest contention since performance lagged behind many other datastores but they have done well in taking advantage of the Postgres integration and ecosystem. There are plenty of scenarios that benefit from having automatic time-based partitioning and querying within the same operational PG datastore instead of running a separate analytical system. The improved performance only helps this case.

mfreed 7y ago

Hi @georgewfraser: We understand this point, and you aren't the first to mention it on HN =) But our users, and the users of most so-called "time series databases", do not typically generate the classic scan-oriented workloads of OLAP systems that motivated C-Store and classic columnar data warehouses. I talk about this in the post: sure, there are deep and narrow queries, but there are also a /lot/ of shallow and wide queries. Most real-time dashboarding, which is a huge use-case for IT/devops monitoring and IoT sensor monitoring, is about "what happened very recently". But you do occasionally want to look back, so you want to keep old data sitting around, just in a cost-effective manner. And there is a lot of data: time-series and monitoring applications are typically write (not read) heavy, and ingest high volumes of data continuously. Enter compression and data tiering, both of which appeared in today's v1.5 release. As such, the primary motivation behind our work on compression was actually saving storage overhead, not improving query performance. Although we'll never pass up on performance improvements when we get them! =) (And we are familiar with the citation -- I've known Daniel and Sam for many years, and one of the engineers who led this work at Timescale was actually Daniel's former PhD advisee Josh Lockerman.)

georgewfraser 7y ago
Thank you for responding. The “shallow and wide” query you describe is not a challenge for any recently built column store, which will partition the data into blocks of 10-100 MB. So when you run a query like: select * from time_series where ingest_time > ? You will only scan the most recent block, and it will be extremely fast. I have no doubt that there are individual queries where a time-series database will “win”, but I earnestly question whether there’s a real-world use case, with a mix of queries, where a special purpose time-series db is preferable to a good column store with time-based partitioning.

m0zg 7y ago

Um, no. Columnar compression can be huge if your data can be sorted favorably. You basically get to filter (both compare and skip) and aggregate without fully decompressing. If you have to scan a column (or a few), this can be huge. It's great if you can skip blocks within the column or entire shards, but that's unlikely to happen unless you're doing a highly selective query. OLAP queries aggregate a ton of data. Judiciously chosen column compression scheme coupled with a good sort order can be a huge, huge advantage.