4 ms·

Saying this > Clickhouse (instead of Postgres), so most queries are orders of magnitude faster. is a bold statement. They designed very differently. Try to d

Saying this > Clickhouse (instead of Postgres), so most queries are orders of magnitude faster. is a bold statement. They designed very differently. Try to do O(1) look up with a primary key and see which one is faster? Clickhouse shine in batch queries where you retrieve multiple row at a time. Its 30x faster than Postgres from my benchmark Clickhouse sucks when you need data integrity, primary key can be duplicated. People use MergeTree engine to dedupe, but even so, dedupe doesn't guarantee the absence of duplication primary key. So at query time you have to call Aggregate Function. Inserting small data frequently will also make Clickhouse choke. You cannot simply have it accept random write. Always have to batch. But good thing is ClickHouse has amazing integration with Postgres such as replication through logical replication, foreign data wrapper so you can query Postgres table inside Clickhouse

mritchie712 4y ago
To be clear, I love Postgres. We use Postgres as our app db. But clickhouse is hands down better for analytical workloads[0] (think aggregate queries over a billion rows). > Clickhouse sucks when you need data integrity, primary key can be duplicated. People use MergeTree engine to dedupe, but even so, dedupe doesn't guarantee the absence of duplication primary key. So at query time you have to call Aggregate Function. Agreed. Fortunately we control the processes that insert data and can handle these issues during ingress. > But good thing is ClickHouse has amazing integration with Postgres such as replication through logical replication, foreign data wrapper so you can query Postgres table inside Clickhouse Yes! This was surprisingly easy. We set up a way[1] for users to connect their pg data in Luabase. 0 - https://tech.marksblogg.com/benchmarks.html https://tech.marksblogg.com/benchmarks.html 1 - https://luabase.notion.site/Postgres-c6abe018218d49348ad754982ac4ee25 https://luabase.notion.site/Postgres-c6abe018218d49348ad7549...

mritchie712 4y ago

To be clear, I love Postgres. We use Postgres as our app db. But clickhouse is hands down better for analytical workloads[0] (think aggregate queries over a billion rows). > Clickhouse sucks when you need data integrity, primary key can be duplicated. People use MergeTree engine to dedupe, but even so, dedupe doesn't guarantee the absence of duplication primary key. So at query time you have to call Aggregate Function. Agreed. Fortunately we control the processes that insert data and can handle these issues during ingress. > But good thing is ClickHouse has amazing integration with Postgres such as replication through logical replication, foreign data wrapper so you can query Postgres table inside Clickhouse Yes! This was surprisingly easy. We set up a way[1] for users to connect their pg data in Luabase. 0 - https://tech.marksblogg.com/benchmarks.html https://tech.marksblogg.com/benchmarks.html 1 - https://luabase.notion.site/Postgres-c6abe018218d49348ad754982ac4ee25 https://luabase.notion.site/Postgres-c6abe018218d49348ad7549...