Updating Appending and Compacting - SmithSoft-Studios/JAZMIN GitHub Wiki

Updating, Appending and Compacting

A JAZMIN file is written once, then changed in one of two ways:

update() (rewrite) append() compact()
What it does Streams the file into a new version with the changes, then swaps it in atomically Adds the changes to the end of the file; existing bytes are never touched Rewrites the file, dropping deleted and replaced rows
Cost The whole file The size of the change The whole file
Change one section of a 1.37M-row file 2.7 s 63 ms —
Insert or replace in the middle of a sortedBy file Yes No: appended rows, upserts included, must sort after the existing rows —
Deleted or replaced rows Removed Marked deleted, skipped by readers Removed
Revoke access, or narrow a grant Yes (fresh secrets) No Yes (fresh secrets)
File size Minimal Grows with every append Back to minimal
Readers with the file open Keep the version they opened Keep working Keep the version they opened

Use update() for occasional changes, and append() for frequent ones, with a compact() now and then (or autoCompact to do it for you).

Changes you can make

Change JavaScript .NET (JazminUpdate / JazminAppend)
Add rows insert: [rows] Insert = [rows]
Replace rows with the same key, or add them upsert: [rows], keyColumns: ['id'] Upsert = [rows], KeyColumns = ["id"]
Remove rows delete: { status: 'void' } (any filter) Delete = JazminFilter.Eq("status", "void")
Change columns on the rows a filter matches (1.6.0, update only) set: { status: 'paid' }, where: { id: 7 } Set = ..., Where = JazminFilter.Eq("id", 7)
Change several tables in one rewrite (1.6.0, update only) tables: { transactions: { ... } } (Several Tables) Tables = ... (JazminTableChanges)
Change metadata (merged in) metadata: { ... } Metadata = new JsonObject { ... }
Add or remove embedded files addFiles, removeFiles AddFiles, RemoveFiles
Grants (access-controlled files) grant, revoke (update only) Grant, Revoke (update only)
Add, change or remove locks on stored files and masked columns (1.7.0) locks: { contracts: { password } | null } (Locked files) Locks = ...
Mask or unmask columns (1.7.0, update only) masks: { salary: { lock: 'hr' } | null } (Masked Columns) Masks = ...
Which table table: 'lines' Table = "lines"

All three, step by step

import { append, compact, open, update, write } from '@smithsoft-studios/jazmin';
import fs from 'node:fs';

const columns = [
  { name: 'id', type: 'int', nullable: false, index: 'sorted' },
  { name: 'status', type: 'string' },
  { name: 'amount', type: 'decimal' },
];
write('invoices.jzm', Array.from({ length: 10_000 }, (_, i) => ({ id: i + 1, status: i % 50 === 0 ? 'void' : 'open', amount: `${(i % 900) + 100}.00` })), {
  columns,
});
const state = (label) => {
  const r = open('invoices.jzm');
  console.log(label.padEnd(16), `${r.rowCount} rows, ${r.appendCount} appends, ${r.deletedRowCount} deleted waiting,`,
    `${Math.round(fs.statSync('invoices.jzm').size / 1024)} KB, invoice 5000:`, r.find({ id: 5000 }).next().value?.status);
  r.close();
};
state('written');

// A rewrite: one insert, one correction, and the voids removed
console.log(update('invoices.jzm', {
  insert: [{ id: 10_001, status: 'open', amount: '250.00' }],
  upsert: [{ id: 5000, status: 'paid', amount: '600.00' }],
  keyColumns: ['id'],
  delete: { status: 'void' },
}));
state('after update');

// Appends: cheap, for frequent changes
for (let day = 0; day < 5; day++) {
  append('invoices.jzm', {
    insert: [{ id: 10_002 + day, status: 'open', amount: '99.00' }],
    upsert: [{ id: 100 + day, status: 'paid', amount: '100.00' }], // the old version is marked deleted
    keyColumns: ['id'],
  });
}
state('after 5 appends');

const { rowCount, bytesBefore, bytesAfter } = compact('invoices.jzm');
console.log(`compacted: ${rowCount} rows, ${Math.round(bytesBefore / 1024)} KB -> ${Math.round(bytesAfter / 1024)} KB`);
state('after compact');

// In a sortedBy file, appended rows (upserts too) must sort after the existing rows
write('ledger.jzm', [{ id: 1, status: 'open' }, { id: 2, status: 'open' }], { sortedBy: ['id'] });
try {
  append('ledger.jzm', { upsert: [{ id: 1, status: 'paid' }], keyColumns: ['id'] });
} catch (e) {
  console.log(e.message);
}
console.log(update('ledger.jzm', { upsert: [{ id: 1, status: 'paid' }], keyColumns: ['id'] }).updated, 'row corrected by update()');

