Database Branching

Database Branching

Database branching in HeliosDB Nano works like Git branches for your data. Create isolated copies of your database for development, testing, or experimentation without duplicating storage.

Overview

Branches are lightweight, copy-on-write database snapshots that share unchanged data with their parent. This makes creating branches nearly instantaneous regardless of database size.

Creating Branches

From Current State

-- Create a branch from the current state of main
CREATE DATABASE BRANCH dev FROM main;

-- Or use AS OF NOW explicitly
CREATE DATABASE BRANCH dev FROM main AS OF NOW;

From a Point in Time

-- Branch from a specific timestamp
CREATE DATABASE BRANCH hotfix FROM main
AS OF TIMESTAMP '2025-01-15 00:00:00';

-- Branch from a specific transaction
CREATE DATABASE BRANCH feature FROM main
AS OF TRANSACTION 12345;

Switching Branches

SQL Syntax

USE BRANCH dev;

REPL Command

\use dev

Check Current Branch

\show branch

Listing Branches

REPL

\branches

SQL

SELECT * FROM pg_database_branches();

Output includes:

• name - Branch name
• parent - Parent branch name
• created_at - Creation timestamp
• commit_count - Number of commits
• is_current - Whether this is the active branch

Merging Branches

Basic Merge

MERGE DATABASE BRANCH dev INTO main;

With Conflict Strategy

MERGE DATABASE BRANCH dev INTO main
WITH (strategy = 'theirs');

Strategies:

Strategy	Description
fail	Fail on any conflict (default)
ours	Keep target branch version on conflict
theirs	Keep source branch version on conflict

Dropping Branches

DROP DATABASE BRANCH dev;

-- With IF EXISTS to avoid errors
DROP DATABASE BRANCH IF EXISTS dev;

Use Cases

Development Environment

-- Create dev branch
CREATE DATABASE BRANCH dev FROM main;
USE BRANCH dev;

-- Make experimental changes
ALTER TABLE users ADD COLUMN preferences JSONB;
INSERT INTO users (name, preferences) VALUES ('Test', '{}');

-- If changes work, merge back
USE BRANCH main;
MERGE DATABASE BRANCH dev INTO main;

Feature Testing

-- Create branch for testing a feature
CREATE DATABASE BRANCH feature_x FROM main;
USE BRANCH feature_x;

-- Run integration tests with modified data
DELETE FROM users WHERE test_account = true;
-- Tests run here...

-- Discard when done (no merge needed)
USE BRANCH main;
DROP DATABASE BRANCH feature_x;

Point-in-Time Recovery

-- Create branch from before the incident
CREATE DATABASE BRANCH recovery FROM main
AS OF TIMESTAMP '2025-01-15 00:00:00';

-- Verify data is correct
USE BRANCH recovery;
SELECT COUNT(*) FROM orders;

-- If good, make it the new main
-- (requires careful planning in production)

How It Works

1. Copy-on-Write: Branches share data pages with their parent. Only modified pages are copied.
2. Snapshot Isolation: Each branch has its own transaction log and snapshot.
3. Minimal Overhead: Creating a branch is O(1) regardless of data size.

Best Practices

1. Name branches descriptively: feature_auth_refactor, bugfix_order_calc
2. Keep branches short-lived: Merge or delete when done
3. Test merges on a copy first: Create a test branch to verify merge results
4. Use timestamps for recovery branches: Makes it clear what state you’re restoring

Limitations

• Cannot merge into a branch that has diverged significantly
• Circular branch relationships are not allowed
• Branch names must be unique

Related

• Time-Travel Queries - Query historical data without branches
• REPL Commands - \branches, \use commands