Example: a versioned notes app

The repo ships a small notes app under examples/notes-app: a markdown editor where every keystroke saves a new version, yet storage stays tiny and you can jump to any past version instantly. The stack is SvelteKit and Drizzle, but the interesting part is three pieces of SQL. This walks through them.

The schema

Two tables. notes holds metadata; note_versions holds every revision and is the xpatch table.

CREATE TABLE notes (    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),    title      TEXT NOT NULL,    created_at TIMESTAMPTZ DEFAULT NOW());CREATE TABLE note_versions (    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),    note_id     UUID NOT NULL REFERENCES notes(id) ON DELETE CASCADE,    content     TEXT NOT NULL,    version_num INT NOT NULL,    created_at  TIMESTAMPTZ DEFAULT NOW()) USING xpatch;

content is the big field that repeats almost unchanged between versions, and it is NOT NULL, as delta columns must be.

The configuration

This is where the app tunes for its workload, many tiny edits per document. The call lives in setup.ts:

SELECT xpatch.configure('note_versions'::regclass,    group_by       => 'note_id',               order_by       => 'version_num',            delta_columns  => ARRAY['content'],         keyframe_every => 200,                      compress_depth => 100,                      enable_zstd    => true                  );

1. Each note is its own version chain. A new version of note A never touches note B.

2. Versions are ordered by version_num within each note.

3. Only content is delta-compressed; version_num and timestamps are stored plainly.

4. A full keyframe only every 200 versions. Keystroke edits are tiny, so long delta chains barely cost anything, and fewer keyframes means less storage.

5. Try the last 100 versions as a delta base and keep the smallest. Keystroke-level edits resemble many recent versions, so a deep search finds a very close match.

6. Zstandard on top, the default.

Saving a version

There is no UPDATE. Each save reads the latest version_num for the note, adds one, and inserts a new row:

-- next version number for this noteSELECT COALESCE(MAX(version_num), 0) + 1 FROM note_versions WHERE note_id = $1;-- then insert itINSERT INTO note_versions (note_id, content, version_num)VALUES ($1, $2, $3)RETURNING *;

That append-only shape is the whole point: history is immutable, and the delta chain just grows.

Reading versions

Listing or loading a version is a plain query. Reconstruction is invisible:

SELECT version_num, content, created_atFROM note_versionsWHERE note_id = $1ORDER BY version_num DESC;

Every version comes back as full text, whether it was stored as a keyframe or a 6-byte delta.

Showing the compression

The app displays live stats from xpatch.stats():

SELECT total_rows, compressed_size_bytes, compression_ratioFROM xpatch.stats('note_versions');

In the example's own measurements, around 150 keystroke-level versions of a document drop from roughly 32 KB of raw content to about 1 KB stored. Your numbers depend entirely on how much each save changes.

Run it yourself

cd examples/notes-appbun run db:start   # PostgreSQL with pg-xpatch, via docker-composebun run dev        # tables and config are created on first run

Open the editor, type, and watch the version list and compression ratio update.

The takeaway

The pattern generalizes far beyond notes: pick the entity as group_by, a monotonic version as order_by, and delta-compress the large text or json column. Wikis, config history, chat-message edits, and audit trails all fit the same three pieces of SQL.