Search Shortcut cmd + k | ctrl + k
differential
Downloads 29this week
GitHub stars 0
Extension descriptor (YAML)

Versioned DuckDB databases - snapshots, time travel, branches, clones and S3 export over native storage

Maintainer(s): tobilg

Installing and Loading 

INSTALL differential FROM community;
LOAD differential;

Example 

-- Run in order in one DuckDB session, using fresh demo paths and database names.
-- Replace the absolute paths below; the S3 section also needs an existing bucket
-- and AWS credentials (environment variables, a profile, SSO or an instance role).
INSTALL differential FROM community;
LOAD differential;
SET differential_repository = '/absolute/path/differential-demo/repository';

-- LOCAL STORAGE: create, snapshot, read history, branch, clone, restore and clean up.
-- TYPE differential secrets hold storage defaults, never credentials.
CREATE SECRET local_storage (
    TYPE differential,
    DATA_PATH '/absolute/path/differential-demo/local-data'
);
ATTACH 'differential:sales' AS sales (SECRET local_storage);
CREATE TABLE sales.orders (id INTEGER PRIMARY KEY, amount INTEGER);
INSERT INTO sales.orders VALUES (1, 10), (2, 20);
CALL differential_create_snapshot('sales', name => 'baseline',
    message => 'initial load', request_id => 'sales-baseline'); -- snapshot 1
INSERT INTO sales.orders VALUES (3, 30);
CALL differential_create_snapshot('sales', name => 'three-orders'); -- snapshot 2

SELECT sum(amount) FROM sales.orders;                       -- 60
SELECT sum(amount) FROM sales.orders AT (VERSION => 1);     -- 30
ATTACH 'differential:sales' AS sales_history (SNAPSHOT_NAME 'baseline');
SELECT count(*) FROM sales_history.orders;                  -- 2
SELECT snapshot_id, snapshot_name FROM sales.snapshots();
DETACH sales_history;

-- A branch shares the database identity; a clone is an independent database.
CALL differential_create_branch('sales', 'dev', snapshot => 1);
ATTACH 'differential:sales' AS sales_dev (BRANCH 'dev');
INSERT INTO sales_dev.orders VALUES (4, 40);
CALL differential_create_snapshot('sales_dev', message => 'dev order'); -- snapshot 3
SELECT sum(amount) FROM sales_dev.orders;                   -- 70
DETACH sales_dev;
CALL differential_clone_database('sales', 'sales_copy', snapshot => 1);
ATTACH 'differential:sales_copy' AS sales_copy;
SELECT sum(amount) FROM sales_copy.orders;                  -- 30
DETACH sales_copy;

-- Restore requires a detached branch and preserves its previous contents.
-- DETACH also releases the hidden reader pins taken by AT queries.
DETACH sales;
CALL differential_restore('sales', 1);                      -- restored snapshot 4
ATTACH 'differential:sales' AS sales;
SELECT sum(amount) FROM sales.orders;                       -- 30
DETACH sales;
CALL differential_drop_branch('sales', 'dev');
CALL differential_expire_snapshots('sales', versions => [2, 3], dry_run => true);
CALL differential_expire_snapshots('sales', versions => [2, 3]);
CALL differential_cleanup_old_files(cleanup_all => true);
CALL differential_delete_orphaned_files(dry_run => true);
CALL differential_compact();
-- The main branch, baseline snapshot and independent clone remain usable.

-- S3 STORAGE: credentials, exports, history, remote reads and recovery.
-- Replace the bucket/prefix and region. The bucket must already exist.
INSTALL httpfs;
LOAD httpfs;
INSTALL aws;
LOAD aws;
-- TYPE s3 supplies credentials by matching SCOPE; these secrets are session-scoped.
-- REFRESH auto keeps temporary credentials current through the credential chain.
CREATE SECRET s3_auth (
    TYPE s3,
    PROVIDER credential_chain,
    REFRESH auto,
    REGION 'us-east-1',
    SCOPE 's3://my-bucket/differential-demo/'
);
CREATE SECRET s3_storage (
    TYPE differential,
    DATA_PATH 's3://my-bucket/differential-demo/'
);
ATTACH 'differential:archive' AS archive (SECRET s3_storage);
CREATE TABLE archive.events (id INTEGER PRIMARY KEY, payload VARCHAR);
INSERT INTO archive.events VALUES (1, 'one'), (2, 'two');
SELECT snapshot_id, export_state, export_locator
FROM differential_create_snapshot('archive', name => 'baseline',
    request_id => 'archive-baseline');                     -- snapshot 1, complete
-- Save the immutable export URI for recovery later in this session.
SET VARIABLE baseline_export = (
    SELECT 's3://my-bucket/differential-demo/' || export_locator
    FROM differential_snapshots('archive') WHERE snapshot_id = 1
);

-- Subsequent exports share unchanged data. Deferred exports can be retried safely.
INSERT INTO archive.events VALUES (3, 'three');
CALL differential_create_snapshot('archive', name => 'three-events',
    export_mode => 'deferred');                            -- snapshot 2, pending
