TechPrufer
Get started
PlanetScale
PlanetScale logoPlanetScale · Feature trail

Database Branching and Blue/Green Deployments

24 posts · May 2021 – Sep 2024

What this trail covers

PlanetScale's branching technology provides a Git-like workflow for database schema changes, enabling safe, non-blocking, and rolling upgrades. This post introduces native MySQL `INPLACE` and `INSTANT` DDL, detailing their capabilities and limitations. `INPLACE` is found to be unsuitable for production due to replication lag and resource intensiveness. `INSTANT` DDL is ideal for metadata changes and simple column additions/drops but is limited in scope and lacks revertibility for destructive operations. For more complex changes, external tools like Vitess, gh-ost, and pt-online-schema-change are still the go-to solutions, offering similar design principles but operating differently and taking longer. The post emphasizes that while `INSTANT` DDL is preferred where supported, the majority of common schema changes still require these external tools, leading to a complexity of maintaining multiple techniques. Vitess and Spirit are highlighted for their ability to auto-detect when a migration can be fully handled by `INSTANT` DDL.

All PlanetScale trails
  1. Instant deploy requestsIntroduces 'instant deployments' for PlanetScale deploy requests, leveraging MySQL's INSTANT DDL algorithm for specific schema changes (e.g., adding columns, changing default values) and table operations (create, drop, alter views). This opt-in feature offers near-instant schema deployment for eligible changes, contrasting with the default Online DDL strategy. Caveats include non-revertibility and potential brief table locks.
  2. The State of Online Schema Migrations in MySQLThis post provides a technical deep dive into the state of online schema migrations in MySQL as of 2024. It analyzes the native MySQL `INPLACE` and `INSTANT` DDL algorithms, detailing their operational characteristics, limitations, and risks. For `INPLACE`, it highlights issues with replication lag, resource consumption, and uninterruptibility, deeming it unsuitable for production. For `INSTANT`, it explains its speed and efficiency for metadata changes and simple column operations but notes its limited scope and lack of revertibility for destructive actions like `DROP COLUMN`. The post also contrasts these native options with external tools like Vitess, gh-ost, and pt-online-schema-change, outlining their shared design principles and operational differences, and concludes that external tools remain essential for the majority of complex schema changes.
  3. PlanetScale branching vs. Amazon Aurora blue/green deploymentsThis post compares PlanetScale's branching strategy with Amazon Aurora's blue/green deployments for database maintenance. It details how Aurora's blue/green deployments work by creating a clone and using binlog replication for synchronization, highlighting limitations with schema changes and the downtime associated with switchovers. In contrast, PlanetScale's branching creates isolated Vitess clusters for development, uses a 'ghost' table approach for non-blocking schema changes via deploy requests, and performs rolling upgrades on Vitess tablets for instance type adjustments and version updates, minimizing downtime and cost.
  4. PlanetScale vs. Amazon AuroraThis post compares PlanetScale's database branching and change management capabilities with Amazon Aurora's blue/green deployments. It highlights PlanetScale's use of Vitess and Kubernetes for non-blocking, rolling upgrades and its Git-like workflow for schema changes, contrasting this with Aurora's lack of native tooling for schema changes and its reliance on Aurora Backtrack for recovery from DDL operations.
  5. How PlanetScale unlocks developer productivityThis post elaborates on how PlanetScale's database branching and deploy request features directly contribute to unlocking developer productivity. It details how these capabilities integrate database schema changes into the DevOps cycle, akin to code changes, by providing isolated development branches, reviewable deploy requests (similar to pull requests), and easy schema change reversion. The post highlights the reduction in bottlenecks caused by traditional DBA workflows or developer caution around database modifications, framing these features as key enablers for faster iteration and innovation.
  7. Performant database tree traversal with RailsThis post addresses a performance bottleneck in the Rails API related to tree traversal for calculating database schema merge bases. The N+1 query problem was solved by introducing an in-memory cache (`SnapshotMergeBaseCache`) to store schema snapshots and preloading recent snapshots for each branch to reduce database round trips. The post also briefly discusses alternative solutions like database recursive CTEs and the materialized path technique.
  8. Using PlanetScale with Serverless Framework Node applications on AWSThis post demonstrates how to integrate PlanetScale databases with Node.js applications built using the Serverless Framework on AWS. It details setting up a basic serverless application, connecting to PlanetScale using the serverless driver, managing database credentials securely via environment variables and AWS SSM, and leveraging Serverless Framework stages to map to different PlanetScale branches for development and production environments. It also shows how to deploy the application and interact with the database.
  9. Increase developer productivity with Database DevOpsThis post highlights how PlanetScale's database branching and DevOps capabilities enhance developer productivity by streamlining schema changes, recovery, and monitoring. It emphasizes the Git-like workflow for database schema changes, enabling safe, non-blocking, and rolling upgrades, and contrasts this with traditional blue/green deployments. The integration with CI/CD tooling for risk mitigation and quick reverts is also a key contribution.
  10. Backward compatible database changesThis post details the 'expand, migrate, contract' pattern for safely making backward-compatible database schema changes, which is crucial for avoiding downtime and disruptions. It breaks down the pattern into six steps: expanding the schema, expanding application code to write to both old and new schemas, migrating data, migrating application code to read from the new schema, contracting application writes to only the new schema, and finally contracting the schema by deleting the old elements. The post also provides an example walkthrough of this pattern for combining a `star` table into a `repo` table by adding a `star_count` column.
  11. Database branching: three-way merge for schema changesThis post introduces and explains the technical implementation of a three-way merge strategy for database schema changes within PlanetScale's branching system. It details how semantic diffs are generated using Vitess's `schemadiff` library and compared against a base schema (`main`) to detect conflicts between concurrent schema branches. The post provides examples of no-conflict, clear-conflict, subtle-conflict (column ordering), and nuanced-conflict (index ordering) scenarios, explaining how PlanetScale handles these to ensure safe and collaborative schema evolution.
  12. Safely making database schema changesThis post elaborates on safely making database schema changes by detailing best practices like smaller, frequent, and backward-compatible changes, and the use of feature flags. It further explains PlanetScale's branching and deploy request features, including automated linting, schema reviews, non-blocking schema changes, gated deployments, and the revert feature powered by VReplication. It also highlights the role of Insights in monitoring query performance post-deployment.
  13. An update to our workflow: safe migrationsIntroduces 'safe migrations' as an optional feature that allows direct DDL execution on production branches, which was previously prohibited. This provides flexibility for users who prefer existing framework migration tools or are in early development stages, while still offering the option for non-blocking schema changes. The post details how this affects existing databases and outlines scenarios where disabling safe migrations might be beneficial.
  14. Zero downtime Rails migrations with the PlanetScale Rails gemThis post introduces the `planetscale_rails` gem, which provides Rake tasks for managing Rails schema migrations with PlanetScale databases. It details a workflow for zero-downtime migrations by separating schema and code deployments, leveraging PlanetScale deploy requests and branches. It outlines safe patterns for common migration scenarios like adding, removing, and renaming columns, and adding/removing indexes, and also covers handling data migrations via production console scripts or dedicated Ruby classes.
  15. DevOps with PlanetScaleThis post details how PlanetScale's database branching and Data Branching® features integrate with the DevOps lifecycle, specifically in the Plan, Code, and Test phases. It highlights the use of the PlanetScale CLI and API for automating branch creation during sprint planning, the security benefits of `pscale connect` for tunneled connections, and the importance of using dedicated test branches seeded with production data for integration testing. It also mentions the role of Deploy Requests in the Release phase.
  16. Improvements to database branch pagesThis post details UI/UX improvements to the database branch pages. For production branches, it introduces a single-page view consolidating all branch information, with an expandable/collapsible schema view that includes table search and size information. For development branches, it adds a diff view showing changes relative to the parent branch, highlighting changed tables above others to aid in deploy request creation.
  17. Run SQL script files on a PlanetScale databaseThis post demonstrates how to use the PlanetScale CLI to run SQL script files against a database, enabling the creation and population of databases with batch commands. It details the `pscale database create` and `pscale shell` commands, showing how to pipe script files into the shell for execution and how to interact with the database afterwards.
  18. Database DevOpsThis post introduces the concept of applying DevOps practices to databases, highlighting the limitations of traditional database management. It details how PlanetScale's development branches and deploy requests enable a Git-like workflow for schema changes, allowing for automation, safeguards, and rollbacks. The post also emphasizes the importance of query monitoring through PlanetScale Insights for continuous performance improvement.
  19. Deploy requests now alert on potential unwanted changesThis post introduces a new UI warning in PlanetScale's deploy request UI that alerts developers to potential data loss from schema changes, such as dropping columns or tables. It also details a safe, multi-step process for renaming columns to avoid downtime, involving creating new columns, double writes, backfilling data, and then dropping the old column.
  20. The operational relational schema paradigmThis post introduces a new paradigm for operational relational schema management, emphasizing non-blocking, lightweight, asynchronous, scheduled, interruptible, trackable, failure-agnostic, revertible, and redeployable schema migrations. It argues that traditional relational databases have not kept pace with modern development practices for continuous deployment and proposes a set of core tenets for modern schema migrations. The resulting flow aims to give developers ownership and confidence in their schema changes.
  21. Revert a migration without losing dataIntroduces a new schema revert feature that allows users to revert a migration with zero downtime and zero data loss within 30 minutes of deployment. This is enabled by Vitess's VReplication, which performs a lossless sync at the MySQL transaction level to restore the database to its previous state, including any data added during the erroneous migration.
  22. Behind the scenes: How schema reverts workThis post details the technical implementation of PlanetScale's schema revert feature, which leverages Vitess's VReplication to enable instant, zero-data-loss reverts of schema changes. It explains how VReplication's transactional tracking of backfill and ongoing changes, coupled with GTID precision, allows for a seamless swap back to a previous schema state. The post highlights that the shadow table created during the initial schema change is retained and continuously synced, facilitating a quick revert process without re-copying data.
  23. How to seed a database with Prisma and Next.jsThis post details how to seed a PlanetScale database using Prisma in a Next.js project. It covers the concepts of database seeding for initial setup, automated testing, and PlanetScale's branching feature. The tutorial walks through setting up a Next.js starter repository, configuring the database connection string, and creating a seed script using Prisma's `createMany` and `deleteMany` functions, as well as raw SQL for resetting auto-increment values. It also explains how to configure the seed command in `package.json` either via a custom script or the `prisma.seed` field.
  24. Using the PlanetScale CLI with GitHub Actions workflowsThis post details how to integrate the PlanetScale CLI with GitHub Actions workflows to automate database schema changes. It provides pre-built workflows for common tasks like creating databases, adding columns/indexes, and merging deploy requests. It also guides users on building custom workflows using shell scripts and the `pscale` CLI, emphasizing the use of service tokens for authentication and helper scripts for managing database branch readiness and authentication.
  25. Automatically copy migration data in PlanetScale branchesThis post introduces the capability to automatically copy migration data from framework-specific migration tables (e.g., Rails, Phoenix, Django, .NET, Prisma, Sequelize) to PlanetScale database branches. This ensures that the migration history is preserved when creating new branches or deploy requests, preventing tools from incorrectly re-applying past migrations. It provides examples for Prisma and Ruby on Rails, and links to relevant documentation.
  26. Building PlanetScale with PlanetScaleThis post details how PlanetScale engineers use the platform's branching and deploy request features for their own internal database schema changes. It explains the workflow from creating a migration file to using the `pscale branch switch` command to create and connect to a database branch. It also covers how to configure Rails to connect to the branch using an environment variable and how to apply migrations to the branch. The post highlights the creation of deploy requests, the review process via GitHub pull requests and Slack, and the deployment of schema changes to the production branch, emphasizing the zero-downtime and non-blocking nature of these operations.
  27. Non-Blocking Schema ChangesThis post details PlanetScale's non-blocking schema change workflow, which leverages Vitess and gh-ost. It introduces database branching as a method for creating isolated sandbox environments for testing schema changes. Deploy requests are used to manage schema changes, and a deploy queue ensures sequential deployment. The system analyzes schema changes for conflicts against the main branch and preceding deploy requests to prevent issues. The workflow aims to allow developers to push schema changes as easily as code changes.