Ask HN: What is the best database structure to store Big Data?

[mafiaboi]

Asking HN data engineers for opinions and recommendations:

I am building on top of the blockchain data using Google Cloud and Bigquery however the queries for most of the data is just too slow and takes a long time.

I believe there is huge improvement in database tools as well as new DBs popping up. Are there any recommendations that I can implement to make my SQL queries much faster?

Two that I came across is trino.io and Apache Spark. Anyone used these before and saw noticeable performance changes?

[orbz]

You're probably going to be hard pressed to really beat out the sheer data throughput capacity of BigQuery without significant developer investment. I've done a lot of Apache Spark, Flume and Beam and they all have their own quirks and problems points. All of them are not at interactive speeds, have OOM issues for certain operations and require a lot of glue code/data management.

Honestly at this point you're probably going to want to start looking into the query planner and execution stats of your BigQuery jobs to see if you're doing something really weird and optimize that: https://cloud.google.com/bigquery/docs/query-plan-explanatio...

[mafiaboi]

Is there anything I can put on top of BQ to make things faster by any chance as far as you know?

[orbz]

Hmm you can preprocess the data or have intermediate queries get materialized to new tables you can query. That’ll reduce some of the query time.

[PaulHoule]

https://en.wikipedia.org/wiki/Column-oriented_DBMS

and past that making the "big" data as "small" as you can by removing points that aren't necessary, removing fields that aren't necessary, and representing the remainder with as few bits as possible.

[mafiaboi]

That makes sense but very manual and biased process probably

[mafiaboi]

What do you guys think about Trino.io?