4 ms·

If you need an API (dune doesn't have one), check out Luabase (https://luabase.com/ https://luabase.com/). We're built on Clickhouse (instead of Postgres), so m

If you need an API (dune doesn't have one), check out Luabase (https://luabase.com/ https://luabase.com/). We're built on Clickhouse (instead of Postgres), so most queries are orders of magnitude faster. We also support scheduling, webhooks and a bunch more. I'm one of the founders (mike@luabase.com), let me know if you have questions!

hbarka 4y ago
> We're built on Clickhouse (instead of Postgres), so most queries are orders of magnitude faster. Woah woah dems fightin words. Is this true?
- gfody 4y ago
  postgres isn't known for being fast, quite the opposite if your usecase is one clickhouse can handle (timeseries/olap) most queries are orders of magnitude faster.
  - akulkarni 4y ago
    Time-series queries are faster on TimescaleDB (built on PostgreSQL) than ClickHouse. https://www.timescale.com/blog/what-is-clickhouse-how-does-it-compare-to-postgresql-and-timescaledb-and-how-does-it-perform-for-time-series-data/ https://www.timescale.com/blog/what-is-clickhouse-how-does-i... Disclaimer: Timescale co-founder
    - gfody 4y ago
      clickhouse is more generally olap and not suitable for oltp, you could call it an olap+timeseries solution similarly to how you position timescale as oltp+timeseries (not all olap solutions support timeseries well but clickhouse does, though their actual positioning is modest: https://clickhouse.com/docs/en/faq/use-cases/time-series https://clickhouse.com/docs/en/faq/use-cases/time-series) how much of timescale's performance would you say is inherited from PostgreSQL?
      - akulkarni 4y ago
        clickhouse is more generally olap and not suitable for oltp, you could call it an olap+timeseries solution similarly to how you position timescale as oltp+timeseries You are correct, TimescaleDB is time-series + OLTP. But I would argue that ClickHouse is just OLAP, but is better at time-series than other OLAP. But maybe now I am splitting hairs (and my apologies if I am). how much of timescale's performance would you say is inherited from PostgreSQL? The performance areas where TimescaleDB shines is mostly stuff we have built. But the reliability, tooling, and ecosystem are mostly inherited from Postgres (which I think is a very good thing)
- jakewins 4y ago
  As I understand, the comparison doesn’t make sense. ClickHouse is an OLAP db, PG is an OLTP system. I haven’t used ClickHouse, but it seems it’s the kind of thing to back reporting functions, data science workloads etc, not back a transactional website load?
  - mritchie712 4y ago
    It makes sense in the context that many people use Dune (the product mentioned in this article) for crypto analytics and Dune uses Postgres.
- mritchie712 4y ago
  Yes, quite true https://www.timescale.com/blog/what-is-clickhouse-how-does-it-compare-to-postgresql-and-timescaledb-and-how-does-it-perform-for-time-series-data/ https://www.timescale.com/blog/what-is-clickhouse-how-does-i...
  - akulkarni 4y ago
    Results of query benchmarking between TimescaleDB [built on PostgreSQL] and ClickHouse. TimescaleDB outperforms in almost every query category I don’t think that post says what you think it says ;-) (Disclaimer, Timescale co-founder)
    - mritchie712 4y ago
      Sorry, wrong link: https://tech.marksblogg.com/benchmarks.html https://tech.marksblogg.com/benchmarks.html
ushakov 4y ago
do you stream ETL data to Clickhouse? what kind of delay do you have between when the event occurs and when it's in the database?
- mritchie712 4y ago
  Exactly, we stream blockchains (3 now!) to our warehouse with < 60 second delay from the live data
ushakov 4y ago
if you don't need API just use an Ethereum ETL: https://github.com/blockchain-etl https://github.com/blockchain-etl query Ethereum via RPC https://ethereum.org/en/developers/docs/apis/json-rpc/ https://ethereum.org/en/developers/docs/apis/json-rpc/ or use a free one hosted one by Google: https://cloud.google.com/blog/products/data-analytics/introducing-six-new-cryptocurrencies-in-bigquery-public-datasets-and-how-to-analyze-them?hl=en_US https://cloud.google.com/blog/products/data-analytics/introd... there's also open-source Graph Protocol: https://github.com/graphprotocol https://github.com/graphprotocol
kureikain 4y ago
Saying this > Clickhouse (instead of Postgres), so most queries are orders of magnitude faster. is a bold statement. They designed very differently. Try to do O(1) look up with a primary key and see which one is faster? Clickhouse shine in batch queries where you retrieve multiple row at a time. Its 30x faster than Postgres from my benchmark Clickhouse sucks when you need data integrity, primary key can be duplicated. People use MergeTree engine to dedupe, but even so, dedupe doesn't guarantee the absence of duplication primary key. So at query time you have to call Aggregate Function. Inserting small data frequently will also make Clickhouse choke. You cannot simply have it accept random write. Always have to batch. But good thing is ClickHouse has amazing integration with Postgres such as replication through logical replication, foreign data wrapper so you can query Postgres table inside Clickhouse
- mritchie712 4y ago
  To be clear, I love Postgres. We use Postgres as our app db. But clickhouse is hands down better for analytical workloads[0] (think aggregate queries over a billion rows). > Clickhouse sucks when you need data integrity, primary key can be duplicated. People use MergeTree engine to dedupe, but even so, dedupe doesn't guarantee the absence of duplication primary key. So at query time you have to call Aggregate Function. Agreed. Fortunately we control the processes that insert data and can handle these issues during ingress. > But good thing is ClickHouse has amazing integration with Postgres such as replication through logical replication, foreign data wrapper so you can query Postgres table inside Clickhouse Yes! This was surprisingly easy. We set up a way[1] for users to connect their pg data in Luabase. 0 - https://tech.marksblogg.com/benchmarks.html https://tech.marksblogg.com/benchmarks.html 1 - https://luabase.notion.site/Postgres-c6abe018218d49348ad754982ac4ee25 https://luabase.notion.site/Postgres-c6abe018218d49348ad7549...

hbarka 4y ago

> We're built on Clickhouse (instead of Postgres), so most queries are orders of magnitude faster. Woah woah dems fightin words. Is this true?

gfody 4y ago
postgres isn't known for being fast, quite the opposite if your usecase is one clickhouse can handle (timeseries/olap) most queries are orders of magnitude faster.
- akulkarni 4y ago
  Time-series queries are faster on TimescaleDB (built on PostgreSQL) than ClickHouse. https://www.timescale.com/blog/what-is-clickhouse-how-does-it-compare-to-postgresql-and-timescaledb-and-how-does-it-perform-for-time-series-data/ https://www.timescale.com/blog/what-is-clickhouse-how-does-i... Disclaimer: Timescale co-founder
  - gfody 4y ago
    clickhouse is more generally olap and not suitable for oltp, you could call it an olap+timeseries solution similarly to how you position timescale as oltp+timeseries (not all olap solutions support timeseries well but clickhouse does, though their actual positioning is modest: https://clickhouse.com/docs/en/faq/use-cases/time-series https://clickhouse.com/docs/en/faq/use-cases/time-series) how much of timescale's performance would you say is inherited from PostgreSQL?
    - akulkarni 4y ago
      clickhouse is more generally olap and not suitable for oltp, you could call it an olap+timeseries solution similarly to how you position timescale as oltp+timeseries You are correct, TimescaleDB is time-series + OLTP. But I would argue that ClickHouse is just OLAP, but is better at time-series than other OLAP. But maybe now I am splitting hairs (and my apologies if I am). how much of timescale's performance would you say is inherited from PostgreSQL? The performance areas where TimescaleDB shines is mostly stuff we have built. But the reliability, tooling, and ecosystem are mostly inherited from Postgres (which I think is a very good thing)
jakewins 4y ago
As I understand, the comparison doesn’t make sense. ClickHouse is an OLAP db, PG is an OLTP system. I haven’t used ClickHouse, but it seems it’s the kind of thing to back reporting functions, data science workloads etc, not back a transactional website load?
- mritchie712 4y ago
  It makes sense in the context that many people use Dune (the product mentioned in this article) for crypto analytics and Dune uses Postgres.
mritchie712 4y ago
Yes, quite true https://www.timescale.com/blog/what-is-clickhouse-how-does-it-compare-to-postgresql-and-timescaledb-and-how-does-it-perform-for-time-series-data/ https://www.timescale.com/blog/what-is-clickhouse-how-does-i...
- akulkarni 4y ago
  Results of query benchmarking between TimescaleDB [built on PostgreSQL] and ClickHouse. TimescaleDB outperforms in almost every query category I don’t think that post says what you think it says ;-) (Disclaimer, Timescale co-founder)
  - mritchie712 4y ago
    Sorry, wrong link: https://tech.marksblogg.com/benchmarks.html https://tech.marksblogg.com/benchmarks.html

ushakov 4y ago

do you stream ETL data to Clickhouse? what kind of delay do you have between when the event occurs and when it's in the database?

mritchie712 4y ago
Exactly, we stream blockchains (3 now!) to our warehouse with < 60 second delay from the live data

ushakov 4y ago

if you don't need API just use an Ethereum ETL: https://github.com/blockchain-etl https://github.com/blockchain-etl query Ethereum via RPC https://ethereum.org/en/developers/docs/apis/json-rpc/ https://ethereum.org/en/developers/docs/apis/json-rpc/ or use a free one hosted one by Google: https://cloud.google.com/blog/products/data-analytics/introducing-six-new-cryptocurrencies-in-bigquery-public-datasets-and-how-to-analyze-them?hl=en_US https://cloud.google.com/blog/products/data-analytics/introd... there's also open-source Graph Protocol: https://github.com/graphprotocol https://github.com/graphprotocol

kureikain 4y ago

Saying this > Clickhouse (instead of Postgres), so most queries are orders of magnitude faster. is a bold statement. They designed very differently. Try to do O(1) look up with a primary key and see which one is faster? Clickhouse shine in batch queries where you retrieve multiple row at a time. Its 30x faster than Postgres from my benchmark Clickhouse sucks when you need data integrity, primary key can be duplicated. People use MergeTree engine to dedupe, but even so, dedupe doesn't guarantee the absence of duplication primary key. So at query time you have to call Aggregate Function. Inserting small data frequently will also make Clickhouse choke. You cannot simply have it accept random write. Always have to batch. But good thing is ClickHouse has amazing integration with Postgres such as replication through logical replication, foreign data wrapper so you can query Postgres table inside Clickhouse

mritchie712 4y ago
To be clear, I love Postgres. We use Postgres as our app db. But clickhouse is hands down better for analytical workloads[0] (think aggregate queries over a billion rows). > Clickhouse sucks when you need data integrity, primary key can be duplicated. People use MergeTree engine to dedupe, but even so, dedupe doesn't guarantee the absence of duplication primary key. So at query time you have to call Aggregate Function. Agreed. Fortunately we control the processes that insert data and can handle these issues during ingress. > But good thing is ClickHouse has amazing integration with Postgres such as replication through logical replication, foreign data wrapper so you can query Postgres table inside Clickhouse Yes! This was surprisingly easy. We set up a way[1] for users to connect their pg data in Luabase. 0 - https://tech.marksblogg.com/benchmarks.html https://tech.marksblogg.com/benchmarks.html 1 - https://luabase.notion.site/Postgres-c6abe018218d49348ad754982ac4ee25 https://luabase.notion.site/Postgres-c6abe018218d49348ad7549...