11 ms·

As someone that uses SQLite a bit for analytics, I feel like the elevator pitch on the github and the website ( https://www.duckdb.org/ https://www.duckdb.org/

• cheez 6y ago
  Same. Best elevator pitch would be how it complements (or replaces) SQLite
• tdeck 6y ago
  Just came here to say the same thing. I read the readme and it's not clear what this does exactly as compared to SQLite, particularly since it used SQL. What kinds of analytics is it optimized for? Does it have different data structures for streaming data? I can tell a lot of work went into this project and I think it could benefit from a more thorough introduction.
  • jabba_d_hut 6y ago
    It's a columnar database and the query engine is inspired from MonetDB which processes columns in vectors - "Execution engine: The vectorized execution engine is inspired by the paper "MonetDB/X100: Hyper-Pipelining Query Execution" by Peter Boncz, Marcin Zukowski and Niels Nes.".
    • tdeck 6y ago
      Interesting that the distinctive features are buried in the acknowledgements section.
      • pgwhalen 6y ago
        I wouldn't call that "buried in the acknowledgements section." I wish more software projects would present themselves in this way actually. Inevitably everything is built upon prior art, so it makes sense to say where the ideas came from or which parts are novel (if any - sometimes packaging together a bunch of existing ideas is valuable in itself). It sure beats "[project] is a new key-value store that is even faster than [other project]".
• mkl 6y ago
  Agreed. I was trying to understand it earlier, and there doesn't seem to be a description of what its features are and what it's good for. It jumps straight to "how to use" without "why to use".
  • ngenstyle 6y ago
    My first instinct based on a cursory glance (ie. specific language compatibilities) is that it's a portable embedded database for data analytics of the cybersecurity domain, particularly mobile penetration testing possibly.
• jdotjdot 6y ago
  Adding to the pile of people agreeing. SQLite works okay for analytics for now, and I would be super-interested in something embeddable more tailor-made for analytics, but don't understand yet in what specific ways DuckDB is an improvement.
• kyllo 6y ago
  Here's a YT video of one of the implementers giving a talk to CMU students about what DuckDB is, why they made it, and how it works: https://www.youtube.com/watch?v=PFUZlNQIndo https://www.youtube.com/watch?v=PFUZlNQIndo In a nutshell, it's a file-per-database embedded DB like SQLite, and it's installable as a Python or R package from PyPI or CRAN, but unlike SQLite, it's a column store with vectorized operations so it's more performant for analytical queries.
  • alecco 6y ago
    I usually just extend SQLite with virtual columns and try to do the filtering/aggregate before sending tuples to the engine. This does the trick most of the time and it's relatively straightforward to implement.
    • mywacaday 6y ago
      I'm need to move an excel sheet to something like SQLite, hadn't heard of virtual columns, fixes most of the issues I have, thanks!
  • stingraycharles 6y ago
    The fact that it’s column oriented is a massive improvement for analytics over SQLite, I don’t understand why they don’t mention this more explicitly on their website and GitHub pages.
  • sradman 6y ago
    The video clarified my initial confusion. DuckDB is an embedded column store library and borrows the SQLite C API and some of the SQLite philosophy (single cpp/hpp amalgamation file, no external dependencies, and resource sharing) but it uses PostgreSQL syntax and parser, and a column oriented memory/storage engine based on their experience with MonetDB/VectorWise. DuckDB borrows code from HyPer DB which looks like a in-memory hybrid OLTP/OLAP clone of SAP HANA. They did not discuss any hybrid OLTP/OLAP implementation details in the video so the borrowed HyPer code may only be the wrapper for the PostgreSQL parser. Desktop column stores do have a precedent in the enterprise market. Microsoft's first attempt at a column store started with embedded integration with PowerPivot and eventually made its way into SQL Server. Sybase IQ, built on zero-admin SQL Anywhere, has been used as a local column store for Pivot Table applications (think BusinessObjects, Cognos, Tableau, QlikView, and Excel Pivot Tables). Pivot Table integrations are typically custom-built for each data warehouse vendor or MDX/XMLA driver. DuckDB is focused on integrating with the desktop data science platforms R and Python (dbplyr and pandas). Analytics/OLAP in SQL means lightning fast GROUP BY queries and more recently SQL OLAP Windows. "SQLite for Analytics" confused me but that is probably due to my own historical mental clutter. The name DuckDB refers to a pet duck and not the duck typing system in SQLite.
• georgewfraser 6y ago
  It’s much faster for analytical queries, because it’s a column store rather than a row store.
  • ivan_ah 6y ago
    Some more info about columnar DBs here https://en.wikipedia.org/wiki/Column-oriented_DBMS https://en.wikipedia.org/wiki/Column-oriented_DBMS for anyone not familiar with the idea.
• wenc 6y ago
  I bookmarked DuckDB some time ago intending to revisit, and when I did, I had forgotten why I bookmarked it and the website's contents unfortunately didn't help for reminding me. If the documentation had said something like "a columnar database based on sqlite, optimized for analytics", I think that would pique the interest of many. Columnar database = column compression = fast filtering/aggregations = high performance analytics compared to pure row stores. There are very few free columnar databases available that are production grade (or built on production grade technology), so a columnar database built on top of sqlite is big news.
  • mytherin 6y ago
    Apologies, a website rework is currently in progress :) DuckDB is indeed a free columnar database system, but it is not entirely built on top of SQLite. It exposes the same front-end and uses components of SQLite (the shell and testing infrastructure), but the execution engine/storage code is new.
    • erichocean 6y ago
      I can see why you are avoiding "built on top of SQLite", but I really think you should reconsider and go with that terminology. It means the "right" thing to most people: I can use this like I use SQLite now.
