Skip to content
HeliosDB HeliosDB Lite

Database Branching Tutorial

Build: default — no feature flag required SQL: CREATE BRANCH, USE BRANCH, MERGE BRANCH, DROP BRANCH

UVP

Schema migrations, A/B tests, and “what if” experiments usually mean a second database. Lite gives you git-like branches in the same database — instant copy-on-write divergence from main, an isolated workspace for arbitrary DML and DDL, and a MERGE that surfaces conflicts the way git does. Branches stack with time-travel, encryption, RLS, and materialized views unchanged. Zero-downtime schema changes, dev/staging branches against production data, and forensic clones from any point in history — all in one binary.

Prerequisites

  • HeliosDB Lite
  • A REPL or psql client connected to a Lite instance
  • 15 minutes

1. Create a Branch

CREATE BRANCH <name> FROM <parent> clones a writable snapshot. Copy-on-write means the operation is instant — pages are duplicated only when you mutate them on the branch.

-- Snapshot the current state of `main` as a new branch.
CREATE BRANCH dev FROM main;


-- Optional metadata.
CREATE BRANCH staging FROM main
WITH (
    region          = 'eu-west-1',
    metadata_owner  = 'team-data'
);

The branch defaults to active state. You can list every branch via the system view:

SELECT branch_name, parent_id, size_mb, status
FROM pg_database_branches()
ORDER BY created_at;

2. Switch Branch — USE BRANCH

Every subsequent statement on the session targets the chosen branch:

USE BRANCH dev;


ALTER TABLE users ADD COLUMN preferences JSONB;
INSERT INTO users (id, name, preferences) VALUES (999, 'Test', '{"theme":"dark"}');


SELECT * FROM users WHERE id = 999;       -- visible only on `dev`

To return to the trunk:

USE BRANCH main;
SELECT * FROM users WHERE id = 999;       -- 0 rows: untouched on main

3. Branch From a Point in Time

Combine CREATE BRANCH with AS OF to fork from any historical timestamp — perfect for “reproduce the bug from last Tuesday” sessions:

CREATE BRANCH audit_2024 FROM main
AS OF TIMESTAMP '2024-12-31 23:59:59';


USE BRANCH audit_2024;
SELECT count(*) FROM transactions;        -- exactly the count at year-end

See TIME_TRAVEL_TUTORIAL for the temporal half of this story.

4. Merge — Resolving Conflicts

MERGE BRANCH <source> INTO <target> walks the divergent set of keys and reconciles them with one of four strategies:

StrategyBehaviour
AutoConflict-free keys flow; conflicts default to source. (Default.)
ManualAny conflict aborts the merge — you fix them yourself.
TheirsSource wins on every conflict.
OursTarget wins on every conflict.
USE BRANCH main;


-- Default strategy = Auto.
MERGE BRANCH dev INTO main;


-- Or pick explicitly:
MERGE BRANCH dev INTO main WITH (strategy = 'manual');

A successful merge returns Merge completed: N keys merged, M conflicts resolved. In manual mode, a merge that hits conflicts fails, and the error lists the conflicting keys so you can resolve them on the branch and merge again.

5. Drop a Branch

DROP BRANCH dev;
DROP BRANCH IF EXISTS dev;     -- idempotent

Two safety checks:

  • Cannot drop main. The branch with is_default = true (the trunk) is protected.
  • Cannot drop a branch with active children. Drop the children first, or merge them up.

DROP queues the data for branch garbage collection — see BRANCH_GC_QUICK_REF for retention tuning.

6. Use Cases

Zero-downtime schema migration

CREATE BRANCH migration_v2 FROM main;
USE BRANCH migration_v2;


ALTER TABLE orders RENAME COLUMN amount TO total_amount;
ALTER TABLE orders ADD COLUMN currency TEXT DEFAULT 'USD';


-- Run integration tests against `migration_v2`.
-- When green:
USE BRANCH main;
MERGE BRANCH migration_v2 INTO main;
DROP BRANCH migration_v2;

The trunk continues to serve traffic the entire time. The merge is atomic; clients see the schema flip in one step.

A/B testing with branch-routed traffic

CREATE BRANCH variant_a FROM main;
CREATE BRANCH variant_b FROM main;


USE BRANCH variant_a;
UPDATE pricing SET strategy = 'discount_aggressive';


USE BRANCH variant_b;
UPDATE pricing SET strategy = 'bundle_focused';

Then route incoming requests to one of the two branches based on a feature flag. After the experiment, merge the winner.

Dev / staging snapshots

-- Every Monday, refresh `staging` from production.
DROP BRANCH IF EXISTS staging;
CREATE BRANCH staging FROM main;

CoW means staging is “free” until devs start writing to it.

7. Inspecting Branches

The system view pg_database_branches() returns one row per branch:

SELECT
    branch_name,
    branch_id,
    parent_id,
    created_at,
    fork_point_lsn,
    size_mb,
    status
FROM pg_database_branches();

status is one of Active, Merged, or Dropped. The latter two retain metadata until garbage collection runs.

Where Next