---
title: "Database Branching Tutorial"
---

# 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.

```sql
-- 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:

```sql
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:

```sql
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:

```sql
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:

```sql
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](https://www.heliosdb.com/docs/lite/guides/tutorials/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:

| Strategy | Behaviour |
|---|---|
| `Auto` | Conflict-free keys flow; conflicts default to *source*. (Default.) |
| `Manual` | Any conflict aborts the merge — you fix them yourself. |
| `Theirs` | Source wins on every conflict. |
| `Ours` | Target wins on every conflict. |

```sql
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

```sql
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

```sql
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

```sql
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

```sql
-- 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:

```sql
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

- [TIME_TRAVEL_TUTORIAL](https://www.heliosdb.com/docs/lite/guides/tutorials/time_travel_tutorial/) — temporal queries; pair with branches for forensic clones.
- [CDC and Tenant Migration Tutorial](https://www.heliosdb.com/docs/lite/guides/cdc_and_migration_tutorial/) — capture changes for replication.