AI agents are no longer just reading your database, they are authoring the migrations. The write path is a different risk class, and it needs its own gate.

When the Agent Writes the Migration, Who Approves It?

How to run SQL Server schema migrations safely in production, covering state-based vs migration-based workflows, online index operations, batched backfills, the expand-and-contract pattern, and CI/CD.

SQL Server Schema Migration and Change Management

How to store, rotate, and scope database credentials: secrets managers, dynamic credentials, the application path vs the human path, and the mistakes that leak passwords.

Managing Database Passwords and Secrets: Best Practices

The practical ways to audit MySQL activity, from the general query log to audit plugins, binlog CDC, and Bytebase, and where each one falls short.

MySQL Audit Logging Guide

Dynamic data masking is chosen by access path, not product. Five approaches (engine-native, in-database, application, BI layer, and Bytebase) and which caller each one masks.

How to Implement Dynamic Data Masking: Five Approaches by Access Path

Static vs dynamic data masking, when each applies, how they differ on backups, performance, and reversibility, and when to run both.

Static vs Dynamic Data Masking: When to Use Which

Compare BigQuery data masking options: native column-level dynamic data masking (built-in masking rules via policy tags and data policies, Dataplex catalog) and Bytebase fleet-wide masking.

BigQuery Dynamic Data Masking

Compare SQL Server data masking options, native Dynamic Data Masking (five mask types, SQL Server 2022 granular UNMASK) and Bytebase fleet-wide masking.

SQL Server Dynamic Data Masking

Compare Oracle data masking options. Native Data Redaction (five function types via DBMS_REDACT, EXEMPT REDACTION POLICY) and Bytebase fleet-wide masking.

Oracle Dynamic Data Masking

You write a one-line UPDATE against production. Here's the lane that keeps it safe: the four phases, the controls, and what each one prevents.

How to Handle Ad-hoc Database Data Changes?

How to run dynamic data masking so it survives an audit: mask by role, go column-level, log unmask events, review policy changes in code, and keep environments in sync. Plus a vendor comparison and an end-to-end walkthrough.

Dynamic Data Masking Best Practices

Database access control practices that hold up in a SOC 2 audit: least privilege, separation of duties, just-in-time access, statement-level enforcement, and the common mistakes that get flagged.

Database Access Control (DAC) Best Practices

TRUNCATE and DELETE both remove rows, but they differ in speed, rollback behavior, and locking. Here is how each one behaves in PostgreSQL, MySQL, and SQL Server, and when to reach for which.

TRUNCATE vs DELETE in SQL: Differences Across PostgreSQL, MySQL, and SQL Server

A practical walk through six database design patterns you will actually meet in production: normalization, star schema, EAV, polymorphic associations, multi-tenancy, and the anti-patterns worth avoiding.

Database Design Patterns Every Developer Should Know

A practical walkthrough of pgvector: installing the extension, storing embeddings, writing similarity queries, building HNSW indexes, and knowing when plain PostgreSQL is enough.

pgvector: How to Add Vector Search to PostgreSQL

How upsert works across MySQL, PostgreSQL, and SQL Server, with the syntax for each, the edge cases worth knowing before you ship, and how upserts interact with migrations.

Upsert in SQL: INSERT ON DUPLICATE KEY vs ON CONFLICT Explained

AI agents need more than DDL. The full database lifecycle codified (schema, dependencies, ownership, history) gives LLMs the context to query correctly.

From Schema as Code to Schema as Context

How to find and fix slow MySQL queries: reading the slow query log, using EXPLAIN, tuning indexes, and rewriting queries, with a worked example for each.

How to Fix Slow MySQL Queries: A Practical Guide

Blue-green deployment is easy when your servers are stateless. The trouble starts at the database. This guide walks through why database blue-green is hard and how to do it without corrupting your data.

Database Blue-Green Deployment: A Practical Guide

How to give AI agents governed access to your databases. Identity, authorization, query control, audit, and masking, applied to a new principal type.

How to Govern AI Agent Database Access

A walk-through of how renaming a single column can quietly break an analytics pipeline, why it happens, and how schema change detection and notification help you catch it early.

Schema Change Detection and Notification: Why a Simple Column Rename Can Break Your Data Pipeline

How to read a Postgres query plan, spot the common performance problems, and fix them without breaking other queries.

Postgres Query Plan: How to Read and Fix Performance Issues

A walk through every layer of the Postgres permission system that can raise ERROR 42501, why each one fires, and how the behavior differs from MySQL.

The Anatomy of Postgres Permission ERROR 42501

A practical look at Postgres logs: what they record, how the logging collector works, which parameters actually matter in production, and the mistakes that turn logs into expensive noise.

Postgres Logs Explained: A Practical Guide for Production

What multi-environment database deployment actually involves, the problems teams keep hitting, and the patterns (manual scripts vs GitOps) for handling them.

Database Multi-Environment Deployments - The Challenges and Patterns

