SingleStore Database Branching: How to Boost Developer Productivity

It's a game-changing feature that allows you to instantly clone your database into an independent, updatable copy. Imagine creating a staging environment or recovering from a bad update in minutes. Sounds too good to be true? Let's dive in.

Database branches share the same history as their parent — but once created, they are independent, fully updatable databases. This means you can insert, update and delete data in a branch without affecting the performance or stability of the parent database.

This flexibility is a developer's dream come true. Need to prototype a new feature? Create a private copy of the production database. Want to test a risky update? Branch away without fear. 

Lets dive into some practical use cases for database branching.

What is database branching?

Database branching allows developers to create isolated copies of a database schema, enabling them to work on different versions of the database simultaneously. Inspired by the concept of branching in version control systems for code, database branching lets developers create separate branches to work on various features or bug fixes without impacting their main database.

In the context of databases, this means you can test and experiment with different database schema changes, data migrations, and other modifications without affecting the production database. Imagine having the freedom to innovate and iterate on your database schema without the fear of disrupting your live environment. With database branching, you can create a safe space to explore new ideas, ensuring that your production database remains stable and secure. Compared to traditional methods, like spinning up entirely new databases and having to load it with data manually to test changes, branching offers a much more seamless and efficient way to do this.

Benefits of database branching

The benefits of branching are far-reaching. Depending on how you use it, the exact benefits will vary by use case. Let's look at two areas where branching is helpful and widely applicable to most developers and use cases.

Prototype new apps with confidence

Imagine you're building an internal chat app, similar to SingleStore's SQrL, powered by a large 1TB database. You want to expand its knowledge base, but making changes directly in production? That's a recipe for sleepless nights.

Creating a development branch is crucial for experimentation and testing. It provides an isolated space to modify the schema without affecting the production database, ensuring safe migrations and streamlined workflows.

Traditionally, creating a staging environment and restoring a 1TB database could consume hours of your day. But with SingleStore’s DB Branching, you can create a private copy of your production database in just minutes — mostly spent attaching the DB to a workspace. And, the time it takes to branch is independent of your database size.

Also, this lightning-fast process doesn't incur additional storage costs. Once your branch is live, it's an independent database. You're free to insert, update or delete data without impacting your production environment. It's like having a sandbox in the cloud!

Data recovery made easy

Here's where things get really interesting. With SingleStore, you can create branches in the past —- it's like having a time machine for your data!

Let's say you're managing an internal analytical database. You've just run an update query on a specific calculated column, but oops! You've accidentally modified the input columns too. Time to panic? Not with database branching. You can create a branch of your database down to the second by specifying a timestamp. For example:

Using SingleStore's query history dashboard, you can pinpoint when you ran the problematic query and create a branch from just before that moment. Simple! You now have an untouched version of your database pre-query. If you didn't branch at just the right time, simply detach the branched database and reattach it at a different point in time (sooner or later, either one works).

From here, you have options. If you can afford  brief downtime, simply drop your original database and rename your newly branched database to match the original. Or, use an 'INSERT INTO' statement to restore only the affected rows. This procedure accomplishes what we call 'online point-in-time restore' (PITR), and it's entirely self-service. 

All of this can be done in minutes, saving your productivity — and sanity — sidestepping a potential data disaster.

How does database branching work?

Let's peek under the hood of SingleStore's engine to understand how database branching works.

At SingleStore, we leverage object storage for what we call "bottomless databases." This approach has a key characteristic: it's append-only, meaning you only add to existing objects, and don’t modify or delete. Once data is written to an object, it becomes immutable.

Using object store and our internally developed 'Metadata Store' service, we can create a DB branch by simply generating pointers to individual blobs in the object storage. When changes are made to the new database, we don't alter the original blobs. Instead, we create new blobs and pointers to them. It even works this way for deletes. We create delete bitmaps to mask out rows that have been removed, and the delete bitmap is new data. This is what enables the incredible speed and efficiency of our branching process.

This approach explains why we can complete a branch operation in minutes, regardless of the database size. It’s not copying data; it’s creating a new set of references to existing data. Moreover, this makes branching a highly cost-effective operation. You’re only paying for the storage of changed pages, not an entire copy of the database. It’s like getting a whole new database for the price of a few edits!

It’s also worth emphasizing in SingleStore, a database branch is a full-fledged database with all the same durability guarantees as the parent.

Best practices for database branching

In general, there are some best practices to be aware of as you start to roll out branching to your database. To maximize the benefits, it’s crucial to follow some best practices:

• Create a main production branch: Designate a main production branch as the source of truth for your database schema. This branch should always reflect the current state of your production database.
• Create development branches: For each new feature or bug fix, create separate development branches. This isolation helps prevent conflicts and ensures that changes can be tested thoroughly before being merged into the main production branch.
• Use a consistent naming convention: Implement a consistent naming convention for your branches. This practice makes it easier to identify and manage different branches, streamlining your workflow.
• Regularly merge changes: To keep your database schema up-to-date, regularly merge changes from development branches into the main production branch. This practice helps maintain consistency and reduces the risk of integration issues. The longer a branch is allowed to deviate from the main branch, the higher chance of conflict when merging.

By adhering to these best practices, you can ensure a smooth and efficient database branching process, enhancing your development workflow.

How to get started

Branch creation with SingleStore's database branches is straightforward. You can create a branch with a simple SQL statement:

This command creates a new independent database called branch_db and attaches it to the Workspace where you ran the query. SingleStore Workspaces (WS) are independent deployments of compute resources used to run workloads. They can be scaled on the fly independently from storage, enabling granular scalability and isolation of compute resources.

Combining workspaces with database branching allows you to easily create an isolated testing environment within minutes, where you can prototype without sharing your production compute resources. When you're done testing, simply suspend or terminate your Workspace to manage compute resource overhead or costs.

You can also create branches using our intuitive UI. Since branches are created from a database, you simply expand your database options and click "Create Branch". This will prompt you to select the name of the branch, along with the workspace you want to attach the branch to. This process will create a database branch and attach it to that specific Workspace.

SingleStore: The world’s fastest database

SingleStore's database branching is like a Swiss Army knife for data management, boosting developer productivity, mitigating risks and enabling seamless testing environments. Developers used to pay millions of dollars for high-end SANs to enable branching (at the storage volume level) with legacy SQL databases like SQL Server, DB2 and Oracle. Now, it's included as a standard feature in SingleStore. With the ability to modify data without impacting production and enjoy full durability benefits without duplicating storage costs, SingleStore database branching is a game-changer for enterprises of all sizes.

But we didn't stop there. Using our Database Branching as a foundation, we also built SmartDR, an innovative disaster recovery solution. SmartDR allows you to replicate data across regions without constantly burning through compute resources in your secondary region. Say goodbye to the old "pay double, hope you never use it" DR dilemma. Whether you're a developer looking to supercharge your workflow or a database admin aiming to bulletproof your operations, SingleStore's database branching and SmartDR offer a more flexible, secure and cost-effective future for your data.

For more information about database branching, please visit our documentation page or reach out to team@singlestore.com for a personalized demo.

Try SingleStore free today.