DuckDB – An embeddable SQL database like SQLite, but supports Postgres features • Hacker News Board

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

#101

Post by chrisdalke » Sun, Sep 20, 2020, 1:05 PM UTC

viraptor wrote: post #5

chrisdalke wrote: post #3

Earlier quoted context omitted.

I would also love this, in the past I’ve used the H2 in-memory database for tests with Spring boot applications. The Hibernate ORM handles configuring the H2 schema but they’re not fully compatible, so it means I have to be careful to not rely on Postgres-specific features. I generally am not testing database logic, just need a working persistence layer to get the tests running, but an in-memory Postgres implementati…

Although it's something I'd love to write, supporting it over a long time as pure FOSS would probably be a sad experience. I wonder what model could be used here. Maybe something like patreon or "sponsor a feature" with an open-source project? FOSS core + proprietary Java / .NET bindings because corps can pay?

Yeah, for sure this is the type of niche, high-effort work that would be used extensively by the closed-source world and yet struggle to find adequate support.

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

#102

Post by Avamander » Sun, Sep 20, 2020, 1:21 PM UTC

viraptor wrote: post #17

striking wrote: post #13

Earlier quoted context omitted.

RAM FS + fsync off?

Those solutions still have a high overhead. There's acid compliance, serialisation in memory, maintaining indices, and many other layers. Compare it to an ideal testing solution with no initialisation cost and insert being literally: parse the query, add a new entry to a list, done.

Have you tried postgresql with libeatmydata?

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

#103

Post by Avamander » Sun, Sep 20, 2020, 1:23 PM UTC

KingOfCoders wrote: post #92

Sorry for my perhaps stupid question, has anyone tried this with ESP32/Arduino? I would need an anlytical DB for an hiking application (Temperatur/Pressure data etc.)

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

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

#104

Post by at6 » Sun, Sep 20, 2020, 1:25 PM UTC

mytherin wrote: post #45

tmpz22 wrote: post #42

Earlier quoted context omitted.

How might one go about using this in a language that’s not supported in the documentation such as golang?

Generally you would need to write bindings for the system. Experimental DuckDB bindings for Go do exist [1], however, they are rather old and might not work anymore. We also have JDBC support, which might help for languages that know how to use that protocol. ODBC support is not implemented yet, but also planned. We also have a SQLite-compatible C API [2], that can potentially be used to use an existing SQLite driver…

Hi, author of the Go bindings here. I've just tested them and they are still working with the latest DuckDB version from master.

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

#105

Post by ulanmaasan » Sun, Sep 20, 2020, 1:26 PM UTC

So I tried a simple query in the live demo and it seems that the WHERE clause isn't filtering anything. Am I missing something?

  SELECT * FROM part WHERE p_size = 7;

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

#106

Post by KingOfCoders » Sun, Sep 20, 2020, 1:53 PM UTC

Avamander wrote: post #103

KingOfCoders wrote: post #92

Sorry for my perhaps stupid question, has anyone tried this with ESP32/Arduino? I would need an anlytical DB for an hiking application (Temperatur/Pressure data etc.)

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

Would also store on MicroSD.

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

#107

Post by greggyb » Sun, Sep 20, 2020, 1:58 PM UTC

chrismorgan wrote: post #97

FridgeSeal wrote: post #93

Earlier quoted context omitted.

OLAP databases can/are still Relational databases. The difference is that they’re optimised for different workloads. SQLite/MySQL/Postgres/MSSQL etc are all OLTP databases whose primary operation is based around operations on single (or few) rows. OLAP databases like ClickHouse/DuckDB, Monet, Redshift, etc are optimised for operating on columns and performing operations like bulk aggregations, group-bys, pivots, etc…

I gather that there may be some differences in functionality offered, but that it’s probably not much. So I presume performance is the biggest area of difference. In that regard: how big are the differences in performance for each category’s representative workloads?

I routinely see order of magnitude or greater improvements moving analytical workloads to compressed, columnstore engines vs rowstore engines.

The shortest useful description I can give of what constitutes an analytical workloads is that it is table-scan-heavy, and often concerned with a small subset of columns in a table.

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

#108

Post by 737maxtw » Sun, Sep 20, 2020, 2:04 PM UTC

brunoluiz wrote: post #96

3np wrote: post #85

Please think twice before choosing an embeddable database for hostable software. I see so many open-source projects that get hampered own the line because they chose sqlite and there's now a SPoF that would require a significant rewrite to eliminate - especially as sqlite depending on configuration either corrupts or becomes extremely slow when stored on distributed/network file systems. I assume this goes for duckdb…

Not sure where an ORM would help in this case. If anything, it might get in the way sometimes (in terms of performance), as some queries might not be well optimised. But yeah, people shouldn't just "follow the hype". They should always ask why would use this and not that.

I think ORM might have been brought up because in many embedded DBs there is some level of language native bindings that eliminate the need for an ORM. A couple of examples that come to mind would be mnesia (KV store rather than SQL but still an example) or some of the SQLite providers for c# which give you their own mini orm (that you will have to refactor if you change out dbs instead of using sqlite with an orm from the get go)

My preference for ORMs is still on the 'give me a SQL DSL and map my objects' side. Let me use SQL as SQL, give me convenience in interfacing with the DB. Complex features like object tracking tend to be nice up front but require extra diligence down the line to avoid pitfalls, and tend to be risky from a standpoint of composability.

Sorry. Almost went on an ORM rant there.

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

#109

Post by GordonS » Sun, Sep 20, 2020, 2:09 PM UTC

brunoluiz wrote: post #96

3np wrote: post #85

Please think twice before choosing an embeddable database for hostable software. I see so many open-source projects that get hampered own the line because they chose sqlite and there's now a SPoF that would require a significant rewrite to eliminate - especially as sqlite depending on configuration either corrupts or becomes extremely slow when stored on distributed/network file systems. I assume this goes for duckdb…

Not sure where an ORM would help in this case. If anything, it might get in the way sometimes (in terms of performance), as some queries might not be well optimised. But yeah, people shouldn't just "follow the hype". They should always ask why would use this and not that.

I guess the thought behind using an ORM here was as an abstraction over the database/storage layer, so theoretically you could change switch out the database easily.

In practice though, real projects often have to sidestep the ORM and handwrite SQL, sometimes using database-specific features.

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

#110

Post by 3np » Sun, Sep 20, 2020, 2:13 PM UTC

brunoluiz wrote: post #96

3np wrote: post #85

Please think twice before choosing an embeddable database for hostable software. I see so many open-source projects that get hampered own the line because they chose sqlite and there's now a SPoF that would require a significant rewrite to eliminate - especially as sqlite depending on configuration either corrupts or becomes extremely slow when stored on distributed/network file systems. I assume this goes for duckdb…

Not sure where an ORM would help in this case. If anything, it might get in the way sometimes (in terms of performance), as some queries might not be well optimised. But yeah, people shouldn't just "follow the hype". They should always ask why would use this and not that.

What I meant more specifically was a database-independent RBDMS ORM (e.g EF Core, sqlalchemy) - for cases when there aren't resources or need for separate db driver implementations.