All Products
Search

This Product

    ApsaraDB RDS:Online DDL (rds_online_ddl)

    Document Center

    ApsaraDB RDS:Online DDL (rds_online_ddl)

    Last Updated:Mar 28, 2026

    Changing a column to a type that is not binary-compatible — such as converting an int4 column to int8 — forces PostgreSQL to rewrite the entire table. During the rewrite, the table is locked and all reads and writes are blocked. While many DDL operations in PostgreSQL (such as CREATE INDEX CONCURRENTLY) support concurrent execution, non-binary-compatible column type changes still require a full table rewrite with a lock. The rds_online_ddl extension eliminates this lock window, letting you change column types on live tables without interrupting application traffic.

    Prerequisites

    Before you begin, make sure your instance meets all of the following requirements:

    • Major engine version: PostgreSQL 12 or later

    • Minor engine version: 20250830 or later

    • Instance parameter: wal_level is set to logical. To change this parameter, see Modify instance parameters.

    • Table schema: The target table has a primary key or a UNIQUE constraint

    • Account: A privileged account is created on the instance

    Install and remove the extension

    Important

    Run these commands using a privileged account.

    Install:

    CREATE EXTENSION rds_online_ddl;

    To verify the installation, run:

    SELECT * FROM pg_extension;

    Remove:

    DROP EXTENSION rds_online_ddl;

    Change a column type online

    Call rds_online_ddl.alter_table() with the fully qualified table name and an ALTER TABLE statement:

    SELECT rds_online_ddl.alter_table('<schema>.<table>', '<ALTER TABLE statement>');
    ParameterDescription
    <schema>.<table>Fully qualified table name, for example public.orders
    <ALTER TABLE statement>A standard ALTER COLUMN ... TYPE clause, for example ALTER COLUMN id TYPE int8

    Example: upgrade an integer column from int4 to int8

    1. Create a test table and load sample data.

      CREATE TABLE test (id int4 PRIMARY KEY, info TEXT);
INSERT INTO test SELECT x, repeat(x::text, 2) FROM generate_series(1, 1000000) AS x;

    2. Run the online column type change.

      SELECT rds_online_ddl.alter_table('public.test', 'ALTER COLUMN id TYPE int8');

    3. (Optional) Monitor progress. For large tables, the operation can take a long time. To track its status, query the progress view:

      FieldDescription
      insert_initialRows of historical data copied so far
      nindexes_builtIndexes built on the temporary table
      nindexes_totalTotal indexes to build
      insert_applied / update_applied / delete_appliedIncremental changes applied per operation type
      insert_decoded / update_decoded / delete_decodedIncremental changes decoded per operation type
      SELECT * FROM rds_online_ddl.pg_stat_progress_online_ddl;

    Before running in production

    • Test first. Run the same operation in a test environment and verify the results before touching production data.

    • Check disk space. The operation requires free space of at least 2x the total size of the table and its indexes. For example, if the table and its indexes total 10 GB, make sure you have at least 20 GB free.

    • Back up your data. Verify that you have a valid backup before proceeding.

    How it works

    The extension avoids long-term table locks by working on a temporary copy of the table and swapping it in at the end. The exclusive lock in step 6 is held only for the duration of the swap.

    The full process:

    1. Create a temporary table with the same schema as the original table.

    2. Run the specified ALTER COLUMN TYPE operation on the temporary table.

    3. Copy historical data from the original table into the temporary table.

    4. Build all indexes from the original table on the temporary table.

    5. Use logical decoding to sync incremental changes (inserts, updates, and deletes) generated during the previous steps into the temporary table, ensuring final data consistency.

    6. Acquire a brief exclusive lock on the original table and update its schema.

    7. Swap the underlying files of the original table and the temporary table, including all indexes.

    8. Drop the temporary table and commit the transaction.

    The operation is atomic. If it fails or is interrupted at any point, the table rolls back to its original schema automatically — no manual recovery is needed.

    Limitations

    LimitationDetails
    Unsupported table typesTables with foreign key constraints are not supported. Partitioned tables are not supported. Only regular tables are supported.
    StorageRequires approximately 2x the total size of the original table and its indexes in free disk space.
    USING clauseThe USING clause of ALTER TABLE may not be fully supported due to logical replication limitations. Test it in a non-production environment before using it in production.

    Troubleshooting

    SymptomLikely causeResolution
    alter_table() fails immediatelyThe target table does not have a primary key or UNIQUE constraintAdd a PRIMARY KEY or UNIQUE constraint to the table before running the operation
    alter_table() fails immediatelywal_level is not set to logicalSet wal_level to logical in the instance parameters and restart the instance
    Insufficient disk space errorFree disk space is less than 2x the table sizeFree up disk space or expand the instance storage before proceeding

    Billing

    The rds_online_ddl extension is free.