This is the first article by our guest author, Lucio Chiessi. Lucio is a Staff DBA at Trustly Inc. with extensive experience managing mission-critical environments and high-volume transactional PostgreSQL databases, focused on performance tuning, high availability, and scalable architectures.  

Database migrations are a critical step in the lifecycle of any application, allowing teams to deploy new features, create and change database objects, and scale infrastructure. However, in high-volume mission-critical environments, executing Data Definition Language (DDL) statements can quickly turn into a production nightmare.  

The primary culprit behind application downtime during DDL deployments is the mismanagement of PostgreSQL’s locking mechanism. A single poorly planned ALTER TABLE statement can request an exclusive lock, blocking incoming application queries, exhausting connection pools, causing cascading timeouts, and ultimately leading to revenue loss. 

To achieve true zero-downtime deployments, database migrations must be fast, defensive, and meticulously designed to avoid heavy locks and long-running queries.

I presented on this topic a while back at PGConf Brazil. You can check the YouTube video here (Portuguese audio only, sorry).

Rules for Safe PostgreSQL Migrations

1. Run a Modern PostgreSQL Version (>= 11)

Upgrading your database engine is one of the easiest ways to mitigate DDL migration pain. Starting with PostgreSQL 11, adding a column with a DEFAULT value no longer requires rewriting the entire table, turning a potentially catastrophic operation on large datasets into a near-instantaneous metadata change.

The only requirement in the DEFAULT clause must be a constant value.  NOT NULL constraints can be used in new columns, associated with default clauses.  When PostgreSQL does not need to rewrite the table, a simple and fast table metadata change solves your DDL.

The table rewrite is the most dangerous operation, especially when you are performing DDL on large tables.  So, you must plan the DDL following these rules.

2. Understand PostgreSQL Lock Mechanics

DDL operations almost always require heavy, exclusive locks (such as AccessExclusiveLock), which block all other operations, including reads (SELECT) and writes (INSERT, UPDATE, DELETE). PostgreSQL uses a first-in, first-out lock queue. If your migration script is blocked waiting for a lock, every single query that arrives after your migration script will also be forced to wait, effectively freezing your application.

I like to use this manual page to see and understand the lock conflicts. It’s very advisable for DBAs to understand how lock types conflict with one another. In this case, if you have a conflict (cells with X), a process trying to get the lock will wait for. 

3. Always use the Timeouts parameters

Never allow a migration script to wait indefinitely for a lock. You MUST enforce strict session-level timeouts to ensure that if a lock cannot be acquired immediately, the script backs off safely without queuing up and blocking application traffic.  So, you must use these two session-level timeout parameters:

-- cancel your query if it can't acquire the desired lock into 5 seconds

set lock_timeout to ‘5s’;

-- cancel your query if it is still in execution after 30 seconds

set statement_timeout to ‘30s’;

Adjust the timeout values according to your needs. These will provide protection for locks or long-running queries. But, this will be only a protection, because the migrations must be planned to avoid this.

4. Monitor and Eliminate Long Explicit Transactions

Long-running transactions, or connections left idle in transaction state, are the worst option for clean & safe migrations. They hold onto locks, preventing your migration from acquiring the exclusive access it needs. Identify these blocking processes and terminate them using administrative functions if necessary:

-- use pg_cancel_backend for session/pid executing queries

SELECT pg_cancel_backend(pid);

-- use pg_terminate_backend for session/pid in idle in transaction state

SELECT pg_terminate_backend(pid);

5. Prevent Long Reads and Writes During DDL Execution

Large batch updates or massive sequential reads right before or during a deployment will delay lock acquisition and extend the window of risk. Keep data modifications small and batched.

6. Always Use Conditional Clauses

Defensive migration scripts should minimize errors that cause unneeded transaction rollbacks. Make frequent use of conditional syntax (IF NOT EXISTS) when available:

ALTER TABLE table_name ADD COLUMN IF NOT EXISTS column_name data_type;