written          10000 rows, 0 appends, 0 deleted waiting, 53 KB, invoice 5000: open
{
  rowCount: 9801,
  inserted: 1,
  updated: 1,
  deleted: 200,
  expiredGrantsRemoved: 0
}
after update     9801 rows, 0 appends, 0 deleted waiting, 51 KB, invoice 5000: paid
after 5 appends  9807 rows, 5 appends, 4 deleted waiting, 53 KB, invoice 5000: paid
compacted: 9807 rows, 53 KB -> 51 KB
after compact    9807 rows, 0 appends, 0 deleted waiting, 51 KB, invoice 5000: paid
Row 2 is out of order for sortedBy [id]. Appended rows must sort after the existing rows; use update() to insert them in the middle
1 row corrected by update()

In .NET

using Jazmin;
using Jazmin.Query;

var columns = new[]
{
    new JazminColumn("id", JazminType.Int) { Nullable = false },
    new JazminColumn("status", JazminType.String),
};
using (var writer = JazminWriter.Create("invoices.jzm", columns, new JazminWriteOptions { SortedBy = ["id"] }))
    for (long i = 1; i <= 1000; i++) writer.WriteValues(i, i % 50 == 0 ? "void" : "open");

var updated = JazminFile.Update("invoices.jzm", new JazminUpdate
{
    Upsert = [new Dictionary<string, object?> { ["id"] = 500L, ["status"] = "paid" }],
    KeyColumns = ["id"],
    Delete = JazminFilter.Eq("status", "void"),
});
Console.WriteLine(updated);

var appended = JazminFile.Append("invoices.jzm", new JazminAppend
{
    Insert = [new Dictionary<string, object?> { ["id"] = 1001L, ["status"] = "open" }],
    AutoCompact = new JazminAutoCompact(Appends: 20, DeletedRatio: 0.25), // compact when either is reached
});
Console.WriteLine(appended);

var compacted = JazminFile.Compact("invoices.jzm");
Console.WriteLine($"{compacted.RowCount} rows; {(compacted.BytesAfter < compacted.BytesBefore ? "smaller" : "no smaller")} after compacting");

JazminUpdateResult { RowCount = 981, Inserted = 0, Updated = 1, Deleted = 19, ExpiredGrantsRemoved = 0 }
JazminAppendResult { RowCount = 982, Inserted = 1, Updated = 0, Deleted = 0, AppendCount = 1, DeletedRowCount = 0, Compacted = False, ExpiredGrantsRemoved = 0 }
982 rows; smaller after compacting

Rules and guarantees

  • Atomic. update() and compact() write a new version next to the file and swap it in. If anything fails, the original is untouched and the temporary file is removed. Readers never see a half-written file.
  • Crash-safe appends. If the process dies during an append, readers ignore the incomplete part and use the previous version (reader.recovered is true); the next append removes it. A failed append (an invalid row, say) leaves the file exactly as it was.
  • Memory is bounded by the size of your changes, not the file. An update that inserted, upserted and deleted a 274-row section of a 1.37M-row access-controlled file took 3.0 s with a 74 MB process peak in Node.
  • Sorted files stay sorted. update() merges new rows into place; a row whose sort column changes moves. Appended rows must sort after the existing ones, and that includes upserts, because the new version of a row is written at the end. If you correct older rows often, index the key column instead of declaring sortedBy, or correct them with update().
  • One writer at a time. append, update and compact take a lock file (invoices.jzm.lock). A second writer gets "Another writer is changing ...". If a writer process was killed, delete the stale lock file.
  • Readers see the version they opened, on Windows too. Open a new reader to see the changes.
    • On Windows, a program holding the file without allowing renames (some editors and backup tools) blocks a rewrite with a clear error; the original is untouched.
    • Node on Windows replaces an open file in two quick steps, so for an instant the name is missing; .NET does it in one step.
  • Keys: encrypted and password files need their key or password, and keep their protection. Access-controlled files need the owner key; an access key gets "Only the file owner's master key can modify this file".
  • Writer features are checked (1.7.0). append, update, compact and rotateKey refuse a file that uses a writer feature this library doesn't know, so an older writer can't drop what a newer one wrote; reading is unchanged. reader.writerFeatures (.NET WriterFeatures) lists them. The first are file-locks and masked-columns. Releases 1.0 to 1.6 don't check them yet: see Versions and Compatibility.

How big appends get, and when to compact

