5 ms·

DuckDB 1.1.0 Released

203 points by craigkerstiens 2y ago

ZeroCool2u 2y ago
Damn, GeoParquet and R-Tree for spatial indexes is huge!!! ESRI better watch their back!
- DonnyV 2y ago
  Unfortunately ESRI will probably just build an application around it, promote the app like they created something new and sell it to their customers. Same thing they did with GDAL.
simonw 2y ago
I feel like R-Tree spatial indexes are potentially the most exciting new feature in this release, but they're buried right down at the bottom of the announcement: https://duckdb.org/2024/09/09/announcing-duckdb-110.html#r-tree https://duckdb.org/2024/09/09/announcing-duckdb-110.html#r-t...
- maxxen 2y ago
  Thanks! I've been wanting to add this since I first started out working on DuckDB almost two years ago but I finally managed to accumulate the time (and the skills required!) to finish it up over the summer. It still has a long way to go, support for indexes in extensions are pretty... raw, and we only push down constant filters into index scans (so no spatial index-join acceleration yet). But I think having a proper spatial index is one of those things that are kind of required to really elevate the spatial extension from being just a toy and I'm super stoked to work more on it during the next release cycle and all the new possibilities that it opens up.
  - maxxen 2y ago
    I still need to update the docs and there's probably going to be a blog post on it in the future, but for now there are some more details in the PR https://github.com/duckdb/duckdb_spatial/pull/383 https://github.com/duckdb/duckdb_spatial/pull/383
RandomCitizen12 2y ago
The namesake: https://en.wikipedia.org/wiki/Eaton%27s_pintail https://en.wikipedia.org/wiki/Eaton%27s_pintail
log4shell 2y ago
Congratulations to duckdb team! Can't wait to try some of the newly released features and performance improvements. I am quite curious about the plans for python dataframe like API for duckdb, and python ecosystem in general.
- exergy 2y ago
  there is Ibis[0] as a fairly mature package. They recently adopted duckdb as the default execution engine and it can give you a nice python dataframe API ontop of duckdb, with hot-swappability towards heavier engines. With tools like this providing a comprehensive python API and the ability to always fall back to raw SQL, i am not sure DuckDB devs should focus on the python API at all beyond basic (to_table, from_table) features. Impressive progress and a real chance to shake up the data tool market, but still a way to go: There is is still much to do especially on large table formats (iceberg/delta) and memory management when running on bigger boxes on cloud. Eg the elusive "Failed to allocate ..." bug[1] is an inhibitor to the claim that big data is dead[2]. As it is, we tried and abandoned DuckDB as a cheaper replacement for some databricks batch jobs. [0] https://github.com/ibis-project/ibis https://github.com/ibis-project/ibis [1] https://github.com/duckdb/duckdb/issues/12667 https://github.com/duckdb/duckdb/issues/12667, https://github.com/duckdb/duckdb/issues/9880 https://github.com/duckdb/duckdb/issues/9880, https://github.com/duckdb/duckdb/issues/12528 https://github.com/duckdb/duckdb/issues/12528 [2] https://motherduck.com/blog/big-data-is-dead/ https://motherduck.com/blog/big-data-is-dead/
- cmdlineluser 2y ago
  The last I read, the Spark API was to become the focus point. https://duckdb.org/docs/api/python/spark_api https://duckdb.org/docs/api/python/spark_api Not sure what the current status is. ref: https://github.com/duckdb/duckdb/issues/2000#issuecomment-1849927294 https://github.com/duckdb/duckdb/issues/2000#issuecomment-18...
