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

DuckDB-Wasm: Efficient analytical SQL in the browser

duckdb.org

Post reply on HN
Print view View original

41–50 of 62 posts

Re: DuckDB-Wasm: Efficient analytical SQL in the browser

#41

Post by tomrod »

1egg0myegg0 wrote: post #20
ankoh wrote: post #18

Earlier quoted context omitted.

Yes we do! DuckDB-Wasm can read files using HTTP range requests very similar to the sql.js-httpvfs from phiresky. The blog post contains a few examples how this can be used, for example, to partially query Parquet files over the network. E.g. just visit shell.duckdb.org and enter: select * from ' https://shell.duckdb.org/data/tpch/0_01/parquet/orders.parqu... ' limit 10;

NIIIICE! Data twitter was pretty excited about that cool SQLite trick - now you can turn it up a notch!

Is data twitter == #datatwitter, like Econ Twitter is #econtwitter?

If so, I have another cool community to follow!

Re: DuckDB-Wasm: Efficient analytical SQL in the browser

#42

Post by dgudkov »

brandmeyer wrote: post #25
mytherin wrote: post #15

Earlier quoted context omitted.

DuckDB developer here. DuckDB is a regular RDBMS that has persistent ACID storage, but is tuned towards analytical workloads, i.e. read-heavy workloads with aggregates that require full scans of the data. Any data you write to tables is stored persistently on disk, and not all your data needs to fit in memory either. Our tagline is “SQLite for analytics”, as DuckDB is an in-process database system similar to SQLite t…

Maybe this is a silly question: Why is the A/B choice between a row-major database and a column-major database, instead of between row-major tables and column-major tables within a flexible database? What's stopping the other leading brands from implementing columnar storage, queries, and such with a COLUMN MAJOR table attribute?

SQL calculations on columnar data are quite different from row-based databases, so its effectively a different database engine. You can take multiple advantages of columnar data store, because it usually employs a form of vocabulary compression. For instance, obtaining distinct values of a field in a columnar DB is much faster because it's typically just the vocabulary of the field, so it doesn't even require a full table scan. Many other columnar computations such as filtering or aggregation can be done on compressed data without decompression.

Re: DuckDB-Wasm: Efficient analytical SQL in the browser

#43

Post by ankoh »

obeliskora wrote: post #37
ankoh wrote: post #18

Earlier quoted context omitted.

Yes we do! DuckDB-Wasm can read files using HTTP range requests very similar to the sql.js-httpvfs from phiresky. The blog post contains a few examples how this can be used, for example, to partially query Parquet files over the network. E.g. just visit shell.duckdb.org and enter: select * from ' https://shell.duckdb.org/data/tpch/0_01/parquet/orders.parqu... ' limit 10;

It would be really cool to load duckdb files too. sql.js-httpvfs seems convenient because it works on everything in database so you don't have to create indexes, or setup keys and constraints in the client.

I agree! DuckDB-Wasm can already open DuckDB database files in the browser the very same way.

Re: DuckDB-Wasm: Efficient analytical SQL in the browser

#45

Post by mytherin »

tomnipotent wrote: post #40
mytherin wrote: post #32

Earlier quoted context omitted.

Why compare DuckDB to Parquet when you can use DuckDB and Parquet [1] :) [1] https://duckdb.org/2021/06/25/querying-parquet.html

Does DuckDB also use a PAX-like format like Parquet? Without going into code, the best I could find with a little googlefu is the HyPer/Data Blocks paper - is this a relevant read?

DuckDB's storage format has similar advantages as the Parquet storage format (e.g. individual columns can be read, partitions can be skipped, etc) but it is different because DuckDB's format is designed to do more than Parquet files.

Parquet files are intended to store data from a single table and they are intended to be written-once, where you write the file and then never change it again. If you want to change anything in a Parquet file you re-write the file.

DuckDB's storage format is intended to store an entire database (multiple tables, views, sequences, etc), and is intended to support ACID operations on those structures, such as insertions, updates, deletes, and even altering tables in the form of adding/removing columns or altering types of columns without rewriting the entire table or the entire file.

Tables are partitioned into row groups much like Parquet, but unlike Parquet the individual columns of those row groups are divided into fixed-size blocks so that individual columns can be fetched from disk. The fixed-size blocks ensure that the file will not suffer from fragmentation as the database is modified.

The storage is still a work in progress, and we are currently actively working on adding more support for compression and other goodies, as well as stabilizing the storage format so that we can maintain backwards compatibility between versions.

Re: DuckDB-Wasm: Efficient analytical SQL in the browser

#46

Post by ankoh »

sgarrity wrote: post #44

How does this compare/relate to https://jlongster.com/future-sql-web (if at all)?

The author outlines many problems that you'll run into when implementing a persistent storage backend using the current browser APIs.

We faced many of them ourselves but paused any further work on an IndexedDB-backend due to the lack of synchronous IndexedDB apis (e.g. check the warning here https://developer.mozilla.org/en-US/docs/Web/API/IDBDatabase...). He bypasses this issue using SharedArrayBuffers which would lock DuckDB-Wasm to cross-origin-isolated sites. (See the "Multithreading" section in our blog post)

We might be able to lift this limitation in the future but this has some far-reaching implications affecting the query execution of DuckDB itself.

To the best of my knowledge, there's just no way to do synchronous persistency efficiently right now that wont lock you to a browser or cross-origin-isolation. But this will be part of our ongoing research.

Re: DuckDB-Wasm: Efficient analytical SQL in the browser

#47

Post by sitkack »

munro wrote: post #36

Cool! This is the first time hearing about DuckDB, exciting as I heavily use SQLite. And these benchmarks are showing it's 6-15 times faster than sql.js (SQLite) [1], along with another small benchmark I found [2]. I usually just slather on indexes in SQLite tho, so indexed queries may not stand up as well; and might not be as fast when it's on storage, as this is comparing in memory performance (I think?), but I'll…

Do you need compression to get more bandwidth or are you trying to save money on storage costs?

DuckDB is a column based db, so you are going to see a throughput increase for queries that only use a handful of columns.

Re: DuckDB-Wasm: Efficient analytical SQL in the browser

#48

Post by wenc »

deadliftpro wrote: post #39
nicoburns wrote: post #11

Earlier quoted context omitted.

DuckDB is columnar, so in theory a lot faster than Postgres or SQL server for Analytical workloads. DuckDB is to Clickhouse, TimescaleDB, Redshift, etc as SQLite is to Postgres, MySQL, SQL Server.

From where do you get that sql server does not support columnar? That is a wrong claim.

I don’t think the OP said that SQL Server doesn’t support columnar, only that by analogy SQL Server is primarily a row store (which for most of its history was true).

Columnar technology in SQL Server only became usable in SQL 2016 (it existed in 2012 but was too restrictive — I know because I tried to use it).

In 2016 and above, you can either create a columnar index (non clustered column store) or convert an entire table into a columnar table (clustered column store). The technology is actually pretty impressive and I’ve used it in production where I have row stores coexisting with column stores within the same database.

Re: DuckDB-Wasm: Efficient analytical SQL in the browser

#50

Post by sedatesteak »

I got excited about duckdb recently too. Used it yesterday for a new project at work and immediately ran into a not implemented exception for my (awful) column naming structure and discovered there is no pivot function.

Otherwise, it's great, but obviously still a wip.

For those wondering, I have a helper function for soql queries for salesforce that follows the structure object.field

Referring to a tablealias.[column.name] or quotes instead of brackets was a no go.

Post reply on HN