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

Friendlier SQL with DuckDB

duckdb.org

Post reply on HN
Print view View original

21–30 of 134 posts

Re: Friendlier SQL with DuckDB

#21

Post by 1egg0myegg0 »

eis wrote: post #20
pholanda wrote: post #16

Earlier quoted context omitted.

Hey eis, you are correct. We do not support index storage in our latest release. I am currently implementing this, and it is in a fairly advanced stage. So it should be featured in the next release. This took a little while because we use a fairly modern index structure with no literature definition on how to buffer manage it.

It's good to hear that persistent indexes are coming soon. I saw it was on the roadmap but didn't know how far out this feature was. Do you have an idea when that release could be out? BTW Do you have some kind of code/docs one can take a look at regarding the index structure? I'm a part-time data structure nerd :)

Here is a paper that was used when building the DuckDB approach!

https://db.in.tum.de/~leis/papers/ART.pdf

There are some other papers and details about the architecture here: https://duckdb.org/why_duckdb

Re: Friendlier SQL with DuckDB

#22

Post by 1egg0myegg0 »

wenc wrote: post #18

This is fantastic. Column aliases are super helpful in reducing verbose messiness. DuckDB has all but replaced Pandas for my use cases. It’s much faster than Pandas even when working with Pandas data frames. I “import duckdb as db” more than I “import pandas as pd” these days. The only thing I need now is a parallelized APPLY syntax in DuckDB.

Fugue has a DuckDB back end and I believe they can actually use Dask and DuckDB in combination for what I believe is similar to what you are looking for! There is also a way to map Python functions in DuckDB using the relational (dataframe-like) API.

https://fugue-tutorials.readthedocs.io/tutorials/integration...

https://github.com/duckdb/duckdb/pull/1569

Re: Friendlier SQL with DuckDB

#23

Post by eis »

1egg0myegg0 wrote: post #11
eis wrote: post #6

I was just yesterday exploring DuckDB and it looked very promising but I was very surprised to find out that indexes are not persisted (and I assume that means they must fit in RAM). > Unique and primary key indexes are rebuilt upon startup, while user-defined indexes are discarded. The second part with just discarding previously defined indexes is super surprising. https://duckdb.org/docs/sql/indexes This was an ins…

Persistent indexes are being actively worked on! Stay tuned. As for the crashes - DuckDB is very well tested and used in production in many places. The core functionality is very mature! Let us know if you test it out! Happy to help if I can. (disclaimer - on the DuckDB team)

Hi, good to hear that you guys care about testing. One thing apart from the Github issues that led me to believe it might not be super stable yet was the benchmark results on https://h2oai.github.io/db-benchmark/ which make it look like it couldn't handle the 50GB case due to a out of memory error. I see that the benchmark and the used versions are about a year old so maybe things changed a lot since then. Can you chime in regarding the current story of running bigger DBs like 1TB on a machine with just 32GB or so RAM? Especially regardung data mutations and DDL queries. Thanks!

Re: Friendlier SQL with DuckDB

#26

Post by 1egg0myegg0 »

eis wrote: post #23
1egg0myegg0 wrote: post #11

Earlier quoted context omitted.

Persistent indexes are being actively worked on! Stay tuned. As for the crashes - DuckDB is very well tested and used in production in many places. The core functionality is very mature! Let us know if you test it out! Happy to help if I can. (disclaimer - on the DuckDB team)

Hi, good to hear that you guys care about testing. One thing apart from the Github issues that led me to believe it might not be super stable yet was the benchmark results on https://h2oai.github.io/db-benchmark/ which make it look like it couldn't handle the 50GB case due to a out of memory error. I see that the benchmark and the used versions are about a year old so maybe things changed a lot since then. Can you ch…

Yes, that benchmark result is quite old in Duck years! :-)

We actually run that benchmark as a part of our test suite now, so I am certain that there is improvement from that version.

The biggest DuckDB I've used so far was about 400 GB on a machine with ~250 GB of RAM.

There is ongoing work that we are treating as a high priority for handling larger-than-memory intermediate results within a query. But we can handle larger than RAM in many cases already - we sometimes run into issues today if you are joining 2 larger than RAM tables together (depending on the join), or if you are aggregating a larger than RAM table with really high cardinality in one of the columns you are grouping on.

Would you be open to testing out your use case and letting us know how it goes? We always appreciate more test cases!

Re: Friendlier SQL with DuckDB

#27

Post by 1egg0myegg0 »

projektfu wrote: post #24

I find that the examples are very confusing because they are using names that sound like rows or tables (jar_jar_binks, planets) as fields in the examples.

Ah, well, that was a risk that I took... Thank you for the feedback though! The Star Wars puns were too hard to resist...

If you have a specific question, definitely post it here and I will clarify!

Re: Friendlier SQL with DuckDB

#29

Post by Oxodao »

Came across this a few time but never got to try it out because the only golang binding is unofficial and I can't get CGO to work as expected...

That would be really neat to have an official one. This articles makes me want to try it even more

Re: Friendlier SQL with DuckDB

#30

Post by mattrighetti »

`EXCLUDE`

Extremely useful, is there a reason why this is something not implemented in SQL in the first place? I often find myself writing very long queries just to select basically all columns except for two or three of them.

Post reply on HN