Datasets bigger than memory

A dataset is a table kept on disk in one .vectro file. It opens instantly, queries read it one chunk at a time, and its size has no limit from memory (an engine holds at most 4 GB at once). The same filters, group-bys, joins, top-k and SQL work on it as on a table in memory.

import { mkdtempSync, rmSync } from 'node:fs';
import { tmpdir } from 'node:os';
import { join } from 'node:path';
import { Engine, sql } from 'vectrojs';

const dir = mkdtempSync(join(tmpdir(), 'vectro-'));
const engine = new Engine();

// a year of orders, one a minute
const n = 525_600;
const orders = engine.table({
  ts: Array.from({ length: n }, (_, i) => new Date(Date.UTC(2024, 0, 1) + i * 60_000)),
  region: Array.from({ length: n }, (_, i) => ['EU', 'US', 'APAC'][i % 3]),
  price: Float64Array.from({ length: n }, (_, i) => (i * 7919) % 1000),
  qty: Float64Array.from({ length: n }, (_, i) => 1 + (i % 9)),
});

const path = join(dir, 'orders.vectro');
console.log(engine.writeDataset(path, orders, { rowsPerChunk: 50_000 }));
orders.dispose();

const ds = engine.openDataset(path);
console.log(ds.length, ds.chunkCount, ds.columnNames);

console.log(ds.filter("ts >= '2024-12-01'").groupBy('region')
  .agg({ orders: 'count()', revenue: 'sum(price * qty)' }).toRows());
console.log(ds.lastRun);   // chunks read and skipped, bytes read

console.log(sql('SELECT region, AVG(price) AS avg_price FROM ds GROUP BY region', { ds }).toRows());

await ds.close();
await engine.close();
rmSync(dir, { recursive: true, force: true });

Writing and opening by path is Node only. Browsers open datasets from a File, a Blob or a URL, and write them into browser storage (below).

The file

A dataset holds chunks of 4M rows (rowsPerChunk changes it). Each column of each chunk is stored compressed, as in compressed columns, and then packed to the bit: a quantity from 1 to 9 takes 4 bits, a price in cents up to 999.99 takes 17, and a column that counts up at a fixed step (row numbers, a time every second) takes none.

Loading a chunk is one read per column; every thread then unpacks the bits back into the 1-, 2- or 4-byte codes the kernels read. Nothing is parsed. The footer at the end of the file holds the schema, one dictionary per text column, and the min and max of every column for each chunk and each block of 65,536 rows. Those min and max values are the zone maps that let a query skip what it doesn’t need.

The file format may still change before 1.0.

Writing a dataset

There are three ways in. Each returns a summary, { path, rows, chunks, bytes }.

Call	Writes
engine.writeDataset(path, source, opts)	a table, a lazy query, a dataset, or { column: values }
engine.createDataset(path, opts)	a writer: call append(table or columns) as often as you like, then close()
engine.csvToDataset(csvPath, path, opts)	a CSV of any size, read 64 MB at a time

Each has an …Async version (writeDatasetAsync, createDatasetAsync with appendAsync / closeAsync, csvToDatasetAsync) that doesn’t block the thread. The options:

Option	Meaning
rowsPerChunk	rows per chunk; default 4,194,304
append	true adds rows to an existing dataset with the same columns
bitPack	false stores codes in whole bytes: bigger files, no unpacking
types, delimiter, header, pieceBytes, onProgress	CSV only: column types, the separator, whether there is a header row, bytes read per piece, and a callback ({ bytes, size, rows }) after each piece

writeDataset(path, query) over another dataset writes the result chunk by chunk, so the result needn’t fit in memory either. A full sort written this way is an external sort.

import { mkdtempSync, rmSync, writeFileSync } from 'node:fs';
import { tmpdir } from 'node:os';
import { join } from 'node:path';
import { Engine } from 'vectrojs';

const dir = mkdtempSync(join(tmpdir(), 'vectro-'));
const engine = new Engine();

// a CSV, converted in pieces
let csv = 'day,store,sales\n';
for (let i = 0; i < 30_000; i++) csv += `2024-01-${String(1 + (i % 28)).padStart(2, '0')},${['north', 'south'][i % 2]},${i % 250}\n`;
writeFileSync(join(dir, 'sales.csv'), csv);
console.log(engine.csvToDataset(join(dir, 'sales.csv'), join(dir, 'sales.vectro'), {
  pieceBytes: 200_000,
  onProgress: ({ bytes, size }) => console.log(`read ${bytes} of ${size} bytes`),
}));

// a writer, fed batches as they come
const w = engine.createDataset(join(dir, 'events.vectro'));
w.append({ kind: ['click', 'view'], ms: [12, 40] });
w.append({ kind: ['view', 'buy'], ms: [35, 900] });
console.log(w.close());

// more rows later, into the same file
engine.writeDataset(join(dir, 'events.vectro'), { kind: ['refund'], ms: [5] }, { append: true });

const events = engine.openDataset(join(dir, 'events.vectro'));
console.log(events.toRows());

// a query's result as a new dataset
engine.writeDataset(join(dir, 'slow.vectro'), events.filter('ms > 30').select('kind', 'ms'));
const slow = engine.openDataset(join(dir, 'slow.vectro'));
console.log(slow.length);

await slow.close();
await events.close();
await engine.close();
rmSync(dir, { recursive: true, force: true });

A dataset is written by one writer at a time. A writer that fails is aborted and leaves no footer, so a half-written file can’t be opened by mistake.