A practical guide to HITRUST database compliance covering access control, audit logging, change management, and data protection.

Database Compliance for HITRUST: Controls, Risks, and Best Practices

What the "Require Primary Key" SQL review rule checks, the production problems a missing primary key tends to cause, and the SQL to fix tables that don't have one.

Require Primary Key: A SQL Review Rule Explained

What the "Enforce NOT VALID in CHECK" SQL Review rule does, why adding a CHECK constraint can lock your table, and how the two-step NOT VALID approach avoids it.

Enforce NOT VALID in CHECK: A SQL Review Rule Explained

The practical ways to audit a PostgreSQL database, from native logging to triggers, logical replication, pgAudit, and Bytebase, with the trade-offs of each.

Postgres Audit Logging Guide

Learn why CASCADE operations in SQL can lead to catastrophic data loss and how the "Prohibit CASCADE" review rule protects your production database.

Prohibit CASCADE: A SQL Review Rule Explained

A walkthrough of moving each Supabase piece (database, auth, storage, functions, realtime, networking) to its AWS counterpart, with a services-first order that keeps the final database cutover simple.

How to Migrate from Supabase to AWS

Enterprise database management is the practice of organizing, securing, and optimizing database systems and workflows at scale, keeping data integrity, availability, and compliance intact across large organizations.

What is Enterprise Database Management (EDM)?

A plain explanation of Postgres Transaction ID (XID) wraparound: what causes it, how to check how close you are, and what keeps it from taking your database offline.

Postgres Transaction ID (XID) Wraparound

How a CI/CD pipeline for database schema migration works end-to-end. What it gates, what it leaves outside, and the tools that fit.

How to Build a CI/CD Pipeline for Database Schema Migration

