Whenever I have to analyze largish¹ data in my laptop, I instinctively fire up my good old PostgreSQL server running locally, ingest the data in there and breezily wrangle my way through the data using SQL. Postgres may not be the fastest alternative out there. But it is feature-rich: you can throw anything at it, and it just works. It’s very hard to make it choke with an out-of-memory error and, by default, runs just fine in resource-constrained environments (e.g. in a Raspberry Pi).

I’ve kept an eye on DuckDB and Apache Arrow as other open-source alternatives for the workflow outlined above. Both share a number of features that make them very appealing for analytical workloads in general, and for the in-laptop-analytics in particular ²:

• Columnar format and vectorized operations
• In-process libraries (no need to install a server)
• Handle larger-than-memory datasets
• APIs available for several languages, including R and Python
• Open-source

I did try both DuckDB and Arrow some time ago (very quickly, though). But I do not use them routinely. At least not just yet. When I first tried them, Arrow did not support grouped aggregations and joins and that was a no-go for me. DuckDB did not have support for grouping sets or filtered aggregation (or at least I could not get it to work) and I found it slow ingesting data from CSV files (much slower than Postgres in that, even though queries were indeed faster).

But both projects move at a very fast pace and now I see release news where Arrow 6.0.0 “adds grouped aggregation and joins in the dplyr interface, on top of the new Arrow C++ query engine” and DuckDB announced “support for GROUPING SETS, ROLLUP, CUBE”. So it is time to take both projects for a spin again (and try and document the test-drive here).

The test-drive

Ultimately, I would like to run an existing data wrangling pipeline using DuckDB and Arrow as backends, and compare running time using PostgreSQL as backend. For this, DuckDB is particularly appealing because they use PostgreSQL parser and support a similar feature set. So, ideally, the pipeline can run on DuckDB simply by changing the connection without having to change the queries. That did not really work as smoothly, so I will be trying step-by-step: i) ingest the data, ii) try some aggregate queries, iii) joins, …, and let’s see how it goes.

The data

For this test it was key to use real data. Meaning a large data set that I would actually have to use / have used for analysis in my laptop. Of course I could have simply used the popular NYC Taxi trip record data or any other large data set publicly available. But that would have been missing the point. I wanted to keep this exercise as close as possible to an actual use case for me. After all, this is just an informal test to see how to integrate DuckDB and/or Arrow in my workflow and to assess the potential benefits, for example, in terms of processing time ³.

I will use a few samples of a larger data set ⁴ that contains 25 columns (2 numeric, 2 timestamp, 10 integer and the rest text, most of them categorical variables, some with low cardinality -e.g. sex-, others with millions of different values). I will use one round of data (~486M rows) stored in a csv file (uncompressed 82GB). First test in smaller samples (tiny, small, mid) before trying out using one whole round of data.

• Tiny data: 1M rows, csv uncompressed size ~170MB
• Small data: 10M rows, csv uncompressed size ~1,7GB
• Mid data: 60M rows, csv uncompressed size ~10GB
• Round data: ~486M rows, csv uncompressed size ~82GB

The code and software

I will be running this test from R, using {targets} to orchestrate the different steps and {bench} to benchmark code snippets and measure execution time.

• DuckDB version 0.3.0 [I did not pay attention and ran this using 0.3.0 and then I noticed 0.3.1 is already available, which enables by default multi-threading; so perhaps I should re-run this with 0.3.1 to see if it changes the results]
• Arrow version 6.0.0
• PostgreSQL version 14.1

Initially I will be using DuckDB and Arrow without fiddling with any configuration (no custom memory_limit or threads; just the defaults). In PostgreSQL, however, I typically tweak a couple of configuration parameters, in this case, shared_buffers=2GB and work_mem=1GB (see this link for some useful rules-of-thumb).

The code for the whole exercise is available in this Github repo

A poorman’s laptop

