Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data

blog.timescale.com

Post reply on HN
Print view View original

131–140 of 184 posts

Re: Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data

#131

Post by darthShadow »

ryanbooz wrote: post #119
darthShadow wrote: post #95

Earlier quoted context omitted.

Does the disk usage go down later once the numerous parts are merged or not? I would assume it does but reading the article implies that it does not.

Great question. Yes, eventually it does, but (at least for now) it wasn't something we could reliably force as part of the query cycle and know everything was in it's "best" state with ClickHouse. To be honest, we didn't provide the final compressed size of either database because of the need to wait. The code that's currently used by TSBS was submitted by Altinity, a heavy supporter of ClickHouse in the U.S., but TS…

Thanks for the answer.

May be worth pointing that out in the article since the increased disk usage has been mentioned multiple times in the article without any indication that it's only temporary until ClickHouse merges the parts.

Re: Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data

#132

Post by ants_a »

ryanbooz wrote: post #117
ants_a wrote: post #107

Earlier quoted context omitted.

From my experience of benchmarking these databases on scientific data (highly regular timeseries) and looking at the internals of both, these kinds types of number happen when answering the query needs crunching through many rows, but the output has few. i.e. the queries are filtering and/or aggregating a ton of input rows, that can't be excluded by indexes or queried from preaggregations. From what I can tell it com…

(post author) You do a great job summarizing some of the benefits of ClickHouse we mentioned in the post, including the vectorized engine! That said, I'm not sure I'd refer to PostgreSQL/TimescaleDB engine architecture as resembling IE6 JS support. Obviously YMMV, but every release of PG and TimescaleDB bring new advancements to query optimizations for the architecture they are designed for, which was the focus of th…

The optimization engine is of course great (despite occasionally missing hard), but I am not referring to it. I am referring to the way that PostgreSQL executes query plans, the way rows are pulled up the execution tree, is very similar to first iterations JavaScript engines - a tree based interpreter. Picking out columns from rows and evaluating expressions used to work the same until PG11, where we got a bytecode based interpreter and a JIT for those. But so far rows are still working the same way, and it hurts pretty bad when row lookup is cheap and the rows end up either thrown away or aggregated together with basic math.

Re: Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data

#133

Post by mfreed »

ants_a wrote: post #132
ryanbooz wrote: post #117

Earlier quoted context omitted.

(post author) You do a great job summarizing some of the benefits of ClickHouse we mentioned in the post, including the vectorized engine! That said, I'm not sure I'd refer to PostgreSQL/TimescaleDB engine architecture as resembling IE6 JS support. Obviously YMMV, but every release of PG and TimescaleDB bring new advancements to query optimizations for the architecture they are designed for, which was the focus of th…

The optimization engine is of course great (despite occasionally missing hard), but I am not referring to it. I am referring to the way that PostgreSQL executes query plans, the way rows are pulled up the execution tree, is very similar to first iterations JavaScript engines - a tree based interpreter. Picking out columns from rows and evaluating expressions used to work the same until PG11, where we got a bytecode b…

With TimescaleDB compression, 1000 rows of uncompressed data are compressed into column segments, moved to external TOAST pages, and then pointers to these column segments are stored in the table's "row" (along with other statistics, including some common aggregates).

So while the query processor might still be "row-by-row", each "row" it processes actually corresponds to a column segment for which parallelization/vectorization is possible. And because these column segments are TOASTed, the row itself are just pointers, and you only need to read in those compressed column segments that you are actually SELECTing.

Anyway, might have known this, just wanted to clarify. Thanks for discussion!

Re: Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data

#134

Post by FridgeSeal »

claytonjy wrote: post #73
dominotw wrote: post #66

Earlier quoted context omitted.

> Clickhouse the obvious choice for a warehouse > Clickhouse/Snowflake/Redshift. but clickhouse is very unlike the other two. when i think of a warehouse i think star schema, data modeling ect not something that hates joins.

Agreed, I wouldn't use Clickhouse for usual warehouse stuff either, mostly because I can't imagine it plays well with dbt which is a non-starter these days. I'd still argue Clickhouse is closer to Snowflake/Redshift than anything OLTP, and their name is intentionally chosen to evoke warehouse-like scenarios.

What makes you think CH doesn’t like joins?

Having used Redshift, Snowflake and CH for similar workloads, I’d much prefer ClickHouse to the other 2.

Snowflake is hideously expensive for the subpar perf it offers in my experience and Redshift is mediocre at best in general.

Re: Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data

#135

Post by qorrect »

benwilson-512 wrote: post #113
qorrect wrote: post #102

Earlier quoted context omitted.

> Compression is seriously impressive Does this effect your query performance ?

In practice we've seen it actually improve performance, because when fetching a data range for a device fewer actual rows have to be fetched from the disk. You pick certain columns (like device ID) that remain uncompressed and indexed for rapid querying, and then the actual value columns are compressed for a range of time.

Very cool thanks for sharing

> This may mean that you need to put data_starts_at, data_ends_at columns on various other tables in your database to make sure you always know where you find your data.

Do you have a link to docs for this ? Does this mean literally put a first column named (xstartx) and an end column (xendx) as the last column ? How do you then utilize it ?

Thanks so much!

Re: Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data

#136

Post by nojvek »

I’m surprised Timescale hasn’t given a comparison with SingleStoreDB.

I’ve found SingleStore column scans at parity with ClickHouse in speed. At same time SingleStore uses a hybrid skip-list, columnstore data structure in their universal storage (which is default table format).

So you have high throughput transactions, as well as insanely fast aggregate scans.

Usually in column stores, they are great at append, not so much with updates and deletes.

Re: Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data

#137

Post by eternalban »

MichaelRazum wrote: post #112
jurajmasar wrote: post #100

Disclaimer: I'm a co-founder of https://logtail.com , ClickHouse-based hosted log management platform. PostgreSQL, TimescaleDB, and ClickHouse are all impressive pieces of software. We use both PostgreSQL and ClickHouse at Logtail. ClickHouse shines for true OLAP use-cases and is very hard to beat performance-wise when configured properly. Example: > Poor inserts and much higher disk usage (e.g., 2.7x higher disk usa…

Could you explain what you mean by ops heavy? Just curious. Actually have a production system where Timescale as well as clickhouse are running in parallel. So far clickhouse didn't do any trouble, but it is rarely used right now.

>> Disclaimer: I'm a co-founder of https://logtail.com, ClickHouse-based hosted log management platform.

Re: Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data

#139

Post by zepolen »

mrwnmonm wrote: post #138

If you have thousands of clients writing to the database individual rows, one per request, and thousands of clients making queries (some of them are complex, some are not). Does ClickHouse even get used in this scenario?

Yes, Clickhouse is heavily used for storing http access logs which follows that scenario.

Re: Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data

#140

Post by pgwhalen »

sam0x17 wrote: post #18

Can someone give me a real-world example of a scenario where they actually need a time series database, like an example query with the business use case / justification? Just super curious.

Most useful economic or financial markets data is some kind of time series. For example, what was the maximum price of a stock in the last year, and how does it compare to the current price?

FRED (https://fred.stlouisfed.org/) is a great resource for publicly available economic time series data.

Post reply on HN