Queries

A dataset has the same query methods as a table: filter, derive, select, join, groupBy().agg, agg, sort, top, limit, count, sum, mean, min, max, toRows, collect, explain, each with an …Async version. They build a lazy query, optimized as a whole, then run chunk by chunk with the same kernels on every core:

• Only the columns the query uses are read.
• Chunks and blocks the filters rule out are not read at all.
• Totals add up across chunks (a mean is a sum and a count), groups merge by key, top(k) keeps the best k so far, and limit stops reading once it has its rows.
• With the …Async calls, the next chunk is read while the current one is computed.

ds.lastRun tells you what the last query did: { chunks, read, skipped, rows, bytesRead, ms }. explain() prints the plan.

const orders = engine.openDataset('orders.vectro');
await orders.lazy()
  .filter("region == 'EU'")
  .join(customers, { on: 'cust' })
  .top(10, '-price')
  .toRowsAsync();

collect() and toRows() with no filter or limit bring the whole dataset into memory, and that is subject to the 4 GB limit. Aggregate, filter or take a top-k first.

Big group-bys, sorts and joins

Results that don’t fit in memory go to temporary files on disk (“spilling”), split by key range, and each range is finished on its own.

• Many groups. Keys that are labels or whole-number IDs add straight into one array per aggregation, each thread its own range of groups. Other keys keep partial groups in memory and, past 8M groups, spill them to disk by key range; each range is merged on its own (split again if still too big), in key order.
• Joins. The other table (or the other dataset’s matching rows) is hashed once for all chunks. When both sides are datasets and the other side has more than 16M matching rows, both go to disk split by key range and each range is joined on its own (a “grace” hash join): 100M orders joined with 20M accounts took 7.6 s with 406 MB of engine memory. Rows then come out range by range rather than in the left table’s order.
• Sorts written with writeDataset(path, query) are external sorts, by ranges of the first key.

Not yet: a join on text keys needs its other side to fit in memory, exact distinct counts of values other than IDs don’t spill, approxDistinct on a dataset counts exactly, and window functions and percentiles in SQL need the rows they use (the columns in the query) to fit in memory.

In the browser

Pages open datasets read-only from a File the user picked or dropped, a Blob, or a URL. A URL is read with HTTP range requests (the server must answer Range with 206), neighbouring reads are merged, and only the chunks and columns a query needs are fetched. Queries use the async calls.

import { Engine, sql } from 'vectrojs/browser';

const engine = new Engine();
const orders = await engine.openDataset('/data/orders.vectro');   // or input.files[0]
await sql('SELECT region, COUNT(*) FROM orders GROUP BY region', { orders }).toRowsAsync();

A billion rows (a 5.1 GB file) open in a page in 51 ms; “the last day” answers in 24 ms reading 0.3 MB; a full group-by streams 2.7 GB in 7 s from a local server.

Pages also write datasets: into the browser’s own file storage (OPFS) by name, to a file the user chose (a handle from showSaveFilePicker()), or into memory with memoryFile() and then .blob(). Bytes go to disk chunk by chunk, so a CSV bigger than memory converts too.

await engine.csvToDataset(droppedFile, 'orders.vectro', { onProgress: (p) => show(p) });
await engine.writeDataset('eu.vectro', orders.filter("region == 'EU'"));
const stored = await engine.openStoredDataset('orders.vectro');
await engine.storedDatasets();            // [{ name, size }]
await engine.storedDatasetFile('eu.vectro'); // a File, to download
await engine.removeStoredDataset('eu.vectro');

A 246 MB CSV (5M rows) becomes a 24 MB dataset in browser storage in 2.8 s. Spilling works in the browser too: huge group-bys, sorts written as datasets and joins of two big datasets put their partitions in a temporary folder in browser storage, removed when the query ends.

memoryFile() and opening from a Blob work in Node as well:

import { Engine, memoryFile } from 'vectrojs';

const engine = new Engine();
const file = memoryFile('scores.vectro');
await engine.writeDatasetAsync(file, { team: ['red', 'blue', 'red'], score: [3, 5, 4] });

const scores = await engine.openDatasetAsync(file.blob());
console.log(await scores.groupBy('team').agg({ total: 'sum(score)' }).toRowsAsync());

await scores.close();
await engine.close();

How fast

Measured against DuckDB 2.0 on its own database file, both on disk, the same SQL on identical rows (best of 3, Apple M1 Pro):

Query	100M rows: Vectro	DuckDB	1 billion rows: Vectro	DuckDB
scan everything (count, revenue)	49 ms	61 ms	460 ms	648 ms
filter: region and quantity	70 ms	72 ms	665 ms	723 ms
the last day (1 chunk read)	3 ms	1 ms	3 ms	5 ms
group by region	114 ms	124 ms	1.13 s	1.24 s
top 10 of 1M customers	460 ms	487 ms	4.46 s	4.62 s
top 10 rows by price	10 ms	87 ms	11 ms	827 ms
join 1M customers, group by tier	84 ms	68 ms	745 ms	587 ms
distinct customers per region	247 ms	517 ms	2.45 s	6.50 s
file on disk	0.5 GB	0.5 GB	5.1 GB	5.4 GB
peak memory (RSS)	343 MB	3.1 GB	0.37 GB	6.6 GB

A billion rows would take 37 GB as plain columns in memory; Vectro’s engine never used more than 163 MB for them. DuckDB is still faster at a join followed by a GROUP BY on the joined table’s columns (1.3x).

The full API is in the Dataset reference.