Zero-Downtime Database Migrations: Patterns for Safe Schema Evolution | Harness Blog

Database systems store some of the most sensitive data of an organization such as PII, financial records, and intellectual property, making strong database governance non-negotiable. As regulations tighten and audit expectations increase, teams need governance that scales without slowing delivery.

Harness Database DevOps addresses this by applying policy-driven governance using Open Policy Agent (OPA). With OPA policies embedded directly into database pipelines, teams can automatically enforce rules, capture audit trails, and stay aligned with compliance requirements. This blog outlines how to use OPA in Harness to turn database compliance from a manual checkpoint into a built-in, scalable part of your DevOps workflow.

The Challenges of Database Compliance

Organizations face multiple challenges when navigating database compliance:

• Complex Regulatory Requirements: Standards such as GDPR, HIPAA, PCI-DSS, and SOX impose strict controls on data access, consent, storage, and processing. Compliance requires both preventative controls (e.g., access restrictions) and demonstrable evidence of effective enforcement. 
• Lack of Visibility: Traditional database operations often lack centralized oversight, making it difficult to answer questions like “Who accessed data?”, “Which change was deployed?” or “Were controls enforced consistently?” without expensive, manual processes.
• Manual Processes and Human Error: Manual access approvals, change reviews, or ad-hoc scripting introduce risks, from privilege creep to inconsistent documentation that can lead to compliance gaps.

These challenges highlight the necessity of embedding governance directly into database development and deployment pipelines, rather than treating compliance as a reactive checklist.

Governance at Scale with Harness Database DevOps

Harness Database DevOps is designed to offer a comprehensive solution to database governance - one that aligns automation with compliance needs. It enables teams to adopt policy-driven controls on database change workflows by integrating the Open Policy Agent (OPA) engine into the core of database DevOps practices. 

What is OPA and Policy as Code?

Open Policy Agent (OPA) is an open-source, general-purpose policy engine that decouples policy decisions from enforcement logic, enabling centralized governance across infrastructures and workflows. Policies in OPA are written in the Rego declarative language, allowing precise expression of rules governing actions, access, and configurations.

Harness implements Policy as Code through OPA, enabling teams to store, test, and enforce governance rules directly within the database DevOps lifecycle. This model ensures that compliance controls are consistent, auditable, and automatically evaluated before changes reach production.

Building a Governance Framework Using OPA Policies

Here’s a structured approach to implementing database governance with OPA in Harness:

1. Define Compliance and Governance Objectives

Start by cataloging your regulatory obligations and internal governance policies. Examples include:

• Restricting access to sensitive tables based on roles or departments.
• Prohibiting destructive schema changes (e.g., DROP TABLE) in production.
• Enforcing least privilege by limiting modify rights only to authorized service accounts.
• Requiring reviews and approvals for schema migrations above a threshold.

Translate these requirements into quantifiable rules that can be expressed in Rego.

2. Author OPA Policies in Harness

Within the Harness Policy Editor, define OPA policies that codify governance rules. For example, a policy might block any migrations containing operations that remove columns in production environments without explicit DBA approval.

Harness policies are modular and reusable, you can import and extend them as part of broader governance packages. This allows cross-team reuse and centralized management of rules. Key aspects include:

• Policy Modules: Group related rules into packages for clarity.
• Policy Severity: Optionally set enforcement thresholds (e.g., error vs. warning).
• Testing and Simulation: Harness provides testing tools to validate policies against real or sample inputs before activation.

By expressing governance as code, you ensure consistency and remove ambiguity in policy enforcement.

3. Integrate Policies with CI/CD Pipelines

Policies can be linked to specific triggers within your database deployment workflow, for instance, evaluating rules before a migration is applied or before a pipeline advances to production. This integration ensures that non-compliant changes are automatically blocked, while compliant changes proceed seamlessly, maintaining the balance between speed and control.

Operationalizing Database Compliance

Automated Enforcement

Harness evaluates OPA policies at defined decision points in your pipeline, such as pre-deployment checks. This prevents risky actions, enforces access controls, and aligns every deployment with governance objectives without manual intervention. 

Audit Trails and Traceability