CALL differential_retry_export('archive', 2);              -- snapshot 2, complete
SELECT snapshot_id, export_state FROM archive.snapshots();
SELECT count(*) FROM archive.events AT (VERSION => 1);     -- 2

-- Any host with the same scoped S3 credentials can open this branch head read-only.
-- It sees the latest exported snapshot at ATTACH time; reattach to refresh it.
ATTACH 'differential:s3://my-bucket/differential-demo/heads/archive/main' AS archive_latest;
SELECT count(*) FROM archive_latest.events;                 -- 3
DETACH archive_latest;
SELECT database_name, branch_name, snapshot_id
FROM differential_remote_heads('s3://my-bucket/differential-demo/');
DETACH archive;

-- Evict only an export-complete snapshot. Historical reads then fetch its data from S3.
CALL differential_evict_snapshots('archive', versions => [1]);
CALL differential_cleanup_old_files(cleanup_all => true);
ATTACH 'differential:archive' AS archive_history (SNAPSHOT_VERSION 1);
SELECT count(*) FROM archive_history.events;                -- 2
DETACH archive_history;

-- Recover the saved immutable export into a new writable LOCAL database.
-- The named differential secret chooses its destination; s3_auth reads the source.
CALL differential_recover_export(getvariable('baseline_export'), 'archive_recovered',
    secret => 'local_storage');
ATTACH 'differential:archive_recovered' AS archive_recovered;
SELECT count(*) FROM archive_recovered.events;              -- 2
INSERT INTO archive_recovered.events VALUES (9, 'recovered locally');
DETACH archive_recovered;

-- Branch/clone/restore work with S3 too; evicted data is rehydrated when needed.
-- Branches and clones inherit the source's S3 data path.
CALL differential_create_branch('archive', 'dev', snapshot => 1);
ATTACH 'differential:archive' AS archive_dev (BRANCH 'dev');
INSERT INTO archive_dev.events VALUES (4, 'dev');
CALL differential_create_snapshot('archive_dev');           -- snapshot 3, complete
DETACH archive_dev;
CALL differential_clone_database('archive', 'archive_copy', snapshot => 1);
ATTACH 'differential:archive_copy' AS archive_copy;
SELECT count(*) FROM archive_copy.events;                   -- 2
DETACH archive_copy;
CALL differential_restore('archive', 1);                    -- snapshot 4, complete
ATTACH 'differential:archive' AS archive;
SELECT count(*) FROM archive.events;                        -- 2
DETACH archive;

CALL differential_drop_branch('archive', 'dev');
CALL differential_expire_snapshots('archive', versions => [2, 3], dry_run => true);
CALL differential_expire_snapshots('archive', versions => [2, 3]);
CALL differential_cleanup_old_files(cleanup_all => true);
CALL differential_compact();
-- Expiry and garbage collection reclaim unreferenced LOCAL data, not S3 objects.
-- Existing immutable S3 exports remain readable; the main branch and clone remain.
SELECT database_name, data_path FROM differential_databases();
SELECT kind, state FROM differential_operations();
DROP SECRET s3_storage;
DROP SECRET s3_auth;
DROP SECRET local_storage;

About differential

differential stores ordinary DuckDB databases in a durable, content-addressed differential store, so every version shares unchanged data. Native DuckDB SQL, transactions and indexes are unchanged; the extension adds immutable snapshots with commit metadata, AT (VERSION/TIMESTAMP) time travel, writable branches, independent clones, restore, expiry with garbage collection, and incremental, content-addressed S3 export that any host can attach read-only, including following a branch's latest snapshot through S3 branch heads. Several processes, and optionally several local users, can share one repository.

The SQL surface follows DuckLake's conventions: ATTACH 'differential:<db>' options (BRANCH, SNAPSHOT_VERSION, SNAPSHOT_TIME, SNAPSHOT_NAME, DATA_PATH), SET differential_* settings and database-first differential_* functions.

Status: experimental. Linux and macOS only. Commits are durable at DuckDB's sync points; exports are durable once complete. S3 is tested against MinIO and exercised on AWS; power-loss behaviour is not yet verified. See the repository for the SQL reference, the lifecycle guide and the storage format.

Added Functions