Each append writes the new rows, a chunk directory for each partition it touched, and a small new header: under 1 KB on top of the data for a file of 5,000 chunks. The header also lists earlier appends' directories, so what an append adds grows until the next compaction (the 600th one-row append added 2.2 KB to an encrypted file). Deleted rows keep their space until compact().

Pick an autoCompact threshold that suits how often you append and delete: { appends: 100 } keeps both in check for most uses, and { deletedRatio: 0.25 } compacts when a quarter of the rows are dead.

Changing indexes and the sort order (1.6.0)

update() and compact() change an existing file's indexes and sort order in one rewrite that keeps everything else: rows, keys and grants, embedded files, saved shapes, the document and metadata.

update('orders.jzm', { key, indexes: { customer: ['sorted', 'trigram'], note: 'trigram', city: [] } }); // columns not named keep theirs
update('orders.jzm', { key, sortedBy: ['customer', 'date'] });                                        // null for no order

// fragment
JazminFile.Update("orders.jzm", new JazminUpdate
{
    Key = key,
    Indexes = new Dictionary<string, IReadOnlyList<JazminIndexKind>> { ["customer"] = [JazminIndexKind.Sorted, JazminIndexKind.Trigram], ["city"] = [] },
    SortedBy = ["customer", "date"],   // [] for none
});
  • Sorted indexes find values, lists and ranges; trigram (text search) indexes find text a value contains, in text columns. The first sort column needs no sorted index: the order finds its values. A column the file stops being sorted by gets a sorted index, so lookups on it stay fast.
  • A new sort order is applied within a memory budget: up to sortRunRows rows (10,000, 25,000 or 100,000, by priority) at a time, more through temporary files locked with a key made for them and deleted afterwards. Rows with equal sort values keep their order. From 1.7.0 large sorted indexes are built within a budget too: see Tuning Speed and Memory.
  • Cost on 1,000,000 rows: an index added or removed costs about a plain rewrite (1.4–2.5 s, against 1.7–2.2 s); a new sort order about three times that (5.4–6.1 s in JavaScript, 6.6–6.9 s in .NET), because every row is read and written twice. Three runs each, Node 24 and .NET 10 on a 12th-gen Intel i7.

Text orders that ignore case or compare numbers (1.7.0)

A key of a table's sort order can compare text ignoring case and accents (nocase: Apple, banana, Émile) or with the numbers inside it by value (natural: file2 before file10), or both. Write it as { column, nocase, natural } in sortedBy (.NET Sort with JazminSortKey), in write(), update() and new tables.

import { open, update, write } from '@smithsoft-studios/jazmin';

write('names.jzm', ['file10', 'File2', 'émile', 'file1', 'Apple'].map((name) => ({ name })), { columns: [{ name: 'name', type: 'string' }] });
update('names.jzm', { sortedBy: [{ column: 'name', nocase: true, natural: true }] });

const reader = open('names.jzm');
console.log([...reader.rows()].map((r) => r.name));
console.log(reader.sort, reader.sortedBy);
reader.close();

[ 'Apple', 'émile', 'file1', 'File2', 'file10' ]
[ { column: 'name', nocase: true, natural: true } ] undefined

using Jazmin;

using (var writer = JazminWriter.Create("names.jzm", [new JazminColumn("name", JazminType.String)]))
{
    foreach (var name in new[] { "file10", "File2", "émile", "file1", "Apple" }) writer.WriteValues(name);
}
JazminFile.Update("names.jzm", new JazminUpdate { Sort = [new JazminSortKey("name") { NoCase = true, Natural = true }] });

using var reader = JazminReader.Open("names.jzm");
Console.WriteLine(string.Join(", ", reader.Rows().Select(r => r["name"])));
Console.WriteLine(string.Join(", ", reader.Sort!));

Apple, émile, file1, File2, file10
name (nocase, natural)
  • reader.sort (.NET Sort) gives the whole order. reader.sortedBy keeps only its leading plain keys (none here, so undefined), the part every reader relies on, so releases 1.0 to 1.6 read such files correctly.
  • Writers check rows against the order, and appends, updates, compaction and re-sorts keep it.
  • Filters still compare values exactly: { name: 'apple' } doesn't find Apple. Use icontains for that.
  • Older writers (1.0 to 1.6) that append to such a file drop the rest of the order; the rows stay in it.

Progress and Stop for rewrites (1.7.0)

write(), update() and compact() take onProgress and signal, as reads do (Progress and Stop on long reads). In .NET, JazminUpdate, JazminWriteOptions and JazminFile.Compact(path, update) take Progress (an IProgress<JazminWriteProgress>) and a CancellationToken.

