8 ms·

Comparing SQLite, DuckDB and Arrow with UN trade data

246 points by marcle 5y ago

isoprophlex 5y ago
The relevant results from the linked article: ## format median_time mem_alloc ## 1 R (RDS) 1.34m 4.08GB ## 2 SQL (SQLite) 5.48s 6.17MB ## 3 SQL (DuckDB) 1.76s 104.66KB ## 4 Arrow (Parquet) 1.36s 453.89MB I'd bet that doing the same with Pandas would require time and space similar to RDS (1). I really hope DuckDB makes it in the Python world, everything I read about it seems very promising. Using it myself for toy projects was pleasant, too.
- ryndbfsrw 5y ago
  Have you used the Polars (https://www.pola.rs/ https://www.pola.rs/) package? It does what Pandas does with a fraction of the RAM and twice the speed
  - thinker5555 5y ago
    Thanks for that! Polars looks really interesting.
- avidphantasm 5y ago
  Here’s hoping that DuckDB will add support for spatial data, indexing, and query predicates. It would be great if this were a first-class feature instead of bolted on like SpatiaLite is to SQLite.
  - avidphantasm 5y ago
    Maybe I should get involved and try to help this…however DB hacking is a bit out of my depth at the moment…
  - loxias 5y ago
    It could! But those are not exactly "easy things to do" ;) I'm sure it could happen, for a sufficiently large grant.
    - avidphantasm 5y ago
      Are you a contributor? Is there a way to bring this up with the team? Perhaps a place to start would be to add support for storing OGC Simple Features and corresponding to/from conversion functions. I don’t have a lot of spare time, but may want to take a stab at a proof of concept if some developers could help orient me to the code.
      - mytherin 5y ago
        DuckDB developer here - we absolutely welcome outside contributions. Feel free to open an issue or discussion on our github for a feature request, and we would be happy to point you in the right direction!
- kristjansson 5y ago
  Of course, since the memory allocation comes from Rprofmem (via benchmark::mark), this only measures allocations of memory for objects on R's heap. Allocations by C extensions (like DuckDB and SQLite) aren't tracked. They're surely _more_ space efficient than just reading everything into RAM, but perhaps by a smaller margin than shown here.
- petespeed 5y ago
  https://duckdb.org/docs/api/python https://duckdb.org/docs/api/python
- hantusk 5y ago
  this benchmark is more comprehensive for this type of analytical work: https://h2oai.github.io/db-benchmark/ https://h2oai.github.io/db-benchmark/
- andyferris 5y ago
  I thought pandas was in some sense evolving towards arrow? (For those who aren’t aware, they share a co-creator). Edit: that said a benchmark would be worthwhile, and similarly the tidiverse should evolve towards arrow speed I hope since they also share a co-creator.
mjburgess 5y ago
Does the R code, for eg., SQLite, actually build an SQL query? This just looks like a tidyverse library comparison. I'd expect a benchmark using their own libs, rather than assuming tidyverse will have an optimal way of querying them.
- wodenokoto 5y ago
  Yes, it does. The ´collect()´ function at the end, is responsible for executing the previous chain in the database. The fact that ´tbl´ in the beginning is called with a connection, ensures that all the following functions don't execute on a dataframe, but instead builds up a query. R, kinda like Julia, executes different versions of a function, depending on the type of the first parameter. From an API point of view, I think it is absolutely ingenious!
  - chrisjc 5y ago
    In other words, it creates a "push-down" to SQLite? So that we can appreciate your point about the elegance of the API, how would you do the opposite to allow execution to take place on the data-frame?
    - phillc73 5y ago
      There's an R package for that! Well, multiple really. The `sqldf`[1] package has been around for a while, there's `tidyquery`[2] and also one I wrote called `duckdf`[3]. All three support writing SQL queries to directly access dataframes. [1] https://github.com/ggrothendieck/sqldf https://github.com/ggrothendieck/sqldf [2] https://github.com/ianmcook/tidyquery https://github.com/ianmcook/tidyquery [3] https://github.com/phillc73/duckdf https://github.com/phillc73/duckdf
      - chrisjc 5y ago
        Not exactly what I was asking, but still some pretty awesome links/projects. Thanks for the links. I'm pretty sure that I'll be getting my hands dirty with R eventually.
cosmaioan 5y ago
I would be curios to see DuckDB with parquet https://duckdb.org/docs/data/parquet https://duckdb.org/docs/data/parquet
- flakiness 5y ago
  It does work well. In fact, probably it's more common for people to use external formats like Parquet or Arrow than using its internal format since the internal format isn't stable yet.
