VOOZH about

URL: https://dzone.com/articles/non-blocking-database-migrations

⇱ Non-blocking Database Migrations

Related

  1. DZone
  2. Data Engineering
  3. Databases
  4. Non-blocking Database Migrations

Non-blocking Database Migrations

Learn more about data base migrations and dealing with large data.

By Sep. 20, 22 · Tutorial
Likes
Comment
Save
5.7K Views

Join the DZone community and get the full member experience.

Join For Free

Database migrations are a common part of any web application. They are used to update the database schema to match the application's code. In a traditional web application, the database migrations are run synchronously, which means that the application is blocked until the migration is complete. This is not ideal, as it means that the application is unavailable to users during the migration. Long past the days when stopping the service for maintenance was acceptable; we need to be able to run migrations without blocking the application.

It's easy to perform database migrations in small databases or if you have no load. But what if you have a large database and a lot of users?

Initial Database Structure

Let's imagine we have a simple table for storing customer data:

MySQL 
DROP TABLE IF EXISTS customer;
CREATE TABLE
 customer (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 email VARCHAR(256),
 balance FLOAT NOT NULL DEFAULT 0,
 UNIQUE INDEX email_idx (email)
);

INSERT
INTO
 customer (email, balance)
VALUES
 ('[email protected]', 42),
    ('[email protected]', -42);

My main focus will be on the "balance" field.

It's a float, which means that it can have a fractional part. We want to change the type of this field to an integer so that we can store the balance in cents. This is a common practice in financial applications.

But perhaps you already have an active application with millions of users. As a developer, your responsibility is to make sure the application is available to users during the migration. Also, you need to make sure the application will work correctly after the migration, and in case it's not working properly, you have to roll back your changes without many customers noticing it.

So the plan is:

  1. Create a new field "balance_cents" with the integer type.
  2. Deploy the application with the new field. A new application version should be available for a limited amount of users.
  3. Make sure the application is working correctly with the new field.
  4. Deploy the new application version for all users.
  5. Drop the old field "balance".

The tricky part is to make data consistent between the old and new fields. As these fields are related to each other, we need to make sure changes in the old field are reflected in the new field, and vice versa.

Let's go step by step and see how we can implement this plan.

Create a New Field

Creating a new field "balance_cents" with the integer type is a simple task:

MySQL 
ALTER TABLE
 customer
 ADD COLUMN
        balance_cents INT NOT NULL DEFAULT 0;

Set Up Synchronization Between the Old and New Fields

At this point, the application does not know anything about the new field, and it is a perfect time to set up synchronization between the old and new fields. And to achieve this, we need to create triggers

First, let's create a trigger on the insert.

MySQL 
DROP TRIGGER IF EXISTS insert_balances;
CREATE TRIGGER insert_balances
 BEFORE INSERT
 ON customer
 FOR EACH ROW IF new.balance <> 0 THEN -- insert from the old code
 SET
 new.balance_cents = CEIL(new.balance * 100);

ELSEIF new.balance_cents <> 0 THEN -- insert from the new code
 SET
 new.balance = new.balance_cents / 100;

END IF;
$$

DELIMITER ;

Let's do some experiments to see how this trigger works.

Insert from the old code:

MySQL 
INTO
 customer (email, balance)
VALUES
 ('[email protected]', -1.23);

SELECT
 email,
 balance,
 customer.balance_cents
FROM
 customer
WHERE
    email = '[email protected]';
Plain Text 
+-----------------------------------+---------+---------------+
| email | balance | balance_cents |
+-----------------------------------+---------+---------------+
| [email protected] | -1.23 | -123 |
+-----------------------------------+---------+---------------+

Insert from the new code:

MySQL 
INSERT
INTO
 customer (email, customer.balance_cents)
VALUES
 ('[email protected]', 345);

SELECT
 email,
 balance,
 customer.balance_cents
FROM
 customer
WHERE
    email = '[email protected]';
Plain Text 
+-----------------------------------+---------+---------------+
| email | balance | balance_cents |
+-----------------------------------+---------+---------------+
| [email protected] | 3.45 | 345 |
+-----------------------------------+---------+---------------+

So the trigger works as expected. 

Now let's create a trigger for an update.

MySQL 
DROP TRIGGER IF EXISTS update_balances;
DELIMITER $$
CREATE TRIGGER update_balances
 BEFORE UPDATE
 ON customer
 FOR EACH ROW IF new.balance <> old.balance THEN -- update from the old code
 SET
 new.balance_cents = CEIL(new.balance * 100);

