Quacking Performance: DuckDB

Posted on Jul 22, 2025

Intro-duck-tion

If you’ve not came across DuckDB previously I’d highly recommend looking into it, it’s fast, open source and rapidly gaining traction within the Data & Analytics space. DuckDB is designed for fast SQL queries on analytical workloads, namely it’s design choices favour aggregation and grouping, particularly in instances where only a subset of columns are used. This greatly reduces memory usage and runtime in applicable instances.

Warehouse Storage: Rows vs Columns

To highlight the differences between row-based and column-based data stores, I’ll use the analogy that we’re a grocery store storing customer orders.

Imagine a massive warehouse storing customer orders. In a row-based system, each order is packed into its own shopping cart or box. For instance, cart 12345 contains everything for John’s order: 2 bottles of detergent, some soft drinks, some food, and the order date. When you need to retrieve John’s complete order, you simply grab his cart and have everything in one place which is extremely efficient when retrieving full orders. Retrieving multiple full orders, or all orders for a specific person is also very efficient due to the row-based storage and indices. Row-based relational stores are extremely popular for use in applications because of this.

R┌│││││││││││└O──W──-─┌││││││└─B────A──DBDD──S──Cerra──E──ateit──D──reane────trdk──S──gs──T──1e──O──2n──R──3t──A──4──G──5──E─────┐││││││┘─(──S──h─┌││││││└─o────p──BDDD──p──Crrea──i──aeitt──n──ranee──g─W─tdkr───a─sg──C─r─1e──a─e─2n──r─h─3t──t─o─4──s─u─6──)─s────e┐││││││┘────F──l┌││││││└──o────o─DBD───r─Cera────atet────reae────trd────g────1e────2n────3t────4────7───────┐││││││┘──────┌││││││└───────BDDD────Crera────aetit────raene────tdrk────gs────1e────2n────3t────4────8──────┐││┐││││││┘│┘

In a column-based system like DuckDB, the warehouse is organized into aisles by product type. All detergent from every order goes into the detergent aisle, all bread goes into the bread aisle, and so on. To reconstruct John’s complete order, you’d need to walk through multiple aisles collecting his items which would be notably slower than just retrieving his shopping cart.

However, should you need to know how many detergents were sold in the last three months, column based storage excels. You simply walk down the detergent aisle, scanning quantities and dates without touching or seeing any food, soft drinks or any other products. In the row-based warehouse, you’d have to inspect lots of individual shopping carts, search inside and count detergent bottles which is less efficient.

C┌││││││││││││└O──L──U─┌│││││││└─M────N──DIIII──-──etttt──B──teeee──A──eAmmmm──S──ri──E──gs1234──D──el────ne──S──t──T────O────R────A─┐│││││││┘─G──E───┌│││││││└─(────A──IIII──i──tttt──s──eeee──l──BAmmmm──e──ri──s─W─es1234──)─a─al───r─de───e────h────o────u────s┐│││││││┘──e────F┌│││││││└──l────o─IIII───o─tttt───r─Deeee────rAmmmm────ii────ns1234────kl────se───────────────────┐│││││││┘──────┌│││││││└───────2222────0000────2222────DA3333────ai----────ts0000────el1122────se----────0101────1515──────────┐││┐│││││││┘│┘

In practice, when doing large analytical queries where columns are likely to be omitted and data sets are large (usually being the result of joining large business tables) DuckDB will outperform row based data sources. DuckDB will skip over columns that aren’t included in the query reducing memory usage and runtime whereas a row-based system will still need to read through each row. Notably however, inserts, edits and manipulation of individual rows are slow (we need to visit every aisle) so column-based storage is most advantageous in read-only & analytical use cases.

How should I use it?

The biggest gain you’ll see from this technology is avoiding directly querying data in relational data stores by instead utilizing an analytics data store or cache. Cloud providers have various options for storing large flat files but to start an on-premise file share works too (I nearly always recommend starting simple and scaling when necessary). Use this to store datasets which are pre-joined as relational database will outperform column stores when executing joins and you’ll save this processing step by caching the resulting dataset. Data can be stored in a number of formats, Parquet is the most common.

┌││└┌││││││└┌│││││││└───────P──────o──┌│└────s──────┌││└──t───s───────gD───a────M───rB─│││──l────o───e───e────nS───S─E──s────tu───Q─T──.────hm───L─L──p────lm────/──a────ya────E──r────r──┐││┘x──q────Sy──p──u────a──o──e────l──r──t────e──t─┐│┘───s──┌││└─────────────────┌│└────────A────┐││┘────n─u─────M──a─s─────yD──l─e──┌│││└─┌││└──SB──y─r─────A────Q─│││─t─s─────g─U───L──i─.───🦆──g─s────E─c─c───A──r─eD────T─s─s───In──e─ra────L──v───Dna──g─s────/─D────u-l──a─Ah──┐││┘E─a────cPy──t─nb──x─t────krt──e─ao──p─a───│││─Doi─│││─d─la──o─s┐│┘──Bcc───yr──r─t─R─es─G─D─td──┌││└t─o─e─s─e─a─i─────r┌│└─a─s─n─t─c─────e───d──e─a─s──────e────r─s────O──(─v──F──a─e────r──N─e──i┐│││┘t─t┐││┘──aD──F─n──le─s──cB──S─t──e───l──)─s──s─┌││└──e─│││──.──────────j────E────E──s────v────T──o────eS────L──n────nA──┐││┘/──────tS──E────────x─┐│┘───MR──p────ee──o────tp──┌││└r─┌│└───ro────t──────ir───S───l────ct───Q───o────s───L───g──────────s───────SD───.───────eB───c───┐││┘──r───s─────v───v─────e─│││─┐│┘────r───────E──────T┐││││││┘┐│││││││┘┐││┘L/Export

The goal here is to reduce push & pull from your relational data stores and to store the data in a column format (you’ll get less juice from the squeeze if DuckDB needs to translate from CSV or another row based format). This architecture will mean repeat queries are more performant as the data is stored natively in a column storage. It has the added benefit of reducing load on databases and transfering it to the data store.

DuckDB can pull from relational databases directly and this will still result in more performant queries, but will still be slower than storing it in DuckDB’s native format or Parquet.

How do I get started?

Super simple, if you’re using SAS the 2025.07 release includes SAS/ACCESS for DuckDB and will work once you upgrade - simply use a LIBNAME:

LIBNAME POND DUCKDB;

This libname can create local DuckDB storage or connect to NFS or Cloud storage like Azure Blob Storage.

DuckDB also runs as an in-process database so you can integrate it into your application or run queries as command line tools. I’ll avoid trying to reinvent the wheel with a getting started guide, but grab your favourite dataset and run some queries (time will show you how long the query took):

time duckdb < query.sql