7. Run CONCURRENTLY Operations Outside the Migration File

Operations executed with the CONCURRENTLY keyword (such as index creation) cannot run within a standard multi-statement atomic transaction block. These must be decoupled from your primary automated migration workflow and run independently before the DDL migration process.

But to keep the compatibility between the environments, you can repeat these DDL using the IF NOT EXISTS, to avoid errors.

8. Monitor Everything in Real-Time during the migration deployment

Never deploy blind. Watch pg_stat_activity and your lock tables concurrently during deployment so you can intervene instantly if a lock queue begins to form.  In the migration apply process, I like to use this query below to see running queries, locks and vacuum/autovacuum:

select a.pid,a.leader_pid,a.usename,a.application_name,a.state,
       a.wait_event_type,a.wait_event,
 case a.wait_event_type when 'Lock' then pg_blocking_pids(a.pid) else
       array[]::integer[] end as blocker_pids,
       (clock_timestamp() - a.xact_start) as xage,
       (clock_timestamp() - a.query_start) as qage,
 av.phase as vac_phase,av.vac_perc,av.index_vacuum_count as index_vac_ct,
       a.query,a.backend_type,a.backend_xmin,
 case a.state 
   when 'active' then 'select pg_cancel_backend('||a.pid||');'
   else 'select pg_terminate_backend('||a.pid||');'
   end as ddl
from pg_catalog.pg_stat_activity a
left join lateral (
select pid,phase,index_vacuum_count,
round(coalesce(nullif(heap_blks_vacuumed,0),heap_blks_scanned)/nullif(heap_blks_total::numeric(20,2),0) * 100,2) as vac_perc 
from pg_catalog.pg_stat_progress_vacuum where pid = a.pid
) as av on true
where (state like 'idle in tran%' or state = 'active') and (clock_timestamp() - query_start) > '3 seconds'::interval and a.pid <> pg_backend_pid()
and (backend_type = 'client backend' or backend_type like '%vac%' or backend_type like '%cron%') 
order by state, xage desc;

And, if you need to see locks only in some desired tables, you can use the query below:

select l.pid,q.application_name,l.locktype,l.mode,t.relname,l.granted,q.state,
       age(clock_timestamp(),q.query_start) as query_age,
       age(clock_timestamp(),q.xact_start) as xact_age,
       q.query,q.usename,q.client_addr 
from pg_locks l,pg_class t,pg_stat_activity q  
where l.relation = t.oid and l.pid = q.pid
and t.relname in ('table_name_here')
order by state,query_age desc;

Safe Patterns for Common DDL Operations

Adding Columns and Modifying Types

Adding a nullable column or a column with a default value is safe in modern PostgreSQL. However, changing a column type explicitly using a USING clause often forces a full table rewrite, triggering a prolonged exclusive lock.  The rule here is use a constant value in the DEFAULT clause.

-- Safe: Instant metadata change only (PostgreSQL >= 11)

ALTER TABLE tb1 ADD COLUMN IF NOT EXISTS ativo boolean NOT NULL DEFAULT true;

-- Dangerous: Forces a complete table rewrite and heavy lock

ALTER TABLE tb1 ADD COLUMN verified_at TYPE timestamp NOT NULL DEFAULT now();

Setting Columns to NOT NULL Safely

Directly altering a column to SET NOT NULL forces PostgreSQL to scan the entire table to verify that no null values exist, blocking concurrent writes. To circumvent this, add a CHECK constraint as NOT VALID, validate it in a separate process, and then safely apply the NOT NULL constraint:

-- On the first migration
-- Step 1: Add a non-blocking check constraint marked NOT VALID

ALTER TABLE tb1 ADD CONSTRAINT check_auth_code_nn CHECK (auth_code IS NOT NULL) NOT VALID;

-- Outside migration process
-- Step 2: Validate the constraint (scans table with a low-level share lock, allowing writes)

ALTER TABLE tb1 VALIDATE CONSTRAINT check_auth_code_nn;