ELSEIF new.balance_cents <> old.balance_cents THEN -- update from new code
 SET
 new.balance = new.balance_cents / 100;

END IF;
$$

DELIMITER ;

And now let's test it.

Making updates from the old code:

MySQL 
UPDATE customer
SET
 balance = -1.45
WHERE
 email = '[email protected]';

SELECT
 email,
 balance,
 balance_cents
FROM
 customer
WHERE
    email = '[email protected]';
Plain Text 
+-----------------------------------+---------+---------------+
| email | balance | balance_cents |
+-----------------------------------+---------+---------------+
| [email protected] | -1.45 | -145 |
+-----------------------------------+---------+---------------+

Making updates from the new code:

MySQL 
UPDATE customer
SET
 balance_cents = 567
WHERE
 email = '[email protected]';

SELECT
 email,
 balance,
 balance_cents
FROM
 customer
WHERE
    email = '[email protected]';
Plain Text 
+-----------------------------------+---------+---------------+
| email | balance | balance_cents |
+-----------------------------------+---------+---------------+
| [email protected] | 5.67 | 567 |
+-----------------------------------+---------+---------------+

Our triggers work as expected. Now we need to fill the empty "balance_cents" field with data from the "balance" field.

Filling the Empty “balance_cents” Field

The simplest way to fill the empty "balance_cents" field is to use the UPDATE statement:

MySQL 
UPDATE customer
SET
    balance_cents = CEIL(balance * 100);

But that update query will put a lot of pressure on the database. And as our main goal is to avoid downtime, the update process should be performed in small batches.

It is possible to create a migration script inside the application, but as we are playing with SQL, let's create a stored procedure.

MySQL 
DROP PROCEDURE IF EXISTS batch_update_balance_cents;

DELIMITER $$
CREATE PROCEDURE batch_update_balance_cents(
 start_id INT,
 end_id INT,
 batch_size INT)
BEGIN
 DECLARE batch_start INT DEFAULT start_id;
 DECLARE batch_end INT DEFAULT start_id + batch_size;

 IF end_id < start_id + batch_size THEN
 SET end_id = start_id + batch_size;
 END IF;

 WHILE batch_end <= end_id
 DO
 UPDATE customer
 SET
 balance_cents = CEIL(balance * 100)
 WHERE
 id BETWEEN batch_start AND batch_end;

 SET batch_start = batch_start + batch_size;
 SET batch_end = batch_end + batch_size;
 END WHILE;
END$$
DELIMITER ;

CALL batch_update_balance_cents(1, (SELECT
 MAX(id)
 FROM
                                        customer), 1000);

And now let's check the result:

MySQL 
SELECT *
FROM
    customer;
Plain Text 
+----+-----------------------------------+---------+---------------+
| id | email | balance | balance_cents |
+----+-----------------------------------+---------+---------------+
| 1 | [email protected] | 42 | 4200 |
| 2 | [email protected] | -42 | -4200 |
| 3 | [email protected] | -1.23 | -123 |
| 4 | [email protected] | 5.68 | 568 |
+----+-----------------------------------+---------+---------------+

All old entries were updated.

Drop Triggers and Stored Procedure

Migration is done. Everyone is happy about our latest changes. New application code is deployed to all customers.

It's time to drop triggers and stored procedures.

MySQL 
DROP PROCEDURE IF EXISTS batch_update_balance_cents;
DROP TRIGGER IF EXISTS update_balances;
DROP TRIGGER IF EXISTS insert_balances;

Drop the Old Field

And now, no one uses the old field. It's time to drop it.

MySQL 
ALTER TABLE customer
    DROP COLUMN balance;

Conclusion

In this article, we have shown how to migrate from one field to another without downtime.

We have used triggers and stored procedures to keep the data in sync.

I used a particular example with the balance field, but it's possible to use the same approach for any other field or set of fields.

Database IT MySQL Plain text Web application application Blocking (computing) Data (computing) sql Data Types

Opinions expressed by DZone contributors are their own.

Related

  • SQL Commands: A Brief Guide
  • Introduction to Data Replication With MariaDB Using Docker Containers
  • Building a Database Written in Node.js From the Ground Up
  • Beyond Partitioning and Z-Order: A Deep Dive into Liquid Clustering for Unity Catalog Managed Tables

Partner Resources

×
Comments

The likes didn't load as expected. Please refresh the page and try again.

Let's be friends: