Analysing Data in Web3 with SQL | Hacker News Reader

Analysing Data in Web3 with SQL

thisgoke.medium.com

49 pointswebmaven24 comments

If you need an API (dune doesn't have one), check out Luabase (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!

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

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

1 - https://luabase.notion.site/Postgres-c6abe018218d49348ad7549...

> We're built on Clickhouse (instead of Postgres), so most queries are orders of magnitude faster.

Woah woah dems fightin words. Is this true?

Yes, quite true

https://www.timescale.com/blog/what-is-clickhouse-how-does-i...

  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)

Sorry, wrong link:

https://tech.marksblogg.com/benchmarks.html

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?

It makes sense in the context that many people use Dune (the product mentioned in this article) for crypto analytics and Dune uses Postgres.

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.

Time-series queries are faster on TimescaleDB (built on PostgreSQL) than ClickHouse.

https://www.timescale.com/blog/what-is-clickhouse-how-does-i...

Disclaimer: Timescale co-founder

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)

how much of timescale's performance would you say is inherited from PostgreSQL?

    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)

if you don't need API just use an Ethereum ETL: https://github.com/blockchain-etl

query Ethereum via 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/introd...

there's also open-source Graph Protocol: https://github.com/graphprotocol

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?

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

Is anyone willing to try to explain for me what this article is actually about in a way that doesn't need so much jargon?

Is this actually about some kind of Web3 Data Analytics or is this "I downloaded the entire block chain and parsed it into a relational database"?

I was wondering regarding Web3 data, if anyone was succesful to self-host the transactions in some local database.

When I was trying to do that, my local system could keep up only with doing writes, but not much more. What specs you need to have for that? I guess now its like 10tb of transactions in Ethereum, 1m transactions per day.

Google was

https://cloud.google.com/blog/products/data-analytics/introd...

storing billions of records in database is hard, but querying them is harder

if you want to try, get yourself some proper tools first: https://github.com/blockchain-etl

or you can just query transactions via RPC

Yes, it's well into the TBs of data now. Even compressed we're over 1TB for just Ethereum at Luabase (https://luabase.com/, we provide a "data stack in a box" for blockchain data)

Also if somebody wants to get into this type, I would recommend infura as source of data, or some other similar providers.

i'd also add alchemy (alchemy.com) to the list

> Web3 is going through rapid development and high variance

Well thats one way to put it.

“We are searching for the problem our solution ‘solves’.”

Reply on news.ycombinator.com