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.
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.
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.
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.Con...
Could very easily have made a similar interface if necessary.
ad 1) An index is an additional data structure that takes space and needs maintenance when the data changes. This is especially problematic for realtime data. DuckDBs main data structure and implementation is designed for efficient scanning without needing an additional index ("vectorised columnar").
ad 2) We support STDDEV_SAMP and STDDEV_POP. There is also an interface to define your own functions. We are very aware of how SQLite is doing things and even support their client API. But we also might eventually add their way of adding UDFs from Python.
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.
A page of field values is a much better structure to compress than a page of tuple values. This is especially true of the sort of denormalized data that is typical in analytical workloads. E.g. dates may only be considered in a period of a few years - this will take much less space to encode than the full allocation for a date data type. Similarly we may have categorical fields with only a few values. Imagine a product category with only 8 values. This can take a set of numbers which may be otherwise stored as an integer, and encode it to 3 bits, plus some space for a dictionary.
Another easy bit of compression to apply to columns is run length encoding. Dates, again, serve as a good example. If the data is sorted by date, you can choose to store a data structure that captures (start index, date value, end index). If you had 1,000 records on the same date, you could encode this with only three likely-integer-sized values, rather than 1,000 repetitions of a single likely-integer-sized value.
Such compression offers several benefits. First, it can reduce data volumes, and allow more query workload to come from RAM, either through DB or filesystem caching, or by an in-memory architecture. Additionally, the smaller the data size is, the faster a scan can be completed. Analytic workloads tend toward queries that require a full table scan (though with a columnstore, it's more a full column scan). If the data size is smaller, then you can stream the entire thing through whatever your query is faster.
Do you have an example of a workload that benefits from this structure?
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.
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.
It means the "right" thing to most people: I can use this like I use SQLite now.
It sure beats "[project] is a new key-value store that is even faster than [other project]".
now get off my lawn :)
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.
I'm sure there's some great reason, but I don't see it: Why would I want to use this over SQLite? Is it considerably faster? Does it handle large scale better? I suppose I could try and throw it some use cases I could come up with, but I'd worry I'm missing the best use case for this tool.