Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

DuckDB – An embeddable SQL database like SQLite, but supports Postgres features

duckdb.org

Post reply on HN
Print view View original

121–130 of 167 posts

Re: DuckDB – An embeddable SQL database like SQLite, but supports Postgres features

#121

Post by legg0myegg0 »

awb wrote: post #14

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?

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.

Re: DuckDB – An embeddable SQL database like SQLite, but supports Postgres features

#122

Post by ComodoHacker »

ksec wrote: post #47

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

Looks like DuckDB doesn't seek investments. So they can afford to be defeloper-first. Not everyone can, though.

Re: DuckDB – An embeddable SQL database like SQLite, but supports Postgres features

#123

Post by webmobdev »

simonw wrote: post #22

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 gian…

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.

Re: DuckDB – An embeddable SQL database like SQLite, but supports Postgres features

#124

Post by kfk »

How does duckdb compare to aws athena or spectrum? Both services offer sql directly on parquet files. I guess you would say duckdb is not distributed but if you create one on time when the user needs to run the sql on the parquet you’d be in a similar use case?

Re: DuckDB – An embeddable SQL database like SQLite, but supports Postgres features

#125

Post by wffurr »

webmobdev wrote: post #123
simonw wrote: post #22

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 gian…

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.

Having sqlite included in all browsers with standard bindings would have been great.

Re: DuckDB – An embeddable SQL database like SQLite, but supports Postgres features

#127

Post by chrismorgan »

legg0myegg0 wrote: post #118
chrismorgan wrote: post #97

Earlier quoted context omitted.

I gather that there may be some differences in functionality offered, but that it’s probably not much. So I presume performance is the biggest area of difference. In that regard: how big are the differences in performance for each category’s representative workloads?

Before the latest optimization, and only using 1 core, vs. SQLite we were seeing 133x performance on a basic group by or join, and about 4x for a pretty complex query. It was roughly even to Pandas in performance, but it can scale to larger than memory data and now it can use multiple cores! As an example, I could build views from 2 Pandas DataFrames with 2 columns and 1 million rows each, join them, and return the 1…

How about the other way? When will SQLite perform better than DuckDB?

Re: DuckDB – An embeddable SQL database like SQLite, but supports Postgres features

#128

Post by snthpy »

mytherin wrote: post #39
Abishek_Muthian wrote: post #31

Earlier quoted context omitted.

> - 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?

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 diff…

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

[2] https://ursalabs.org/blog/2020-feather-v2/

[3] https://www.youtube.com/watch?v=PFUZlNQIndo

Re: DuckDB – An embeddable SQL database like SQLite, but supports Postgres features

#129

Post by defnotashton2 »

magicalhippo wrote: post #98
3np wrote: post #85

Please think twice before choosing an embeddable database for hostable software. I see so many open-source projects that get hampered own the line because they chose sqlite and there's now a SPoF that would require a significant rewrite to eliminate - especially as sqlite depending on configuration either corrupts or becomes extremely slow when stored on distributed/network file systems. I assume this goes for duckdb…

> especially as sqlite depending on configuration either corrupts or becomes extremely slow when stored on distributed/network file systems 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…

You just saved me a lot of time. In the middle of migrating homelab from day from docker/swarm to kube and had not thought of this. Ty.

Re: DuckDB – An embeddable SQL database like SQLite, but supports Postgres features

#130

Post by mceachen »

magicalhippo wrote: post #98
3np wrote: post #85

Please think twice before choosing an embeddable database for hostable software. I see so many open-source projects that get hampered own the line because they chose sqlite and there's now a SPoF that would require a significant rewrite to eliminate - especially as sqlite depending on configuration either corrupts or becomes extremely slow when stored on distributed/network file systems. I assume this goes for duckdb…

> especially as sqlite depending on configuration either corrupts or becomes extremely slow when stored on distributed/network file systems 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…

Yup. I was surprised and did the same sleuthing for PhotoStructure when libraries are stored on remote filesystems. I had to add a "local db replica" mode to work around this issue.

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.

Post reply on HN