Database Branching

Benefits

• It is a cost-effective solution that saves on infrastructure and time in maintaining duplicate environments.

• You can create multiple isolated datasets of your production database, each containing data from a specific time.

Use Cases

• Development and Testing

  As a developer, you can instantly spin up isolated branches with the latest replica of the production database or at a specific point in time. This enables efficient testing and iterative development without impacting the production environment.

• Performance Tuning

  You can evaluate multiple versions of your application on a branch, isolating and resolving any issues while the production database remains active. Any performance bugs can be investigated and tuned in the branch before implementing the solutions in the production environment.

Creating a Branch Using SQL

The following syntax creates a branch on the same workspace as the parent database:

ATTACH DATABASE <parentDB_name> [AS <new_branchDB_name>]

For all options available with this command refer ATTACH DATABASE

Example Scenarios

The following examples use a database named sales, and a table named orders. Create a database named sales:

CREATE DATABASE sales;

Create a table named orders:

CREATE TABLE orders
(order_id bigint(11) NOT NULL,
customer_id int(11) NOT NULL,
order_date date NOT NULL,
order_status char(1) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
total_price decimal(15,2) NOT NULL,
SHARD KEY (order_id),
SORT KEY (order_date));

Scenario 1: As a developer, you want to test a new business logic before implementing the query on a production dataset.

In the Portal UI, connect to the SQL Editor or the database endpoint and run the command:

ATTACH DATABASE sales AS branch_sales;

You can navigate to the orders table within the branch_sales database and test your new business logic without impacting the production dataset. You can insert, update or delete rows, test the performance of your queries etc.

Once done testing, you can simply drop the branch database:

DROP DATABASE branch_sales;

Scenario 2: Branching can use PITR and this requires the database to be deployed using an Enterprise project. In the following case, a faulty query has deleted 1000 rows from the orders table. You can recover the data by simply creating a branch of your database at a point in time before the bad query was executed.

Suppose the bad query was executed on January 2nd. You may branch the sales database at a timestamp before the query was executed and recover the data.

ATTACH DATABASE sales AS recover_sales AT TIME '2024-01-02 21_57_31';

To recover the data, you may query the missing rows and copy them to the production orders table.

Alternatively, you may drop the sales database and then rename recover_sales as sales. Note: when you do this you will have to reconnect this newly renamed sales database to your application.

To drop your existing sales database:

DROP DATABASE IF EXISTS sales;

To rename recover_sales as sales:

DETACH DATABASE recover_sales;
ATTACH DATABASE recover_sales AS sales;

Scenario 3: To rename an existing database, create a new database branch from the original database. This branch serves as a point-in-time copy, containing all data from the original database exactly as it existed at the moment of branching.

ATTACH DATABASE x_db AS x_db_new_name;

After the branch is successfully created, you can detach or drop the original database.

Finally, rename the newly created branch to the original database name.

Using Branches

You can create a branch at the current time or at a previous point in time. Branches are read/write by default. The branch database automatically inherits user permissions from the parent database.

Users with either the CREATE DATABASE or ATTACH DATABASE permission can create branches. Users with CREATE DATABASE permission will automatically get the ATTACH DATABASE permission. However, users with only the ATTACH DATABASE permission can also create a branch.

Branches are independent, so any updates to the branch database are not propagated to the parent database, and updates to the parent database are not propagated to the branch databases. Similarly dropping the branch database does not impact the parent database and vice versa.

Creating a branch database duplicates the in-memory data and blob cache but not the data stored in the object store. Hence, storage used before the branch was created is not counted again and only new or updated data in the branch increases storage usage.