Hacker News Reader

Comment by georgewfraser | Hacker News Reader

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

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.

View on HN

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

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.

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

Clickhouse is a distributed relational columnar database. It competes with MemSQL, Vertica, Actian, Greenplum, and hosted options like Redshift, Bigquery, Snowflake, etc.

I assume they were pointing out you don't need to go to a commercial offering.

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.

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

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.

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.

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

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.

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?

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.

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.

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.

Reply on news.ycombinator.com