Get the top HN stories in your inbox every day.
simonw
gijzelaerr
And MonetDB took some inspiration back from DuckDB to develop an embedded version also:
https://github.com/MonetDBSolutions/MonetDBe-Python/
Disclaimer: i'm working on this.
Firerouge
Could you elaborate on some of the similarities and differences between DuckDB and MonetDB?
mkersten
Disclaimer: i am working on MonetDB and I established the CWI database architectures group
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
webmobdev
If everything you said is accurate, somebody should make another pitch for WebSQL ( https://en.wikipedia.org/wiki/Web_SQL_Database ) with a custom version of DuckDb!
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.
wffurr
Having sqlite included in all browsers with standard bindings would have been great.
jansommer
It would! But it would also be a security nightmare. Exploit SQLite and you could use it in Safari, Chrome and Firefox - everybody could be affected.
afiori
This could work if there is a subset of SQL where duckDB and SQLite behave almost identically
webmobdev
I often wondered why someone didn't do it with BerkleyDb too? I know Oracle owns it, but it is open source and someone like Mozilla could have implemented it in their browser just to get the W3C rolling on WebSQL.
Abishek_Muthian
> - Each database is a single file on disk
Are these architecture independent? i.e. If I create the database on x86_64 and move it to ARM64; would it work seamlessly?
mytherin
One of the authors here - portability of the storage is indeed one of our goals. We test that the same file can be loaded everywhere on different systems, including between ARM and x86.
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.
snthpy
DuckDB looks very interesting and I'm quite excited to examine it more closely in the next few days!
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
Abishek_Muthian
Thanks for the confirmation on portability, I didn't see Go support in the home page; Is there an official Go driver support planned as well?
StreamBright
Do you consider (or maybe you already have) the advanced columnar features? Run length encoding, dictionary encoding, etc. It would be great to see how those perform under these workloads.
marton78
Couldn't you have used an already existing format for storage, e.g. Apache ORC?
brian_herman
Yes, their testing folder is 26mb while their source code of the database is only 5.6mb. It looks like they took the same approach to testing as sqlite also.
undefined
tmpz22
How might one go about using this in a language that’s not supported in the documentation such as golang?
mytherin
Generally you would need to write bindings for the system. Experimental DuckDB bindings for Go do exist [1], however, they are rather old and might not work anymore.
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...
at6
Hi, author of the Go bindings here. I've just tested them and they are still working with the latest DuckDB version from master.
haggy
I really appreciate the breakdown here but this comment smells like a giant plant for DuckDB.
Smerity
A giant plant as in someone from their team planning this out and then planting this as a comment..?
simonw wrote Datasette[1] which makes extensive use of SQLite and acts as a viewer, allowing you to create interactive websites and APIs from arbitrary databases. He'd be a very long term plant and it instead seems far more likely he's interested in the possibilities of DuckDB compatibility within the context of Datasette and other similar projects.
simonw
I actually started looking at it today because someone was asking about it on the Datasette discussions board: https://github.com/simonw/datasette/discussions/968
gregsadetsky
I don’t think that it’s reasonable to assume that Simon would risk his reputation by coming here to astroturf.
ksec
I really wish ALL software project / framework / libary could follow the lead here. Instead of your marketing page telling me how world changing awesome tech you have, which really is a consumer marketing strategy. Just do it like DuckDB,
When to use DuckDB ; When to not use DuckDB
LockAndLol
I couldn't put my finger on why I felt like I had just read something useful, but that's it: clear, concise and straight-forward information to help you evaluate use.
Only thing wrong I saw was the detection of the platform. It detected my linux box as "Unix" and proposed I download OSX libs.
FridgeSeal
We should make a list of technology that does this, because I know Clickhouse also has a reasonably detailed page on when to use it and when to not use it and why. Postgres also has a very nice “do and donts” wiki page.
stjo
Postgres “do and donts”: https://wiki.postgresql.org/wiki/Don%27t_Do_This
fsloth
What an excellent point! I suppose the consumer marketing strategy comes from pure imitation - people look around them how "professional" products discuss themselves - with the aim of selling a product and service - and simply copy that.
Algorithms textbooks don't create marketing blurbs for linked lists...
ComodoHacker
Looks like DuckDB doesn't seek investments. So they can afford to be defeloper-first. Not everyone can, though.
maigret
I'd wish every product would tell what's in for me. Telling that it's world changing doesn't mean it's useful or impactful for me, as consumer or as developer. Usually, usual products brand themselves as "new" for differentiation, but real new products stay away from this to not scare people. Usually you notice when something is really "new" (aka means paradigm change) when the marketing says loud "Don't worry, everything will stay the same as it was, no visible difference for you!".
adrianmonk
First of all, really neat project.
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.
mytherin
Thanks for reporting that! We will have a look at fixing it :)
alexchamberlain
FWIW I got the same issue on Android, though offering me the "correct" link wouldn't do me much good either :)
viraptor
I was hoping from the title that it aims for postgres SQL compatibility, but I can't find it explicitly mentioned in the docs. This really makes me think I really want something like sqlite://memory which completely disregards speed or even persistence. Instead you could say for example "open an in-memory database that behaves like postgres 9" and run your tests against it. With typical fixtures of 10 or so rows, you wouldn't even need a query plans - just naive filte+copy over whole tables for most operations.
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.
giancarlostoro
> SQL Parser: We use the PostgreSQL parser that was repackaged as a stand-alone library. The translation to our own parse tree is inspired by Peloton.
Right at the bottom of this page:
viraptor
That's awesome news, thanks for that!
giancarlostoro
No problem I was curious as well and hunting it down, this project does seem interesting, I'm a huge fan of database systems like SQLite and H2. There's something wonderful about being able to copy a simple file and share it with other developers. This one seems rather interesting as well.
mytherin
One of the authors of DuckDB here: we use the PostgreSQL parser, and try to be generally compatible with the syntax used by SQLite, Postgres and MySQL. In some cases those are unfortunately mutually exclusive (e.g. null ordering - we have a PRAGMA to change this system-wide). Making the SQL dialect “as compatible as possible” with these systems is very much one of our goals, and if you find any incompatibilities feel free to open an issue :)
mst
I'd note that when moving from pg to $other the thing that really trips me up isn't the syntax changes, it's the lack of ARRAY and ROWTYPE.
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.
mytherin
We already have support for LIST and STRUCT (which I think are equivalent to ARRAY and ROW, respectively). There is still some functionality missing there (notably storage, and several functions) - but the base functionality is there and the remainder definitely will be added.
jrockway
I would just create a per-test-file database (and delete it the next time the test runs). The overhead is very small (compared to booting Postgres) and it works exactly like production because it is exactly production.
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.
oauea
And if you need to run expensive migrations, use a template database.
https://www.postgresql.org/docs/current/manage-ag-templatedb...
majodev
While PostgreSQL templates allow to scaffold new databases (one per test-spec) quite fast, it's even faster to have a warmed up pool of databases ready: https://github.com/allaboutapps/integresql#background
Disclaimer: author.
chrisdalke
I would also love this, in the past I’ve used the H2 in-memory database for tests with Spring boot applications.
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.
viraptor
Although it's something I'd love to write, supporting it over a long time as pure FOSS would probably be a sad experience. I wonder what model could be used here. Maybe something like patreon or "sponsor a feature" with an open-source project? FOSS core + proprietary Java / .NET bindings because corps can pay?
chrisdalke
Yeah, for sure this is the type of niche, high-effort work that would be used extensively by the closed-source world and yet struggle to find adequate support.
vips7L
IIRC h2 supports a postgres compatibility mode. Though I'm not sure how feature complete it is.
c17r
Man, I’ve wanted the “compatible SQL engine with only RAM storage” for testing for YEARS. Closest I got was some shenanigans with MSSQL’s LocalDB.
mStreamTeam
I use docker to spin up new postgresql DBs on the fly
GordonS
Same here, and I run the same set of migrations that run in production. To he clear, this is only done once per test session, not for individual tests, and the tests are written in such a way that they don't interfere with each other.
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.
toyg
I do that too but it’s a noticeable overhead, I guess it wouldn’t be great for mass-testing scenarios.
striking
RAM FS + fsync off?
viraptor
Those solutions still have a high overhead. There's acid compliance, serialisation in memory, maintaining indices, and many other layers. Compare it to an ideal testing solution with no initialisation cost and insert being literally: parse the query, add a new entry to a list, done.
xyzzy_plugh
I'd build this, but I'm not sure if I'd be able to get anyone to fund it.
dkarlovi
You shouldn't rely on any "just like X" systems for tests, you should use X exactly, including correct config and version as production.
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.
awb
Cool project. Really appreciate the "When to use DuckDB" and "When to not use DuckDB" sections.
Anyone using this in production? Also, any benchmarks vs. other DBs?
legg0myegg0
I work at a Fortune 100 company and we have this in production for our self-service analytics platform as a part of our data transformation web service. Each web request can do multiple pandas transformations, or spin up it's own DuckDB or SQLite db and execute arbitrary SQL transformations. It fits our use case like a glove and is super fast to/from Pandas.
sradman
3-month old HN thread [1] with good information from the authors and others.
“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.
ryndbfsrw
The performance of DuckDB's predecessor, MonetDBLite, was/is stellar. DuckDB, when I tried in 6 months ago, was a bit behind in terms of csv parsing or aggregation by groups and didn't seem much faster than regular sqlite so I didn't really jump at it. Does anyone here know if the performance improved since then and how it compares to MonetDBLite?
mytherin
We are currently actively working on performance optimization, and have recently finished implementing parallelization. Expect more performance improvements soon :)
mastazi
I think the title chosen by the poster misses the mark a bit, the main point seems to be that it’s an OLAP DB optimised for analytics.
Piisamirotta
This is slightly offtopic but do you guys have any good guides/best conventions how to save timeseries data to sqlite/similar? I'm no DB expert so struggling with this. I'm having a dynamic number of measurements that usually have the same timestamp. So one table for timestamp_id, signal_id and value?
jononor
What would be the recommended way to migrate data from SQLite and DuckDB? Should I dump SQL from SQLite and load in DuckDB? Or use Parquet, or CSV export/import? I think those interested in the tagline "like SQLite but more features" might be interested in a quick howto/guide like that.
sterlinm
This talk they did with a database research group at CMU may be of interest as well. https://youtu.be/PFUZlNQIndo
microtherion
A suggestion for the web site: It would be good to mention the license more prominently. I had to click through to Github to find out what it was, and I believe this information would matter to more people than just me.
microtherion
Correction: As CRConrad, whose comment was inexplicably downvoted, pointed out, the MIT License IS mentioned on the front page (And the Wayback Machine proves it was mentioned before the HN post).
CRConrad
Dunno if this was put up in reaction to your comment, or you missed it, but looking at their front page right now it says “Permissive MIT License”.
Get the top HN stories in your inbox every day.
I spent a while looking at this today. It's really interesting.
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.