Database Branching

Database Branching

Database branching in HeliosDB-Lite 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