Every policy evaluation is logged, creating an auditable trail of who changed what, when, and why. These logs serve as critical evidence during compliance audits or internal reviews, reducing the overhead and risk associated with traditional documentation practices.

Role-Based Controls and Least Privilege

By enforcing the principle of least privilege, policies ensure that users and applications possess only the necessary permissions for their specific roles. This restriction on access is crucial for minimizing the potential attack surface and maintaining compliance with regulatory requirements for data access governance.

Best Practices for Policy-Driven Governance

• Start with High-Impact Policies: Prioritize controls around sensitive data and production environments.
• Leverage Policy Libraries: Use reusable policy templates as a starting point and customize them for your organizational context.
• Iterate with Continuous Feedback: Use audit results and pipeline failures as feedback loops to refine policies.
• Align with Compliance Frameworks: Map OPA policies to specific regulatory requirements (e.g., GDPR’s principle of accountability) to demonstrate traceability during audits.
• Educate Teams: Ensure developers and DBAs understand the governance policies and the reasons behind them to reduce friction.

Conclusion

Database governance is an essential pillar of enterprise compliance strategies. By embedding OPA-based policy enforcement within Harness Database DevOps, organizations can automate compliance controls, minimize risk, and maintain developer productivity. Policy as Code provides a scalable, auditable, and consistent framework that aligns with both regulatory obligations and the need for agile delivery.

Transforming database governance from a manual compliance burden into an automated, integrated practice empowers teams to innovate securely, confidently, and at scale - ensuring that every change respects the policies that protect your data, your customers, and your brand.

‍

There was a time when database design was an event. It happened once, early in a project, often before the first line of application code was written. Architects would gather with domain experts, sketch entities and relationships, debate normalization levels, and arrive after weeks of discussion, at what was believed to be the schema. Once approved, that schema was treated as immutable.

This mindset assumed that the future was predictable. But it rarely is. Modern database design is no longer about defining a perfect schema upfront, but about enabling safe, continuous evolution as systems and requirements change.

Database Design Is Not a One-Time Event

At the beginning, requirements are usually clear and limited. The schema reflects the system’s first understanding of the domain.

