Explore large tables in a web browser

[pgarrison]

Requirements

Open a table from the user's machine with the following dimensions in BFF on the web.

• 8 million rows
• 40-80 columns
• Up to 255 characters per column

The implementation may require a specific file format (e.g., parquet). If at all possible, we should do it without a database.

If some features are infeasible without a database, they can be disabled. These include:

• Sorting on a column
• Identifying which columns are non-null on the current subset

The following features must be supported:

• Hierarchical group by
  • However, the implementation may only support this for columns without too many unique values.
• Filter on multiple columns

Notes

This is related to #68, but this ticket is focused on non-FMS usage. The requirements here are weaker, since replacing AICS FES would require performance and feature-completeness on par with FES.

[pgarrison]

• Investigation in Investigate using parquet files as main data store for explorer #68 had duckdb-wasm unable to open 10M row test files.
  • DuckDB (not duckdb-wasm) seems to have good support for partial reads from parquet files, consistent with @aswallace's observation that using duckdb in python could do large SELECT queries efficiently.
  • There has been recent progress on the duckdb-wasm side (Sept 2025) to better support partial reads [1]
• Partial reads via Window.showOpenFilePicker() and FileSystemFileHandle, while promising, are not supported in firefox and safari (FileSystemFileHandle is, but not showOpenFilePicker).
• Since the compressed file sizes aren't super large, we could copy the user's file into the broadly supported Origin Private File System (OPFS) and then do partial reads from there.
  • Available storage depends on the browser, but 10GiB (total storage per-origin) seems safe to expect. This seems sufficient since the compressed test file sizes are under 1GB.
  • duckdb-wasm already has some support for OPFS, but documentation is limited. It's unclear if copying the input file to OPFS is something duckdb-wasm would handle, or if BFF would need to do that.
• Outside of duckdb-wasm, other options include hyparquet (f.k.a. hyperparquet) and parquet-wasm.
  • parquet-wasm is rust compiled to wasm, and claims to be much faster and more memory efficient than hyparquet for operations on large files.

[pgarrison]

I did a quick test of parquet-wasm. It appears to support either reading the whole parquet file into memory (which is not necessarily prohibitive), or iterating through all the rows of a parquet file.

• First, I tried loading a full test file into memory. The largest test file I could load without a memory allocation error was 1M rows and 3 columns (12MB compressed).
• Next, I tried opening a test file and iterating through the records.
  • The following options are available.

    • batchSize: The number of rows in each batch. If not provided, the upstream parquet default is 1024.
      

    • rowGroups: Only read data from the provided row group indexes.
      

    • limit: Provide a limit to the number of rows to be read.
      

    • offset: Provide an offset to skip over the given number of rows.
      

    • columns: The column names from the file to read.
      

    • concurrency: The number of concurrent requests to make
      

  • With the default settings, the largest test file I could load was 8M rows and 15 columns (207MB compressed). Iterating over all the batches took 11s.
  • Loading a single column instead, I was able to scale up to my largest test file so far: 10M rows and 20 columns. Iterating over all the batches took 2s.
    • This test didn't actually do any work on those batches. If I instead do a count of rows with a specific value, it takes 4s to run this query on the 10M row test.

Overall, parquet-wasm seems feasible, but not ideal, since it doesn't support pushing queries down to the parquet reader.

Here's the code for that final test.

import { tableFromIPC } from 'apache-arrow';

export async function open(event: Event) {
  const parquet = await import(
    "https://cdn.jsdelivr.net/npm/parquet-wasm@0.7.1/esm/+esm"
  )
  await parquet.default();
  const file = event!.target!.files[0] as File;
  console.time("Read file");
  const reader = await parquet.ParquetFile.fromFile(file);
  
  const columnName = '0';
  const batches = await reader.stream({ columns: [columnName]});
  let firstVal = null;
  let count = 0;
  for await (const batch of batches) {
    const table = tableFromIPC(batch.intoIPCStream());
    for (const row of table) {
      const val = row[columnName];
      if (firstVal === null) {
        firstVal = val;
      }
      if (val === firstVal) {
        count++;
      }
    }
    console.log(table.numRows, table.numCols, count);
    // batch.free()
  }

  reader.free();
  console.timeEnd("Read file");
}

[pgarrison]

I did some tests with duckdb-wasm (v1.30.0) and was able to do the same query from my parquet-wasm test (count how many rows have a specific value in a column) on the same 20 column, 10M row parquet file in 115ms.

How I loaded the table

const file = event!.target!.files[0] as File;
await db.registerFileHandle('local.parquet', file, duckdb.DuckDBDataProtocol.BROWSER_FILEREADER, true);

Above, event.target is an <input type="file"> element.

Test 1

This test uses column "0" of my test parquet file, where each row has one of 8 distinct values at random, each a 255-character random string.

• I got one of the values in the column with 'select "0" from local.parquet limit 1'
• Then I queried for the number of rows with this value select count(*) from local.parquet where "0" = '${firstVal}'
• Completed in 115ms on my 10M row test file (N=1)
• Completed in 73ms on my 1M row test file (N=1)

Test 2

This test uses column "17" of my test parquet file, where each row has one of 1000 distinct values at random, each a 255-character random string.

• I got all 1000 values with select distinct "17" from local.parquet
• Completed in 129.5s +/- 0.7 (N=2) on my 10M row test file

Test 3

This test uses the column "floats" of my test parquet file, where each row has a unique random float.

• I got all 10M distinct values with select distinct floats from local.parquet
• Completed in 6.4s +/- 0.55 (N=2)

[pgarrison]

I did a couple duckdb-wasm tests with @aswallace's 100 column 10M row test file (900MB). These use the "File Name" column, which numbers files consecutively from File0 to File9999999, and the "File Size" column, which appears to be a random integer between 1024 and 8191.

Test 4

• select distinct "File Size" from local.parquet
• Result: 7168 distinct values
• 196ms (N=1)

Test 5

• select count(*) from local.parquet where '9999' in "File Name"
• Result: 3700
• 435ms (N=1)

Test 6

• select distinct "File Size" from local.parquet where '9' in "File Name"
• Result: 2891 distinct values
• 457ms (N=1)

[pgarrison]

(All the testing discussed above has been Firefox on MacOS.)

[pgarrison]

If I try to open Anya's test file in BFF, I get the following error message (toast):

Out of Memory Error: could not allocate block of size 256.0 KiB (3.1 GiB/3.1 GiB used) Database is launched in in-memory mode and no temporary directory is specified. Unused blocks cannot be offloa...

BFF's data import code has a line that I haven't been using: CREATE TABLE "${name}" AS FROM parquet_scan('${name}');. If I add this line to my demo's import logic, I reproduce the same error message.

Error: Out of Memory Error: could not allocate block of size 256.0 KiB (3.1 GiB/3.1 GiB used)
Database is launched in in-memory mode and no temporary directory is specified.
Unused blocks cannot be offloaded to disk.

Launch the database with a persistent storage back-end
Or set SET temp_directory='/path/to/tmp.tmp'

Possible solutions:
* Reducing the number of threads (SET threads=X)
* Disabling insertion-order preservation (SET preserve_insertion_order=false)
* Increasing the memory limit (SET memory_limit='...GB')

See also https://duckdb.org/docs/stable/guides/performance/how_to_tune_workloads

[pgarrison]

With my smallest test file (3 columns, 1M rows), the CREATE TABLE command succeeds but does not appear to provide a clear performance improvement. I only did one quick test for this though.