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.

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.

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!

Painlessly recover your data

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.

How to get started

Getting started 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 database systems 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 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.