function_name function_type description comment examples
differential_branches table Lists the branches of a differential database with their head snapshot and, for shared databases, their state relative to the S3 branch head. NULL [SELECT branch_name, head_snapshot_id, remote_state FROM differential_branches('sales');]
differential_claim_branch_heads table Takes a single-writer database's S3 branch heads over after a copy of the repository wrote them. NULL [SELECT branch_name, snapshot_id FROM differential_claim_branch_heads('sales');]
differential_cleanup_old_files table Collects local data that no snapshot, branch, pin or pending export references any more. NULL [SELECT deleted_objects FROM differential_cleanup_old_files(cleanup_all => true);]
differential_clone_database table Creates an independent database from a snapshot of another one, on the same data path and sharing its data. NULL [SELECT database_id FROM differential_clone_database('sales', 'sales_copy');]
differential_compact table Returns free pages of the repository's metadata store to the file system. NULL [SELECT pages_reclaimed FROM differential_compact(max_pages => 1000);]
differential_create_branch table Creates a writable branch from a snapshot, sharing all data with it. NULL [SELECT branch_id FROM differential_create_branch('sales', 'dev', snapshot => 1);]
differential_create_snapshot table Checkpoints an attached writable branch and publishes it as an immutable snapshot with optional commit metadata; exports it to S3 and, for shared databases, moves the S3 branch head. NULL [SELECT snapshot_id, export_state FROM differential_create_snapshot('sales', name => 'v1', message => 'first load');]
differential_current_snapshot table Returns the snapshot an attachment reads, or the latest snapshot of the attached branch or database. NULL [SELECT id FROM differential_current_snapshot('sales');]
differential_databases table Lists the databases of the differential repository with their data path and writer mode. NULL [SELECT database_name, data_path, writers FROM differential_databases();]
differential_delete_orphaned_files table Removes files in local pools that no mapping references, left behind by crashes. NULL [SELECT orphaned_files FROM differential_delete_orphaned_files(dry_run => true);]
differential_drop_branch table Preserves a detached branch's contents as a snapshot and drops the branch (in a shared database, for every writer). NULL [SELECT preservation_snapshot_id FROM differential_drop_branch('sales', 'dev');]
differential_evict_snapshots table Drops the local copy of snapshots whose S3 export completed; they are then read through S3. NULL [SELECT snapshot_id FROM differential_evict_snapshots('sales', versions => [1], dry_run => true);]
differential_expire_snapshots table Retires snapshots selected by version list and/or age so their local data can be collected; S3 exports remain. NULL [SELECT snapshot_id FROM differential_expire_snapshots('sales', versions => [1], dry_run => true);]
differential_import_database table Copies an offline native DuckDB file into a new differential database and publishes it as snapshot 1. NULL [SELECT database_id FROM differential_import_database('/data/native.duckdb', 'imported');]
differential_operations table Lists the durable operation records of the repository, used to replay requests by request_id. NULL [SELECT kind, state, snapshot_id FROM differential_operations();]
differential_providers table Lists the repository's storage locations (metadata store, local pools, S3 locations) with their S3 configuration and credential source, never credentials. NULL [SELECT data_path, credential_source FROM differential_providers();]
differential_recover_export table Copies an exported snapshot from S3 into a new writable differential database, verifying every chunk. NULL [SELECT database_id FROM differential_recover_export('s3://my-bucket/lake/descriptors/', 'restored');]
differential_remote_heads table Lists the branch heads exported to an S3 location, each with the path to ATTACH its latest snapshot. NULL [SELECT database_name, branch_name, head FROM differential_remote_heads('s3://my-bucket/lake/');]
differential_resolve_conflict table Reconciles a diverged branch of a shared database with its S3 head, keeping local work as a new branch or as a local snapshot. NULL [SELECT kept_snapshot_id, kept_branch FROM differential_resolve_conflict('team', keep => 'branch');]
differential_restore table Resets a detached branch to a snapshot after preserving its current contents as a snapshot. NULL [SELECT snapshot_id, preservation_snapshot_id FROM differential_restore('sales', 1, branch => 'dev');]
differential_retention_roots table Lists why each stored database image is retained (snapshots, branches, pins, exports). NULL [SELECT reason, identity FROM differential_retention_roots();]
differential_retry_export table Finishes pending S3 exports of a database (all, or one snapshot) and republishes its branch heads. NULL [SELECT snapshot_id, export_state FROM differential_retry_export('sales');]
differential_snapshots table Lists every snapshot of a differential database (for a shared database also other writers' snapshots, read from S3) with commit metadata, publication and S3 export state. NULL [SELECT snapshot_id, snapshot_name, export_state FROM differential_snapshots('sales');]
differential_status table Reports the extension's capabilities, durability profiles, limits and last background publication error as capability/status rows. NULL [SELECT capability, status FROM differential_status();]
differential_storage_stats table Returns storage, S3, cache and timing counters of the repository and process as metric/value rows. NULL [SELECT metric, value FROM differential_storage_stats() WHERE metric = 'local_payload_bytes';]
differential_writers table Lists the advisory 'being written' markers of a shared database in S3: which hosts are writing which branches. NULL [SELECT branch_name, host, live FROM differential_writers('team');]

Overloaded Functions

This extension does not add any function overloads.

Added Types

This extension does not add any types.

Added Settings

name description input_type scope aliases
differential_auto_gc Run bounded differential garbage collection after native checkpoints BOOLEAN GLOBAL []
differential_remote_cache_size Size of the repository's disk cache for data read from S3 (e.g. '4GB'); 0 = off. Read when a remote file is opened VARCHAR GLOBAL []
differential_repository Differential repository root (relative paths resolve against the working directory); set before first use VARCHAR GLOBAL []
differential_repository_shared Create the repository for several local users (group-writable); set before first use BOOLEAN GLOBAL []
differential_s3_max_requests Parallel S3 requests per process (1-64), shared by all S3 locations and DuckDB instances in the process BIGINT GLOBAL []
differential_snapshot_on_checkpoint Publish every completed native checkpoint of a writable branch as a public snapshot BOOLEAN GLOBAL []