4 ms·

Is there an ELI5 for this company? I'm having a difficult time understanding it from their website. Is it an alternative to Postgres etc? Something that runs on

Is there an ELI5 for this company? I'm having a difficult time understanding it from their website. Is it an alternative to Postgres etc? Something that runs on top of it? And analyzes your DB automatically?

doix 1y ago
I guess you could say it's an alternative to postgres. It's a different database, that's column oriented which makes different tradeoffs. I'd say DuckDB is a better comparison, if you're familiar with it.
- pythonaut_16 1y ago
  Expanding for the original question: Roughly speaking, Postgres is to SQLite what Clickhouse is to DuckDB. OLTP -> Online Transaction Processing. Postgres and traditional RDBMS. Mainly focused on transactions and addressing specific rows. Queries like "show me all orders for customer X". OLAP -> Online Analytical Processing. Clickhouse and other columnar oriented. For analytical and calculation queries, like "show me the total value of all orders in March 2024". OLTP database typically store data by column rather than row, and usually have optimizations for storage space and query speed based on that. As a tradeoff they're typically slower for OLTP type queries. Often you'd bring in an OLAP db like Clickhouse when you have a huge volume of data and your OLTP database is struggling to keep up.
  - ksynwa 1y ago
    What's the significance of "online" in these acronyms?
    - edoceo 1y ago
      Live and real-time
    - stonemetal12 1y ago
      It is a rather old acronym. The other option was batch processing, you will get your results in the mail type thing. Here "Online" means results while connected to the system, not real time since there is no time requirement for results.
    - IMTDb 1y ago
      Online means you expect the responses to come quickly (seconds) after launching the request. The opposite is "offline" where you expect the results to come a long time after making the request (hours / days). ClickHouse is designed so you can build dashboard with it. Other offline system are designed so you can build reports that you send in PDF over email with them.
lbhdc 1y ago
Its a db company that offers an open source database and cloud managed services. The database is OLAP where Postgres is an OLTP database. Essentially it very fast at complex queries, and is targeted at analytics workloads.
- datavirtue 1y ago
  Postgres has been used as the basis for several OLAP systems. These guys are probably using a modified Greenplum.
  - lbhdc 1y ago
    As far as I am aware it is not a derivative of another database. https://dbdb.io/db/clickhouse https://dbdb.io/db/clickhouse
  - __s 1y ago
    I got to see Citus at Microsoft fail to close against ClickHouse for an internal project ClickHouse spun out of Yandex & is open source, https://github.com/ClickHouse/clickhouse https://github.com/ClickHouse/clickhouse Disclosure: I started at Citus & ended up at ClickHouse
jameslk 1y ago
When Postgres takes a while to answer analytical questions like "what's the 75th percentile of response time for these 900 some billion requests rows, grouped by device, network, and date for the past 30 days", that's when you might want to try out ClickHouse
- NewJazz 1y ago
  I'm struggling with TimescaleDB performance right now and wondering if the grass is greener.
  - applied_heat 1y ago
    What is the workload or query that is causing issues?
    - NewJazz 1y ago
      We denormalized some data then wanted to quickly filter by it. I managed to find a decent index to get us through, but now I'm stuck with another dimension in my data that I'd rather not have. I think I'll have to create a new table, migrate data, then rename it.
  - sukruh 1y ago
    It is.
  - whatevermom 1y ago
    Migrated from TimescaleDB to ClickHouse and it was like night and day. Naive reimplementation of the service performed wayyyy better than timescaledb. Self-hosted.
  - andness 1y ago
    Started migrating away from TimescaleDB some time ago too. Initially we self-hosted to test it out. It was very quickly clear that it was a lot better for our use case and we decided to go with Clickhouse Cloud to not have to worry about the ops. The pricing for the cloud offering is very good IMO. We use it for telemetry data from a fleet of IoT devices.