wenc 2y ago
Lots of automatic performance optimizations on what is already a very fast engine. (I’ve stopped using Pandas) I know most software folks feel some type of way about SQL (most don’t grok it beyond a simple SELECT) but this is one of the advantages of declarative languages like SQL and a plan-execute programming paradigm where a plan is created before instructions are run, making it amenable to plan optimization. Maybe the syntax of the language could be improved (e.g. Linq) but conceptually SQL is historically when we’ve blundered into the right. Data operations are often done in sets rather than loops and it’s a worthwhile investment for software engineers to learn to think in this way if they want to work with data correctly at scale. Stonebraker was right in that people who avoid SQL are doomed to reinvent it poorly.
- theLiminator 2y ago
  Imo dataframe apis can be superior, take a look at polars. I personally much prefer it to SQL. For duckdb, ibis looks like a pretty nice way of using the duckdb query engine.
  - isoprophlex 2y ago
    Dataframe APIs, sure. Pandas? Not even once.
    - neeleshs 2y ago
      Can you please elaborate what's wrong with Pandas? Im looking to use either Polars or Pandas in a project and looking for insights.
      - fsndz 2y ago
        I don't find pandas intuitive (API simplicity), then you have the hard to debug issues and perf
        isoprophlex 2y ago
        Yes, agreed. The API is a big inconsistent kludge, has many warts, and generally requires too much typing and memorization. The performance is subpar. There are some very annoying design choices wrt. implicit type coercion that don't jive with my personal preferences, which caused me recurring grief. And to engage in some light gatekeeping, there sure is a lot of terrible pandas code out there written by people that have no business calling themselves programmers. I fully realize this can happen anywhere, but I'm never excited anymore to read a line of pandas.
ashkankiani 2y ago
Love the expanded C API support! Also those performance improvements are massive! Pushing through filters and the streaming optimization for fetchone() is great! This makes it more viable to use duckdb in smaller queries from python. I'm pretty excited for variables too! I really wanted them for when I'm using the CLI. Same with query/query_table! I appreciate the push for features that make people's lives easier while also still improving performance. Everyone who I've introduced duckdb to (at work or outside of work) eventually is blown away (some still have lingering SQL stigma)
beingflo 2y ago
I've been eyeing DuckDB for a metric collection hobby project. Quick benchmark showed promising query performance over SQLite (unsurprising considering DuckDB is column oriented), but quite a bit slower for inserts. Does anyone have experience using it as an "online" backend DB as opposed to a data analytics engine for interactive use? From what I gather they are trying to position themselves more in the latter use case.
- 89vision 2y ago
  Depends on the scale of users you expect for your project. Generally I like to keep oltp and olap tools in their lanes, but if < 100 people are going to be using it probably doesn't matter. I doubt duckdb has any sort of acid guarantees, so thats something to keep in mind.
  - sudarshnachakra 2y ago
    DuckDB does have ACID guarantees and transactions but I'd not be surprised if they are rarely used (if at all). Ref: https://duckdb.org/docs/sql/statements/transactions https://duckdb.org/docs/sql/statements/transactions In the concurrency documentation they explicitly specify that it's not designed for lots of small transactions Concurrency: https://duckdb.org/docs/connect/concurrency https://duckdb.org/docs/connect/concurrency
- pantsforbirds 2y ago
  You can always use sqlite as your primary data store, and then directly query the sqlite database from duckdb whenever you need analytics.
- voidsnax 2y ago
  Doing row-by-row inserts into DuckDB is really slow. Accumulating rows in an in-memory data structure and periodically batching them into something like an in-memory Arrow table, and then reading the Arrow table into DuckDB, is fast and has been tenable for my own use cases.
fforflo 2y ago
The C extensions API is a big big very big thing. As someone who routinely write small PG extensions, I'd love to be able to kinda use the same code for multiple backend DBs. And I guess lots of inspiration has come from all the efforts that embed DuckDb in Postgres as a miniOLAP
- pantsforbirds 2y ago
  What sort of extensions do you find yourself writing? I think that is fascinating and thinking back, probably something I should have at least considered a few times.
BurnGpuBurn 2y ago
Today I learned: SELECT 1 / 0 AS var_name; yields a double with value infinite. Which is SQL spec. Must be fun times to actually use that :-)
- ok123456 2y ago
  IEEE 754 defines the result of divisions and multiplications involving NaN and Inf, which are used extensively in graphics programming. For example, 'select 1.0/(1.0/0.0)' becomes 0.0 again. This allows for inf/nans to be canceled out instead of having to do error checking and exception handling every time one crops up.