A practical look at what LGPD (Brazil's General Personal Data Protection Act) means for database operations, how it differs from GDPR, and the controls teams need to actually comply.

Database Compliance for LGPD: Implications and Best Practices

What a Postgres LATERAL JOIN does, when to reach for one, and the patterns and gotchas you'll run into using it.

What is Postgres LATERAL JOIN?

What MySQL Online DDL actually does, how its three algorithms (INSTANT, INPLACE, and COPY) differ, and the limitations to plan around before you run a schema change in production.

MySQL Online DDL: A Practical Guide

Should your tables be named user or users? This post walks through the trade-offs of singular vs. plural table naming, what the common stacks default to, and a set of conventions you can copy into your own handbook.

SQL Table Naming Dilemma: Singular vs. Plural

How PostgreSQL handles case in identifiers and string values, where it bites, and the settings that keep your queries consistent and fast.

Postgres Case Sensitivity Explained

What pg_database_owner does in Postgres, why it was added, and how to use it without hitting the "cannot have explicit members" error.

What is pg_database_owner in Postgres

A reference for which Postgres DDL operations rewrite the whole table (and take heavy locks) versus which ones are metadata-only, plus how to test it yourself before you run it in production.

Which Postgres Operation causes a table rewrite

A practical rundown of the Postgres Row-Level Security traps that quietly break performance or leak data, with the fix for each.

Postgres Row-Level Security Footguns

A walk through the three ways to roll back changes in Postgres: transactions and SAVEPOINT, Point-in-Time Recovery, and compensating DML, and when each one fits.

Postgres Rollback Explained

Learn how Postgres locks affect schema migrations and which DDL operations conflict with DML to achieve zero-downtime deployments.

Postgres Schema Migration without Downtime

The limitations and gotchas of gh-ost for online MySQL schema changes, and when to reach for something else instead.

gh-ost Limitations You Need to Know Before Production

PostgreSQL best practices for application developers: connection naming, timeouts, online migrations, role separation, and UPSERT for reliable production apps.

Postgres best practices I wish every app developer knew

Learn how to use CREATE INDEX CONCURRENTLY in Postgres to build indexes without blocking writes. Pitfalls, safety checks, and performance tips.

How to Use Postgres CREATE INDEX CONCURRENTLY

Learn how Bytebase enables organizations to implement comprehensive Database Security Lifecycle Management, unifying security and operations from development to production.

Database Security Lifecycle Management

This guide focuses on HIPAA compliance at the database layer, providing healthcare organizations and database teams with practical guidance for implementing HIPAA Security Rule requirements.

HIPAA Data Security and Retention Requirements

Compare the online index creation difference between Postgres and MySQL

Postgres vs. MySQL: Online Index Creation

SOC 2 Data Security and Retention Requirements for Database Systems and How Bytebase Helps

SOC 2 Data Security and Retention Requirements

Compare the different indexing options between Postgres and MySQL

Postgres vs. MySQL: Indexing Options

Compare the JSON support difference between Postgres and MySQL

Postgres vs. MySQL: JSON Support

While PostgreSQL RLS is a powerful feature, it has inherent design limitations that hinder broader adoption. In this post, we’ll examine these limitations and explore alternatives.

Postgres Row-Level Security (RLS) Limitations and Alternatives

Compare the DDL transaction difference between Postgres and MySQL

Postgres vs. MySQL: DDL Transaction Difference

Understanding DynamoDB Pricing (2025)

Complete guide to AWS Aurora pricing: instance types and costs, Aurora Serverless v2 pricing, I/O-Optimized vs Standard, reserved instance savings, and real-world cost examples. Learn how to optimize your Aurora costs.

AWS Aurora Pricing Explained: Instance Types, Serverless, and Cost Optimization (2026)

Understanding AWS RDS Pricing (2025)

Explain why MySQL provides a variety of timeout settings.

MySQL Timeout Explained

Learn the difference between DDL and DML in SQL databases. DDL (Data Definition Language) defines database structure with CREATE, ALTER, DROP. DML (Data Manipulation Language) manages data with SELECT, INSERT, UPDATE, DELETE. Includes examples and comparison table.

DDL vs DML in SQL: What is Data Definition Language and Data Manipulation Language?

The top 10 database schema design best practices with code snippets

Top 10 Database Schema Design Best Practices

Database version control is the practice of managing and tracking changes to a database schema and its associated data over time. Why best practice matters? Mitigate risks, improve collaboration, and ensure data audibility.

Database Version Control Best Practice

Database access governance is a critical component of any organization's security strategy. By implementing the right tools and policies, you can protect sensitive data in your databases, ensure regulatory compliance, and maintain a secure and productive engineering environment.

Database Access Governance: Why It Matters and How to Get it Right

Database Security Compliance is a critical framework that not only mitigates legal and financial risks but also enhances overall data governance.

Database Security Compliance

A comprehensive guide to Change Advisory Boards (CAB), including their purpose, limitations, popular tools, and best practices for database change management.

What is a Change Advisory Board (CAB)?

Understanding production databases, their critical importance, common deletion mistakes, and best practices for safeguarding these essential systems.

What is Production Database

Give an overview about Database Activity Monitoring and its difference from Database Monitoring

What is Database Activity Monitoring (DAM)?

A deep dive into multi-tenant database architecture patterns, exploring pros and cons of shared vs. isolated models, and how to choose the right approach for your SaaS application.

Multi-Tenant Database Architecture Patterns Explained

How to integrate ServiceNow, GitHub with Bytebase to Automate Database Changes.

ServiceNow and GitHub Integration for Database Change Automation

Pros and Cons between choosing uuidv1, uuidv4, uuidv7

How to Choose Database UUID?

What are the differences between Primary Key and Foreign Key

Primary Key vs Foreign Key: What are the Differences?

Explain write ahead logging in database and the implementation difference between different databases.

What is Write Ahead Logging (WAL)

Just-in-Time database access — the use cases, the traditional workflow and its blind spot, and how Bytebase governs the SQL, not just the request.

Just-in-Time Database Access

Explain the different database permission levels

Database Security: Read, Write, and Admin Permission Levels

Explain database rollback and how to automate it

Database Rollback: When Shit Hits the Fan

Database DevSecOps is the practice of integrating security throughout the entire database development lifecycle, much like how DevSecOps evolved from DevOps.

What is Database DevSecOps?

Dynamic Data Masking (DDM) protects sensitive data in real-time by dynamically altering the data returned to the application or user without changing the data at rest

What is Dynamic Data Masking (DDM)

Database DevOps integrates DevOps practices into database management, automating processes like schema changes, testing, and deployment.

What is Database DevOps?

Explain why Postgres provides a variety of timeout settings.

Postgres Timeout Explained

Pros and Cons between choosing UUID or auto increment integer / serial as the primary key for SQL database.

How to Choose between UUID and Auto Increment Integer / Serial as the Primary Key?

A comprehensive overview of the essential practices and principles necessary for achieving GDPR compliance within database operations

Database Compliance for GDPR: Implications and Best Practices

How Bytebase tracks database changes throughout the lifecycle

How Bytebase Tracks Database Change

Explain the pricing details of Google Cloud AlloyDB and compare it with Google Cloud SQL

Understanding Google Cloud AlloyDB Pricing (updated for 2025)

Describe the current feature gap of Snowflake CI/CD story and present a solution

Snowflake CI/CD and DevOps Best Practice

Bytebase is an all-in-one solution for database development lifecycle management. It replaces fragmented database tools and unifies the workflow in a single place.

All the Database Tools Bytebase Replaces

Database schema drift is the case where the actual schema in the live database is different from the source of truth. It's also one of the most frequent root cause of the database related outages.

What is Database Schema Drift?

Compare PostgreSQL data masking options — PostgreSQL Anonymizer 2.0 (Rust rewrite, five strategies, security labels) and Bytebase fleet-wide masking.

Postgres Dynamic Data Masking

Collaboration and Teamwork, Change Tracking and History, Migration Scripts, Branching and Merging, Deployment and Rollback, VCS Integration GitOps are key aspects of database version control

What is Database Version Control?

Compare MySQL and MariaDB data masking — 9.7 Enterprise DDM, the legacy plugin, Percona, MariaDB MaxScale's proxy filter, and Bytebase fleet-wide masking.

MySQL Dynamic Data Masking

Snowflake provides auto-suspend and auto-resume to help customers to save budget when the warehouse is inactive

What is Snowflake Auto-Suspend and Auto-Resume?

While Snowflake revolutionizes data management and analytics, effectively managing schema changes and tracking change history becomes increasingly crucial.

Snowflake Schema Change and CI/CD Best Practice

Database schema migration involves modifying the structure of objects within relational databases, evolving their schema from the current state to a new desired state. The two main schema transformation strategies are migration-based migration and state-based migration.

What is Database Schema Migration?

You add a column. The migration locks a 200M-row table for forty minutes. Here's the lane that keeps planned schema change from breaking prod.

How to Handle Database Migration / Schema Change?

The second release of the 🐰  year, the version number was upgraded from 1.12.0 to 1.12.1, and although it was a minor upgrade (since no schema changes were involved), it still included 230 code commits

Bytebase 1.12.1 Deep Dive

First release of the Year of Rabbit 🐰 is out, the version number was upgraded from 1.11.0 to 1.12.0. - SQL Review Policy - Price Plan Update.

Bytebase 1.12.0 Deep Dive

We are thrilled to announce the first release launch of 2023, marking a new beginning for our product and our valued customers. The version number was upgraded from 1.10.0 to 1.11.0. - MongoDB - Schema Diagram - Schema Editor.

Bytebase 1.11.0 Deep Dive

Last week, we released Bytebase 1.10.0. Let's take a deeper look into the new features - Audit Log - Terraform Provider to manage user roles in PostgreSQL - Support for large SQL files.

Bytebase 1.10.0 Deep Dive

If we can develop a complete Database CI/CD workflow, and break the wall between developers and DBAs, will it improve release efficiency?

VCS Integrated Database CI/CD Workflow Best Practice

Last week, we released Bytebase 1.9.1. Let's take a deeper look into the new features - schema editor, mark issues as Need Attention, database access control, and data masking.

Bytebase 1.9.1 Deep Dive

Database Change Management (DCM) explained — definition, why it is hard, the ideal workflow, state-based vs change-based approaches, tool evaluation criteria, and how it fits into DevOps.

What is Database Change Management (DCM)?


When something goes wrong in Postgres, "roll it back" can mean three very different things depending on how far the change has traveled. If you're still inside the transaction, a rollback is instant and lossless. If you've already committed, you're into recovery territory, and the right answer depends on how much damage you did and when you noticed. This post walks through the three approaches Postgres gives you, and where each one helps and where it gets in your way.

## Built-in Transaction Rollback and SAVEPOINT

Postgres transactions let you roll back every change inside a transaction block. For finer control, `SAVEPOINT` lets you set markers within a transaction so you can undo part of it without throwing away the work you did earlier.

### Using SAVEPOINT

Create a savepoint:

```sql
SAVEPOINT my_savepoint;
```

Roll back to it:

```sql
ROLLBACK TO SAVEPOINT my_savepoint;
```

A practical pattern for risky operations:

```sql
BEGIN;

-- Step 1: safe operations
INSERT INTO employees (name, department) VALUES ('Alice', 'Engineering');

SAVEPOINT sp_batch;

-- Step 2: risky operations
INSERT INTO employees (name, department) VALUES ('Bob', 'Marketing');
-- Oops, Bob is actually in Sales

-- Roll back only the risky step
ROLLBACK TO SAVEPOINT sp_batch;

-- Step 3: continue with corrected operation
INSERT INTO employees (name, department) VALUES ('Bob', 'Sales');

COMMIT;
```

The savepoint stays usable after you roll back to it. One thing to watch: any savepoints you created after it are not just released, they're destroyed and invalidated by the rollback.

### Limitations

- Some DDL statements (`CREATE DATABASE`, `DROP DATABASE`, `CREATE TABLESPACE`, `DROP TABLESPACE`) can't run inside transactions at all.
- It only works while the transaction is open. Once you `COMMIT`, `ROLLBACK` can't undo anything.

## Point-In-Time Recovery (PITR)

PITR restores a database to a specific moment in time using continuous WAL archiving. Postgres's Write-Ahead Log records every change, and PITR combines a base backup with the archived WAL files to replay changes up to the moment you want.

### Cloud Provider Support

The major cloud providers all give you a one-click PITR experience:

- **AWS RDS for Postgres**: Restore to point in time via Console/CLI/API
- **Google Cloud SQL**: PITR from the console interface
- **Azure Database for Postgres**: Portal "Restore" to latest or a chosen restore point

### Named Restore Points

You can create targeted recovery points so you don't have to hunt for the right timestamp later:

```sql
-- Before risky migration
SELECT pg_create_restore_point('pre_migration_2025_09_04');
```

When you recover, point at `recovery_target_name = 'pre_migration_2025_09_04'` instead of guessing the moment things went wrong.

### Advantages

- Works no matter what, whether the transaction was committed or not.
- Recovers from mistakes you discover hours or days later, long after a transaction rollback is off the table.

### Limitations

- It operates at the cluster level. PITR rolls back the entire database, not a single table or row.
- That makes it a heavyweight tool, and a poor fit for small, isolated changes.
- Rolling back one bad `UPDATE` this way also undoes every valid change that came after it.

## Cross-Transaction DML Rollback (Compensating Changes)

Once a bad `UPDATE`, `DELETE`, or `INSERT` is committed, transaction rollback is gone and PITR is overkill. What you actually want is **compensating DML**: new statements that put the previous values back. Think of it as `git revert` for data.

### Manual Compensating DML Example

Say you accidentally ran:

```sql
UPDATE accounts SET status = 'inactive' WHERE org_id = 42;
```

If you keep an audit or history table, you can compensate from it:

```sql
-- Revert to last known status per row
UPDATE accounts a
SET status = h.old_status
FROM account_status_history h
WHERE a.id = h.account_id
  AND h.org_id = 42
  AND h.changed_at = (
       SELECT max(h2.changed_at)
       FROM account_status_history h2
       WHERE h2.account_id = a.id
         AND h2.changed_at < :mistake_time
     );
```

That looks tidy on a single column. In a real system you also have to think about sequences, cascades, triggers, and other side effects, which is where hand-writing these scripts gets error-prone.

### Bytebase Solution

Bytebase offers [point-and-click rollback](https://docs.bytebase.com/change-database/rollback-data-changes) for exactly this case:

1. **Prior Backup**: Before a DML statement runs, Bytebase captures the affected rows and stores them in a dedicated `bbdataarchive` schema.
1. **1-Click Rollback**: It generates and runs the rollback script for you.

### Workflow Benefits

- No hand-written rollback scripts to get wrong.
- The rollback goes through the same review and approval process as any other change.
- You can roll back across multiple databases in one go.
- The whole thing stays controlled and auditable.

## Choosing the Right Rollback Method

With the three approaches in hand, here's how they line up against the situations you'll actually hit:

| Situation                             | Best Tool                                 | Why                                                  |
| ------------------------------------- | ----------------------------------------- | ---------------------------------------------------- |
| Still in session, haven't committed   | **Transaction rollback / SAVEPOINT**      | Instant, lossless; keep good work, discard bad chunk |
| Committed a small wrong UPDATE/DELETE | **Cross-transaction rollback (Bytebase)** | Surgical fix; no cluster restore                     |
| Dropped table / mass data corruption  | **PITR**                                  | Ubiquitous, reliable; recovers to clean time point   |

The short version: transactions and `SAVEPOINT` are how you avoid the mistake in the first place, so reach for them before you run anything risky. PITR is the safety net for when the blast radius is unclear or the damage is large, and it's everywhere and cloud-friendly. And compensating DML, whether you write it by hand or let Bytebase's workflow handle it, is for the small, precise fixes after a commit where PITR would be far too blunt.



## Understanding Postgres Locks

Postgres uses a [sophisticated locking system](https://www.postgresql.org/docs/current/explicit-locking.html) with multiple lock modes. DDL operations often acquire locks that conflict with routine DML operations like `INSERT`, `UPDATE`, and `DELETE`. This means a seemingly harmless schema change can bring your entire application to a halt if not executed carefully.

Let's understand what locks DML operations acquire:

| DML                        | Lock Mode         | Description                                                            |
| -------------------------- | ----------------- | ---------------------------------------------------------------------- |
| `SELECT`                   | **ACCESS SHARE**  | Only conflicts with ACCESS EXCLUSIVE                                   |
| `INSERT`/`UPDATE`/`DELETE` | **ROW EXCLUSIVE** | Conflicts with SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE |

Now we can see how the two most common schema migration operations conflict with DML:

| DDL Operation              | Lock Mode            | SELECT        | INSERT/UPDATE/DELETE |
| -------------------------- | -------------------- | ------------- | -------------------- |
| `ALTER TABLE` (most cases) | **ACCESS EXCLUSIVE** | ❌ **BLOCKS** | ❌ **BLOCKS**        |
| `CREATE INDEX`             | **SHARE**            | ✅ Allows     | ❌ **BLOCKS**        |

Many `ALTER TABLE` forms acquire an **ACCESS EXCLUSIVE** lock, which is the most restrictive lock in Postgres. This completely blocks your application - no reads, no writes, nothing can happen on that table until the operation completes.

`CREATE INDEX` uses a **SHARE** lock, which allows `SELECT` statements but blocks all write operations (`INSERT`, `UPDATE`, `DELETE`). For a large table, index creation can take hours, effectively making your application read-only.

Both scenarios cause downtime. In the following sections, we'll explore alternatives that avoid these blocking behaviors.

## ALTER TABLE without Downtime

While most `ALTER TABLE` operations acquire `ACCESS EXCLUSIVE` locks, Postgres provides several techniques to avoid downtime for common schema changes.

### Adding Columns with DEFAULT Values

Since [Postgres 11](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=16828d5c0273b4fe5f10f42588005f16b415b2d8), adding a column with a `DEFAULT` value no longer requires a table rewrite in many cases.

```sql
-- ✅ Fast operation (Postgres 11+)
-- No table rewrite, minimal lock time
ALTER TABLE users ADD COLUMN status text DEFAULT 'active';

-- ❌ Slow operation (requires table rewrite)
-- Only use if you need a non-constant default
ALTER TABLE users ADD COLUMN created_at timestamp DEFAULT now();
```

**How it works**: Postgres stores the default value in the system catalog instead of rewriting every row. When you read a row that doesn't have the new column, Postgres automatically returns the default value.

### Adding Constraints in Two Phases

For constraints that need validation against existing data, use the `NOT VALID` approach:

#### Check Constraints

```sql
-- Step 1: Add constraint without validating existing data
-- Fast - only blocks briefly to update catalog
ALTER TABLE users ADD CONSTRAINT users_age_positive
  CHECK (age > 0) NOT VALID;

-- Step 2: Validate existing data (can take time but doesn't block writes)
-- Uses SHARE UPDATE EXCLUSIVE lock - allows reads/writes
ALTER TABLE users VALIDATE CONSTRAINT users_age_positive;
```

#### Foreign Key Constraints

```sql
-- Step 1: Add foreign key without validation
-- Fast - only updates catalog, doesn't validate existing data
ALTER TABLE orders ADD CONSTRAINT orders_user_id_fk
  FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;

-- Step 2: Validate existing relationships
-- Can take time but allows concurrent operations
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_id_fk;
```

### Setting Columns to NOT NULL

The safest approach for `SET NOT NULL` is a four-step process:

```sql
-- Step 1: Add a check constraint (fast with NOT VALID)
ALTER TABLE users ADD CONSTRAINT users_email_not_null
  CHECK (email IS NOT NULL) NOT VALID;

-- Step 2: Validate the constraint (allows concurrent operations)
ALTER TABLE users VALIDATE CONSTRAINT users_email_not_null;

-- Step 3: Set NOT NULL (fast since constraint guarantees no nulls)
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- Step 4: Drop the redundant check constraint
ALTER TABLE users DROP CONSTRAINT users_email_not_null;
```

**Why this works**: The final `SET NOT NULL` is fast because Postgres can see from the validated constraint that no null values exist.

**Postgres 18 Update**: The upcoming Postgres 18 introduces a [simplified form](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=a379061a2) that allows `NOT NULL` constraints to use the `NOT VALID` attribute directly:

```sql
-- Postgres 18+ - Simplified approach
ALTER TABLE users ALTER COLUMN email SET NOT NULL NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_email_not_null;
```

## CREATE INDEX without Downtime

Instead of the blocking `CREATE INDEX`, use `CREATE INDEX CONCURRENTLY` to build indexes without interfering with application traffic:

```sql
-- ❌ Blocks all writes during index creation
CREATE INDEX idx_users_email ON users(email);

-- ✅ Allows writes to continue during index creation
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
```

`CREATE INDEX CONCURRENTLY` uses a **SHARE UPDATE EXCLUSIVE** lock instead of a **SHARE** lock, allowing `INSERT`, `UPDATE`, and `DELETE` operations to continue normally while the index is being built.

**Trade-offs**: While concurrent index creation avoids downtime, it takes longer to complete and has some limitations (cannot be used inside transactions, requires more careful error handling).

For a comprehensive guide on concurrent index creation, see our detailed article on [CREATE INDEX CONCURRENTLY](/blog/postgres-create-index-concurrently).

## Use lock_timeout

Regardless of the lock level, all DDL operations should use [lock_timeout](https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-LOCK-TIMEOUT) to prevent creating lock queues that can freeze your application.

### The Lock Queue Problem

When an `ACCESS EXCLUSIVE` operation waits for existing queries to complete, it blocks all subsequent operations from starting. Your application can appear completely frozen while waiting for one long query to finish.

```sql
-- Session 1: Long-running query (holds ACCESS SHARE)
SELECT COUNT(*) FROM large_table; -- Takes 5 minutes

-- Session 2: DDL waiting for ACCESS EXCLUSIVE
ALTER TABLE large_table ALTER COLUMN description TYPE text; -- Waits behind the SELECT

-- Session 3: New application query
SELECT * FROM large_table WHERE id = 123; -- BLOCKED by waiting DDL!
```

### Set lock_timeout

The `lock_timeout` parameter can be set at multiple levels:

```sql
-- Option 1: Session level (temporary)
SET lock_timeout = '5s';

-- Option 2: Role level (recommended - persistent across sessions)
CREATE ROLE ddl_user WITH LOGIN PASSWORD 'secure_password';
ALTER ROLE ddl_user SET lock_timeout = '5s';

-- Option 3: Database level (applies to all connections)
ALTER DATABASE mydb SET lock_timeout = '5s';
```

Setting `lock_timeout` at the Postgres role level is recommended because:

1. The setting applies to all sessions for that role
1. No need to remember to set it in each DDL session

```sql
-- Use the dedicated DDL user for schema migrations
-- Connect as ddl_user and run:
ALTER TABLE users ALTER COLUMN description TYPE text;
```

If the operation can't acquire the lock within 5 seconds, it fails with an error instead of blocking the application indefinitely.

### Retry Logic

Combine `lock_timeout` with retry logic to handle busy periods:

```sql
-- Example DDL script with retry logic
DO $$
DECLARE
    max_attempts INTEGER := 10;
    attempt INTEGER := 1;
    success BOOLEAN := FALSE;
BEGIN
    WHILE attempt <= max_attempts AND NOT success LOOP
        BEGIN
            SET lock_timeout = '2s';
            ALTER TABLE users ALTER COLUMN description TYPE text;
            success := TRUE;
            RAISE NOTICE 'DDL succeeded on attempt %', attempt;
        EXCEPTION
            WHEN lock_not_available THEN
                RAISE NOTICE 'Attempt % failed, retrying in 30 seconds...', attempt;
                PERFORM pg_sleep(30);
                attempt := attempt + 1;
        END;
    END LOOP;

    IF NOT success THEN
        RAISE EXCEPTION 'DDL failed after % attempts', max_attempts;
    END IF;
END $$;
```

<HintBlock type="info">

Bytebase automatically handles [retry logic](https://github.com/bytebase/bytebase/blob/c32bf12c40fec3adc16f56c274d490e407465b04/backend/plugin/db/pg/pg.go#L439-L460) during schema migrations.

</HintBlock>

## Conclusion

Achieving zero-downtime Postgres schema migrations requires understanding locks and applying the right techniques:

1. **Understand the impact**: Know which DDL operations block your application
1. **Use modern Postgres features**: Leverage `CONCURRENTLY`, `NOT VALID`, and other optimizations
1. **Always set lock_timeout**: Prevent lock queues that can freeze your application
1. **Plan for retries**: Handle busy periods gracefully with retry logic

By following these practices, you can deploy schema changes confidently without impacting your users.

## References

1. [Postgres locking mode](https://www.postgresql.org/docs/current/explicit-locking.html)
1. [Postgres 11: ADD COLUMN with DEFAULT optimization](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=16828d5c0273b4fe5f10f42588005f16b415b2d8)
1. [Postgres 18: NOT NULL constraints with NOT VALID](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=a379061a2)
1. [How to Use Postgres CREATE INDEX CONCURRENTLY](/blog/postgres-create-index-concurrently)



[gh-ost](https://github.com/github/gh-ost) is a solid tool for online schema changes in MySQL, but it's not a silver bullet. Some of its limitations are hard blockers that will stop a migration cold; others are quieter gotchas that only bite you at cut-over time or under heavy write load. This article walks through both, with the scenarios where each one tends to show up, so you know ahead of time whether gh-ost fits your case or whether you should reach for something else.

---

## 1. Hard Blockers: If You Hit These, Stop!

Some `gh-ost` limitations are non-negotiable. If your environment falls into any of the categories below, gh-ost simply won't run, and you'll need an alternative like [pt-online-schema-change](https://docs.percona.com/percona-toolkit/pt-online-schema-change.html) or a different migration strategy.

### Foreign Keys (FKs)

`gh-ost` does **not** support tables with foreign key constraints. If your table is referenced by other tables, meaning it's a "parent" table, `gh-ost` refuses to run. That refusal is deliberate: it's there to keep you from quietly breaking referential integrity. Helpful as a safety net, but a real wall for many applications.

Say you have a `customers` table with a primary key `id`, and an `orders` table with a foreign key `customer_id` that references `customers.id`. Try to alter `customers` with `gh-ost` and the migration fails, because `customers` is a parent table. Your options here are to temporarily drop the foreign key constraints, switch to a tool like [pt-online-schema-change](https://docs.percona.com/percona-toolkit/pt-online-schema-change.html), or do the migration inside a maintenance window.

### Triggers

Tables that already have triggers aren't supported either. If your table carries triggers for auditing, logging, or anything else, `gh-ost` can't migrate it.

So if your `payments` table has audit triggers, you'll need to move that auditing logic up into the application layer, or pick a tool that can work alongside triggers.

### No Shared Unique Key

`gh-ost` needs the source and ghost tables to share an identical `PRIMARY KEY` or `UNIQUE KEY` to iterate over the rows. If your schema change touches or removes that key, gh-ost can't proceed.

Changing the layout of a multi-column primary key is the classic example. You'll have to break the migration into steps (add a new unique key, backfill the data, then switch over to the new key) or use a tool that handles this kind of change directly.

## 2. Infrastructure Requirements: Must-Haves for a Smooth Migration

Past the hard blockers, `gh-ost` has a few infrastructure prerequisites that need to be in place before a migration goes smoothly.

### Binary Log Format and Image

`gh-ost` requires **Row-Based Replication (RBR)**. If your primary uses Statement-Based Replication (SBR), you'll need to point gh-ost at a replica configured with RBR. On top of that, many managed cloud database services require `binlog_row_image=FULL`.

**Example Command (for cloud environments):**
 ```bash
 gh-ost --assume-rbr --host=<replica_host> --cut-over=default ...
 ```

### Privileges

`gh-ost` needs specific replication-related privileges, and in some cases `SUPER`-like capabilities. Getting those can be awkward on managed platforms like [Amazon RDS](https://aws.amazon.com/rds/) or [Azure Database for MySQL](https://azure.microsoft.com/en-us/products/azure-database-for-mysql), where you don't have full control over the instance. Check your platform's docs for the permissions it grants to online schema migration tools before you commit to gh-ost.

## 3. The "Online" Lock: Cut-Over and Long-Running Transactions

"Online" doesn't mean zero locks. The final cut-over phase still needs a **metadata lock (MDL)** to swap the old and new tables. The lock is usually brief, but any long-running query against the table can block the swap, and a brief pause turns into a real incident.

Here's how it goes wrong: a business intelligence team kicks off a 15-minute `SELECT` on the table you're migrating, right as you're about to cut over. `gh-ost` waits for the MDL, your deployment hangs, and the lock can back up other application traffic behind it. The way around it is coordination: schedule cut-overs for low-traffic windows, give other teams a heads-up, and have a plan to kill any long-running query that gets in the way.

## 4. Performance and Capacity Pitfalls

`gh-ost` isn't a magic bullet for performance, and it comes with its own capacity considerations worth knowing up front.

### Headroom for a Full Copy

`gh-ost` creates a "ghost" table and copies every row from the original. That means you need enough free disk space for a full second copy of the table, plus the extra binary logs that pile up during the migration.

### High Write Rates Can Outrun gh-ost

Under extreme write loads, `gh-ost`'s single-threaded binary log processing can become the bottleneck. The backlog of changes to apply grows faster than gh-ost can drain it, and the migration falls further and further behind. [pt-online-schema-change](https://docs.percona.com/percona-toolkit/pt-online-schema-change.html) sometimes does better here. The honest answer is to test both in your own environment and see which one keeps up with your workload.

### Dropping the Old Table

After the cut-over, `gh-ost` drops the old table. On very large tables that drop can spike I/O and replication lag. Do it during a low-traffic window, or hand it off to a more careful housekeeping process for dropping big tables.

## 5. Topology Compatibility and Native DDL

### Galera/PXC Not Supported

`gh-ost` isn't compatible with [Galera](https://galeracluster.com/) or [Percona XtraDB Cluster (PXC)](https://www.percona.com/software/mysql-database/percona-xtradb-cluster), because of how it locks and swaps tables. For those topologies, [pt-online-schema-change](https://docs.percona.com/percona-toolkit/pt-online-schema-change.html) is the safer pick.

### Native MySQL May Be Faster

On modern MySQL (8.0+), a lot of DDL operations are now `INSTANT` or `INPLACE`, which means they run with near-zero impact and no external tool at all. Before reaching for [gh-ost](https://github.com/github/gh-ost), check whether a native DDL operation already covers what you need.

Adding a column with a default value, for instance, may be an `INSTANT` operation in MySQL 8.0, depending on the data type and default value. When that's the case, a plain `ALTER TABLE` is both simpler and faster than gh-ost.

---

## Quick Decision Checklist

A quick way to gauge whether `gh-ost` fits your migration:

- Table has **FKs or triggers**? → **Don't use gh-ost. Consider** [pt-online-schema-change](https://docs.percona.com/percona-toolkit/pt-online-schema-change.html).
- Can you get **ROW binlogs** and a **shared UNIQUE/PRIMARY key**? → **OK.**
- Can you coordinate the **cut-over** (no long queries)? → **OK.**
- Using **Galera/PXC topology**? → **Prefer** [pt-online-schema-change](https://docs.percona.com/percona-toolkit/pt-online-schema-change.html).
- On **MySQL 8.0+?** → **Check if native INSTANT/INPLACE DDL already solves it.**

If you can check every box and you've tested the migration on a replica first, `gh-ost` is an excellent choice for online schema changes on large, hot tables with minimal production impact.


The
Bytebase Blog

Explore the standard for database development

Solutions & Platform

Comparisons

Legal

Resources

Company