> the DuckDB backend can do everything the pandas backend can do, but better
Maybe this should be a focus. Putting together killer demos that show this to people. Because I don’t see it.
Maybe I’m thinking about it in the wrong terms. I think of DuckDB in terms of what we’re doing with pandas (which isn’t perfect but works), and also as an embedded database like a SQLite alternative.
In both aspects it seems like we have more friction working in DuckDB than in pandas and SQLite. Most common problem is getting errors about the DuckDB file being locked or in use, by the process that’s trying to read from it (which just spawned). I thought maybe it was me, but even Jetbrains tooling had the same weird issues with DuckDB.
Compared to SQLite, which just works, with virtually any language or tooling, I don’t see the benefit to DuckDB. I see some people say it’s better and more efficient for larger data, but I couldn’t get it to work reliably with tiny data on a single machine.
I use both DuckDB and SQLite at work. SQLite is better when the database gets lots of little writes, when the database is going to be stored long term as the primary source of truth, when you don't need to do lots of complicated analytical queries etc. Very useful for storing both real and simulated data long term.
DuckDB is much nicer in terms of types, built in functions, and syntax extensions for analytical queries and also happens to be faster for big analytical queries although most of our data is small enough that it doesn't make a big difference over SQLite (although it still is a big improvement over using Pandas even for small data). DuckDB only just released versions with backwards compatible data storage though, so we don't yet use it as a source of truth. DuckDB has really improved in general over the last couple years as well and finally hit 1.0 3 months ago. So depending what version you tried it out on tiny data, it may be better now. It's also possible to use DuckDB to read and write from SQLite if you're concerned about interop and long term storage although I haven't done that myself so don't know what the rough edges are.
I think you're trying to use DuckDB incorrectly. If you have a writer connection open to the DB, you can't open reader connections as well (including from IntelliJ). You'll get file lock errors, as you noticed. You can either have a single writer connection at once, or multiple reader connections at once. DuckDB is significantly better than SQLite for analytical queries on big data sets, but it's not a replacement for a transactional database such as SQLite.
Maybe this should be a focus. Putting together killer demos that show this to people. Because I don’t see it.
Maybe I’m thinking about it in the wrong terms. I think of DuckDB in terms of what we’re doing with pandas (which isn’t perfect but works), and also as an embedded database like a SQLite alternative.
In both aspects it seems like we have more friction working in DuckDB than in pandas and SQLite. Most common problem is getting errors about the DuckDB file being locked or in use, by the process that’s trying to read from it (which just spawned). I thought maybe it was me, but even Jetbrains tooling had the same weird issues with DuckDB.
Compared to SQLite, which just works, with virtually any language or tooling, I don’t see the benefit to DuckDB. I see some people say it’s better and more efficient for larger data, but I couldn’t get it to work reliably with tiny data on a single machine.