simantel 1y ago
It's an alternative to Postgres in the sense that they're both databases. Read up on OLAP vs. OLTP to see the difference.
- bbohan 1y ago
  [flagged]
  - davidjfelix 1y ago
    So what's the goal here? Astroturf SingleStore ads in any post about databases?
    - bbohan 1y ago
      Not an ad. You reading the replies in this thread?
      - davidjfelix 1y ago
        Yeah -- I see nobody but you mentioning HTAP and I see every comment on your account talking about the same product. That's astroturfing.
        bbohan 1y ago
        Talking about a product I like is astroturfing? There's mention of Postgres, BigQuery, DuckDB, the whole 9. We can only speak of the big players?
        davidjfelix 1y ago
        Is that the case? It's just a product you like?
whobre 1y ago
It's not like Postgres at all, except on the very superficial level. It is an analytical engine like BigQuery, Snowflake, Teradata, etc...
arecurrence 1y ago
Clickhouse has a wide range of really interesting technologies that are not in Postgres; fundamentally, it's not an OLTP database like Postgres but more-so aimed at OLAP workloads. I really appreciate Clickhouse's focus on performance and quite a bit of work goes into optimizing the memory allocation and operations among different data types. The heart of Clickhouse are these table engines (they don't exist in Postgres) https://clickhouse.com/docs/engines/table-engines https://clickhouse.com/docs/engines/table-engines . The primary column (or columns) is ordered in some way and adjacent values in memory are from the same column in the table. Index entries span wide areas (EG: By default there's only one key record in the primary index for every 8192 rows) because most operations in Clickhouse are aggregate in nature. Inserts are also expected to be in bulk (They are initially a new physical part that is later merged into the main table structure). A single DELETE is an ALTER TABLE operation in the MergeTree engine. :) This structure allows it to literally crunch billions of values per second (brutally, not with pre-processing, erm, "tricks" although there is a lot of support for that in Clickhouse as well). I've had tables with hundreds of columns and 100+ billion rows that are nearly as performant as a million row table if I can structure the query to work with the table's physical ordering. Clickhouse recommends not using nullable fields because of the performance implications (it requires storing a bit somewhere for each value). That's how much they care about perf and how close to the raw data type it is that their memory allocation uses. :)
- porridgeraisin 1y ago
  > Inserts are also expected to be in bulk (They are initially a new physical part that is later merged into the main table structure). A single DELETE is an ALTER TABLE operation in the MergeTree engine. > They are initially a new physical part that is later merged into the main table structure > A single DELETE is an ALTER TABLE operation Can you explain these two further?
  - arecurrence 1y ago
    The Clickhouse docs are so good that I'd point straight to them https://clickhouse.com/docs/sql-reference/statements/alter/delete https://clickhouse.com/docs/sql-reference/statements/alter/d... . The reason I mentioned it is because it's a huge surprise to some people that... from the docs: "The ALTER TABLE prefix makes this syntax different from most other systems supporting SQL. It is intended to signify that unlike similar queries in OLTP databases this is a heavy operation not designed for frequent use. ALTER TABLE is considered a heavyweight operation that requires the underlying data to be merged before it is deleted." There's also a "lightweight delete" available in many circumstances https://clickhouse.com/docs/sql-reference/statements/delete https://clickhouse.com/docs/sql-reference/statements/delete. Something really nice about the ClickHouse docs is that they devote quite a bit of text to describing the design and performance implications of using an operation. It reiterates the focus on performance that is pervasive across the product. Edit: Per the other part of your question, why inserts create new parts and how they are merged is best described here https://clickhouse.com/docs/engines/table-engines/mergetree-family/mergetree#mergetree-data-storage https://clickhouse.com/docs/engines/table-engines/mergetree-...
Silasdev 1y ago
SQL, OLAP, Primary use case is fast aggregations on append only data, like usage analytics. It's fast, it's........ really fast!! But you need to get comfortable with their extended SQL dialect that forces you to think a little different than with usual SQL if you want to keep perf high.
joshstrange 1y ago
If you go into it with MySQL/Postgres knowledge you will probably hate it. Source: me I almost wish it didn’t use SQL so that it was clear how different it is. Nothing works like you are used to, footguns galore, and I hate zookeeper. I’d replace it with Postgres in a heartbeat if I thought I could get away with it, I don’t think our data size really needs CH. Unfortunately, my options are “spin up a Custer on company resources to prove my point” or “spin it up on my own infra” (which is not possible since that would require pulling company data to my servers which I would never do). So instead I’m stuck dealing with CH.

