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

131–140 of 167 posts

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

#131

Post by mytherin »

mst wrote: post #113
mytherin wrote: post #37

Earlier quoted context omitted.

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…

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.

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.

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

#132

Post by mytherin »

StreamBright wrote: post #119
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…

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.

Definitely. We have quite detailed plans for compression :) more to follow when we get to it.

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

#133

Post by mceachen »

gralx wrote: post #27
cryptonector wrote: post #24

Earlier quoted context omitted.

What? SQLite3 absolutely has FKs.

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 design decision for backward comparability (which meant this default is false) is absolutely defensible.

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

#134

Post by mytherin »

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…

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

[1] https://github.com/cwida/duckdb/pull/866

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

#135

Post by prionassembly »

xiaodai wrote: post #29
winrid wrote: post #21

Earlier quoted context omitted.

If you want to do linear regression aggregations with any DB, one thing you can do is store the coifficents and then aggregate them on request. You sacrafice some accuracy for speed.

Model fitting. How to do?

Stochastic gradient descent.

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

#136

Post by dkarlovi »

viraptor wrote: post #2

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…

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.

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

#137

Post by jansommer »

wffurr wrote: post #125
webmobdev wrote: post #123

Earlier quoted context omitted.

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.

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.

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

#138

Post by andyfleming »

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.

How would that be different from someone editing something in window.localStorage?

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

#139

Post by StreamBright »

mytherin wrote: post #132
StreamBright wrote: post #119

Earlier quoted context omitted.

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.

Definitely. We have quite detailed plans for compression :) more to follow when we get to it.

Excellent! Thanks for the update.

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

#140

Post by jansommer »

andyfleming wrote: post #138
jansommer wrote: post #137

Earlier quoted context omitted.

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.

How would that be different from someone editing something in window.localStorage?

If that's all you need, it wouldn't make sense to include SQLite in the browser. If you're going to include a relational database you might as well use it
Post reply on HN