tlavoie 2y ago
This episode of Kris Jenkins' Developer Voices podcast talks with a couple authors of a new book on DuckDB, and does a great job of explaining the sorts of things that make it so unusual: https://www.youtube.com/watch?v=_nA3uDx1rlg https://www.youtube.com/watch?v=_nA3uDx1rlg
adwf 2y ago
Big fan of DuckDB! Has saved us a number of times when having to deploy at a remote client with limited on-prem customisation for security reasons (ie. no to installing a big Postgres or other RDBMS solution). Powerful tooling; all local to the environment and the data being worked on; SQL, so it's pretty close to a drop-in replacement compared to our old solution. Really great stuff and I was very happy to see the project gain the confidence to hit 1.0 a while back and now 1.1. Congrats to everyone!
mrbonner 2y ago
I wanted to use ibis with Duck as the backend. But I am afraid the chicken and egg problem of using existing ML libraries defaulting to use pandas as the dataframe API. Does anyone have a workaround?
- datadrivenangel 2y ago
  It's super easy to switch back and forth between dataframes and duckdb, so there's no lock in?
  - leventov 2y ago
    It's not super easy to port a library with domain-specific ML/DS algorithms built around Pandas, such as accepting Pandas dataframes as arguments to its functions and class constructors.
- wenc 2y ago
  DuckDB understands Pandas and Polars dataframes so you can move back and forth seamlessly between them in-memory (with Polars, it's nearly zero-copy via an in-memory data structure). df = pd.DataFrame({"a": [1,2,3], "b": [4,5,6]}) # New Pandas dataframe df2 = duckdb.query("select 2*a as c, b from df") # DuckDB df3 = df2.df() # materialize as Pandas, .pl() for Polars ml_df = ML.func(df3) # pass dataframe into ML function df4 = duckdb.query("select count(distinct x) from ml_df")
openplatypus 2y ago
Perfect timing. We are currently building a spike to validate migration to DuckDB. We are extremely optimistic based on community feedback.
lakomen 2y ago
"In process analytics database" none the wiser

Damn, GeoParquet and R-Tree for spatial indexes is huge!!! ESRI better watch their back!

DonnyV 2y ago
Unfortunately ESRI will probably just build an application around it, promote the app like they created something new and sell it to their customers. Same thing they did with GDAL.

I feel like R-Tree spatial indexes are potentially the most exciting new feature in this release, but they're buried right down at the bottom of the announcement: https://duckdb.org/2024/09/09/announcing-duckdb-110.html#r-tree https://duckdb.org/2024/09/09/announcing-duckdb-110.html#r-t...

maxxen 2y ago
Thanks! I've been wanting to add this since I first started out working on DuckDB almost two years ago but I finally managed to accumulate the time (and the skills required!) to finish it up over the summer. It still has a long way to go, support for indexes in extensions are pretty... raw, and we only push down constant filters into index scans (so no spatial index-join acceleration yet). But I think having a proper spatial index is one of those things that are kind of required to really elevate the spatial extension from being just a toy and I'm super stoked to work more on it during the next release cycle and all the new possibilities that it opens up.
- maxxen 2y ago
  I still need to update the docs and there's probably going to be a blog post on it in the future, but for now there are some more details in the PR https://github.com/duckdb/duckdb_spatial/pull/383 https://github.com/duckdb/duckdb_spatial/pull/383

RandomCitizen12 2y ago

The namesake: https://en.wikipedia.org/wiki/Eaton%27s_pintail https://en.wikipedia.org/wiki/Eaton%27s_pintail

log4shell 2y ago

Congratulations to duckdb team! Can't wait to try some of the newly released features and performance improvements. I am quite curious about the plans for python dataframe like API for duckdb, and python ecosystem in general.

exergy 2y ago
there is Ibis[0] as a fairly mature package. They recently adopted duckdb as the default execution engine and it can give you a nice python dataframe API ontop of duckdb, with hot-swappability towards heavier engines. With tools like this providing a comprehensive python API and the ability to always fall back to raw SQL, i am not sure DuckDB devs should focus on the python API at all beyond basic (to_table, from_table) features. Impressive progress and a real chance to shake up the data tool market, but still a way to go: There is is still much to do especially on large table formats (iceberg/delta) and memory management when running on bigger boxes on cloud. Eg the elusive "Failed to allocate ..." bug[1] is an inhibitor to the claim that big data is dead[2]. As it is, we tried and abandoned DuckDB as a cheaper replacement for some databricks batch jobs. [0] https://github.com/ibis-project/ibis https://github.com/ibis-project/ibis [1] https://github.com/duckdb/duckdb/issues/12667 https://github.com/duckdb/duckdb/issues/12667, https://github.com/duckdb/duckdb/issues/9880 https://github.com/duckdb/duckdb/issues/9880, https://github.com/duckdb/duckdb/issues/12528 https://github.com/duckdb/duckdb/issues/12528 [2] https://motherduck.com/blog/big-data-is-dead/ https://motherduck.com/blog/big-data-is-dead/
cmdlineluser 2y ago
The last I read, the Spark API was to become the focus point. https://duckdb.org/docs/api/python/spark_api https://duckdb.org/docs/api/python/spark_api Not sure what the current status is. ref: https://github.com/duckdb/duckdb/issues/2000#issuecomment-1849927294 https://github.com/duckdb/duckdb/issues/2000#issuecomment-18...

wenc 2y ago

Lots of automatic performance optimizations on what is already a very fast engine. (I’ve stopped using Pandas) I know most software folks feel some type of way about SQL (most don’t grok it beyond a simple SELECT) but this is one of the advantages of declarative languages like SQL and a plan-execute programming paradigm where a plan is created before instructions are run, making it amenable to plan optimization. Maybe the syntax of the language could be improved (e.g. Linq) but conceptually SQL is historically when we’ve blundered into the right. Data operations are often done in sets rather than loops and it’s a worthwhile investment for software engineers to learn to think in this way if they want to work with data correctly at scale. Stonebraker was right in that people who avoid SQL are doomed to reinvent it poorly.

theLiminator 2y ago
Imo dataframe apis can be superior, take a look at polars. I personally much prefer it to SQL. For duckdb, ibis looks like a pretty nice way of using the duckdb query engine.
- isoprophlex 2y ago
  Dataframe APIs, sure. Pandas? Not even once.
  - neeleshs 2y ago
    Can you please elaborate what's wrong with Pandas? Im looking to use either Polars or Pandas in a project and looking for insights.
    - fsndz 2y ago
      I don't find pandas intuitive (API simplicity), then you have the hard to debug issues and perf
      - isoprophlex 2y ago
        Yes, agreed. The API is a big inconsistent kludge, has many warts, and generally requires too much typing and memorization. The performance is subpar. There are some very annoying design choices wrt. implicit type coercion that don't jive with my personal preferences, which caused me recurring grief. And to engage in some light gatekeeping, there sure is a lot of terrible pandas code out there written by people that have no business calling themselves programmers. I fully realize this can happen anywhere, but I'm never excited anymore to read a line of pandas.

ashkankiani 2y ago

Love the expanded C API support! Also those performance improvements are massive! Pushing through filters and the streaming optimization for fetchone() is great! This makes it more viable to use duckdb in smaller queries from python. I'm pretty excited for variables too! I really wanted them for when I'm using the CLI. Same with query/query_table! I appreciate the push for features that make people's lives easier while also still improving performance. Everyone who I've introduced duckdb to (at work or outside of work) eventually is blown away (some still have lingering SQL stigma)

beingflo 2y ago

I've been eyeing DuckDB for a metric collection hobby project. Quick benchmark showed promising query performance over SQLite (unsurprising considering DuckDB is column oriented), but quite a bit slower for inserts. Does anyone have experience using it as an "online" backend DB as opposed to a data analytics engine for interactive use? From what I gather they are trying to position themselves more in the latter use case.

89vision 2y ago
Depends on the scale of users you expect for your project. Generally I like to keep oltp and olap tools in their lanes, but if < 100 people are going to be using it probably doesn't matter. I doubt duckdb has any sort of acid guarantees, so thats something to keep in mind.
- sudarshnachakra 2y ago
  DuckDB does have ACID guarantees and transactions but I'd not be surprised if they are rarely used (if at all). Ref: https://duckdb.org/docs/sql/statements/transactions https://duckdb.org/docs/sql/statements/transactions In the concurrency documentation they explicitly specify that it's not designed for lots of small transactions Concurrency: https://duckdb.org/docs/connect/concurrency https://duckdb.org/docs/connect/concurrency
pantsforbirds 2y ago
You can always use sqlite as your primary data store, and then directly query the sqlite database from duckdb whenever you need analytics.
voidsnax 2y ago
Doing row-by-row inserts into DuckDB is really slow. Accumulating rows in an in-memory data structure and periodically batching them into something like an in-memory Arrow table, and then reading the Arrow table into DuckDB, is fast and has been tenable for my own use cases.

fforflo 2y ago

The C extensions API is a big big very big thing. As someone who routinely write small PG extensions, I'd love to be able to kinda use the same code for multiple backend DBs. And I guess lots of inspiration has come from all the efforts that embed DuckDb in Postgres as a miniOLAP

pantsforbirds 2y ago
What sort of extensions do you find yourself writing? I think that is fascinating and thinking back, probably something I should have at least considered a few times.

BurnGpuBurn 2y ago

Today I learned: SELECT 1 / 0 AS var_name; yields a double with value infinite. Which is SQL spec. Must be fun times to actually use that :-)

