DuckDB – An embeddable SQL database like SQLite, but supports Postgres features
duckdb.org
duckdb.org
It's not based on SQLite at all (except for borrowing the SQLite shell implementation) but it looks very much like SQLite, in particular:
- It's designed to work as an embedded library, eliminating the network overhead you usually get when talking to a database
- Each database is a single file on disk
- It ships as an "amalgamation" build - a single giant C++ file (SQLite is a single giant C file)
Impressively, if you run "pip install duckdb" it Just Works - you can then "import duckdb" and start using it, with an interface that looks very similar to the sqlite3 module that ships with Python.
The key reason this exists is that it's a column store, with vectorized operations across columns - making it ideal for analytical workloads. This blog entry has some benchmarks that illustrate how well it works in that regard: https://uwekorn.com/2019/10/19/taking-duckdb-for-a-spin.html
It's also backed up by some strong computer science. It's by the academic researchers behind MonetDB and includes implementations of a bunch of interesting papers: https://duckdb.org/docs/why_duckdb#standing-on-the-shoulders...
It's a really interesting piece of software, and unlike many other "new databases" it feels like it fills a very genuine gap in my toolbox.
https://github.com/MonetDBSolutions/MonetDBe-Python/
Disclaimer: i'm working on this.
DuckDB is designed as an experimental system after heavy exposure to the technniques deployed in MonetDB (open-source), Hyper, and Vectorwise.
The properties of the embedded version of MonetDB can be found here https://monetdbe.readthedocs.io/en/latest/introduction.html#
Some difference between MonetDB and DuckDB can be found here https://monetdbe.readthedocs.io/en/latest/migrations.html
and the blogpost mentioned above is covered in https://twitter.com/MonetDB/status/1282412295235280901?s=20
Every browser maker was interested in implementing it but the W3C couldn't go ahead with it because everyone chose to implement it using SQLite, where as W3C required more than one db back-end implementation to move forward.
Are these architecture independent? i.e. If I create the database on x86_64 and move it to ARM64; would it work seamlessly?
I should mention the storage is still very much a work-in-progress. We are actively working on redesigning the storage to add compression and other extra features, meaning the storage format can change and be incompatible between different DuckDB versions. We plan to stabilize the storage format with V1.0, after which every subsequent DuckDB version should be able to read database files created by previous versions.
I just wanted to add to the discussion that an unchanging file format, or at least a backwards compatible one, is a key feature of sqlite. See for example Richard Hipp's comments here [1] (I think he also mentioned earlier in the talk that the file format has become a limiting factor now in terms of some of the refactoring that they can do). The file format therefore seems likely to be a major factor in the long term success of this project and I am glad to see that you are taking your time before settling on any architecture here.
Given that you are targeting the data science and analytics space, what are your plans for integration with arrow and the feather file format? From a purely user/developer perspective, arrow's aim of shared memory data structures across different analytics tools, seems like a great goal. I know Wes McKinney and Ursa Labs have also spent quite some time at the file storage part of this, see for example the Feather V2 announcement [2].
What are your thoughts on the tradeoffs they considered and how do you see the requirements of DuckDB in relation to theirs?
From the Carnegie Mellon DuckDB talk [3], I saw that you already have a zero-copy reader to the pandas memory data structures, so the vision I have is that DuckDB could be the universal SQL interface to arrow datasets which can then also be shared with more complex ML models. Is that something that we can hope for or are there obstacles to this?
[1] https://youtu.be/Jib2AmRb_rk?t=3150
We already support reading from the arrow in-memory format [1], therefore it is already possible to use DuckDB to perform SQL-on-Arrow. More support there is definitely possible, and I think this is a very promising direction.
We also have a sqlite3 compatible C API that can be used to use DuckDB. This is actually how we use the sqlite3 shell: we directly use the sqlite shell but bind it to the duckdb library using this API. It might be possible to use the Go SQLite driver in a similar manner to talk to DuckDB.
We also have JDBC support, which might help for languages that know how to use that protocol. ODBC support is not implemented yet, but also planned.
We also have a SQLite-compatible C API [2], that can potentially be used to use an existing SQLite driver that uses the SQLite C API.
[1] https://github.com/marcboeker/go-duckdb
[2] https://github.com/cwida/duckdb/blob/master/tools/sqlite3_ap...
When to use DuckDB ; When to not use DuckDB
Only thing wrong I saw was the detection of the platform. It detected my linux box as "Unix" and proposed I download OSX libs.
Algorithms textbooks don't create marketing blurbs for linked lists...
There's an issue with the Installation section of the web page, though. Running Chrome on Linux, it says "System detected: Linux", which is right. But under "CLI", it offers me a download of duckdb_cli-osx-amd64.zip.
Just in case it was just the zip file name that's wrong, I downloaded it, but the duckdb binary inside is a "Mach-O 64-bit x86_64 executable" according to the file command.
The correct file is available if I click on Other Installations (https://duckdb.org/docs/installation/).
Also, if I try it on running Firefox on Linux, it says "System detected: UNIX" and gives me the OS X download.
Currently I'm using https://pypi.org/project/testing.postgresql/ which spawns a temporary server per each test suite, which has quite a lot of overhead.
Right at the bottom of this page:
I'm not sure whether those are in scope for you but it'd be nice if the docs said "to be implemented" or "out of scope" somewhere ... and my apologies in advance if they do and I somehow missed it.
Struct looks 10x uglier but perfectly easy to generate, mind, but the whole thing is non-trivial to find :/
In general, I am not comfortable using an "almost equivalent" database for tests. Every database engine has its quirks, and if you don't know what they are, your program that passes its tests only "almost" works in production, which is annoying.
https://www.postgresql.org/docs/current/manage-ag-templatedb...
Disclaimer: author.
The Hibernate ORM handles configuring the H2 schema but they’re not fully compatible, so it means I have to be careful to not rely on Postgres-specific features. I generally am not testing database logic, just need a working persistence layer to get the tests running, but an in-memory Postgres implementation would be amazing.
The overhead is actually pretty small, less than 10s. I'd saw too much for unit tests, but we'll within the tolerable range for integration/functional tests. Compared with the time I'd spend hacking together some brittle and unrealistic in-memory alternative, I much prefer to use a real database.
What you want is having a Postgres server, but optimized for tests. What I've done is 1. One instance per pipeline 2. Parallelize by having separate databases per process 3. Build schema once 4. Before each test, purge the database (or run tests in transactions so you can just rollback in the end) 5. Run in Docker, use tmpfs volumes for writes (no disk writes)
It runs fairly quickly.
Anyone using this in production? Also, any benchmarks vs. other DBs?
“An embeddable column store for data science” (Pandas and dbplyer) would have been a good description at that time but the addition of JDBC/Java support expands the use cases.
That reminds me of LMBD, which is similar in some ways, except bring a key/value store. Very highly recommend checking it out too to see what embeddable DB makes sense for your project.
Expressed otherwise: why would I choose DuckDB over SQLite, or SQLite over DuckDB?
SQLite/MySQL/Postgres/MSSQL etc are all OLTP databases whose primary operation is based around operations on single (or few) rows.
OLAP databases like ClickHouse/DuckDB, Monet, Redshift, etc are optimised for operating on columns and performing operations like bulk aggregations, group-bys, pivots, etc on a large subsets or whole tables.
If I was recording user purchases/transactions: SQLite.
If I was aggregating and analysing a batch of data on a machine: DuckDB.
I read an interesting engineering blog from Spotify(?) where in their data pipeline instead of passing around CSV’s or rows of JSON, passed around SQLite databases: DuckDB would probably be a good fit there.
The shortest useful description I can give of what constitutes an analytical workloads is that it is table-scan-heavy, and often concerned with a small subset of columns in a table.
I could add more indices to MSSQL and do all sorts of query optimisations, but out of the box the OLAP wins hands down for that workload.
Embeddable databases absolutely have legit use-cases, but I see them being used for projects where it'd have saved so much trouble to use a proper ORM (perhaps with sqlite support!) from the beginning.
But yeah, people shouldn't just "follow the hype". They should always ask why would use this and not that.
My preference for ORMs is still on the 'give me a SQL DSL and map my objects' side. Let me use SQL as SQL, give me convenience in interfacing with the DB. Complex features like object tracking tend to be nice up front but require extra diligence down the line to avoid pitfalls, and tend to be risky from a standpoint of composability.
Sorry. Almost went on an ORM rant there.
In practice though, real projects often have to sidestep the ORM and handwrite SQL, sometimes using database-specific features.
I had Plex running via Docker, and the Plex configuration/metadata stored via NFS on my NAS. For quite a while seeking would be intermittently slow, or plain crash the Plex server requiring a restart of the container. Other weird issues as well.
Finally I had enough and did some debugging and searching, and found that Plex uses sqlite in a way that is not compatible with networked storage. So I moved it to an iSCSI volume, mounted as a regular drive on the Docker host and it's been rock solid since.
But yeah, didn't expect that.
When running PhotoStructure under docker, it turned out to be simpler to just assume the DB is stored on a remote filesystem, and enable this mode by default. When running outside a container I can detect if the host volume is remote automatically and set the mode appropriately.
The reason why, other than missing file locking semantics, seems to be from WAL mode's use of an SHM file. Unsurprisingly, memory-mapped files don't work well from remote shares.
PRAGMA foreign_keys = on;
ftfy.
You really shouldn't be needing to create new connections often. Really only once for any given process.
There are some 20 PRAGMAs that PhotoStructure sets for library databases, but it's only at process startup, and it takes a couple millis to run them all. It's wonderful to be able to configure stuff so easily.
I also think the design decision for backward comparability (which meant this default is false) is absolutely defensible.
I'm modelling a program in Python on a work computer without administrative privileges, which means it has to stay in user space. Because I haven't decided on a final implementation language, I don't want to commit to any one language's memory model. Vanilla SQL is my solution there.
My specification limits procedures to SQL as much as possible, using the implementation language only for user interaction with and computations on the database not possible in standard SQL. It minimizes use of the heap, which requires opening connections to the database frequently, but this is practical given the low performance requirements of the application.
SQLite3 satisfies my spec except for the extra diligence required first to implement the foreign keys PRAGMA in order to maintain referential integrity, and second to remove it if an RDBMS closer to the SQL standard were eventually chosen.
In a nutshell, my constraints are user space, limiting operations to standard SQL as much as possible, referential integrity, and minimal implementation language dependencies besides having an available SQL API. Given those constraints, would you recommend a different RDBMS? Or would you agree SQLite3 is my least worst option?
has anyone tried this with ESP32/Arduino? I would need an anlytical DB for an hiking application (Temperatur/Pressure data etc.)
(I made one using Proxy API, called ref-db. The cons is it require manual indexing to speed up lookup by non-primary key)
This is really neat. Wish I had a problem I could use it for now.
SELECT * FROM part WHERE p_size = 7;My bet was on embeddable, so SQLite, is that the right choice?
It seeems a nightmarish scenario to have 500 postgres instances running on client machines and being accessible from the internet albeit with security enabled and such...
am i right?
We want to move data from COBOL files to a database, we're making our own migration tools/scripts in python (In order to mainly rename weird name table/column names to sanish ones), and we can target any SQL flavour/database we want to insert that data at.. the question is what's the better call here...
SQLite seems pretty good enough, at least until we can move our whole app to a more web-friendly environment at which point something like postgres might make more sense? but that could take a couple years... (we're a small team/company in a very specific niche/locale)
Do note that sqlite itself doesn't have the kindest handling of date/times, you are at the mercy of your data provider (unless you are going native) Especially when timezones are involved.
If it's running on windows machines you could consider Sql Server LocalDB (runs in user mode, i believe you are allowed to distribute as such in an app). But it doesn't sound like that would make sense from what I've read for your case and tech stack.
Sqlite is a good start because even if you are wrong, it should be pretty dang easy to migrate to whatever is right. :)
Actually SQL Server was one of the other contendents, but I disregard it due to it not being open source and having some hard caps in the free license (I think it's 10GB)...
But, I didn't knew about SQLserver local db, so we will look into that!
It was asking a lot, but still :-( that JSONB and range datatypes are missing.
E.g. would be used for offline statistical analysis
Alterntively, any way to export cli sql results as csv?