---
title: "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

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

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

```sql
USE BRANCH dev;
```

### REPL Command

```bash
\use dev
```

### Check Current Branch

```bash
\show branch
```

## Listing Branches

### REPL

```bash
\branches
```

### SQL

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

```sql
MERGE DATABASE BRANCH dev INTO main;
```

### With Conflict Strategy

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

```sql
DROP DATABASE BRANCH dev;

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

## Use Cases

### Development Environment

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

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

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

## Performance Characteristics

Figures from internal tests (HeliosDB Lite branch storage); reproduce on your own hardware.

### Branch Creation

- **Latency**: <10ms regardless of database size
- **Throughput**: 1000+ branches/second
- **Storage**: ~500 bytes of metadata per branch

### Read Operations

- **Current Branch Hit**: <0.1ms overhead vs. non-branched read
- **Parent Chain Lookup**: <0.5ms overhead (proportional to chain depth)
- **Throughput**: ~95% of non-branched read throughput

### Write Operations

- **Copy-on-Write**: <0.2ms overhead for first write to a key
- **Subsequent Writes**: Same as non-branched write
- **Throughput**: ~95% of non-branched write throughput

### Storage Overhead

```
Example: 1GB database, 5 branches, 10% data modified per branch

Original:   1GB
Branches:   5 × (500 bytes metadata + 100MB data) ≈ 500MB
Total:      1.5GB (50% overhead, 10% per modified data as expected)
```

## 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](https://www.heliosdb.com/docs/lite/features/time-travel/) - Query historical data without branches
- [REPL Commands](https://www.heliosdb.com/docs/lite/user-guide/repl-commands/) - `\branches`, `\use` commands