So, hype aside, what's the over/under on DuckDB vs Sqlite these days? I'm working on a Thing right now, has started with sqlite due to being a) good enough, b) stupendously optimized and nails hardened, and c) runs on your phone, your toaster, and your server.
What's DuckDB bringing to the table relative to sqlite, which seems like the boring-and-therefore-best choice?
And thinking about my specific project, it's clear that a great solution would allow me to pick row-vs-column storage on a per-table basis.
I basically have a graph over vectors, which is a very 'row-storage' kind of things: I need to get a specific vector (a row of data), get all of its neighbors (rows in the edges table), and do some in-context computations to decide where to walk to next on the graph.
However, we also have some data attached to vectors (covariates, tags, etc) which we often want to work with in a more aggregated way. These tables seem possibly more reasonable to approach with a columnar format.
1. For small individual queries, sqllite: think oltp. Naive graph databases are basically KV stores, and native ones, more optimized here. DuckDB may also be ok here but afaict it is columnar-oriented, and I'm unclear in oltp perf for row-oriented queries.
2. For bigger graph OLAP queries, where you might want to grab 1K, 100K, 1M, etc multi-hop results, duckdb may get more interesting. I'm not sure how optimized their left joins are, but that's basically the idea here.
FWIW, we began been building GFQL, which is basically cypher graph queries on dataframes, and runs on pure vectorized pandas (CPU) and optionally cudf (GPU). No DB needed, runs in-process, columnar. We use on anything from 100 edge graphs to 1 billion, and individual subgraph matches can be at that scale too, just depends on your RAM. It solves low throughout OLTP/dashboarding/etc for smaller graphs by skipping a DB, and we are also using for vector data, eg, AI pipelines on 10M edge similarity graphs where a single query combines weighted nearest neighbor edge graph traversals and rich symbolic tabular node filters. It may be (much) faster than duckdb on GPUs, lower weight if you are in python, duckdb's benefits of arrow / columnar analytics ecosystem, and less gnarly looking / more maintainable if you are doing graph queries vs wrangling it as tabular SQL. New and a lot on the roadmap, but fun :)
Knowing how little I know about your problem and constraints, I'd use SQLite for everything. Then if a specific part seems columnar, do some experiments in DuckDB and see what speedups or space savings you get and then use both.
Sounds like you are more than one, so have some folks do some 4 hour spikes on DuckDB where you think it might be useful.
I'd use Rust as the top level, embedding SQLite, DuckDB and Lua. In my application I used SQLite with FTS and then many SQLite databases not more than 2GB in size with rows containing binary blobs that didn't uncompress to more than 20 megs each.
> a great solution would allow me to pick row-vs-column storage on a per-table basis ... and do some in-context computations to decide where to walk to next on the graph
You may be interested in Datomic[0] or Datascript[1]
DuckDB only supports either one read+write client or arbitrary read clients with no writer. This is the biggest blocker for adopting it in a real-world hosted app.
No. Once a writer connects to a DuckDB database file, it does not allow any other.
SQLite always lets you connect additional reader (and writer) processes. You do not run into any locks until you execute write transactions concurrently.
What's DuckDB bringing to the table relative to sqlite, which seems like the boring-and-therefore-best choice?