5 ms·

An intro article that explains the what and why https://www.hydra.so/blog-posts/2022-12-13-how-we-built-fastest-postgres-db-for-analytics https://www.hydra.so/b

An intro article that explains the what and why https://www.hydra.so/blog-posts/2022-12-13-how-we-built-fastest-postgres-db-for-analytics https://www.hydra.so/blog-posts/2022-12-13-how-we-built-fast...

totetsu 3y ago
And a bit further down the rabbit hole if you need. structure of heap table in postgresql with pictures. https://medium.com/quadcode-life/structure-of-heap-table-in-postgresql-d44c94332052 https://medium.com/quadcode-life/structure-of-heap-table-in-...
tomnipotent 3y ago
Dishonest for them to use the default "Postgres" for the ClickBench comparison instead of "Postgres (tuned)", which uses similar config settings to Hydra and tells a very different story. https://benchmark.clickhouse.com/#eyJzeXN0ZW0iOnsiQXRoZW5hIChwYXJ0aXRpb25lZCkiOmZhbHNlLCJBdGhlbmEgKHNpbmdsZSkiOmZhbHNlLCJBdXJvcmEgZm9yIE15U1FMIjpmYWxzZSwiQXVyb3JhIGZvciBQb3N0Z3JlU1FMIjpmYWxzZSwiQnl0ZUhvdXNlIjpmYWxzZSwiQ2l0dXMiOmZhbHNlLCJjbGlja2hvdXNlLWxvY2FsIChwYXJ0aXRpb25lZCkiOmZhbHNlLCJjbGlja2hvdXNlLWxvY2FsIChzaW5nbGUpIjpmYWxzZSwiQ2xpY2tIb3VzZSI6ZmFsc2UsIkNsaWNrSG91c2UgKHR1bmVkKSI6ZmFsc2UsIkNsaWNrSG91c2UgKHpzdGQpIjpmYWxzZSwiQ2xpY2tIb3VzZSBDbG91ZCI6ZmFsc2UsIkNyYXRlREIiOmZhbHNlLCJEYXRhYmVuZCI6ZmFsc2UsImRhdGFmdXNpb24iOmZhbHNlLCJBcGFjaGUgRG9yaXMiOmZhbHNlLCJEcnVpZCI6ZmFsc2UsIkR1Y2tEQiAoUGFycXVldCkiOmZhbHNlLCJEdWNrREIiOmZhbHNlLCJFbGFzdGljc2VhcmNoIjpmYWxzZSwiRWxhc3RpY3NlYXJjaCAodHVuZWQpIjpmYWxzZSwiR3JlZW5wbHVtIjpmYWxzZSwiSGVhdnlBSSI6ZmFsc2UsIkh5ZHJhIjp0cnVlLCJJbmZvYnJpZ2h0IjpmYWxzZSwiTWFyaWFEQiBDb2x1bW5TdG9yZSI6ZmFsc2UsIk1hcmlhREIiOmZhbHNlLCJNb25ldERCIjpmYWxzZSwiTW9uZ29EQiI6ZmFsc2UsIk15U1FMIChNeUlTQU0pIjpmYWxzZSwiTXlTUUwiOmZhbHNlLCJQaW5vdCI6ZmFsc2UsIlBvc3RncmVTUUwiOmZhbHNlLCJRdWVzdERCIChwYXJ0aXRpb25lZCkiOmZhbHNlLCJRdWVzdERCIjpmYWxzZSwiUmVkc2hpZnQiOmZhbHNlLCJTZWxlY3REQiI6ZmFsc2UsIlNpbmdsZVN0b3JlIjpmYWxzZSwiU25vd2ZsYWtlIjpmYWxzZSwiU1FMaXRlIjpmYWxzZSwiU3RhclJvY2tzICh0dW5lZCkiOmZhbHNlLCJTdGFyUm9ja3MiOmZhbHNlLCJUaW1lc2NhbGVEQiAoY29tcHJlc3Npb24pIjpmYWxzZSwiVGltZXNjYWxlREIiOmZhbHNlLCJQb3N0Z3JlU1FMICh0dW5lZCkiOnRydWV9LCJ0eXBlIjp7InN0YXRlbGVzcyI6ZmFsc2UsIm1hbmFnZWQiOmZhbHNlLCJKYXZhIjpmYWxzZSwiY29sdW1uLW9yaWVudGVkIjpmYWxzZSwiQysrIjpmYWxzZSwiTXlTUUwgY29tcGF0aWJsZSI6ZmFsc2UsInJvdy1vcmllbnRlZCI6ZmFsc2UsIkMiOmZhbHNlLCJQb3N0Z3JlU1FMIGNvbXBhdGlibGUiOnRydWUsIkNsaWNrSG91c2UgZGVyaXZhdGl2ZSI6ZmFsc2UsImVtYmVkZGVkIjpmYWxzZSwiUnVzdCI6ZmFsc2UsInNlYXJjaCI6ZmFsc2UsImRvY3VtZW50IjpmYWxzZSwidGltZS1zZXJpZXMiOmZhbHNlfSwibWFjaGluZSI6eyJzZXJ2ZXJsZXNzIjpmYWxzZSwiMTZhY3UiOmZhbHNlLCJMIjpmYWxzZSwiTSI6ZmFsc2UsIlMiOmZhbHNlLCJYUyI6ZmFsc2UsImM2YS40eGxhcmdlLCA1MDBnYiBncDIiOnRydWUsImM1LjR4bGFyZ2UsIDUwMGdiIGdwMiI6ZmFsc2UsImM2YS5tZXRhbCwgNTAwZ2IgZ3AyIjpmYWxzZSwiMTYgdGhyZWFkcyI6ZmFsc2UsIjIwIHRocmVhZHMiOmZhbHNlLCIyNCB0aHJlYWRzIjpmYWxzZSwiMjggdGhyZWFkcyI6ZmFsc2UsIjMwIHRocmVhZHMiOmZhbHNlLCI0OCB0aHJlYWRzIjpmYWxzZSwiNjAgdGhyZWFkcyI6ZmFsc2UsIm01ZC4yNHhsYXJnZSI6ZmFsc2UsImYxNnMgdjIiOmZhbHNlLCJjNmEuNHhsYXJnZSwgMTUwMGdiIGdwMiI6ZmFsc2UsInJhMy4xNnhsYXJnZSI6ZmFsc2UsInJhMy40eGxhcmdlIjpmYWxzZSwicmEzLnhscGx1cyI6ZmFsc2UsIlMyIjpmYWxzZSwiUzI0IjpmYWxzZSwiMlhMIjpmYWxzZSwiM1hMIjpmYWxzZSwiNFhMIjpmYWxzZSwiWEwiOmZhbHNlfSwiY2x1c3Rlcl9zaXplIjp7IjEiOnRydWUsIjIiOmZhbHNlLCI0IjpmYWxzZSwiOCI6ZmFsc2UsIjEyIjpmYWxzZSwiMTYiOmZhbHNlLCIzMiI6ZmFsc2UsIjY0IjpmYWxzZSwiMTI4IjpmYWxzZSwic2VydmVybGVzcyI6ZmFsc2UsInVuZGVmaW5lZCI6ZmFsc2V9LCJtZXRyaWMiOiJob3QiLCJxdWVyaWVzIjpbdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZV19 https://benchmark.clickhouse.com/#eyJzeXN0ZW0iOnsiQXRoZW5hIC...
- hankchinaski 3y ago
  “Tuned” how?
  - deleted 3y ago
    [deleted]
  - tomnipotent 3y ago
    The default "Postgres" uses: shared_buffers = 128MB max_parallel_workers = 8 max_parallel_workers_per_gather = 2 work_mem = 4MB "Postgres (tuned)" uses: shared_buffers = 8GB (64x the default) max_parallel_workers = 16 max_parallel_workers_per_gather = 8 work_mem = 4MB "Hydra" uses: shared_buffers = 8GB max_parallel_workers = 16 max_worker_processes = 32 (default 8) work_mem = 64MB The default settings are not realistic when running on a c6a.4xlarge instance, and the "Postgres (tuned)" settings more closely match what "Hydra" is configured to use. I consider it dishonest because I do not believe a team working this close to Postgres internals wouldn't know this.
    - _nvp 3y ago
      Yes, setting between Hydra and Postgres (tuned) are somehow aligned. But what Postgres (tuned) also creates are bunch of indexes (https://github.com/ClickHouse/ClickBench/blob/main/postgresql-tuned/index.sql https://github.com/ClickHouse/ClickBench/blob/main/postgresq...) which adds around ~50GB of extra storage.
      - tomnipotent 3y ago
        Equivalent settings makes the comparison more useful, and storage-conscious readers will pick-up that Postgres uses 4-6x more.
        _nvp 3y ago
        Agree, but also, IMO, using index is what gives also performance boost. Just looking at queries that use string search - they have best result overall in complete test. Postgres (tuned) is designed for this specific clickbench table and nothing wrong with that.
cpursley 3y ago
Can somebody explain in the most basic way possible what column oriented databases can do for me and some example use cases?
- ako 3y ago
  Instead of storing your data grouped by record, column databases store data grouped by column. This means that if your query is only interested in one or a few columns but from a large number of records , e.g. to aggregate it to calculate an average, it needs to read less database blocks from disk, so lower io costs. Same for in memory or cached, the data your interested in is easier accessible for lower performance cost. This is especially useful for reporting/analytical purposes where you want to aggregate over data from many records.
  - cpursley 3y ago
    Thanks. So if I understand correctly, this is basically like ignoring all the other columns (that you're not interested in)? And do you run them in conjunction with your existing Postgres, install it as a plugin, or switch your entire db to something like Hydra?
    - ako 3y ago
      Usually you choose the type of database based on your use case. Reporting databases like data warehouses, benefit from using a column oriented database, whereas transactional use cases use a row oriented database. Modern databases allow you to choose per table.
    - beagle3 3y ago
      Yes, the downside being that if you need all the columns of one specific record (row), it will be spread over many column blocks. As a rule of the thumb, if most of your queries are aggregations over few columns of many records (OLAP), column based would be a better choice - whereas if you need to access every column/field of a small number of records (OLTP), record/row based would be a better choice.
    - 3y ago

totetsu 3y ago

And a bit further down the rabbit hole if you need. structure of heap table in postgresql with pictures. https://medium.com/quadcode-life/structure-of-heap-table-in-postgresql-d44c94332052 https://medium.com/quadcode-life/structure-of-heap-table-in-...

tomnipotent 3y ago

Dishonest for them to use the default "Postgres" for the ClickBench comparison instead of "Postgres (tuned)", which uses similar config settings to Hydra and tells a very different story. https://benchmark.clickhouse.com/#eyJzeXN0ZW0iOnsiQXRoZW5hIChwYXJ0aXRpb25lZCkiOmZhbHNlLCJBdGhlbmEgKHNpbmdsZSkiOmZhbHNlLCJBdXJvcmEgZm9yIE15U1FMIjpmYWxzZSwiQXVyb3JhIGZvciBQb3N0Z3JlU1FMIjpmYWxzZSwiQnl0ZUhvdXNlIjpmYWxzZSwiQ2l0dXMiOmZhbHNlLCJjbGlja2hvdXNlLWxvY2FsIChwYXJ0aXRpb25lZCkiOmZhbHNlLCJjbGlja2hvdXNlLWxvY2FsIChzaW5nbGUpIjpmYWxzZSwiQ2xpY2tIb3VzZSI6ZmFsc2UsIkNsaWNrSG91c2UgKHR1bmVkKSI6ZmFsc2UsIkNsaWNrSG91c2UgKHpzdGQpIjpmYWxzZSwiQ2xpY2tIb3VzZSBDbG91ZCI6ZmFsc2UsIkNyYXRlREIiOmZhbHNlLCJEYXRhYmVuZCI6ZmFsc2UsImRhdGFmdXNpb24iOmZhbHNlLCJBcGFjaGUgRG9yaXMiOmZhbHNlLCJEcnVpZCI6ZmFsc2UsIkR1Y2tEQiAoUGFycXVldCkiOmZhbHNlLCJEdWNrREIiOmZhbHNlLCJFbGFzdGljc2VhcmNoIjpmYWxzZSwiRWxhc3RpY3NlYXJjaCAodHVuZWQpIjpmYWxzZSwiR3JlZW5wbHVtIjpmYWxzZSwiSGVhdnlBSSI6ZmFsc2UsIkh5ZHJhIjp0cnVlLCJJbmZvYnJpZ2h0IjpmYWxzZSwiTWFyaWFEQiBDb2x1bW5TdG9yZSI6ZmFsc2UsIk1hcmlhREIiOmZhbHNlLCJNb25ldERCIjpmYWxzZSwiTW9uZ29EQiI6ZmFsc2UsIk15U1FMIChNeUlTQU0pIjpmYWxzZSwiTXlTUUwiOmZhbHNlLCJQaW5vdCI6ZmFsc2UsIlBvc3RncmVTUUwiOmZhbHNlLCJRdWVzdERCIChwYXJ0aXRpb25lZCkiOmZhbHNlLCJRdWVzdERCIjpmYWxzZSwiUmVkc2hpZnQiOmZhbHNlLCJTZWxlY3REQiI6ZmFsc2UsIlNpbmdsZVN0b3JlIjpmYWxzZSwiU25vd2ZsYWtlIjpmYWxzZSwiU1FMaXRlIjpmYWxzZSwiU3RhclJvY2tzICh0dW5lZCkiOmZhbHNlLCJTdGFyUm9ja3MiOmZhbHNlLCJUaW1lc2NhbGVEQiAoY29tcHJlc3Npb24pIjpmYWxzZSwiVGltZXNjYWxlREIiOmZhbHNlLCJQb3N0Z3JlU1FMICh0dW5lZCkiOnRydWV9LCJ0eXBlIjp7InN0YXRlbGVzcyI6ZmFsc2UsIm1hbmFnZWQiOmZhbHNlLCJKYXZhIjpmYWxzZSwiY29sdW1uLW9yaWVudGVkIjpmYWxzZSwiQysrIjpmYWxzZSwiTXlTUUwgY29tcGF0aWJsZSI6ZmFsc2UsInJvdy1vcmllbnRlZCI6ZmFsc2UsIkMiOmZhbHNlLCJQb3N0Z3JlU1FMIGNvbXBhdGlibGUiOnRydWUsIkNsaWNrSG91c2UgZGVyaXZhdGl2ZSI6ZmFsc2UsImVtYmVkZGVkIjpmYWxzZSwiUnVzdCI6ZmFsc2UsInNlYXJjaCI6ZmFsc2UsImRvY3VtZW50IjpmYWxzZSwidGltZS1zZXJpZXMiOmZhbHNlfSwibWFjaGluZSI6eyJzZXJ2ZXJsZXNzIjpmYWxzZSwiMTZhY3UiOmZhbHNlLCJMIjpmYWxzZSwiTSI6ZmFsc2UsIlMiOmZhbHNlLCJYUyI6ZmFsc2UsImM2YS40eGxhcmdlLCA1MDBnYiBncDIiOnRydWUsImM1LjR4bGFyZ2UsIDUwMGdiIGdwMiI6ZmFsc2UsImM2YS5tZXRhbCwgNTAwZ2IgZ3AyIjpmYWxzZSwiMTYgdGhyZWFkcyI6ZmFsc2UsIjIwIHRocmVhZHMiOmZhbHNlLCIyNCB0aHJlYWRzIjpmYWxzZSwiMjggdGhyZWFkcyI6ZmFsc2UsIjMwIHRocmVhZHMiOmZhbHNlLCI0OCB0aHJlYWRzIjpmYWxzZSwiNjAgdGhyZWFkcyI6ZmFsc2UsIm01ZC4yNHhsYXJnZSI6ZmFsc2UsImYxNnMgdjIiOmZhbHNlLCJjNmEuNHhsYXJnZSwgMTUwMGdiIGdwMiI6ZmFsc2UsInJhMy4xNnhsYXJnZSI6ZmFsc2UsInJhMy40eGxhcmdlIjpmYWxzZSwicmEzLnhscGx1cyI6ZmFsc2UsIlMyIjpmYWxzZSwiUzI0IjpmYWxzZSwiMlhMIjpmYWxzZSwiM1hMIjpmYWxzZSwiNFhMIjpmYWxzZSwiWEwiOmZhbHNlfSwiY2x1c3Rlcl9zaXplIjp7IjEiOnRydWUsIjIiOmZhbHNlLCI0IjpmYWxzZSwiOCI6ZmFsc2UsIjEyIjpmYWxzZSwiMTYiOmZhbHNlLCIzMiI6ZmFsc2UsIjY0IjpmYWxzZSwiMTI4IjpmYWxzZSwic2VydmVybGVzcyI6ZmFsc2UsInVuZGVmaW5lZCI6ZmFsc2V9LCJtZXRyaWMiOiJob3QiLCJxdWVyaWVzIjpbdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZV19 https://benchmark.clickhouse.com/#eyJzeXN0ZW0iOnsiQXRoZW5hIC...

hankchinaski 3y ago
“Tuned” how?
- deleted 3y ago
  [deleted]
- tomnipotent 3y ago
  The default "Postgres" uses: shared_buffers = 128MB max_parallel_workers = 8 max_parallel_workers_per_gather = 2 work_mem = 4MB "Postgres (tuned)" uses: shared_buffers = 8GB (64x the default) max_parallel_workers = 16 max_parallel_workers_per_gather = 8 work_mem = 4MB "Hydra" uses: shared_buffers = 8GB max_parallel_workers = 16 max_worker_processes = 32 (default 8) work_mem = 64MB The default settings are not realistic when running on a c6a.4xlarge instance, and the "Postgres (tuned)" settings more closely match what "Hydra" is configured to use. I consider it dishonest because I do not believe a team working this close to Postgres internals wouldn't know this.
  - _nvp 3y ago
    Yes, setting between Hydra and Postgres (tuned) are somehow aligned. But what Postgres (tuned) also creates are bunch of indexes (https://github.com/ClickHouse/ClickBench/blob/main/postgresql-tuned/index.sql https://github.com/ClickHouse/ClickBench/blob/main/postgresq...) which adds around ~50GB of extra storage.
    - tomnipotent 3y ago
      Equivalent settings makes the comparison more useful, and storage-conscious readers will pick-up that Postgres uses 4-6x more.
      - _nvp 3y ago
        Agree, but also, IMO, using index is what gives also performance boost. Just looking at queries that use string search - they have best result overall in complete test. Postgres (tuned) is designed for this specific clickbench table and nothing wrong with that.

cpursley 3y ago

Can somebody explain in the most basic way possible what column oriented databases can do for me and some example use cases?

ako 3y ago
Instead of storing your data grouped by record, column databases store data grouped by column. This means that if your query is only interested in one or a few columns but from a large number of records , e.g. to aggregate it to calculate an average, it needs to read less database blocks from disk, so lower io costs. Same for in memory or cached, the data your interested in is easier accessible for lower performance cost. This is especially useful for reporting/analytical purposes where you want to aggregate over data from many records.
- cpursley 3y ago
  Thanks. So if I understand correctly, this is basically like ignoring all the other columns (that you're not interested in)? And do you run them in conjunction with your existing Postgres, install it as a plugin, or switch your entire db to something like Hydra?
  - ako 3y ago
    Usually you choose the type of database based on your use case. Reporting databases like data warehouses, benefit from using a column oriented database, whereas transactional use cases use a row oriented database. Modern databases allow you to choose per table.
  - beagle3 3y ago
    Yes, the downside being that if you need all the columns of one specific record (row), it will be spread over many column blocks. As a rule of the thumb, if most of your queries are aggregations over few columns of many records (OLAP), column based would be a better choice - whereas if you need to access every column/field of a small number of records (OLTP), record/row based would be a better choice.
  - 3y ago