• smbrian 6y ago
  The pitch is faster, and more space efficient since column stores are far better for analytics than row stores. Some benchmarks that found ~5-10x speedup: https://uwekorn.com/2019/10/19/taking-duckdb-for-a-spin.html https://uwekorn.com/2019/10/19/taking-duckdb-for-a-spin.html Consider someone who analyzes medium-sized volumes of (slowly-changing) data -- OLAP, not OLTP. People who need to do this primarily have 2 alternatives: * columnar database (redshift, snowflake, bigquery) * a data lake architecture (spark, presto, hive) The latter can be slow and wasteful, because the data is stored in a form that allows very limited indexing. So imagine you want query speeds that require the former. Traditional databases can be hugely wasteful for this usecase -- space overhead due to no compression, slow inserts due to transactions. The best analytics databases are closed-source and come with vendor lockin (there are very few good open-source column stores -- clickhouse is one, duckdb is another). Most solutions are multi-node, so they come with operational complexity. So DuckDB could fill a niche here -- data that's big enough to be unwieldy, but not big enough to need something like redshift. It's analogous to the niche SQLite fills in the transactional database world.
  • cheez 6y ago
    I read the post and had a couple of points: 1. Is DuckDB similar to having indexes on each column? Because generally when something is slow, the solution is indexes. I have a 100 GB database which records real time data and is lightning fast because of some minor tuning. 2. The example of STDDEV not be available shows the author's unfamiliarity with SQLite which worries me. https://docs.python.org/2.7/library/sqlite3.html#sqlite3.Connection.create_function https://docs.python.org/2.7/library/sqlite3.html#sqlite3.Con... Could very easily have made a similar interface if necessary.
    • kyllo 6y ago
      Indexes don't solve the same problem as columnar because with an index you still have to dereference a pointer, read the page (performing I/O if on-disk), and locate each tuple you want within the page--the tuples you need to aggregate from may be scattered across many pages, and you have to read the whole page into memory even if you only want one column. With a column store, the values of the same column are stored contiguously so if you're summing a column for example, you can read one or more pages that could consist entirely of values the column you need to sum, sequentially. It's much more efficient for aggregations due to cache locality--much fewer cache misses and I/O operations needed to summarize a column. The major trade-off is that updates and deletes are more expensive in a column store. But this is usually fine for OLAP workloads.
• tejtm 6y ago
  It might be dating me, but seem like a larger version of the difference between C and FORTRAN arrays, C being relational like and FORTRAN being column store like. now get off my lawn :)
  • wenc 6y ago
    Well, seems to me the difference between rowstores and columnstores might be a little more profound than just row and column major orders in C and FORTRAN. (Which I remember from back in the day) In columnstores, it’s not just the ordering that’s advantageous for analytics. It’s also the possibility of scanning only the columns required (to the exclusion of others), and the possibility of column compression using RLE compression, which speeds up search when there are many repeated elements (also compression makes searches cache efficient). This means if you workload only involves a few columns, and your primary operations are filter and aggregate — analytics workloads — then the performance gains are tremendous. On the other hand, if you want to optimize for row by row writes (OLTP), and if your workloads involve operations on many columns, then a rowstore is advantageous. Row and column major layouts are more about exploiting sequentiality, but in practice both C and FORTRAN can provide good linear algebra performance. But for analytics workloads on row vs columnstores, the difference in performance can be in the orders of magnitude. The fastest analytics databases in the world (Kdb, Vertica, Exasol, some of the newer GPU based databases) are almost all columnar.
• jameskraus 6y ago
  If you want to dig into the differences more, I highly recommend the book Designing Data Intensive Applications. Analytics systems have different requirements from normal transaction processing systems, so it naturally follows that DuckDB could specialize to fill those requirements better than SQLite. Many people bring up row stores (SQLite) vs column stores, but there are many other interesting differences and optimizations to be made, so I can see how SQLite may be leaving some niche unfilled.