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

151–160 of 167 posts

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

#151

Post by legg0myegg0 »

snthpy wrote: post #128
mytherin wrote: post #39

Earlier quoted context omitted.

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

It's also possible to return a result set as an Arrow table, so round trip SQL on Arrow queries is possible (Arrow to DuckDB to Arrow)! It's not 100% zero-copy for strings, but it should work pretty well!

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

#152

Post by gralx »

mceachen wrote: post #133
gralx wrote: post #27

Earlier quoted context omitted.

You just have to write PRAGMA foreign_keys = on; each and every single time you want to do an operation that requires an FK constraint, like `INSERT`.

s/each and every single time/once when you instatiate a new database connection/ 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 d…

I'll defer to anyone with expertise.

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?

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

#153

Post by snthpy »

mytherin wrote: post #134
snthpy wrote: post #128

Earlier quoted context omitted.

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

Thanks for your detailed reply! As you mentioned - lessons learned by SQLite here are indeed crucial. We are very carefully trying to craft a storage format before fixing it in-place, specifically to try to avoid these problems. Backwards compatibility is a must, but backwards compatibility to a sane format is massively preferable :) No doubt we will end up making some mistakes in hindsight, though. We already suppor…

Thanks. That's great and I see you even support the new Arrow streaming model!

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

#154

Post by maigret »

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

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!".

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

#155

Post by afiori »

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.

This could work if there is a subset of SQL where duckDB and SQLite behave almost identically

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

#157

Post by jimsmart »

KingOfCoders wrote: post #106
Avamander wrote: post #103

Earlier quoted context omitted.

Ooh, this sounds like a fun use-case but a power loss sounds super painful.

Would also store on MicroSD.

That’s part of the problem. Power loss during SD card write will, more likely than not, cause corruption - FAT file systems do not have journaling.

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

#158

Post by tracker1 »

cmrdporcupine wrote: post #100

I love/hate it when I see pieces of software come out that would solve problems I had 10 or 15 years ago and had imperfect solutions for. This would have been great for a software project for call centre analytics I was employed to work on back in the mid-2000s. I believe I used HSQL (embedded Java DB) but it was really not the right tool. At the time I looked at MonetDB but its architecture wasn't right for what I w…

I used embedded firebird for a couple projects in the early/mid 2000's myself. Wish it were a bit more popular.

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

#159

Post by hajile »

jansommer wrote: post #137
wffurr wrote: post #125

Earlier quoted context omitted.

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

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.

Ironically, all the IndexDB implementations were just thin layers on top of sqlite last I checked.
Post reply on HN