7 ms·

I’ve also had some pretty clear successes moving a ~2TB Postgres database into a 70GB Clickhouse instance, (though, in this case a good 2/3 of the issue was sch

I’ve also had some pretty clear successes moving a ~2TB Postgres database into a 70GB Clickhouse instance, (though, in this case a good 2/3 of the issue was scheme design on the PG side, but there were still another order of magnitude compression to be had from clickhouse.) Queries went from a 12 hour table scan to ~2 minutes, and production queries from 45 sec to 400ms. This is without doing anything particularly tricky on the clickhouse side, nor even putting it on a particularly large machine. When benchmarking it, I put it on a 10yr old laptop, and I was seeing production queries in the seconds range,

alecco 6y ago
> Queries went from a 12 hour table scan to ~2 minutes, and production queries from 45 sec to 400ms. Hmm, why do you have such large tables and then do full scans? Edit: and if it's analytics why were you using a row store instead of a columnar database? It's like saying changing to a Ford Mustang was 10x faster than a rusty John Dere combine.
- lumost 6y ago
  Data is the size that it happens to be for many businesses. this could easily have been a large user view history table or similar online use case that folks happened to need analytic capabilities on.
  - wiredfool 6y ago
    In this case, it's the output from ~10k simulations, each over up to a million zones. Production queries will select either one row by primary key, or sum metrics on up to a million rows over a set of the values.
- mastazi 6y ago
  > and if it's analytics why were you using a row store instead of a columnar database? This point confuses me, parent is telling us how they went from a row-based (pgsql) to a column-based DB (Clickhouse), and you asked them why they didn't?
  - alecco 6y ago
    There are many, many ways to avoid a full scan. Fixing a dumb query and proper indexing (partial indexes if this is expensive), materialized views, and several more. It's very common the developers either resist or are not aware of how to use a database. A full scan is dumb and will retrieve all data of this big table without any filtering or even order. This is highly suspicious. And in the very, very rare case it is absolutely necessary to do a full scan, then Postgres might not be a good idea. Why is it being bashed? I read "Postgres was 12h slow" not "we picked the wrong tool and of course Postgres was slow". Don't blame the hammer. IME, it's common for programmers to not be educated on what modern databases can do. This was made worse with all the NoSQL mania ending up with the MongoDB is web scale. We are still dealing with the fallout of that.
    - wiredfool 6y ago
      FWIW, This project was one that I came in at the deployment stage, and the original requirements were for something about 10x smaller. Budget Gone. Shit happens. There were many places where the right choices weren't made for this, and could have been made better. But even with optimizations in the schema speeding things up by a factor of 3, we would have been looking at something that still didn't hit the performance needs. I'm guessing that to do the reorg on the full DB, we would be looking at O(week). Effective indexes are great, but when one optimized index is tens of GB and takes a day to create, you start to look elsewhere.
      - alecco 6y ago
        That story makes a lot of sense. I took issue with your original comment as it was short on details and could be interpreted in a different light.
- bdcravens 6y ago
  > why were you using a row store instead of a columnar database? According to this comment (https://news.ycombinator.com/item?id=26187645 https://news.ycombinator.com/item?id=26187645) they were using a columnar extension in Postgresql.
  - wiredfool 6y ago
    That's not me.
- wiredfool 6y ago
  Why did I do a full scan? Because I wanted to see just how bad it was, and I wanted to see roughly what was in the database. I ran: `select count (distinct foo)` and got ~9000 distinct items over 2B rows in 12 hours. Running the same query in Clickhouse was O(minute) As a little extra comparison, the total size of the Clickhouse database is 1/2 the size of the indexes in Postgres.
riku_iki 6y ago
> ~2TB Postgres database into a 70GB Clickhouse was your PG DB been compressed?
- wiredfool 6y ago
  Parts may have been , but it was ~2B rows at ~ 1K/row in PG, with a couple of embedded json fields (which were probably compressed, but ... that's coming from a bad starting point anyway). It got converted to a table with ~25 Float32 columns + a couple of key columns.
  - ddorian43 6y ago
    Yes it's an 5-apples vs 1-orange comparison, especially the dynamic json fields where keys are repeated.
    - wiredfool 6y ago
      Fixing the PG schema would have saved me about 2/3 of the space. (from a small test run) Good, but not enough to move the needle. Queries on the PG setup here are back to the bad old days of being limited by bulk disk performance.
      - riku_iki 6y ago
        > Good, but not enough to move the needle You could reduce the gap by running PG on compressed filesystem, so it would be more apples to apples comparison.
        ddorian43 6y ago
        It would be 1-apple to 1-orange comparison. If the columnar store has an "ok" architecture, it should provide 10x+ faster queries.
- FridgeSeal 6y ago
  In addition to what OP mentions in the sibling comment, ClickHouse supports a whole bunch of encodings that can take advantage of column based layouts to further compress the columns in very clever ways
zepearl 6y ago
I had as well excellent results when I experimented with Clickhouse (in my case the table with the most rows that I ever created had 6 columns using a total of ~55 bytes per row - after having inserted ~35 billion rows the table was 450MiB big). My usecases involving big aggregations and mini-searches (when searching/retrieving just few rows) were excellent (big aggr) respectively good (small searches - not as fast as a normal DB, but for me for sure acceptable). For whoever is interested to try it out, be aware that you really should understand how "parts" and "merges" work and your write-workload should take that into consideration (for example dynamically lower the workload when "merges" are running, take into consideration the I/O needed to delete pending old "parts", etc...) - if you don't then you're very likely to get bad performance if your workload is write-heavy. The amount of columns and the sort order is as well extremely important. Last year I decided to start using Clickhouse to host Collecd's metrics ( https://www.segfault.digital/how-to-directly-use-the-clickhouse-db-with-grafana-and-collectd https://www.segfault.digital/how-to-directly-use-the-clickho... ) and so far it's working well. I upgraded twice the version of the CH-software, and killed once by mistake its VM but afterwards I never had problems ("so far", hehe).
- wiredfool 6y ago
  Yeah, DoubleDelta + compression is amazing. I have one column that's an incrementing integer per partition, and for a million rows the total storage for that field is ~20k. That's a savings of something like 10% over the whole dataset.
  - zepearl 6y ago
    > That's a savings of something like 10% over the whole dataset. Sorry, I'm probably misunderstanding you - I'm not sure about that "10%", what does it it refer to? Anyway, I do like a lot in Clickhouse to be able to chain codecs (I personally like to think about "Delta/DoubleDelta/Gorilla/T64 codecs" being "encodings" and the "general purpose codecs LZ4/LZ4HC/ZSTD" being "compression codecs"). I don't have a good math background => about Delta & DoubleDelta I liked this explanation ( https://altinity.com/blog/2019/7/new-encodings-to-improve-clickhouse https://altinity.com/blog/2019/7/new-encodings-to-improve-cl... ) which defines "delta" as tracking "distance" and "doubledelta" tracking "acceleration" between consecutive column values. In the end I tested, using relatively small datasets (which were different between usecases), all combinations of encodings (delta/doubledelta/gorilla/t64) and ZSTD-compression levels (mostly 1/3/9) (I ignored LZ4*). - "Delta"&"DoubleDelta" were often interesting (but in general for my data using "Delta"+ZSTD was already good enough compared to the rest). - "Gorilla" somehow never gave me any benefits if compared to other codecs and/or compression algos. - "T64" is a bit a mistery for me, anyway in some tests it delivered excellent results compared to the other combinations, therefore I'm currently using just T64 for some columns, and for some other columns as T64+ZSTD(9). EDIT: sorry, I think I got it - you probably meant something like "just by doing that on that specific column, the overall storage needs were reduced by 10% for the whole dataset", right?

alecco 6y ago

> Queries went from a 12 hour table scan to ~2 minutes, and production queries from 45 sec to 400ms. Hmm, why do you have such large tables and then do full scans? Edit: and if it's analytics why were you using a row store instead of a columnar database? It's like saying changing to a Ford Mustang was 10x faster than a rusty John Dere combine.

lumost 6y ago
Data is the size that it happens to be for many businesses. this could easily have been a large user view history table or similar online use case that folks happened to need analytic capabilities on.
- wiredfool 6y ago
  In this case, it's the output from ~10k simulations, each over up to a million zones. Production queries will select either one row by primary key, or sum metrics on up to a million rows over a set of the values.
mastazi 6y ago
> and if it's analytics why were you using a row store instead of a columnar database? This point confuses me, parent is telling us how they went from a row-based (pgsql) to a column-based DB (Clickhouse), and you asked them why they didn't?
- alecco 6y ago
  There are many, many ways to avoid a full scan. Fixing a dumb query and proper indexing (partial indexes if this is expensive), materialized views, and several more. It's very common the developers either resist or are not aware of how to use a database. A full scan is dumb and will retrieve all data of this big table without any filtering or even order. This is highly suspicious. And in the very, very rare case it is absolutely necessary to do a full scan, then Postgres might not be a good idea. Why is it being bashed? I read "Postgres was 12h slow" not "we picked the wrong tool and of course Postgres was slow". Don't blame the hammer. IME, it's common for programmers to not be educated on what modern databases can do. This was made worse with all the NoSQL mania ending up with the MongoDB is web scale. We are still dealing with the fallout of that.
  - wiredfool 6y ago
    FWIW, This project was one that I came in at the deployment stage, and the original requirements were for something about 10x smaller. Budget Gone. Shit happens. There were many places where the right choices weren't made for this, and could have been made better. But even with optimizations in the schema speeding things up by a factor of 3, we would have been looking at something that still didn't hit the performance needs. I'm guessing that to do the reorg on the full DB, we would be looking at O(week). Effective indexes are great, but when one optimized index is tens of GB and takes a day to create, you start to look elsewhere.
    - alecco 6y ago
      That story makes a lot of sense. I took issue with your original comment as it was short on details and could be interpreted in a different light.
bdcravens 6y ago
> why were you using a row store instead of a columnar database? According to this comment (https://news.ycombinator.com/item?id=26187645 https://news.ycombinator.com/item?id=26187645) they were using a columnar extension in Postgresql.
- wiredfool 6y ago
  That's not me.
wiredfool 6y ago
Why did I do a full scan? Because I wanted to see just how bad it was, and I wanted to see roughly what was in the database. I ran: `select count (distinct foo)` and got ~9000 distinct items over 2B rows in 12 hours. Running the same query in Clickhouse was O(minute) As a little extra comparison, the total size of the Clickhouse database is 1/2 the size of the indexes in Postgres.

riku_iki 6y ago

> ~2TB Postgres database into a 70GB Clickhouse was your PG DB been compressed?

wiredfool 6y ago
Parts may have been , but it was ~2B rows at ~ 1K/row in PG, with a couple of embedded json fields (which were probably compressed, but ... that's coming from a bad starting point anyway). It got converted to a table with ~25 Float32 columns + a couple of key columns.
- ddorian43 6y ago
  Yes it's an 5-apples vs 1-orange comparison, especially the dynamic json fields where keys are repeated.
  - wiredfool 6y ago
    Fixing the PG schema would have saved me about 2/3 of the space. (from a small test run) Good, but not enough to move the needle. Queries on the PG setup here are back to the bad old days of being limited by bulk disk performance.
    - riku_iki 6y ago
      > Good, but not enough to move the needle You could reduce the gap by running PG on compressed filesystem, so it would be more apples to apples comparison.
      - ddorian43 6y ago
        It would be 1-apple to 1-orange comparison. If the columnar store has an "ok" architecture, it should provide 10x+ faster queries.
FridgeSeal 6y ago
In addition to what OP mentions in the sibling comment, ClickHouse supports a whole bunch of encodings that can take advantage of column based layouts to further compress the columns in very clever ways

zepearl 6y ago

I had as well excellent results when I experimented with Clickhouse (in my case the table with the most rows that I ever created had 6 columns using a total of ~55 bytes per row - after having inserted ~35 billion rows the table was 450MiB big). My usecases involving big aggregations and mini-searches (when searching/retrieving just few rows) were excellent (big aggr) respectively good (small searches - not as fast as a normal DB, but for me for sure acceptable). For whoever is interested to try it out, be aware that you really should understand how "parts" and "merges" work and your write-workload should take that into consideration (for example dynamically lower the workload when "merges" are running, take into consideration the I/O needed to delete pending old "parts", etc...) - if you don't then you're very likely to get bad performance if your workload is write-heavy. The amount of columns and the sort order is as well extremely important. Last year I decided to start using Clickhouse to host Collecd's metrics ( https://www.segfault.digital/how-to-directly-use-the-clickhouse-db-with-grafana-and-collectd https://www.segfault.digital/how-to-directly-use-the-clickho... ) and so far it's working well. I upgraded twice the version of the CH-software, and killed once by mistake its VM but afterwards I never had problems ("so far", hehe).

wiredfool 6y ago
Yeah, DoubleDelta + compression is amazing. I have one column that's an incrementing integer per partition, and for a million rows the total storage for that field is ~20k. That's a savings of something like 10% over the whole dataset.
- zepearl 6y ago
  > That's a savings of something like 10% over the whole dataset. Sorry, I'm probably misunderstanding you - I'm not sure about that "10%", what does it it refer to? Anyway, I do like a lot in Clickhouse to be able to chain codecs (I personally like to think about "Delta/DoubleDelta/Gorilla/T64 codecs" being "encodings" and the "general purpose codecs LZ4/LZ4HC/ZSTD" being "compression codecs"). I don't have a good math background => about Delta & DoubleDelta I liked this explanation ( https://altinity.com/blog/2019/7/new-encodings-to-improve-clickhouse https://altinity.com/blog/2019/7/new-encodings-to-improve-cl... ) which defines "delta" as tracking "distance" and "doubledelta" tracking "acceleration" between consecutive column values. In the end I tested, using relatively small datasets (which were different between usecases), all combinations of encodings (delta/doubledelta/gorilla/t64) and ZSTD-compression levels (mostly 1/3/9) (I ignored LZ4*). - "Delta"&"DoubleDelta" were often interesting (but in general for my data using "Delta"+ZSTD was already good enough compared to the rest). - "Gorilla" somehow never gave me any benefits if compared to other codecs and/or compression algos. - "T64" is a bit a mistery for me, anyway in some tests it delivered excellent results compared to the other combinations, therefore I'm currently using just T64 for some columns, and for some other columns as T64+ZSTD(9). EDIT: sorry, I think I got it - you probably meant something like "just by doing that on that specific column, the overall storage needs were reduced by 10% for the whole dataset", right?