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

31–40 of 167 posts

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

#31

Post by Abishek_Muthian »

simonw wrote: post #22

I spent a while looking at this today. It's really interesting. It's not based on SQLite at all (except for borrowing the SQLite shell implementation) but it looks very much like SQLite, in particular: - It's designed to work as an embedded library, eliminating the network overhead you usually get when talking to a database - Each database is a single file on disk - It ships as an "amalgamation" build - a single gian…

> - Each database is a single file on disk

Are these architecture independent? i.e. If I create the database on x86_64 and move it to ARM64; would it work seamlessly?

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

#32

Post by jrockway »

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…

I would just create a per-test-file database (and delete it the next time the test runs). The overhead is very small (compared to booting Postgres) and it works exactly like production because it is exactly production.

In general, I am not comfortable using an "almost equivalent" database for tests. Every database engine has its quirks, and if you don't know what they are, your program that passes its tests only "almost" works in production, which is annoying.

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

#33

Post by ComputerGuru »

gralx wrote: post #28
joppy wrote: post #26

Earlier quoted context omitted.

SQLite has foreign keys, but they are disabled by default for backwards-compatibility reasons. https://sqlite.org/foreignkeys.html

And it's not possible to enable them by default - there is no existing configuration setting to override that design choice. They hope to include the feature in SQLite4, as I understand it.

SQLite 4 was killed off in favor of just bringing a few changes to SQLite 3 iteratively.

https://www.sqlite.org/src4/doc/trunk/www/index.wiki

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

#35

Post by gregsadetsky »

haggy wrote: post #30
simonw wrote: post #22

I spent a while looking at this today. It's really interesting. It's not based on SQLite at all (except for borrowing the SQLite shell implementation) but it looks very much like SQLite, in particular: - It's designed to work as an embedded library, eliminating the network overhead you usually get when talking to a database - Each database is a single file on disk - It ships as an "amalgamation" build - a single gian…

I really appreciate the breakdown here but this comment smells like a giant plant for DuckDB.

I don’t think that it’s reasonable to assume that Simon would risk his reputation by coming here to astroturf.

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

#36

Post by Smerity »

haggy wrote: post #30
simonw wrote: post #22

I spent a while looking at this today. It's really interesting. It's not based on SQLite at all (except for borrowing the SQLite shell implementation) but it looks very much like SQLite, in particular: - It's designed to work as an embedded library, eliminating the network overhead you usually get when talking to a database - Each database is a single file on disk - It ships as an "amalgamation" build - a single gian…

I really appreciate the breakdown here but this comment smells like a giant plant for DuckDB.

A giant plant as in someone from their team planning this out and then planting this as a comment..?

simonw wrote Datasette[1] which makes extensive use of SQLite and acts as a viewer, allowing you to create interactive websites and APIs from arbitrary databases. He'd be a very long term plant and it instead seems far more likely he's interested in the possibilities of DuckDB compatibility within the context of Datasette and other similar projects.

[1]: https://github.com/simonw/datasette

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

#37

Post by mytherin »

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…

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 free to open an issue :)

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

#38

Post by simonw »

Smerity wrote: post #36
haggy wrote: post #30

Earlier quoted context omitted.

I really appreciate the breakdown here but this comment smells like a giant plant for DuckDB.

A giant plant as in someone from their team planning this out and then planting this as a comment..? simonw wrote Datasette[1] which makes extensive use of SQLite and acts as a viewer, allowing you to create interactive websites and APIs from arbitrary databases. He'd be a very long term plant and it instead seems far more likely he's interested in the possibilities of DuckDB compatibility within the context of Datas…

I actually started looking at it today because someone was asking about it on the Datasette discussions board: https://github.com/simonw/datasette/discussions/968

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

#39

Post by mytherin »

Abishek_Muthian wrote: post #31
simonw wrote: post #22

I spent a while looking at this today. It's really interesting. It's not based on SQLite at all (except for borrowing the SQLite shell implementation) but it looks very much like SQLite, in particular: - It's designed to work as an embedded library, eliminating the network overhead you usually get when talking to a database - Each database is a single file on disk - It ships as an "amalgamation" build - a single gian…

> - Each database is a single file on disk Are these architecture independent? i.e. If I create the database on x86_64 and move it to ARM64; would it work seamlessly?

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 different DuckDB versions. We plan to stabilize the storage format with V1.0, after which every subsequent DuckDB version should be able to read database files created by previous versions.

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

#40

Post by mytherin »

adrianmonk wrote: post #20

First of all, really neat project. There's an issue with the Installation section of the web page, though. Running Chrome on Linux, it says "System detected: Linux", which is right. But under "CLI", it offers me a download of duckdb_cli-osx-amd64.zip. Just in case it was just the zip file name that's wrong, I downloaded it, but the duckdb binary inside is a "Mach-O 64-bit x86_64 executable" according to the file comm…

Thanks for reporting that! We will have a look at fixing it :)
Post reply on HN