Running this may take a while, so I cannot really use my daily driver laptop. But I do have an old laptop lying around that can be used for this. It’s a 10-years old Dell XPS 17. I guess at the time it was a good machine: Processor Intel i7-2630QM, 8GB DDR3 RAM, SSD SATA, nowadays running Windows 10 Home. But for today’s standards, it can be considered a resource-constrained environment.

So it’s important to keep in mind the kind of machine running this exercise. Not sure about this, but I would consider the results as a lower bound to the performance improvements that DuckDB and Arrow could bring (e.g. Arrow explicitly try to take advantage of “modern hardware”). It will be interesting to see how much of a difference modern hardware would make. Hopefully soon, I will get my hands on a new fancy MacBook Pro to replicate this test.

Ingesting data

What will be tested

I will compare the time it takes to ingest the raw data from the csv files into each of the backends.

Arrow

In the case of Arrow, let’s try with the two key file formats: i. parquet and ii. arrow (aka ips/feather; see ?arrow:open_dataset). It boils down to use a couple of functions from the {arrow} R package: open_dataset() and write_dataset(), like this:

  csv_ds <- arrow::open_dataset(
    sources = src_file,
    format = "text",
    delimiter = ";"
  )
  
  arrow::write_dataset(
    dataset = csv_ds,
    path = arrow_path,
    format = "arrow" # and "parquet"
  )

DuckDB

For DuckDB, probably the simplest way is to use a CREATE TABLE AS statement with a query reading directly from the csv file using read_csv_auto(). This lets DuckDB detect everything (column names, delimiter, data types, etc.).

CREATE TABLE table_name AS
SELECT * 
FROM read_csv_auto('src_file.csv')
;

An alternative is the COPY statement. You would have to type more to “manually” create the table and decide on the data type for each column doing something like this: CREATE TABLE table_name ("col1" TEXT, ...);. And after that just copy the csv into that table:

COPY table_name
FROM 'src_file.csv' ( DELIMITER ';', HEADER )
;

A third alternative described in the documentation (hereafter labelled as copy2) is a slight variation of the COPY statement to let DuckDB detect the format “and omit the otherwise required configuration options.”

COPY table_name
FROM 'src_file.csv' ( AUTO_DETECT TRUE )
;

Let’s compare these three approaches, just to take a look if there is any overhead associated to auto detection of csv format and data types.

PostgreSQL

For Postgres, let’s also compare the COPY statement approach, which uses very similar syntax as in DuckDB

COPY table_name
FROM 'src_file.csv' (FORMAT 'csv', DELIMITER ';', HEADER )
;

And an alternative using a foreign table and csv foreign data wrapper.

CREATE EXTENSION IF NOT EXISTS file_fdw; 
CREATE SERVER IF NOT EXISTS csv_src FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE table_name_fg (
  col1 STRING,
  ...
)
SERVER csv_src
OPTIONS (
  FILENAME 'src_file.csv',
  FORMAT 'csv',
  HEADER 'TRUE',
  DELIMITER ';',
  ENCODING 'UTF-8'
)
;

CREATE TABLE table_name AS SELECT * FROM table_name_fg;

Results: Who is faster ingesting such data?

Well, for this exercise Arrow is fast!, way faster than Postgres, but also faster than DuckDB. Arrow native file format is also faster than parquet. Both the tests using tiny and small data show very similar results, where arrow file format is the fastest and compared to that it takes around:

• 1.6x longer to ingest into parquet
• 5x longer to ingest into DuckDB
• 15x longer to ingest into PostgreSQL

There are no major differences associated to the approach used to ingest in each backend (so, no major overhead for auto-detecting file format and data types in DuckDB).

Interestingly, when the data are somewhat larger, at some point DuckDB’s performance degrades and falls behind Postgres. While the other relative times remain in the same ballpark (1.6x parquet, ~12x Postgres), now it takes almost 25x longer for DuckDB to ingest the data, compared to arrow. In absolute numbers, while Arrow takes less than 2 minutes to ingest the data, DuckDB takes about 40 minutes (and Postgres less than 20 minutes).