A rewrite reports { stage, done, total } in up to three stages:

Stage What done counts
sort Rows put into a new sort order (only when the order changes)
write Rows written into the new file, after each chunk
indexes Indexes built at the end, for each table (total: the table's indexes) 
import { compact, open, update, write } from '@smithsoft-studios/jazmin';

const rows = Array.from({ length: 20_000 }, (_, i) => ({ id: i, account: `A${i % 50}` }));
write('orders.jzm', rows, { columns: [{ name: 'id', type: 'int', index: ['sorted'] }, { name: 'account', type: 'string' }], sortedBy: ['id'] });

const last = {};
update('orders.jzm', { sortedBy: ['account', 'id'], onProgress: ({ stage, done, total }) => { last[stage] = `${done} of ${total}`; } });
console.log(last);

const stop = new AbortController();   // a Stop button would call stop.abort()
try {
  compact('orders.jzm', { signal: stop.signal, onProgress: ({ stage, done }) => { if (stage === 'write' && done >= 5000) stop.abort(); } });
} catch (e) {
  console.log('stopped:', e.name, '- the file is as it was, sorted by', open('orders.jzm').sortedBy);
}

{ sort: '20000 of 20000', write: '20000 of 20000', indexes: '1 of 1' }
stopped: AbortError - the file is as it was, sorted by [ 'account', 'id' ]

// fragment
using var stop = new CancellationTokenSource();
JazminFile.Update("orders.jzm", new JazminUpdate
{
    Key = key, SortedBy = ["account", "id"], CancellationToken = stop.Token,
    Progress = new Progress<JazminWriteProgress>(p => ShowStep(p.Stage, p.Total is { } total ? (double)p.Done / total : null)),
});
  • A stop leaves nothing half-done: write() leaves no file, and update() and compact() leave the file as it was, with no temporary file behind. A .NET writer stopped part-way deletes the file it created (on a stream, the output is left incomplete).
  • total is the rows in the tables, or null for write() from a generator, whose length isn't known.
  • In JavaScript a rewrite holds its thread until it finishes, so stop it from onProgress, or from another thread that sets a flag onProgress reads. In .NET, cancel the token from any thread.

Is compacting worth it? storage() (1.6.0)

reader.storage() (.NET reader.Storage()) shows how a file's bytes are used: what compacting would give back, and why. It reads only the file's directories, never its rows; a shared file needs its owner key.

const r = open('statements.jzm', { key: owner });
const s = r.storage();
r.close();
s.unusedBytes;   // bytes the current version no longer uses (headers appends replaced, files replaced or removed)
s.deletedRows;   // rows deleted or replaced by appends, still stored; s.deletedBytes estimates their bytes
s.appendCount;   // appends since the last full write
s.tables;        // per table: rows, deletedRows, chunks, rowsPerChunk, indexes [{ column, kind, segments }]

// fragment
using var r = JazminReader.Open("statements.jzm", new JazminReadOptions { Key = owner });
var s = r.Storage();   // FileBytes, UsedBytes, UnusedBytes, DeletedRows, DeletedBytes, AppendCount, Tables
  • A file written in full has 0 unused bytes.
  • Index segments: one after a full write, and one more per append. A lookup reads every segment; compact() merges them.
  • Rows per chunk: each append adds small chunks. In a shared file, compact({ regroup: true }) merges them.
  • Cost: 0.1 ms on a 1,000,000-row file; 3 ms after 200 appends; 31 ms (JavaScript) or 14 ms (.NET) for a shared file of 250,000 rows in 1,000 partitions.

Access-controlled files

  • New grants can be added with append, and existing ones widened.
  • Revoking or narrowing (fewer partitions or groups, an earlier expiry, offline to online) needs update() or compact(), because only a full rewrite re-locks the file with fresh secrets. append refuses with "The grant for … is narrower than before".
  • Expired grants are dropped by every rewrite.
  • Regrouping: each append starts new chunks for the partitions it touches, so after a year of small syncs a person's rows sit in hundreds of tiny chunks. compact({ key, regroup: true }) (.NET JazminFile.Compact(path, owner, regroup: true)) writes each partition's rows together. A simulated year (50 people, 3 syncs a day, 150,000 rows):
Compacted Compacted with regroup
File 7,241 KB, 37,500 chunks 538 KB, 50 chunks
One person's rows (3,000) 125 KB read, 23 ms 10 KB read, 2 ms

Regrouping needs a file without sortedBy, or one sorted by the partition column first. reader.advise() suggests it when partitions are spread out.