Postgres schema migration

As systems evolve and requirements change, schema migrations enable databases to adapt cohesively without compromising existing data or system functionalities. Postgres – officially PostgreSQL, but we’ll stick with the nickname for this post – bills itself as “The World's Most Advanced Open Source Relational Database.” 

Yet maximizing Postgres’s advanced potential as data structures evolve at a quicker pace than ever before takes a comprehensive approach to change management. That’s where the concept of Postgres schema migration comes in – safely, quickly, and reliably keeping structural changes aligned and tracked. 

What is schema migration?

Database schema migration is the process of managing and applying changes to a database’s structural framework or schema. Sometimes referred to as simply “database migrations,” they allow the schema to evolve alongside the application.

This often involves tasks such as adding or removing tables, columns, or indexes, as well as modifying data types or constraints.

How does schema migration work?

As applications advance, database schemas must adapt swiftly, maintaining or improving performance, flexibility, and governance. Schema migration allows for changes that meet evolving business and technical needs while maintaining data integrity. Keeping the database and application in sync is important to remove bugs or errors caused by data differences, making sure each deployment is accurate and reliable.

There are two basic approaches to schema migration:

• State-based database deployments: This method focuses on the end goal by comparing the current database state with a predefined ideal state, using tools to generate scripts that bridge this gap. It's favored by DBAs for its ability to closely manage and monitor changes, ensuring a controlled update process.

• Artifact-based deployments: Emphasizing the process, this approach captures individual change scripts during development, appealing to developers for its simplicity and control. Migration tools allow for customization of scripts, facilitating quick feedback and faster deployments, akin to how application code is developed.

Example: schema migration in action

i360  builds thousands of PostgreSQL databases in Amazon Aurora. They needed an easy way for developers to migrate schemas while keeping them in sync throughout the CI/CD process.

Here is the schema migration workflow they created using Liquibase:

• Developers create database scripts, rollback scripts, and add comments about the bug or feature ID in their Liquibase changeset that match what they have in TFS for tracking.
• Developers run both their migration and rollback script through Liquibase to verify they are valid, run successfully, and are idempotent.
• Developers check the script and Liquibase changelog into GitLab source control.
• Includes the author name as their email address for notifications for script success and failure.
• A record is added to the approval table in Postgres. 
• A copy of the script and changelogs are put into S3 so that Fargate can have access to running those scripts. 
• The migration runs and kicks off a build.

Additionally, they use drift detection tools to automatically detect differences between database schemas using JSON output

Postgres schema and migration explained

PostgreSQL, commonly referred to as Postgres, is known as one of the world’s most advanced open source relational databases. Here are some of the core differentiators of Postgres:

• Reliability and robustness: PostgreSQL has over 35 years of active development, which has earned it a strong reputation for reliability, feature robustness, and performance. It is designed to handle complex queries and large amounts of data efficiently​.
• Extensibility: PostgreSQL is highly extensible. Users can add new data types, operators, and indexes. It also supports custom functions developed in various programming languages like PL/pgSQL, PL/Tcl, PL/Perl, and PL/Python​.
• Advanced features: PostgreSQL supports a wide range of advanced features such as ACID compliance, complex queries, foreign keys, triggers, updatable views, transactional integrity, and multi-version concurrency control (MVCC). These features make it suitable for applications requiring complex and reliable data transactions​.
• Open source presence: PostgreSQL has a strong, active community that continuously contributes to its development and improvement. This community-driven approach ensures a high level of support and a wealth of resources for users​.

Schema in Postgres

A PostgreSQL schema is a crucial part of its database structure, enabling efficient data management and organization. Here are key features of PostgreSQL schemas:

• Tables and columns: Structure data storage using tables with named columns, specifying data types for each column.
• Constraints: Ensure data integrity with primary keys, foreign keys, unique constraints, and check constraints to enforce rules on the data.
• Indexes: Optimize query performance using various index types, including B-tree, hash, GiST, SP-GiST, GIN, and BRIN indexes.
• Views: Simplify complex queries and provide abstraction by creating virtual tables that represent the result of a query on one or more tables.
• Sequences: Generate unique numeric identifiers using sequence objects, commonly used for auto-increment fields in tables.
• Schemas: Organize database objects into logical groups, providing namespace management and enhancing security by controlling access to these objects.
• Functions and triggers: Create custom functions for reusable code blocks and triggers to automatically execute operations in response to specific database events.

Postgres schema migration challenges

Migrating Postgres schema can present several challenges and limitations. Here are some key issues to consider:

• Schema drift and inconsistencies: During migration, schema drift — where the schema evolves differently across environments — can lead to inconsistencies and synchronization issues between development, staging, and production databases. This can cause failures in deployment and discrepancies in data structures.
• Complex rollbacks and change management: Managing complex rollbacks and ensuring that database changes can be reversed without loss of data integrity is challenging. Manual rollbacks can be error-prone and time-consuming, especially in environments with frequent schema changes.
• Dependency management and ordering of changes: Ensuring that database changes are applied in the correct order and managing dependencies between various schema elements can be complex. Incorrect ordering can lead to failures and broken dependencies, disrupting the migration process.

Database DevOps for Postgres schema migration

These Postgres schema migration challenges don’t need to slow down the pipeline. Instead, a database DevOps solution like Liquibase can integrate Postgres schema change into the rest of the automated pipeline for self-serve deployments. 

To combat schema drift, Liquibase provides robust Drift Detection tools that track and manage schema changes in a version-controlled manner, ensuring environments stay synchronized over time. Schema drift, which occurs when database environments unintentionally diverge, can lead to inconsistencies and unpredictable behavior in applications, especially during development and production deployments.

With Liquibase, teams can use versioned changelogs to define and store database changes, creating an auditable, centralized record of schema evolution. This approach allows automated deployment processes to apply the same updates consistently across environments, mitigating the risk of drift and enhancing control over schema changes.

Liquibase’s Drift Detection feature takes this a step further by actively monitoring environments for unexpected differences, alerting teams to discrepancies that may otherwise go unnoticed. When drift is detected, teams can quickly review and address changes before they impact application performance, data integrity, or deployment success. This proactive monitoring ensures a smooth, reliable migration and operational process, reinforcing stability and reducing errors across all database environments.

In support of easier change management, Liquibase also provides built-in support for Automated and Targeted Rollbacks, allowing developers to define how changes should be undone. This feature makes it easy to revert to previous states in case of errors or issues during migration. Liquibase's automated change management system simplifies the process of applying, tracking, and rolling back changes, ensuring data integrity and reducing downtime​.

Teams can also ensure proper ordering and dependencies with Liquibase, which allows for the explicit definition of the order in which changes should be applied. This ensures that dependencies are respected and that changes are executed in the correct sequence. Liquibase also supports preconditions that check the database state before applying changes, preventing issues caused by unmet dependencies​.

Start automating your database change process

Dive into database CI/CD with Liquibase and discover how database schema change automation works. To get your broader team on board with the cultural and technology investments needed for successful database DevOps, learn the foundations of database change management.