Best practices for instant DDL to add columns in ApsaraDB RDS for PostgreSQL

The lock queue cascade

When ALTER TABLE cannot immediately acquire the lock, it enters a wait queue. PostgreSQL then requires every subsequent query on that table to wait behind the ALTER TABLE — even queries that would normally run concurrently with each other. A DDL statement that is itself blocked therefore becomes a barrier that blocks all traffic behind it, causing a complete TPS drop until the lock is released.

Operations that trigger a full table rewrite

Most ADD COLUMN operations complete in seconds without rewriting the table:

• No default value: Completes within seconds in all PostgreSQL versions.

• Non-volatile default value (for example, a constant string or integer): Completes within seconds on PostgreSQL 11 and later. The default is stored in table metadata rather than written to every row. For details, see PostgreSQL documentation.

Solution 1: Cancel the DDL operation on lock wait timeout

Set a transaction-level lock_timeout. If the DDL cannot acquire the exclusive lock within the specified period, it is automatically canceled rather than continuing to block subsequent queries.

BEGIN;

SET LOCAL lock_timeout = 500;  -- Cancel if the lock is not acquired within 500 ms.
ALTER TABLE <table_name> ADD COLUMN <column_name> VARCHAR;

COMMIT;

Solution 2: Cancel and automatically retry until success (recommended)

Combine a lock_timeout with an automatic retry loop. The DDL retries every second until it succeeds, so each failed attempt blocks the table for at most lock_timeout milliseconds.

DO $$
DECLARE
    msg text;
BEGIN
    LOOP
        BEGIN
            PERFORM pg_sleep(1);              -- Wait 1 second before each attempt.
            SET LOCAL lock_timeout = 500;     -- Cancel if the lock is not acquired within 500 ms.
            ALTER TABLE <table_name> ADD COLUMN <column_name> VARCHAR;
            EXIT;                             -- Exit the loop on success.
        EXCEPTION WHEN OTHERS THEN
            GET STACKED DIAGNOSTICS msg = MESSAGE_TEXT;
            RAISE NOTICE 'failed, error: %s', msg;
        END;
    END LOOP;
    RAISE NOTICE 'success!';
END;
$$;

This approach is preferred over Solution 1 for production use: it eventually completes without manual intervention, and each failed attempt blocks the table for no longer than lock_timeout milliseconds.

Terminate the blocking connection (optional)

If a specific connection is blocking the DDL operation, identify and terminate it.

1. Get the process ID (PID) of the current session.

   SELECT pg_backend_pid();

2. Run the DDL operation.

   ALTER TABLE <table_name> ADD COLUMN <column_name> VARCHAR;

3. Query the process that blocks the DDL operation.

   SELECT pg_blocking_pids(<The PID obtained in step 1>);

4. Terminate the blocking process. Choose one of the following:

   • Cancel the current query without disconnecting the session:

     SELECT pg_cancel_backend(<pid_from_step_3>);

   • Forcibly disconnect the session:

     SELECT pg_terminate_backend(<pid_from_step_3>);

   Note

   Terminating an autovacuum process may fail. PostgreSQL protects autovacuum workers from forced termination in some configurations.