Originally published at: TRM Labs Tech Blog
About TRM Labs
TRM Labs builds blockchain intelligence tools that financial institutions, crypto businesses and government agencies use to detect and investigate fraud and financial crime involving crypto assets. Its analytics platform works over petabytes of data from more than 30 blockchains and serves over 500 customer queries per minute.
TRM picked its storage format and query engine by benchmarking them on its own blockchain data: Apache Iceberg for tables on object storage, StarRocks for serving.
Key takeaway · Benchmark on real workloads
The original post was written by Vijay Shekhawat and Andrew Fisher (Staff Software Engineer), both of the TRM Labs Data Platform team.
The challenge
TRM's first-generation platform split user-facing analytics across two systems. A distributed Postgres cluster (Citus Data) answered point lookups and served small pre-aggregates. Larger queries and ad hoc aggregations that went beyond the Postgres cluster's capacity were federated to BigQuery.
The team had tuned BigQuery for years, and it handled customer analytics well. Two things changed that:
- Multi-site deployment. TRM needed to run its platform on several on-premise sites and share blockchain analytics data between them. A managed service such as BigQuery could not do this. The replacement had to be open source and deployable on Kubernetes.
- Scale and performance. The largest customer-facing workload served from BigQuery holds more than 115 TB and grows 2–3% a month. Its read queries join several levels of tables and filter on time ranges and arrays. Holding a three-second P95 at high concurrency on BigQuery was hard without buying expensive compute slots, and moving the workload into distributed Postgres would have cost too much in storage alone.
The solution
TRM decided to build a lakehouse: lake-style flexibility with the performance and reliability of a warehouse. From its BigQuery and Postgres experience, the team knew it needed to read as little data as possible per query (compression, clustering, partitioning), vectorized execution, horizontal scaling for concurrency, and storage separated from compute so engines could be swapped or combined without copying data. That led it past traditional OLAP stores such as ClickHouse and toward two decisions: a table format and a query engine.
Table format
Storage needs at TRM grow exponentially each year as high-throughput blockchains are added. Cost settled the medium first: TRM noted that even the priciest object store costs 4x less than the cheapest SSD. It then compared three open table formats on object storage: Delta Lake 2.4.0, Apache Hudi 0.14.0 and Apache Iceberg 1.4.0.
Delta Lake was ruled out because it lacks partition evolution and covers much the same ground as Iceberg for batch processing and analytics at scale. In TRM's Hudi benchmark, the best Hudi table ran 3x slower than Iceberg. TRM chose Iceberg for its read performance, wide adoption, active community and support across catalogs and engines.
Query engine
To prepare test data, the team exported 2–3 TB of tables from BigQuery to Parquet on Google Cloud Storage, converted them to Iceberg tables with PySpark (bucketed, sorted and partitioned around how queries filter by address, entity or time period), and kept table metadata in Dataproc Metastore.
TRM then tested three engines whose open source versions read Iceberg: Trino 433 on Google Dataproc, StarRocks 3.1 on GKE through the StarRocks Kubernetes operator, and DuckDB 0.9.2 on GCP virtual machines. Each ran at several cluster sizes, with JMeter generating concurrent load. The tests ran in early 2024.
Lookup/filter. Point and range lookups returning small result sets from a 2.57 TB dataset. StarRocks was fastest in every configuration and reached 470 ms with data caching. Trino ranged from 1,030 ms to 1,410 ms depending on cluster size. DuckDB took 2–3 seconds on a large single node; TRM stopped testing it here because its Iceberg extension did not yet support predicate pushdown.
Complex aggregation. Queries with SUM, COUNT and GROUP BY plus array and date-range filters over a 2.85 TB dataset. StarRocks ran in about 2 seconds without caching and about 500 ms with caching on the largest cluster. Trino got faster as clusters grew but leveled off near 2.5 seconds.
Stress test. Each JMeter run lasted one minute with no query limit, stepping up to 55 concurrent threads. Trino slowed as concurrency rose, while StarRocks stayed ahead on both workloads, more so with data caching on. Trino had no data lake caching at the time of the tests; it arrived in Trino 439, which TRM has not yet evaluated.
The new platform
TRM built its next-generation platform for user-facing analytics with StarRocks as the query engine over Iceberg tables in cloud storage. Distributed Postgres stays in place on SSD / persistent disks, and the platform targets Google Cloud Platform as well as other clouds and on-premise sites.
Because Iceberg is an open format, TRM can add or swap engines later. It plans to re-test Trino and DuckDB as well as newer options such as ClickHouse and Crunchy Data Warehouse.
The results
After tuning Iceberg partitioning and clustering, StarRocks cluster sizing and caching, TRM reports:
- 50% better P95 response times for customer-facing queries.
- 54% fewer query timeout errors, which let the team meet its query performance targets.
- One platform for many sites: Iceberg and StarRocks met the multi-site deployment requirement that BigQuery could not.
- Lower storage cost potential: with data and metadata on object storage, TRM sees room to move workloads off the distributed Postgres cluster and cut SSD spend.
The team also found that production traffic exposed usage patterns and tuning opportunities that the benchmarks had not, and treats testing at real scale as a required step.