doix 1y ago

I guess you could say it's an alternative to postgres. It's a different database, that's column oriented which makes different tradeoffs. I'd say DuckDB is a better comparison, if you're familiar with it.

pythonaut_16 1y ago
Expanding for the original question: Roughly speaking, Postgres is to SQLite what Clickhouse is to DuckDB. OLTP -> Online Transaction Processing. Postgres and traditional RDBMS. Mainly focused on transactions and addressing specific rows. Queries like "show me all orders for customer X". OLAP -> Online Analytical Processing. Clickhouse and other columnar oriented. For analytical and calculation queries, like "show me the total value of all orders in March 2024". OLTP database typically store data by column rather than row, and usually have optimizations for storage space and query speed based on that. As a tradeoff they're typically slower for OLTP type queries. Often you'd bring in an OLAP db like Clickhouse when you have a huge volume of data and your OLTP database is struggling to keep up.
- ksynwa 1y ago
  What's the significance of "online" in these acronyms?
  - edoceo 1y ago
    Live and real-time
  - stonemetal12 1y ago
    It is a rather old acronym. The other option was batch processing, you will get your results in the mail type thing. Here "Online" means results while connected to the system, not real time since there is no time requirement for results.
  - IMTDb 1y ago
    Online means you expect the responses to come quickly (seconds) after launching the request. The opposite is "offline" where you expect the results to come a long time after making the request (hours / days). ClickHouse is designed so you can build dashboard with it. Other offline system are designed so you can build reports that you send in PDF over email with them.

lbhdc 1y ago

Its a db company that offers an open source database and cloud managed services. The database is OLAP where Postgres is an OLTP database. Essentially it very fast at complex queries, and is targeted at analytics workloads.

datavirtue 1y ago
Postgres has been used as the basis for several OLAP systems. These guys are probably using a modified Greenplum.
- lbhdc 1y ago
  As far as I am aware it is not a derivative of another database. https://dbdb.io/db/clickhouse https://dbdb.io/db/clickhouse
- __s 1y ago
  I got to see Citus at Microsoft fail to close against ClickHouse for an internal project ClickHouse spun out of Yandex & is open source, https://github.com/ClickHouse/clickhouse https://github.com/ClickHouse/clickhouse Disclosure: I started at Citus & ended up at ClickHouse

jameslk 1y ago

When Postgres takes a while to answer analytical questions like "what's the 75th percentile of response time for these 900 some billion requests rows, grouped by device, network, and date for the past 30 days", that's when you might want to try out ClickHouse

NewJazz 1y ago
I'm struggling with TimescaleDB performance right now and wondering if the grass is greener.
- applied_heat 1y ago
  What is the workload or query that is causing issues?
  - NewJazz 1y ago
    We denormalized some data then wanted to quickly filter by it. I managed to find a decent index to get us through, but now I'm stuck with another dimension in my data that I'd rather not have. I think I'll have to create a new table, migrate data, then rename it.
- sukruh 1y ago
  It is.
- whatevermom 1y ago
  Migrated from TimescaleDB to ClickHouse and it was like night and day. Naive reimplementation of the service performed wayyyy better than timescaledb. Self-hosted.
- andness 1y ago
  Started migrating away from TimescaleDB some time ago too. Initially we self-hosted to test it out. It was very quickly clear that it was a lot better for our use case and we decided to go with Clickhouse Cloud to not have to worry about the ops. The pricing for the cloud offering is very good IMO. We use it for telemetry data from a fleet of IoT devices.

simantel 1y ago

It's an alternative to Postgres in the sense that they're both databases. Read up on OLAP vs. OLTP to see the difference.