einpoklum 5y ago
Note that: * The comparison used a single, simple, query: filter, group by, and sum. * The comparison does not include repeated multiple queries (similar or dissimilar). * A moderate amount of data is used. This is certainly relevant and important, but it's not an extensive comparison.
- arjenpdevries 5y ago
  (All the possible extensions you mention would be beneficial for the solution using DUCKDB.)
ryndbfsrw 5y ago
I am certain I'm in the wrong here but I'm struggling to understand Arrow's USP. I (originally) assumed it meant python/R users would be able to get around memory limitations when model-fitting but all the examples I've come across are just data manipulation and none of the main modeling packages support it. Those who are using it, what am I missing?
- arjenpdevries 5y ago
  Arrow is meant to share data as-is instead of requiring a copy, and, often, serialization/deserialization. (This requires both ends to be able to handle the Arrow representation.) Eg, it has the potential to speed up query processing in PySpark by a lot, because of its Java/Python interoperability.
- twobitshifter 5y ago
  My use case is that since Arrow keeps all data types and dumps the in memory table to disk, this allows me to backup my work and later reload the data and keep going. Loading and writing the data to disk is very fast It’s much better than using hdf5 for me in that regard.
  - beagle3 5y ago
    And in most cases, if you memory map (mmap on Linux/BSD, MapViewOfFile on windows) it’s way faster than reading the file - because you only ever read what’s needed on one hand, and it stays in cache between invocations.
- RobinL 5y ago
  As a data data scientist, I also found this pretty confusing so I spent some time trying to understanding it better. I wrote it up as a blog post: Demystifying Apache Arrow https://www.robinlinacre.com/demystifying_arrow/ https://www.robinlinacre.com/demystifying_arrow/
- tomnipotent 5y ago
  Arrow eliminates ser/der, and if all actors in the workflow use the format you could see drastic performance improvements for a wide variety of workloads. I've seen ser/der for multi-GB+ processes take up half of the total clock time of the task. Adoption is slow, but it'll get there.