CREATE TABLE users (    
	id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

This design is clean, minimal, and correct, for now. It models what the system knows today: users exist, they have an email, and they were created at a specific time.

At this stage, the schema feels complete, although it never is.

When Reality Adds Context

As the product matures, new questions emerge. The business wants to personalize communication. Support wants to address users by name. Marketing wants segmentation. The schema evolves, not because it was poorly designed, but because the system learned something new.

‍ALTER TABLE users

ADD COLUMN first_name VARCHAR(100),

ADD COLUMN last_name VARCHAR(100);

‍

This change is small, additive, and safe. No existing behavior breaks. No data is lost. The schema now captures richer context without invalidating earlier assumptions.

This is evolutionary design in its simplest form: adapting without disruption.

Managing Database Schema Changes Without Breaking Production

As usage grows, teams discover new workflows. Users can now deactivate their accounts. Regulatory requirements demand traceability.

Instead of redefining the table, the schema evolves to support new behavior.

‍

ALTER TABLE users

ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',

ADD COLUMN deactivated_at TIMESTAMP;

‍

Importantly, this change preserves backward compatibility. Existing queries continue to work. New logic can gradually adopt the new fields. This approach reflects database schema evolution best practices, where changes are incremental, backward-compatible, and safely deployable through CI/CD pipelines. Evolutionary design favors extension over replacement.

Performance Pressures and Structural Refinement

With scale comes performance pressure. Queries that once ran instantly now struggle. Reporting workloads introduce new access patterns.

Rather than redesigning everything, the schema evolves structurally to meet new demands.

CREATE INDEX idx_users_status ON users (status);

This change does not alter the data model conceptually, but it reflects a deeper understanding of how the system is used. Design evolves not just for correctness, but for operational reality.

Database design is no longer theoretical, it is informed by production behavior.

When the Original Model No Longer Fits

Eventually, teams outgrow early assumptions. A single user’s table can no longer represent multiple user roles, tenants, or identity providers. The model needs refinement. Evolutionary design handles this carefully, through parallel structures and gradual migration.

CREATE TABLE user_profiles (
	user_id INT PRIMARY KEY REFERENCES users(id),
    display_name VARCHAR(150),
    preferences JSONB,
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()   
);

‍

Instead of overloading the original table, the design evolves by extracting responsibility. Existing functionality remains stable while new capabilities move forward. At no point was a “big rewrite” required.

The Operational Risks of Unmanaged Database Schema Changes

As changes accumulate, complexity shifts from design to operations. Teams struggle to answer basic questions:

• Which version of the schema is running in production?
• What changes are pending in staging?
• Can this migration be safely rolled back?

This is where evolutionary design demands discipline. Small changes only remain safe when they are visible, validated, and governed.

Why Database DevOps Matters for Schema Evolution?

Modern database design extends beyond tables and columns. It includes how changes are reviewed, tested, approved, and promoted. As applications adopt CI/CD and ship continuously, databases often remain the slowest and riskiest part of the release. Manual migrations, limited visibility, and fear of rollbacks turn schema changes into operational bottlenecks.

Database DevOps addresses this gap by applying software delivery discipline to database changes:

• Schema changes are versioned and traceable
• Migrations are validated before production
• Rollbacks are tested, not improvised
• Audit trails are automatic, preventing high-risk changes from reaching production

By embedding database schema evolution into CI/CD pipelines, teams reduce deployment risk while increasing delivery velocity. Platforms like Harness Database DevOps enable this by combining state awareness, controlled execution, and auditability, making database changes predictable, repeatable, and safe.

The Database as a Record of Learning

Each SQL change tells a story:

• What the system learned
• What assumptions changed
• What scale revealed
• What compliance required

A database is not a monument to early decisions. It is a living artifact that reflects the system’s understanding of its domain at every point in time.

Conclusion: Evolution is the Design

Database design evolution is not a failure of planning, it is evidence of adaptation.

The most resilient systems are not those with perfect initial schemas, but those designed to evolve safely and continuously. By embracing incremental change, versioned history, and automated governance, teams align database design with the realities of modern software delivery.

In a world where applications never stop shipping, database design cannot remain static. They must evolve, with confidence, control, and clarity, supported by Database DevOps practices and platforms such as Harness Database DevOps.

Because in the end, the schema is not the design. The ability to evolve it safely is.

It is crucial to maintain uptime and data consistency while implementing database schema changes. This blog post will provide a real-world example of how to safely alter a database schema to support new requirements with zero downtime, data integrity, and the ability to roll back if needed. nIt expands on a larger scenario, but deep dives into the specifics of how that database migration works.

Our scenario involves an application that tracks inventory across multiple warehouses. Each warehouse is identified by a location field that simply has the name of the city. We’ve recently added a new warehouse in Boston, GA, but our database already contains Boston – the one in Massachusetts. To accommodate this, we need to update our Warehouses table to distinguish between cities and states.

These days, uptime is critical. We cannot afford even one second of downtime, which makes this more complicated. To maximize uptime, we will ensure our database schema can work with the old and new versions of the application simultaneously while ensuring data integrity. Since certain information is tracked in different places before and after migration, maintaining data integrity requires the use of database triggers to ensure that if one location is updated, both are updated. Each change has also been developed with rollback logic so that if something goes wrong, we can fully revert to the previous stable state– with no data loss.

Breaking Down the Changes

The full changes for this scenario can be found in this GitHub repo. These changes are packaged in change-sets, and each change-set can be independently deployed or rolled back. In this blog, we will talk through each of these change-sets and discuss how it ensures safety during the database migration. These changes can be applied using Harness Database Devops or liquibase.

Changeset: Refactor-1

This change-set introduces the new columns to track city and state. It also updates the location column so that if new rows are added that do not specify the location, a unique ID is generated for them. If we did not care about 0 downtime, the ability to rollback while maintaining database consistency, or copying over the existing data, then this would be the entire change.

Full Changeset:

- changeSet:
    id: refactor-1
    author: stephenatwell
    ignore: false
    changes:
    - addColumn:
            tableName: Warehouses
            columns:
              - column:
                  name: City
                  type: nvarchar(50)
              - column:
                  name: State
                  type: nvarchar(50)
    - addDefaultValue:
            columnName: Location
            tableName: Warehouses
            defaultValueComputed: newId()

Changeset: Refactor-1-data-migrate

The use of triggers and transactions in this change-set is what ensures that data remains consistent.

This change-set creates a trigger to automatically populate the new City field with the existing Location data. This ensures that if the old version of the application updates location midway through the migration, then the City field is updated with the new value. This ensures data consistency even if both the old and new versions of the application are running in parallel.

This change-set also copies data from the old ‘location’ column into the new ‘City’ column. This copy is performed in small, incremental batches to minimize locking and performance impact on the production database. During each batch, a set of rows in the table is locked, and the application will not be able to modify those rows until that batch completes. By using small batches, we ensure that only a small percentage of the table is locked at once so that locks are short-lived and do not noticeably impact the performance of our application.

This changelog also disables transactions. If we did not do this, we would lose the benefit of performing our small copies in batches, as each of their transactions would be a child of the main transaction. This transaction nesting prevents locks from being released until the outermost transaction is completed.

Full Changeset:

- changeSet:
    id: refactor-1-data-migrate
    author: stephenatwell
    ignore: false
    # disable transactions so that we can do small/incremental locks during the data copy.
    runInTransaction:  false
    changes:
    - sql:
            sql: |
              CREATE TRIGGER trg_Warehouses_Location_Update
              ON Warehouses
              AFTER INSERT, UPDATE
              AS 
              BEGIN
                  SET NOCOUNT ON;
                  
                  IF EXISTS (SELECT * FROM Inserted WHERE City IS NULL)
                  BEGIN
                      UPDATE Warehouses
                      SET City = i.Location
                      FROM Inserted i
                      WHERE Warehouses.ID = i.ID;
                  END
              END;  
    - sql:
            sql: |
              -- Begin transaction to minimize lock time
              BEGIN TRANSACTION;
              
              
              WHILE 1 = 1
              BEGIN
              
                  -- Update the City column in small batches to minimize lock time
                  UPDATE TOP (2) Warehouses
                  SET 
                      City = Location
                  WHERE 
                      City IS NULL;
                  
                  IF @@ROWCOUNT = 0
                      BREAK;
              END
              
              -- Commit transaction
              COMMIT TRANSACTION; 
    rollback:
    - sql:
            sql: IF OBJECT_ID('dbo.trg_Warehouses_Location_Update', 'TR') IS NOT NULL DROP TRIGGER dbo.trg_Warehouses_Location_Update;

Changeset: boston-georgia:

This change-set updates the reference data in the Warehouses table by adding a new row for the new warehouse in Boston, GA. 

Full Changeset:

- changeSet:
    id: boston-georgia
    author: stephenatwell
    ignore: false
    preConditions:
       - onFail: MARK_RAN
       - sqlCheck:
          expectedResult: 0
          sql: SELECT COUNT(*) FROM Warehouses WHERE City='Boston' AND State='GA'
    changes:
    - insert:
            tableName: Warehouses
            columns:
              - column:
                  name: City
                  value: Boston
              - column:
                  name: State
                  value: GA
    rollback:
    - delete:
            tableName: Warehouses
            where: City='Boston' AND State='GA'

Conclusion

By carefully planning and implementing changes with considerations for consistency, uptime, and rollback, you can ensure smooth transitions and maintain integrity in your data systems. Database changes like these can be safely deployed as part of your CI/CD process using tools like Harness so that database changes no longer slow down your application delivery.

If you encounter similar scenarios and are interested in a seamless DB migration with minimal disruptions, reach out to Harness to learn more about how we can help you realize the benefits of Database DevOps

Learn More

This blog discusses part of a larger scenario that you can learn more about in our overview blog, Database DevOps: managing databases inside your CI/CD pipeline. This scenario leverages Harness Database DevOps and Harness Continuous Delivery to orchestrate this database change along side a change to application code as part of a larger CI/CD pipeline. if you are attending KubeCon 2024, a talk on this topic will also occur on thursday.