Query Faster, Query Smarter: Our Move to DuckDB and What We Learned | by Arcesium engineering blog | Arcesium Engineering | Jun, 2026 | MediumSitemapOpen in appSign up<br>Sign in

Medium Logo

Get app<br>Write

Search

Sign up<br>Sign in

Arcesium Engineering

The Big Picture<br>Initializing DuckDB in Our Workflow<br>Schema Evolution in DuckDB<br>Observations<br>Optimizing DuckDB for Performance<br>Functional Gaps in DuckDB and Workarounds<br>Other Challenges<br>Comparing SQL Dialects: DuckDB vs Trino/Athena<br>Final Word<br>About the author

Written by inventive engineers delivering world-class technology for the investment lifecycle, supporting some of the world’s top asset managers and financial services organizations.

Query Faster, Query Smarter: Our Move to DuckDB and What We Learned

Arcesium engineering blog

12 min read·<br>6 days ago

Listen

Share

| by Simran Batra<br>Press enter or click to view image in full size

The Big Picture<br>Over the past 18 months we migrated thousands of SQL queries across three different engines — Athena to Trino to DuckDB — and cut our query costs in half along the way. This post covers what broke, what worked, and what we wish we’d known on day one.<br>Arcesium’s Portfolio Accounting System processes events and persists them as Parquet files in S3. Our product capability is the raw data and the complex query layer on top of it. AWS Athena was the natural first choice: cost-effective and a good fit for our initial load. As we scaled we hit Athena’s account- and service-level limits, which led to multiple production incidents — roughly 40% of incidents traced back to Athena — and bottlenecks that impacted operations.<br>The breaking point came during a major client onboarding where parallelism pushed us to 10x scale. Athena’s account-level rate limits throttled us hard, so we moved to Trino , which scaled well and handled the increased traffic — but introduced a new problem: cost. Trino was powerful but resource-hungry.<br>We needed a third option — speed without the cost. We picked DuckDB , a lightweight, in-memory OLAP engine. An initial POC showed a 50% runtime reduction with 50% fewer resources , and by late 2024 we had it running for select clients alongside Athena and Trino. By the end of 2025 we had fully migrated to DuckDB, with 50% cost savings realized.<br>So what did it take to move our queries to DuckDB and go live successfully? Let’s dive in.<br>Initializing DuckDB in Our Workflow<br>Version — 1.1.3<br>We store parquet format data in S3 , that’s our data source. To access S3 data we need to create a SECRET to load credentials. It can be created with AWS key/secret values or a credential chain. Sample -<br>CREATE SECRET secret1 (<br>TYPE S3,<br>PROVIDER CREDENTIAL_CHAIN,<br>REGION 'us-east-1',<br>ENDPOINT 's3.us-east-1.amazonaws.com'<br>);<br>select * from read_parquet(["s3:///*.parquet", "s3:///*.parquet"]);Please note — It is recommended to explicitly mention ENDPOINT, it avoids IO Error (Connection error for HTTP HEAD) errors.<br>For production runs, we use in-memory mode, but we also support persistent runs for local debugging<br>Our application is built in Java and uses the DuckDB Java JDBC Client. As part of our workflow, we first create a set of base tables that serve as the foundation for running complex queries to generate various reports. To manage this effectively, we establish a single master connection to configure global settings. For handling individual requests, we use the duplicate() method to create separate connections, ensuring query isolation while maintaining shared state and performance efficiency<br>Some helpful configurations to explore — threads, TimeZone, http_keep_alive, http_retries, http_retry_backoff, http_retry_wait_ms, allocator_background_threads<br>[helpful links — s3api, secret manager, configs]<br>Schema Evolution in DuckDB<br>We used AWS Glue Catalog for schema management when working with Trino/Athena. The combination of Glue Catalog, Trino/Athena, and S3 Parquet files provides a seamless solution for handling schema evolution, such as adding or removing columns, whether in nested structures or flat schemas.<br>However, this approach is not as straightforward with DuckDB. DuckDB, by design, doesn’t rely on an external catalog like AWS Glue. When reading data from a list of Parquet files or using a glob pattern, it adopts a different strategy:<br>First File as Schema Definition: DuckDB infers the schema from the first file encountered in the provided list or glob pattern<br>Handling Schema Differences: If the schemas across files differ, you would need to utilize the union_by_name option to instruct DuckDB to unify the schemas based on column names<br>Limitations with Nested Structures (pre-v1.3.0): Prior to v1.3.0, DuckDB had limitations regarding full support for schema evolution with nested structures, like STRUCTs. It would fail with Mismatch Error in case of mismatch STRUCT column across files.<br>Detailed examples:<br>---Data Preparation

CREATE TABLE t1 (a INTEGER, s STRUCT(v VARCHAR, i INTEGER));<br>INSERT INTO t1 VALUES (1,...