Fiahil 5y ago
So DuckDB is a kind of DataFusion (https://arrow.apache.org/datafusion/ https://arrow.apache.org/datafusion/) ?
- loxias 5y ago
  I don't know what a DataFusion is, or what Arrow is, but DuckDB is, in my understanding (and use of it), an "almost" drop in replacement for SQLite, except with better performance. Anytime you might want to use SQlite as an actual RDBMS (not as an easy disk fileformat), drop in DuckDB instead and get much better performance. DuckDB is actually designed to support OLAP.
  - spaniard89277 5y ago
    Wouldn't SQLite still be better at OLTP?
  - Zababa 5y ago
    > Anytime you might want to use SQlite as an actual RDBMS (not as an easy disk fileformat), drop in DuckDB instead and get much better performance. DuckDB is actually designed to support OLAP. Aren't RDBMS good at both OLAP and OLTP while not being the best at each?
- chrisjc 5y ago
  Did a little searching, but didn't find much about DuckDB and Arrow. > DataFusion is an extensible query execution framework, written in Rust, that uses Apache Arrow as its in-memory format. Does DuckDB use Arrow as its in-memory format? If so, that's pretty awesome. I'm hearing about DuckDB for the first time today and it's already leaving an impression on me.
  - Fiahil 5y ago
    > Does DuckDB use Arrow as its in-memory format? I don't think it's the case, but they serve the same purpose anyway: inject the CSV or parquet files you got from the data wharehouse into something you can use for analytics. Memory-based storage is super fast and fine until your datasets reach a terabyte. So you have a few solutions left: 1. You fire a fresh postgres and you store your data there. It's going to take quite a bit of time to inject those 600M lines into the instance. 2. You have some kind of tool (DataFusion) that allow you to run SQL queries on those parquet files without going through a painful copy/insert process. Performances matter a bit, but not as much because you're already avoiding a full dataset copy and a large-instance expense in the process. Even a 40% perf hit vs Postgres is absolutely acceptable, because you're already winning on both sides (total execution time, financial expenses).
homerowilson 5y ago
I enjoyed this comparison, thanks! Here is a related generally R-centric comparison that you might enjoy of DuckDB, dplyr, data.table, etc. applied to five data-sciency problems I wrote up a few months ago: https://github.com/bwlewis/duckdb_and_r https://github.com/bwlewis/duckdb_and_r
marcle 5y ago
TLDR: Arrow and DuckDB provide fast database aggregates compared with R's RDS format and, to an extent, SQLite. It is unclear how much functionality is available for Arrow under R: any comments? It would also be interesting to see a similar benchmark for Python, which could include the embedded version of MonetDB -- an R package for MonetDB/e is not yet available. Edit: amended the TLDR to reflect jhoechtl's and wodenokoto's comments. SQLite provided reasonably memory efficient aggregates.
- deleted 5y ago
  [deleted]
- jhoechtl 5y ago
  > TLDR: Arrow and DuckDB provide fast and memory efficient database aggregates compared with R's RDS format and SQLite. Arrow and RDS were fast to load. I nowhere read that the authors come to the conclusion that SQlite does NOT provide fast and memory efficient database aggregates. Instead they conclude > SQLite and DuckDB files consists in a single large file (3 GB and 2.5 GB each), but the indexes we created allow their respective packages to read a copy of the tables that has just the year and reporter_iso columns, and therefore allows very fast filtering to provide the exact location of what we need to read in the large tables.
- wodenokoto 5y ago
  I came to a completely different conclusion. RDS is slow to load, because it has to unzip and read everything into memory. All others are fast to load because they have somesort of index into data on disk (at the cost of being much larger at rest) Everything else is fast to load compared to RDS, with arrow being the fastest because its index happened to be optimized for the test query.
chrisjc 5y ago
I'm a little confused with Arrow being included in the comparison. What does Arrow have to do with Parquet? We are talking about the file format Parquet, right? Does Arrow use Parquet as its default data storage format? But isn't Arrow a format too? As I understand it, Arrow is a format optimized for transferring in-memory data from one distributed system to another (ser-des), while also facilitating and optimizing certain set operations. From RAM in one system to RAM in another. Moreover, since Arrow is a format, why is it being compared to databases like SQLite and DuckDB? If we're talking about formats, why not compare Arrow queries against Parquet data to DuckDB queries against Parquet data? https://duckdb.org/docs/data/parquet https://duckdb.org/docs/data/parquet Why not at least benchmark the query execution alone instead of startup and loading of data? For Arrow, isn't it assumed that there is an engine like Spark or Snowflake already up and running that's serving you data in the Arrow format? Ideally, with Arrow you should never be dealing with data starting in a resting format like Parquet. The data should already be in RAM to reap the benefits of Arrow. Its value proposition is it'll get "live" data from point A to B as efficiently as possible, in an open, non-proprietary, ubiquitous (eventually) format. Exactly what of SQLite, DuckDB and Arrow is being compared here? I would assume the benefits of Arrow in R (or DataFrames in general) would be getting data from a data engine into your DataFrame runtime as efficiently as possible. (just as interesting might be where and how push-downs are handled) Perhaps I'm missing the trees for the forest? No disrespect to the author... Seems like they're on a quest for knowledge, and while the article is confusing to me, it certainly got me thinking. Disclaimer: I don't read R too good, and I'm still struggling with what exactly Arrow is. (Comparisons like this actually leave me even more confused about what Arrow is)
- pletnes 5y ago
  This isn’t a comp sci geek doing performance analysis. It’s a guy who wants to do stats, and then throws up 3 options and discusses tradeoffs - memory and time consumption. He got his little workflow from a few minutes to a second, and that is what matters to him.
- wodenokoto 5y ago
  I was also confused at the headline but felt the comparison made sense by the end of the article. > Why not at least benchmark the query execution alone instead of startup and loading of data? Because they don’t intend to have R running with 4gb of data in memory constantly. I’d assume there’s a SQLite /duckdb instance running, but that it is negligible. Maybe the test even includes starting the database server. So the perspective is “I want some data on my hard drive, and I want to query it from R every once in a while, what are my options?” And suddenly a file format becomes comparable to a database. Another way to look at it is to consider the compiled arrow code in the arrow library as a database engine. Then arrow is just another alternative to SQLite.
m0zg 5y ago
Might as well try Arrow Feather, there's an R binding for it. This Flatbuffers-based format can be memory mapped and it requires very little decoding. Super fast and light on resources. Also, Parquet supports different encodings, some of which may be faster, denser, and/or consume less RAM, or all of the above.
xiaodai 5y ago
I think disk.frame should fare pretty well if you use the `srckeep` function.
dgudkov 5y ago
DuckDB is faster than SQLite on an analytical type of query. That's expected because DuckDB is column-based and designed exactly for this type of queries. The analysis would be more telling if it measured insert performance as well.
- marcos100 5y ago
  Kind of. The dataset is small and he probably need to create the database at most once per day and have to read a lot more for his analysis/exploration. Columnar databases are a good fit here.
- HelloNurse 5y ago
  Or reading all fields of a very clustered 5% of rows, to give an even greater advantage to the row-oriented DBMS. Mature users need to evaluate how their workloads match the benchmarks, it isn't a matter of DuckDB or SQLite "winning".
HighChaparral 5y ago
Possibly nothing more than a side issue, but why are the indexes being created before the bulk insert, rather than after?
- fnord123 5y ago
  Also, a composite index on (reporter_iso, year) instead of individual indexes would work better in a single group-by afaict.
flakiness 5y ago
Although this article is focusing on dplyr which provides an uniform API over various data sources, I guess the choice depends more on the personal taste: Do you like staying on R / Python or on SQL? If you prefer SQL, using raw Arrow or in-memory data doesn't make sense - Except duckdb does support SQL queries over these non-db data sources as well. Anyway, R data ecosystem very rich for both R and SQL fans and I as a Python user feel a bit envious.
vertere 5y ago
I'm a bit surprised the DuckDB file size is that large (5x Parquet, and nearly as large as sqlite). I haven't used DuckDB, but I thought it had columnar storage and so would have better compression.
- mytherin 5y ago
  DuckDB developer here, compression is still a work in progress and is being incrementally implemented and added to the system. For now the storage is still mostly uncompressed, but expect this to improve in the coming months.
knome 5y ago
>dbSendQuery(con, "CREATE INDEX year ON yrpc (year)") >dbSendQuery(con, "CREATE INDEX reporter_iso ON yrpc (reporter_iso)") would sqlite use these for the query given? it seems they wouldn't help with grouping, and the grouping seems antagonistic to filtering. if you're trying for performance, you can avoid having the query engine refer back to the original table during processing by using a covering index that includes the data fields in addition to the query fields "CREATE INDEX covering (reporter_iso, year, trade_value_usd_exp, trade_value_usd_imp)" or maybe even something like "CREATE INDEX covering (reporter_iso, year, reporter_iso, trade_value_usd_exp, trade_value_usd_imp)" Though I'm not sure how it's query planner would do with a duplicate column in the index.
- fnord123 5y ago
  The same field, twice, in the same index is some next level thinking. Where would you come across this kind of technique?
  - knome 5y ago
    It was an offhand. I was thinking to keep them in the same order as the group-by while still keeping the field in front for the initial filter, but given a moments thought I doubt it would make any difference, besides wasting some space in the index.
CRConrad 5y ago
Here's another HN page on an article comparing SQLite to other DBs, from just the other day :-) https://news.ycombinator.com/item?id=7432619 https://news.ycombinator.com/item?id=7432619
WFHRenaissance 5y ago
Why aren't indexes being created on the RDS instance? I know it's out of scope based on the title, but also it's used for comparison in the final results table.
kristjansson 5y ago
Lots of confusion derives from the best-of-breed parquet readers for Python and R residing in the Arrow packages, mostly because Arrow is (and does) a lot of things. There's: * arrow, a in-memory format for dataframe-shaped things supporting fast computation, zero-copy sharing, etc. * arrow Feather V1, an on-disk format for dataframe-shaped things * arrow IPC, a (de)serialization for arrow buffers and a protocol for sending/receiving to other processes. * arrow Feather V2, an on-disk format that's basically the IPC serialization written to file[1] * arrow Flight, a protocol for requesting/sending/receiving data to remote machines that's basically gRPC layered over the IPC format * arrow DataFusion/Ballista, nascent system(s) for local/distributed query execution over arrow buffers * other subprojects I'm surely forgetting * a (very good) C++ Parquet reader [2]/[3] developed under the auspices of the project * libraries in / bindings to many languages, including R and Python, supporting interaction with (subsets of) the above. It's only the last piece that's exposed to most data-science-y users, and thus identified with the 'arrow' name. Since those libraries are also very good, and hiding their abstractions well, those users are free to use the functionality relevant to them, be it dealing with parquet, feather, etc. without needing to understand how they work. Not that this is a criticism of the project, or those users! Arrow encompasses lots of different functionality, which enables it to provide different things to different people. As a result, though, 'Arrow' connotes lots of different things (and different _sorts_ of things) to different users, which can cause some confusion if terms aren't fully specified, or even a bit misunderstood [1] https://stackoverflow.com/a/67911190/881025 https://stackoverflow.com/a/67911190/881025 [2] https://github.com/apache/parquet-cpp https://github.com/apache/parquet-cpp [3] https://github.com/apache/arrow/tree/master/cpp https://github.com/apache/arrow/tree/master/cpp

246 points by marcle 5y ago

The relevant results from the linked article: ## format median_time mem_alloc ## 1 R (RDS) 1.34m 4.08GB ## 2 SQL (SQLite) 5.48s 6.17MB ## 3 SQL (DuckDB) 1.76s 104.66KB ## 4 Arrow (Parquet) 1.36s 453.89MB I'd bet that doing the same with Pandas would require time and space similar to RDS (1). I really hope DuckDB makes it in the Python world, everything I read about it seems very promising. Using it myself for toy projects was pleasant, too.

ryndbfsrw 5y ago
Have you used the Polars (https://www.pola.rs/ https://www.pola.rs/) package? It does what Pandas does with a fraction of the RAM and twice the speed
- thinker5555 5y ago
  Thanks for that! Polars looks really interesting.
avidphantasm 5y ago
Here’s hoping that DuckDB will add support for spatial data, indexing, and query predicates. It would be great if this were a first-class feature instead of bolted on like SpatiaLite is to SQLite.
- avidphantasm 5y ago
  Maybe I should get involved and try to help this…however DB hacking is a bit out of my depth at the moment…
- loxias 5y ago
  It could! But those are not exactly "easy things to do" ;) I'm sure it could happen, for a sufficiently large grant.
  - avidphantasm 5y ago
    Are you a contributor? Is there a way to bring this up with the team? Perhaps a place to start would be to add support for storing OGC Simple Features and corresponding to/from conversion functions. I don’t have a lot of spare time, but may want to take a stab at a proof of concept if some developers could help orient me to the code.
    - mytherin 5y ago
      DuckDB developer here - we absolutely welcome outside contributions. Feel free to open an issue or discussion on our github for a feature request, and we would be happy to point you in the right direction!
kristjansson 5y ago
Of course, since the memory allocation comes from Rprofmem (via benchmark::mark), this only measures allocations of memory for objects on R's heap. Allocations by C extensions (like DuckDB and SQLite) aren't tracked. They're surely _more_ space efficient than just reading everything into RAM, but perhaps by a smaller margin than shown here.
petespeed 5y ago
https://duckdb.org/docs/api/python https://duckdb.org/docs/api/python
hantusk 5y ago
this benchmark is more comprehensive for this type of analytical work: https://h2oai.github.io/db-benchmark/ https://h2oai.github.io/db-benchmark/
andyferris 5y ago
I thought pandas was in some sense evolving towards arrow? (For those who aren’t aware, they share a co-creator). Edit: that said a benchmark would be worthwhile, and similarly the tidiverse should evolve towards arrow speed I hope since they also share a co-creator.

mjburgess 5y ago

Does the R code, for eg., SQLite, actually build an SQL query? This just looks like a tidyverse library comparison. I'd expect a benchmark using their own libs, rather than assuming tidyverse will have an optimal way of querying them.

wodenokoto 5y ago
Yes, it does. The ´collect()´ function at the end, is responsible for executing the previous chain in the database. The fact that ´tbl´ in the beginning is called with a connection, ensures that all the following functions don't execute on a dataframe, but instead builds up a query. R, kinda like Julia, executes different versions of a function, depending on the type of the first parameter. From an API point of view, I think it is absolutely ingenious!
- chrisjc 5y ago
  In other words, it creates a "push-down" to SQLite? So that we can appreciate your point about the elegance of the API, how would you do the opposite to allow execution to take place on the data-frame?
  - phillc73 5y ago
    There's an R package for that! Well, multiple really. The `sqldf`[1] package has been around for a while, there's `tidyquery`[2] and also one I wrote called `duckdf`[3]. All three support writing SQL queries to directly access dataframes. [1] https://github.com/ggrothendieck/sqldf https://github.com/ggrothendieck/sqldf [2] https://github.com/ianmcook/tidyquery https://github.com/ianmcook/tidyquery [3] https://github.com/phillc73/duckdf https://github.com/phillc73/duckdf
    - chrisjc 5y ago
      Not exactly what I was asking, but still some pretty awesome links/projects. Thanks for the links. I'm pretty sure that I'll be getting my hands dirty with R eventually.

cosmaioan 5y ago

I would be curios to see DuckDB with parquet https://duckdb.org/docs/data/parquet https://duckdb.org/docs/data/parquet

flakiness 5y ago
It does work well. In fact, probably it's more common for people to use external formats like Parquet or Arrow than using its internal format since the internal format isn't stable yet.

einpoklum 5y ago

Note that: * The comparison used a single, simple, query: filter, group by, and sum. * The comparison does not include repeated multiple queries (similar or dissimilar). * A moderate amount of data is used. This is certainly relevant and important, but it's not an extensive comparison.

arjenpdevries 5y ago
(All the possible extensions you mention would be beneficial for the solution using DUCKDB.)

ryndbfsrw 5y ago

I am certain I'm in the wrong here but I'm struggling to understand Arrow's USP. I (originally) assumed it meant python/R users would be able to get around memory limitations when model-fitting but all the examples I've come across are just data manipulation and none of the main modeling packages support it. Those who are using it, what am I missing?

arjenpdevries 5y ago
Arrow is meant to share data as-is instead of requiring a copy, and, often, serialization/deserialization. (This requires both ends to be able to handle the Arrow representation.) Eg, it has the potential to speed up query processing in PySpark by a lot, because of its Java/Python interoperability.
twobitshifter 5y ago
My use case is that since Arrow keeps all data types and dumps the in memory table to disk, this allows me to backup my work and later reload the data and keep going. Loading and writing the data to disk is very fast It’s much better than using hdf5 for me in that regard.
- beagle3 5y ago
  And in most cases, if you memory map (mmap on Linux/BSD, MapViewOfFile on windows) it’s way faster than reading the file - because you only ever read what’s needed on one hand, and it stays in cache between invocations.
RobinL 5y ago
As a data data scientist, I also found this pretty confusing so I spent some time trying to understanding it better. I wrote it up as a blog post: Demystifying Apache Arrow https://www.robinlinacre.com/demystifying_arrow/ https://www.robinlinacre.com/demystifying_arrow/
tomnipotent 5y ago
Arrow eliminates ser/der, and if all actors in the workflow use the format you could see drastic performance improvements for a wide variety of workloads. I've seen ser/der for multi-GB+ processes take up half of the total clock time of the task. Adoption is slow, but it'll get there.

Fiahil 5y ago

So DuckDB is a kind of DataFusion (https://arrow.apache.org/datafusion/ https://arrow.apache.org/datafusion/) ?

loxias 5y ago
I don't know what a DataFusion is, or what Arrow is, but DuckDB is, in my understanding (and use of it), an "almost" drop in replacement for SQLite, except with better performance. Anytime you might want to use SQlite as an actual RDBMS (not as an easy disk fileformat), drop in DuckDB instead and get much better performance. DuckDB is actually designed to support OLAP.
- spaniard89277 5y ago
  Wouldn't SQLite still be better at OLTP?
- Zababa 5y ago
  > Anytime you might want to use SQlite as an actual RDBMS (not as an easy disk fileformat), drop in DuckDB instead and get much better performance. DuckDB is actually designed to support OLAP. Aren't RDBMS good at both OLAP and OLTP while not being the best at each?
chrisjc 5y ago
Did a little searching, but didn't find much about DuckDB and Arrow. > DataFusion is an extensible query execution framework, written in Rust, that uses Apache Arrow as its in-memory format. Does DuckDB use Arrow as its in-memory format? If so, that's pretty awesome. I'm hearing about DuckDB for the first time today and it's already leaving an impression on me.
- Fiahil 5y ago
  > Does DuckDB use Arrow as its in-memory format? I don't think it's the case, but they serve the same purpose anyway: inject the CSV or parquet files you got from the data wharehouse into something you can use for analytics. Memory-based storage is super fast and fine until your datasets reach a terabyte. So you have a few solutions left: 1. You fire a fresh postgres and you store your data there. It's going to take quite a bit of time to inject those 600M lines into the instance. 2. You have some kind of tool (DataFusion) that allow you to run SQL queries on those parquet files without going through a painful copy/insert process. Performances matter a bit, but not as much because you're already avoiding a full dataset copy and a large-instance expense in the process. Even a 40% perf hit vs Postgres is absolutely acceptable, because you're already winning on both sides (total execution time, financial expenses).

homerowilson 5y ago

I enjoyed this comparison, thanks! Here is a related generally R-centric comparison that you might enjoy of DuckDB, dplyr, data.table, etc. applied to five data-sciency problems I wrote up a few months ago: https://github.com/bwlewis/duckdb_and_r https://github.com/bwlewis/duckdb_and_r

marcle 5y ago

TLDR: Arrow and DuckDB provide fast database aggregates compared with R's RDS format and, to an extent, SQLite. It is unclear how much functionality is available for Arrow under R: any comments? It would also be interesting to see a similar benchmark for Python, which could include the embedded version of MonetDB -- an R package for MonetDB/e is not yet available. Edit: amended the TLDR to reflect jhoechtl's and wodenokoto's comments. SQLite provided reasonably memory efficient aggregates.

deleted 5y ago
[deleted]
jhoechtl 5y ago
> TLDR: Arrow and DuckDB provide fast and memory efficient database aggregates compared with R's RDS format and SQLite. Arrow and RDS were fast to load. I nowhere read that the authors come to the conclusion that SQlite does NOT provide fast and memory efficient database aggregates. Instead they conclude > SQLite and DuckDB files consists in a single large file (3 GB and 2.5 GB each), but the indexes we created allow their respective packages to read a copy of the tables that has just the year and reporter_iso columns, and therefore allows very fast filtering to provide the exact location of what we need to read in the large tables.
wodenokoto 5y ago
I came to a completely different conclusion. RDS is slow to load, because it has to unzip and read everything into memory. All others are fast to load because they have somesort of index into data on disk (at the cost of being much larger at rest) Everything else is fast to load compared to RDS, with arrow being the fastest because its index happened to be optimized for the test query.

chrisjc 5y ago

I'm a little confused with Arrow being included in the comparison. What does Arrow have to do with Parquet? We are talking about the file format Parquet, right? Does Arrow use Parquet as its default data storage format? But isn't Arrow a format too? As I understand it, Arrow is a format optimized for transferring in-memory data from one distributed system to another (ser-des), while also facilitating and optimizing certain set operations. From RAM in one system to RAM in another. Moreover, since Arrow is a format, why is it being compared to databases like SQLite and DuckDB? If we're talking about formats, why not compare Arrow queries against Parquet data to DuckDB queries against Parquet data? https://duckdb.org/docs/data/parquet https://duckdb.org/docs/data/parquet Why not at least benchmark the query execution alone instead of startup and loading of data? For Arrow, isn't it assumed that there is an engine like Spark or Snowflake already up and running that's serving you data in the Arrow format? Ideally, with Arrow you should never be dealing with data starting in a resting format like Parquet. The data should already be in RAM to reap the benefits of Arrow. Its value proposition is it'll get "live" data from point A to B as efficiently as possible, in an open, non-proprietary, ubiquitous (eventually) format. Exactly what of SQLite, DuckDB and Arrow is being compared here? I would assume the benefits of Arrow in R (or DataFrames in general) would be getting data from a data engine into your DataFrame runtime as efficiently as possible. (just as interesting might be where and how push-downs are handled) Perhaps I'm missing the trees for the forest? No disrespect to the author... Seems like they're on a quest for knowledge, and while the article is confusing to me, it certainly got me thinking. Disclaimer: I don't read R too good, and I'm still struggling with what exactly Arrow is. (Comparisons like this actually leave me even more confused about what Arrow is)

pletnes 5y ago
This isn’t a comp sci geek doing performance analysis. It’s a guy who wants to do stats, and then throws up 3 options and discusses tradeoffs - memory and time consumption. He got his little workflow from a few minutes to a second, and that is what matters to him.
wodenokoto 5y ago
I was also confused at the headline but felt the comparison made sense by the end of the article. > Why not at least benchmark the query execution alone instead of startup and loading of data? Because they don’t intend to have R running with 4gb of data in memory constantly. I’d assume there’s a SQLite /duckdb instance running, but that it is negligible. Maybe the test even includes starting the database server. So the perspective is “I want some data on my hard drive, and I want to query it from R every once in a while, what are my options?” And suddenly a file format becomes comparable to a database. Another way to look at it is to consider the compiled arrow code in the arrow library as a database engine. Then arrow is just another alternative to SQLite.

m0zg 5y ago

Might as well try Arrow Feather, there's an R binding for it. This Flatbuffers-based format can be memory mapped and it requires very little decoding. Super fast and light on resources. Also, Parquet supports different encodings, some of which may be faster, denser, and/or consume less RAM, or all of the above.

xiaodai 5y ago

I think disk.frame should fare pretty well if you use the `srckeep` function.

dgudkov 5y ago

DuckDB is faster than SQLite on an analytical type of query. That's expected because DuckDB is column-based and designed exactly for this type of queries. The analysis would be more telling if it measured insert performance as well.

marcos100 5y ago
Kind of. The dataset is small and he probably need to create the database at most once per day and have to read a lot more for his analysis/exploration. Columnar databases are a good fit here.
HelloNurse 5y ago
Or reading all fields of a very clustered 5% of rows, to give an even greater advantage to the row-oriented DBMS. Mature users need to evaluate how their workloads match the benchmarks, it isn't a matter of DuckDB or SQLite "winning".

HighChaparral 5y ago

Possibly nothing more than a side issue, but why are the indexes being created before the bulk insert, rather than after?

fnord123 5y ago
Also, a composite index on (reporter_iso, year) instead of individual indexes would work better in a single group-by afaict.

flakiness 5y ago

Although this article is focusing on dplyr which provides an uniform API over various data sources, I guess the choice depends more on the personal taste: Do you like staying on R / Python or on SQL? If you prefer SQL, using raw Arrow or in-memory data doesn't make sense - Except duckdb does support SQL queries over these non-db data sources as well. Anyway, R data ecosystem very rich for both R and SQL fans and I as a Python user feel a bit envious.

vertere 5y ago

I'm a bit surprised the DuckDB file size is that large (5x Parquet, and nearly as large as sqlite). I haven't used DuckDB, but I thought it had columnar storage and so would have better compression.

mytherin 5y ago
DuckDB developer here, compression is still a work in progress and is being incrementally implemented and added to the system. For now the storage is still mostly uncompressed, but expect this to improve in the coming months.

knome 5y ago

>dbSendQuery(con, "CREATE INDEX year ON yrpc (year)") >dbSendQuery(con, "CREATE INDEX reporter_iso ON yrpc (reporter_iso)") would sqlite use these for the query given? it seems they wouldn't help with grouping, and the grouping seems antagonistic to filtering. if you're trying for performance, you can avoid having the query engine refer back to the original table during processing by using a covering index that includes the data fields in addition to the query fields "CREATE INDEX covering (reporter_iso, year, trade_value_usd_exp, trade_value_usd_imp)" or maybe even something like "CREATE INDEX covering (reporter_iso, year, reporter_iso, trade_value_usd_exp, trade_value_usd_imp)" Though I'm not sure how it's query planner would do with a duplicate column in the index.

fnord123 5y ago
The same field, twice, in the same index is some next level thinking. Where would you come across this kind of technique?
- knome 5y ago
  It was an offhand. I was thinking to keep them in the same order as the group-by while still keeping the field in front for the initial filter, but given a moments thought I doubt it would make any difference, besides wasting some space in the index.

CRConrad 5y ago

Here's another HN page on an article comparing SQLite to other DBs, from just the other day :-) https://news.ycombinator.com/item?id=7432619 https://news.ycombinator.com/item?id=7432619

WFHRenaissance 5y ago

Why aren't indexes being created on the RDS instance? I know it's out of scope based on the title, but also it's used for comparison in the final results table.

kristjansson 5y ago

Lots of confusion derives from the best-of-breed parquet readers for Python and R residing in the Arrow packages, mostly because Arrow is (and does) a lot of things. There's: * arrow, a in-memory format for dataframe-shaped things supporting fast computation, zero-copy sharing, etc. * arrow Feather V1, an on-disk format for dataframe-shaped things * arrow IPC, a (de)serialization for arrow buffers and a protocol for sending/receiving to other processes. * arrow Feather V2, an on-disk format that's basically the IPC serialization written to file[1] * arrow Flight, a protocol for requesting/sending/receiving data to remote machines that's basically gRPC layered over the IPC format * arrow DataFusion/Ballista, nascent system(s) for local/distributed query execution over arrow buffers * other subprojects I'm surely forgetting * a (very good) C++ Parquet reader [2]/[3] developed under the auspices of the project * libraries in / bindings to many languages, including R and Python, supporting interaction with (subsets of) the above. It's only the last piece that's exposed to most data-science-y users, and thus identified with the 'arrow' name. Since those libraries are also very good, and hiding their abstractions well, those users are free to use the functionality relevant to them, be it dealing with parquet, feather, etc. without needing to understand how they work. Not that this is a criticism of the project, or those users! Arrow encompasses lots of different functionality, which enables it to provide different things to different people. As a result, though, 'Arrow' connotes lots of different things (and different _sorts_ of things) to different users, which can cause some confusion if terms aren't fully specified, or even a bit misunderstood [1] https://stackoverflow.com/a/67911190/881025 https://stackoverflow.com/a/67911190/881025 [2] https://github.com/apache/parquet-cpp https://github.com/apache/parquet-cpp [3] https://github.com/apache/arrow/tree/master/cpp https://github.com/apache/arrow/tree/master/cpp