-- Using another migration after the completion of step #2
-- Step 3: Set NOT NULL (now fast because the check constraint guarantees data validity)

ALTER TABLE tb1 ALTER COLUMN auth_code SET NOT NULL;

The NOT VALID clause can be confusing, but the command does not scan the table (checking already existing rows) and can be committed immediately. After that, a VALIDATE CONSTRAINT command can be issued to verify that existing rows satisfy the constraint.  When you use the NOT VALID clause, only new or modified rows will be subject to this constraint.  Please don’t forget to VALIDATE after you have constraints in place at the proper moment. 

Creating Indexes Without Blocking Writes

A standard CREATE INDEX statement will lock out updates on the target table until the entire index is built. Instead, build the index concurrently before running your deployment tool, and then include a fast conditional statement inside your migration script:

-- Step 1: Run independently outside of your migration transaction block

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_tb1_user_id ON tb1 (user_id);

-- Step 2: Included in your automated migration framework (safe and instant)

CREATE INDEX IF NOT EXISTS idx_tb1_user_id ON tb1 (user_id);

Adding Foreign Keys Efficiently

Enforcing a foreign key relationship directly causes an intensive table scan on both the referencing and referenced tables. The correct approach mirrors the NOT NOT strategy: split the creation and the validation.

-- Step 1: Add the foreign key as NOT VALID (immediate execution, only validates new rows)

ALTER TABLE tb1 ADD CONSTRAINT fk_tb1_company FOREIGN KEY (company_id) REFERENCES companies(id) NOT VALID;

-- Step 2: Validate the constraint later in a separate, low-overhead transaction

ALTER TABLE tb1 VALIDATE CONSTRAINT fk_tb1_company;

Enforcing Primary Keys and Unique Constraints

Adding a unique or primary key constraint directly creates an underlying index behind an exclusive lock. The safe pattern relies on building a unique index concurrently first, then attaching it to the constraint:

-- Step 1: Build the index concurrently outside the migration transaction

CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS idx_tb1_uuid ON tb1(uuid);

-- Step 2: Attach the index inside your migration file (lightweight lock change)

ALTER TABLE tb1 ADD CONSTRAINT unique_tb1_uuid PRIMARY KEY USING INDEX idx_tb1_uuid;

Yes!!! You can create a primary key in a table with the database in production.

A Safe Migration Template

By synthesizing these best practices, we can construct a robust, production-grade template for our automated migration deployment tools. This structural pattern isolates DDL modifications, enforces defensive timeouts, and batches commands efficiently to keep lock durations under a fraction of a second:

-- outside and before migration

CREATE INDEX CONCURRENTLY idx_orders_reference ON orders (refer_id);
CREATE UNIQUE INDEX CONCURRENTLY orders_unq01 ON orders (external_order);

--------------- migration begins here ---------------

BEGIN;

-- Enforce strict defensive session limits

SET lock_timeout TO '15s';
SET statement_timeout TO '30s';

-- Group all DDL modifications for the target table into a single block

ALTER TABLE orders 
    ADD COLUMN IF NOT EXISTS status_code int2 NOT NULL DEFAULT 0,
    ADD CONSTRAINT orders_check_01 CHECK (biller_id IS NOT NULL) NOT VALID,
    ADD CONSTRAINT fk_orders_customer 
        FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;

-- You already created this index outside migration, but you need to sync to all environments

CREATE INDEX IF NOT EXISTS idx_orders_reference ON orders (refer_id);

-- Bind previously built concurrent indexes to constraints (fast, just a metadata change)

ALTER TABLE orders ADD CONSTRAINT orders_unique_1 UNIQUE USING INDEX orders_unq01;
COMMIT;

--------------- migration ends here ---------------
-- outside and after migration, making sure that your past data is ready to be constrained

ALTER TABLE orders 
    VALIDATE CONSTRAINT orders_check_01,
    VALIDATE CONSTRAINT fk_orders_customer

-- you are happy here