The results ingesting a whole round of data ⁵ (~486 million rows in a single csv file, 82GB) are mostly consistent with the times shown above for mid data; DuckDB took more than 5 hours to ingest the whole file, followed by PostgreSQL that took a bit less than 3 hours, followed by parquet that took about 1 hour and the fastest is, again, Arrow taking only a bit more than 16 minutes.

## R version 4.1.2 (2021-11-01)
## Platform: x86_64-w64-mingw32/x64 (64-bit)
## Running under: Windows 10 x64 (build 19043)
## 
## Matrix products: default
## 
## locale:
## [1] LC_COLLATE=English_United States.1252 
## [2] LC_CTYPE=English_United States.1252   
## [3] LC_MONETARY=English_United States.1252
## [4] LC_NUMERIC=C                          
## [5] LC_TIME=English_United States.1252    
## 
## attached base packages:
## [1] stats     graphics  grDevices utils     datasets  methods   base     
## 
## other attached packages:
##  [1] conflicted_1.0.4  tarchetypes_0.4.0 targets_0.9.0     odbc_1.3.2       
##  [5] arrow_6.0.0.2     duckdb_0.3.0      DBI_1.1.2         ggplot2_3.3.5    
##  [9] tidyr_1.1.4       dplyr_1.0.7       magrittr_2.0.1   
## 
## loaded via a namespace (and not attached):
##  [1] Rcpp_1.0.7        compiler_4.1.2    pillar_1.6.4      tools_4.1.2      
##  [5] digest_0.6.29     bit_4.0.4         lifecycle_1.0.1   tibble_3.1.6     
##  [9] gtable_0.3.0      pkgconfig_2.0.3   rlang_0.4.12      igraph_1.2.10    
## [13] rstudioapi_0.13   cli_3.1.0         yaml_2.2.1        xfun_0.29        
## [17] fastmap_1.1.0     knitr_1.37        withr_2.4.3       fs_1.5.2         
## [21] generics_0.1.1    vctrs_0.3.8       hms_1.1.1         bit64_4.0.5      
## [25] grid_4.1.2        tidyselect_1.1.1  data.table_1.14.2 glue_1.6.0       
## [29] R6_2.5.1          processx_3.5.2    fansi_0.5.0       pacman_0.5.1     
## [33] purrr_0.3.4       callr_3.7.0       blob_1.2.2        codetools_0.2-18 
## [37] scales_1.1.1      ps_1.6.0          ellipsis_0.3.2    assertthat_0.2.1 
## [41] colorspace_2.0-2  utf8_1.2.2        munsell_0.5.0     cachem_1.0.6     
## [45] crayon_1.4.2

Wrap-up

My take home messages

• Arrow is indeed (unbelievably) fast to parse a csv and ingest data, particularly, into the arrow file format.
• DuckDB looked fine at the beginning, but got slow when the data got larger. Would need to check why that happens (it should be faster than Postgres).
• Despite being fast, Arrow did struggle with encoding.
• I could not quickly find how to tell arrow to read/ingest just some rows and not the whole csv file. It seems there is only support for skip_rows before column names and skip_rows_after_names, but not something like max_rows. This would be handy to inspect huge files and maybe write and test queries on a small sample, before send it on the whole data. In DuckDB, even though read_csv() does not have such an option, you can work around this by using a LIMIT statement while querying the csv file (but watch out if you are trying to avoid invalid lines, that approach can bite you).

Some non-exaustive TODOs:

• Try to find the underlying cause of DuckDB getting slower
• Perhaps fiddle with config options for each backend and ingest method. So far I used only the defaults.
• Take a look at the gains that would bring setting optimal data types
• Compare size on disk
• Compare aggregate queries
• Compare joins
• Sometime, try and run this kind of thing on a not-so-poorman’s laptop
• What about an alter table add column, in a columnar format?
• …