12 ms·

As much as DuckDB is cute I've mostly come to believe that Clickhouse is the perfect thing to pair Postgres with. This is especially true now that they've acqui

As much as DuckDB is cute I've mostly come to believe that Clickhouse is the perfect thing to pair Postgres with. This is especially true now that they've acquired PeerDB and are integrating it into the Clickpipes cloud product. DuckDB is neat, and I understand why a company like BemiDB would build their product on top of it, but as a prospective customer embedded databases are a weird choice for serious workloads when there are other good open-source solutions like Clickhouse available.

exAspArk 2y ago
ClickHouse is definitely a popular choice nowadays. I'm curious whether you self-host ClickHouse or use their Cloud? We wanted to make BemiDB as simple to run as possible with a single binary and object storage (vs large machines, big disks, clustering, running Temporal for CDC, etc.)
- Onavo 2y ago
  Clickhouse has an embedded version (https://github.com/chdb-io/chdb https://github.com/chdb-io/chdb), the issue with duck is that it's too buggy for production loads. You can see a nice list of the issues here: https://news.ycombinator.com/item?id=41490707 https://news.ycombinator.com/item?id=41490707
maxmcd 2y ago
Using duckdb and apache iceberg means that you can run read replicas without any operational burden. Clickhouse is amazing, but they do not allow you to mount dumb read replicas to object storage (yet). I can imagine this product is a very elegant solution for many types of companies/teams/workloads.
- zX41ZdbW 2y ago
 You can mount read replicas on object storage in ClickHouse. Example: CREATE DATABASE test; USE test; CREATE TABLE hackernews_history UUID '66491946-56e3-4790-a112-d2dc3963e68a' ( `update_time` DateTime DEFAULT now(), `id` UInt32, `deleted` UInt8, `type` Enum8('story' = 1, 'comment' = 2, 'poll' = 3, 'pollopt' = 4, 'job' = 5), `by` LowCardinality(String), `time` DateTime, `text` String, `dead` UInt8, `parent` UInt32, `poll` UInt32, `kids` Array(UInt32), `url` String, `score` Int32, `title` String, `parts` Array(UInt32), `descendants` Int32 ) ENGINE = ReplacingMergeTree(update_time) ORDER BY id SETTINGS disk = disk(readonly = true, type = 's3_plain_rewritable', endpoint = 'https://clicklake-test-2.s3.eu-central-1.amazonaws.com/', use_environment_credentials = false); And you can try it right now. Install ClickHouse: curl https://clickhouse.com/ | sh ./clickhouse local Run the query above to attach the table. The table is updated in real time. For example, here is your comment: :) SELECT * FROM hackernews_history WHERE text LIKE '%Clickhouse is amazing%' ORDER BY update_time \G Row 1: ────── update_time: 2024-04-06 16:35:28 id: 39785472 deleted: 0 type: comment by: mightybyte time: 2024-03-21 22:59:20 text: I'll second this. Clickhouse is amazing. I was actually using it today to query some CSV files. I had to refresh my memory on the syntax so if anyone is interested:<pre><code> clickhouse local -q "SELECT foo, sum(bar) FROM file('foobar.csv', CSV) GROUP BY foo FORMAT Pretty" </code></pre> Way easier than opening in Excel and creating a pivot table which was my previous workflow.Here's a list of the different input and output formats that it supports.<a href="https://clickhouse.com/docs/en/interfaces/formats" rel="nofollow">https://clickhouse.com/docs/en/interfaces/formats</a> dead: 0 parent: 39784942 poll: 0 kids: [39788575] url: score: 0 title: parts: [] descendants: 0 Row 2: ────── update_time: 2024-04-06 18:07:34 id: 31334599 deleted: 0 type: comment by: richieartoul time: 2022-05-11 00:54:31 text: Not really. Clickhouse is amazing, but if you want to run it at massive scale you’ll have to invest a lot into sharding and clustering and all that. Druid is more distributed by default, but doesn’t support as sophisticated of queries as Clickhouse does.Neither Clickhouse nor Druid can hold a candle to what Snowflake can do in terms of query capabilities, as well as the flexibility and richness of their product.That’s just scratching the surface. They’re completely different product categories IMO, although they have a lot of technical / architectural overlap depending on how much you squint.Devil is in the details basically. dead: 0 parent: 31334527 poll: 0 kids: [31334736] url: score: 0 title: parts: [] descendants: 0 Row 3: ────── update_time: 2024-11-07 22:29:09 id: 42081672 deleted: 0 type: comment by: maxmcd time: 2024-11-07 22:13:12 text: Using duckdb and apache iceberg means that you can run read replicas without any operational burden. Clickhouse is amazing, but they do not allow you to mount dumb read replicas to object storage (yet).I can imagine this product is a very elegant solution for many types of companies/teams/workloads. dead: 0 parent: 42080385 poll: 0 kids: [] url: score: 0 title: parts: [] descendants: 0 3 rows in set. Elapsed: 3.981 sec. Processed 42.27 million rows, 14.45 GB (10.62 million rows/s., 3.63 GB/s.) Peak memory usage: 579.26 MiB.
 - maxmcd 2y ago
 Whoops, I forgot that tables maintained in the db are not the same as remote archives. :|
 - zX41ZdbW 2y ago
 The setup in my example uses a table updated by one ClickHouse server and observed by an infinite amount of read-only replicas, which could run anywhere.
 - oulipo 2y ago
 Very interesting, can you give more info on how this could be used for instance in my IoT case where I want to keep the last 3 months (say) of data in Postgres, and dump old data in parquet/iceberg on S3, and be able to do analytical queries on the past data? Would that be hard to do? And how does the real-time update work? Could I make it so that my latest data is incrementally sync'd on S3 (eg "the last 3-months block" is incrementally updated efficiently each time there is new data) ? Do you have example code / setup for this?
 - zX41ZdbW 2y ago
 You can store all data in ClickHouse (on S3 or on local storage); there is no need to separate historical and real-time data. To insert data into ClickHouse, you use the INSERT query to insert data as frequently as you'd like. Alternatively, you can set up continuous replication from Postgres to ClickHouse, which is available in ClickHouse Cloud.
 - oulipo 2y ago
 When I try your code I get this, any idea? Query id: daa202a3-874c-4a68-9e3c-974560ba4624 Elapsed: 0.092 sec. Received exception: Code: 499. DB::Exception: The AWS Access Key Id you provided does not exist in our records. (Code: 23, S3 exception: 'InvalidAccessKeyId'): While processing disk(readonly = true, type = 's3_plain_rewritable', endpoint = 'https://clicklake-test-2.s3.eu-central-1.amazonaws.com/ https://clicklake-test-2.s3.eu-central-1.amazonaws.com/', use_environment_credentials = false). (S3_ERROR)
deleted 2y ago
[deleted]

exAspArk 2y ago

ClickHouse is definitely a popular choice nowadays. I'm curious whether you self-host ClickHouse or use their Cloud? We wanted to make BemiDB as simple to run as possible with a single binary and object storage (vs large machines, big disks, clustering, running Temporal for CDC, etc.)

Onavo 2y ago
Clickhouse has an embedded version (https://github.com/chdb-io/chdb https://github.com/chdb-io/chdb), the issue with duck is that it's too buggy for production loads. You can see a nice list of the issues here: https://news.ycombinator.com/item?id=41490707 https://news.ycombinator.com/item?id=41490707

maxmcd 2y ago

Using duckdb and apache iceberg means that you can run read replicas without any operational burden. Clickhouse is amazing, but they do not allow you to mount dumb read replicas to object storage (yet). I can imagine this product is a very elegant solution for many types of companies/teams/workloads.

zX41ZdbW 2y ago
You can mount read replicas on object storage in ClickHouse. Example: CREATE DATABASE test; USE test; CREATE TABLE hackernews_history UUID '66491946-56e3-4790-a112-d2dc3963e68a' ( `update_time` DateTime DEFAULT now(), `id` UInt32, `deleted` UInt8, `type` Enum8('story' = 1, 'comment' = 2, 'poll' = 3, 'pollopt' = 4, 'job' = 5), `by` LowCardinality(String), `time` DateTime, `text` String, `dead` UInt8, `parent` UInt32, `poll` UInt32, `kids` Array(UInt32), `url` String, `score` Int32, `title` String, `parts` Array(UInt32), `descendants` Int32 ) ENGINE = ReplacingMergeTree(update_time) ORDER BY id SETTINGS disk = disk(readonly = true, type = 's3_plain_rewritable', endpoint = 'https://clicklake-test-2.s3.eu-central-1.amazonaws.com/', use_environment_credentials = false); And you can try it right now. Install ClickHouse: curl https://clickhouse.com/ | sh ./clickhouse local Run the query above to attach the table. The table is updated in real time. For example, here is your comment: :) SELECT * FROM hackernews_history WHERE text LIKE '%Clickhouse is amazing%' ORDER BY update_time \G Row 1: ────── update_time: 2024-04-06 16:35:28 id: 39785472 deleted: 0 type: comment by: mightybyte time: 2024-03-21 22:59:20 text: I'll second this. Clickhouse is amazing. I was actually using it today to query some CSV files. I had to refresh my memory on the syntax so if anyone is interested:<pre><code> clickhouse local -q "SELECT foo, sum(bar) FROM file('foobar.csv', CSV) GROUP BY foo FORMAT Pretty" </code></pre> Way easier than opening in Excel and creating a pivot table which was my previous workflow.Here's a list of the different input and output formats that it supports.<a href="https://clickhouse.com/docs/en/interfaces/formats" rel="nofollow">https://clickhouse.com/docs/en/interfaces/formats</a> dead: 0 parent: 39784942 poll: 0 kids: [39788575] url: score: 0 title: parts: [] descendants: 0 Row 2: ────── update_time: 2024-04-06 18:07:34 id: 31334599 deleted: 0 type: comment by: richieartoul time: 2022-05-11 00:54:31 text: Not really. Clickhouse is amazing, but if you want to run it at massive scale you’ll have to invest a lot into sharding and clustering and all that. Druid is more distributed by default, but doesn’t support as sophisticated of queries as Clickhouse does.Neither Clickhouse nor Druid can hold a candle to what Snowflake can do in terms of query capabilities, as well as the flexibility and richness of their product.That’s just scratching the surface. They’re completely different product categories IMO, although they have a lot of technical / architectural overlap depending on how much you squint.Devil is in the details basically. dead: 0 parent: 31334527 poll: 0 kids: [31334736] url: score: 0 title: parts: [] descendants: 0 Row 3: ────── update_time: 2024-11-07 22:29:09 id: 42081672 deleted: 0 type: comment by: maxmcd time: 2024-11-07 22:13:12 text: Using duckdb and apache iceberg means that you can run read replicas without any operational burden. Clickhouse is amazing, but they do not allow you to mount dumb read replicas to object storage (yet).I can imagine this product is a very elegant solution for many types of companies/teams/workloads. dead: 0 parent: 42080385 poll: 0 kids: [] url: score: 0 title: parts: [] descendants: 0 3 rows in set. Elapsed: 3.981 sec. Processed 42.27 million rows, 14.45 GB (10.62 million rows/s., 3.63 GB/s.) Peak memory usage: 579.26 MiB.
- maxmcd 2y ago
 Whoops, I forgot that tables maintained in the db are not the same as remote archives. :|
 - zX41ZdbW 2y ago
 The setup in my example uses a table updated by one ClickHouse server and observed by an infinite amount of read-only replicas, which could run anywhere.
- oulipo 2y ago
 Very interesting, can you give more info on how this could be used for instance in my IoT case where I want to keep the last 3 months (say) of data in Postgres, and dump old data in parquet/iceberg on S3, and be able to do analytical queries on the past data? Would that be hard to do? And how does the real-time update work? Could I make it so that my latest data is incrementally sync'd on S3 (eg "the last 3-months block" is incrementally updated efficiently each time there is new data) ? Do you have example code / setup for this?
 - zX41ZdbW 2y ago
 You can store all data in ClickHouse (on S3 or on local storage); there is no need to separate historical and real-time data. To insert data into ClickHouse, you use the INSERT query to insert data as frequently as you'd like. Alternatively, you can set up continuous replication from Postgres to ClickHouse, which is available in ClickHouse Cloud.
- oulipo 2y ago
 When I try your code I get this, any idea? Query id: daa202a3-874c-4a68-9e3c-974560ba4624 Elapsed: 0.092 sec. Received exception: Code: 499. DB::Exception: The AWS Access Key Id you provided does not exist in our records. (Code: 23, S3 exception: 'InvalidAccessKeyId'): While processing disk(readonly = true, type = 's3_plain_rewritable', endpoint = 'https://clicklake-test-2.s3.eu-central-1.amazonaws.com/ https://clicklake-test-2.s3.eu-central-1.amazonaws.com/', use_environment_credentials = false). (S3_ERROR)

deleted 2y ago

[deleted]