3 ms·

my bet is that hydra uses compression inside already, otherwise it is hard to explain where difference comes from. > it won't be free in terms of CPU cycles i

my bet is that hydra uses compression inside already, otherwise it is hard to explain where difference comes from. > it won't be free in terms of CPU cycles it can reduce IO traffic significantly, and it can be very positive trade off depending on circumstances.

arp242 3y ago
I had assumed that PostgreSQL is so much larger because it creates heaps of indexes (which is probably also why inserts are so much slower for it), but I don't really have a good way to confirm that quickly.
- riku_iki 3y ago
  one can choose to not create "heaps of indexes".
  - arp242 3y ago
    At which point your performance will drop like a brick for these types of queries – I'm pretty sure these indexes weren't added for the craic.
    - riku_iki 3y ago
      it depends on your query obviously. In general, I did very deep benchmarking of pg, clickhouse and duckdb, and I sure didn't make stupid mistakes like this: https://news.ycombinator.com/item?id=36990831 https://news.ycombinator.com/item?id=36990831 My dataset has 50B rows and 2tb of data, and I think columnar dbs are very overhiped and I chose pg because: - pg performance is acceptable, maybe 2-5x times slower than clickhouse and duckdb on some queries if pg is configured correctly and run on compressed storage - clickhouse and duckdb start falling apart very fast because they specialized on very narrow type of queries: https://github.com/ClickHouse/ClickHouse/issues/47520 https://github.com/ClickHouse/ClickHouse/issues/47520 https://github.com/ClickHouse/ClickHouse/issues/47521 https://github.com/ClickHouse/ClickHouse/issues/47521 https://github.com/duckdb/duckdb/discussions/6696 https://github.com/duckdb/duckdb/discussions/6696
      - benn0 3y ago
        Do you have happen to have any documentation about your benchmarking? I'm also considering these options at the moment (currently using pg+timescaledb) and interested in what you found.
        riku_iki 3y ago
        I don't have documentation. I just created large tables, and tried to join, group by, sort them in pg, clickhouse, duckdb, looked what failed or being slow, and tried to resolve it. I am happy to answer specific questions, but I didn't use timescaledb.
      - zX41ZdbW 3y ago
        ClickHouse can do large GROUP BY queries, not limited by memory: https://clickhouse.com/docs/en/sql-reference/statements/select/group-by#group-by-in-external-memory https://clickhouse.com/docs/en/sql-reference/statements/sele...
        riku_iki 3y ago
        as explained in https://github.com/ClickHouse/ClickHouse/issues/47521#issuecomment-1468842138 https://github.com/ClickHouse/ClickHouse/issues/47521#issuec... it can't, that parameters only applies on pre aggregation phase but not aggregation. Feature request is not implemented yet: https://github.com/ClickHouse/ClickHouse/issues/40588 https://github.com/ClickHouse/ClickHouse/issues/40588
        zX41ZdbW 3y ago
        ClickHouse uses "grace hash" GROUP BY with the number of buckets = 256. It can do size about 256 times larger than a memory because only one bucket has to be in memory while merging. It works for distributed query processing as well and is enabled by default. About the linked issue - it looks like it is related to some extra optimization on top of what already exists.
        deleted 3y ago

arp242 3y ago

I had assumed that PostgreSQL is so much larger because it creates heaps of indexes (which is probably also why inserts are so much slower for it), but I don't really have a good way to confirm that quickly.

riku_iki 3y ago
one can choose to not create "heaps of indexes".
- arp242 3y ago
  At which point your performance will drop like a brick for these types of queries – I'm pretty sure these indexes weren't added for the craic.
  - riku_iki 3y ago
    it depends on your query obviously. In general, I did very deep benchmarking of pg, clickhouse and duckdb, and I sure didn't make stupid mistakes like this: https://news.ycombinator.com/item?id=36990831 https://news.ycombinator.com/item?id=36990831 My dataset has 50B rows and 2tb of data, and I think columnar dbs are very overhiped and I chose pg because: - pg performance is acceptable, maybe 2-5x times slower than clickhouse and duckdb on some queries if pg is configured correctly and run on compressed storage - clickhouse and duckdb start falling apart very fast because they specialized on very narrow type of queries: https://github.com/ClickHouse/ClickHouse/issues/47520 https://github.com/ClickHouse/ClickHouse/issues/47520 https://github.com/ClickHouse/ClickHouse/issues/47521 https://github.com/ClickHouse/ClickHouse/issues/47521 https://github.com/duckdb/duckdb/discussions/6696 https://github.com/duckdb/duckdb/discussions/6696
    - benn0 3y ago
      Do you have happen to have any documentation about your benchmarking? I'm also considering these options at the moment (currently using pg+timescaledb) and interested in what you found.
      - riku_iki 3y ago
        I don't have documentation. I just created large tables, and tried to join, group by, sort them in pg, clickhouse, duckdb, looked what failed or being slow, and tried to resolve it. I am happy to answer specific questions, but I didn't use timescaledb.
    - zX41ZdbW 3y ago
      ClickHouse can do large GROUP BY queries, not limited by memory: https://clickhouse.com/docs/en/sql-reference/statements/select/group-by#group-by-in-external-memory https://clickhouse.com/docs/en/sql-reference/statements/sele...
      - riku_iki 3y ago
        as explained in https://github.com/ClickHouse/ClickHouse/issues/47521#issuecomment-1468842138 https://github.com/ClickHouse/ClickHouse/issues/47521#issuec... it can't, that parameters only applies on pre aggregation phase but not aggregation. Feature request is not implemented yet: https://github.com/ClickHouse/ClickHouse/issues/40588 https://github.com/ClickHouse/ClickHouse/issues/40588
        zX41ZdbW 3y ago
        ClickHouse uses "grace hash" GROUP BY with the number of buckets = 256. It can do size about 256 times larger than a memory because only one bucket has to be in memory while merging. It works for distributed query processing as well and is enabled by default. About the linked issue - it looks like it is related to some extra optimization on top of what already exists.
        deleted 3y ago