bbohan 1y ago
[flagged]
- davidjfelix 1y ago
  So what's the goal here? Astroturf SingleStore ads in any post about databases?
  - bbohan 1y ago
    Not an ad. You reading the replies in this thread?
    - davidjfelix 1y ago
      Yeah -- I see nobody but you mentioning HTAP and I see every comment on your account talking about the same product. That's astroturfing.
      - bbohan 1y ago
        Talking about a product I like is astroturfing? There's mention of Postgres, BigQuery, DuckDB, the whole 9. We can only speak of the big players?
        davidjfelix 1y ago
        Is that the case? It's just a product you like?

whobre 1y ago

It's not like Postgres at all, except on the very superficial level. It is an analytical engine like BigQuery, Snowflake, Teradata, etc...

arecurrence 1y ago

Clickhouse has a wide range of really interesting technologies that are not in Postgres; fundamentally, it's not an OLTP database like Postgres but more-so aimed at OLAP workloads. I really appreciate Clickhouse's focus on performance and quite a bit of work goes into optimizing the memory allocation and operations among different data types. The heart of Clickhouse are these table engines (they don't exist in Postgres) https://clickhouse.com/docs/engines/table-engines https://clickhouse.com/docs/engines/table-engines . The primary column (or columns) is ordered in some way and adjacent values in memory are from the same column in the table. Index entries span wide areas (EG: By default there's only one key record in the primary index for every 8192 rows) because most operations in Clickhouse are aggregate in nature. Inserts are also expected to be in bulk (They are initially a new physical part that is later merged into the main table structure). A single DELETE is an ALTER TABLE operation in the MergeTree engine. :) This structure allows it to literally crunch billions of values per second (brutally, not with pre-processing, erm, "tricks" although there is a lot of support for that in Clickhouse as well). I've had tables with hundreds of columns and 100+ billion rows that are nearly as performant as a million row table if I can structure the query to work with the table's physical ordering. Clickhouse recommends not using nullable fields because of the performance implications (it requires storing a bit somewhere for each value). That's how much they care about perf and how close to the raw data type it is that their memory allocation uses. :)

porridgeraisin 1y ago
> Inserts are also expected to be in bulk (They are initially a new physical part that is later merged into the main table structure). A single DELETE is an ALTER TABLE operation in the MergeTree engine. > They are initially a new physical part that is later merged into the main table structure > A single DELETE is an ALTER TABLE operation Can you explain these two further?
- arecurrence 1y ago
  The Clickhouse docs are so good that I'd point straight to them https://clickhouse.com/docs/sql-reference/statements/alter/delete https://clickhouse.com/docs/sql-reference/statements/alter/d... . The reason I mentioned it is because it's a huge surprise to some people that... from the docs: "The ALTER TABLE prefix makes this syntax different from most other systems supporting SQL. It is intended to signify that unlike similar queries in OLTP databases this is a heavy operation not designed for frequent use. ALTER TABLE is considered a heavyweight operation that requires the underlying data to be merged before it is deleted." There's also a "lightweight delete" available in many circumstances https://clickhouse.com/docs/sql-reference/statements/delete https://clickhouse.com/docs/sql-reference/statements/delete. Something really nice about the ClickHouse docs is that they devote quite a bit of text to describing the design and performance implications of using an operation. It reiterates the focus on performance that is pervasive across the product. Edit: Per the other part of your question, why inserts create new parts and how they are merged is best described here https://clickhouse.com/docs/engines/table-engines/mergetree-family/mergetree#mergetree-data-storage https://clickhouse.com/docs/engines/table-engines/mergetree-...

Silasdev 1y ago

SQL, OLAP, Primary use case is fast aggregations on append only data, like usage analytics. It's fast, it's........ really fast!! But you need to get comfortable with their extended SQL dialect that forces you to think a little different than with usual SQL if you want to keep perf high.

joshstrange 1y ago

If you go into it with MySQL/Postgres knowledge you will probably hate it. Source: me I almost wish it didn’t use SQL so that it was clear how different it is. Nothing works like you are used to, footguns galore, and I hate zookeeper. I’d replace it with Postgres in a heartbeat if I thought I could get away with it, I don’t think our data size really needs CH. Unfortunately, my options are “spin up a Custer on company resources to prove my point” or “spin it up on my own infra” (which is not possible since that would require pulling company data to my servers which I would never do). So instead I’m stuck dealing with CH.