ok123456 2y ago
IEEE 754 defines the result of divisions and multiplications involving NaN and Inf, which are used extensively in graphics programming. For example, 'select 1.0/(1.0/0.0)' becomes 0.0 again. This allows for inf/nans to be canceled out instead of having to do error checking and exception handling every time one crops up.

tlavoie 2y ago

This episode of Kris Jenkins' Developer Voices podcast talks with a couple authors of a new book on DuckDB, and does a great job of explaining the sorts of things that make it so unusual: https://www.youtube.com/watch?v=_nA3uDx1rlg https://www.youtube.com/watch?v=_nA3uDx1rlg

adwf 2y ago

Big fan of DuckDB! Has saved us a number of times when having to deploy at a remote client with limited on-prem customisation for security reasons (ie. no to installing a big Postgres or other RDBMS solution). Powerful tooling; all local to the environment and the data being worked on; SQL, so it's pretty close to a drop-in replacement compared to our old solution. Really great stuff and I was very happy to see the project gain the confidence to hit 1.0 a while back and now 1.1. Congrats to everyone!

mrbonner 2y ago

I wanted to use ibis with Duck as the backend. But I am afraid the chicken and egg problem of using existing ML libraries defaulting to use pandas as the dataframe API. Does anyone have a workaround?

datadrivenangel 2y ago
It's super easy to switch back and forth between dataframes and duckdb, so there's no lock in?
- leventov 2y ago
  It's not super easy to port a library with domain-specific ML/DS algorithms built around Pandas, such as accepting Pandas dataframes as arguments to its functions and class constructors.
wenc 2y ago
DuckDB understands Pandas and Polars dataframes so you can move back and forth seamlessly between them in-memory (with Polars, it's nearly zero-copy via an in-memory data structure). df = pd.DataFrame({"a": [1,2,3], "b": [4,5,6]}) # New Pandas dataframe df2 = duckdb.query("select 2*a as c, b from df") # DuckDB df3 = df2.df() # materialize as Pandas, .pl() for Polars ml_df = ML.func(df3) # pass dataframe into ML function df4 = duckdb.query("select count(distinct x) from ml_df")

openplatypus 2y ago

Perfect timing. We are currently building a spike to validate migration to DuckDB. We are extremely optimistic based on community feedback.

lakomen 2y ago

"In process analytics database" none the wiser