Updated on 2026-09-23 GMT+08:00

RDS for PostgreSQL Instant ADD COLUMN Best Practices

Context

In traditional PostgreSQL versions (earlier than 11), when ALTER TABLE ADD COLUMN is executed, the database needs to fill each row in the table with the default value or NULL for the newly added column. When a table contains a large amount of data (for example, hundreds of millions of records), this operation may cause the following issues:

Long table lock: The ACCESS EXCLUSIVE lock must be held on the table throughout the DDL execution, blocking all read and write operations.
A large number of WAL logs: Writing data row by row generates a large number of WAL logs, increasing the primary/standby replication delay.
Long-running transaction: A long-running transaction may cause transaction ID wraparound.
Service interruption: For core service tables, long-running DDL operations may cause service unavailability.

RDS for PostgreSQL, based on PostgreSQL 11 and later versions, supports the Instant ADD COLUMN feature, which can effectively address the preceding issues.

Instant ADD COLUMN Principles

PostgreSQL 11 introduced the Instant ADD COLUMN feature. Its core principles are as follows:

Metadata change: When a column is added, only the metadata (system catalog) of the table is modified, and the table data is no longer rewritten row by row.
Lazy materialization: The values of the new column are calculated and materialized only when they are read for the first time, instead of being written during DDL execution.
Default value optimization: For columns with a VOLATILE default value, the default value expression is stored in the metadata and dynamically calculated during queries.
NOT NULL support: PostgreSQL 11 also supports the instant addition of columns with NOT NULL constraints and default values, which is implemented through constraint check optimization.

This feature ensures that the execution time of the ADD COLUMN operation is controlled within milliseconds, regardless of the table data volume, truly achieving column addition in seconds.

Scenarios

Adding columns to large tables: A table containing tens of millions of rows requires new columns to be added quickly.
Online services: The production environment does not allow tables to be locked for a long time, and the impact of DDL operations on services needs to be minimized.
Adding nullable columns: A column that allows NULL values can be added without specifying a default value.
NOT NULL columns with default values: PostgreSQL 11 and later versions support the addition of NOT NULL columns with constant default values in seconds.

Constraints

An index cannot be created when a column is added. (The operations need to be performed separately.)
A column with a VOLATILE function default value and a NOT NULL constraint simultaneously cannot be added. (Two separate steps are required.)
If a table has an inheritance relationship, its child tables will not automatically inherit the metadata changes of the new column.
The first full table scan following the column addition may trigger lazy materialization, resulting in certain I/O overhead.
This feature does not apply to ADD COLUMN ... GENERATED ALWAYS AS (generated columns).

Common Operations

Scenario 1: Adding a Nullable Column (Fastest, Completed in Milliseconds)
```
ALTER TABLE orders ADD COLUMN remark TEXT;
```
This operation only modifies the system catalog and is completed instantly, with zero impact on services.

Scenario 2: Adding a Column with a Default Value
```
ALTER TABLE orders ADD COLUMN status INT DEFAULT 0;
```
In PostgreSQL 11 and later versions, this operation is also completed in seconds. The default value is stored in the metadata and dynamically returned during queries.

Scenario 3: Adding a NOT NULL Column with a Default Value
```
ALTER TABLE orders ADD COLUMN is_deleted BOOLEAN NOT NULL DEFAULT FALSE;
```
In PostgreSQL 11 and later versions, this operation can be completed in seconds without rewriting table data.

Scenario 4: Adding a Column and Creating an Index (Step-by-Step)
1. Add a column in seconds.
```
ALTER TABLE orders ADD COLUMN user_tag TEXT;
```
2. Create an index online (without blocking read and write operations).
```
CREATE INDEX CONCURRENTLY idx_orders_user_tag ON orders(user_tag);
```
Note: CREATE INDEX CONCURRENTLY does not hold an exclusive lock and can be executed online.

Scenario 5: Handling VOLATILE Default Values

Incorrect method (instant addition not supported)

-- ALTER TABLE orders ADD COLUMN create_time TIMESTAMPTZ NOT NULL DEFAULT NOW();

Correct method (in two steps)

ALTER TABLE orders ADD COLUMN create_time TIMESTAMPTZ DEFAULT NULL;
ALTER TABLE orders ALTER COLUMN create_time SET DEFAULT NOW();
UPDATE orders SET create_time = NOW() WHERE create_time IS NULL;

Suggestions on Best Practices

Version confirmation: Ensure that the RDS for PostgreSQL instance version is 11 or later. You can check the version using SELECT version();.
Advanced planning: Perform DDL operations during off-peak hours. Even if the operations are completed within seconds, it is recommended that they be performed during off-peak hours.
Step-by-step operations: For complex changes (adding columns, creating indexes, and adding constraints), split the operations into multiple independent steps. Perform each step and verify it before proceeding to the next step.
Lazy materialization optimization: After adding a column, you can perform a VACUUM operation to trigger materialization in advance, preventing performance jitter during the first query.
Lock wait monitoring: Before executing a DDL statement, you can use the pg_locks view to monitor the current lock status and ensure that no long-running transactions block the operation.
Backup verification: Before making DDL changes to important tables, you are advised to verify the changes in a read replica or test environment.
Avoiding frequent column additions: Although Instant ADD COLUMN offers excellent performance, frequent table structure modifications can still increase system catalog bloat. It is recommended that you properly plan the table structure.
Using CONCURRENTLY: Use CREATE INDEX CONCURRENTLY when adding indexes to avoid blocking services.

FAQ

Question 1: After an instant column addition, when is the data of the new column written to the disk?
New column data uses a lazy materialization mechanism. It is calculated and written row by row only when the entire table is scanned for the first time or the column is accessed. Materialization can be triggered in advance using VACUUM.
Question 2: Does the table file size increase immediately after a column is added?
No. Instant ADD COLUMN only modifies the metadata, leaving the table file size unchanged. The data will occupy additional space only when lazy materialization occurs.
Question 3: Does Instant ADD COLUMN support all data types?
It supports all standard data types. However, for NOT NULL columns with complex default value expressions (such as VOLATILE functions), you need to perform the operation step by step.
Question 4: Will primary/standby replication be delayed after a column is added?
Instant ADD COLUMN generates only a small number of WAL logs for metadata changes. Therefore, the primary/standby replication delay is minimal. Unlike traditional methods, it does not generate a large number of WAL logs, which can cause delays.
Question 5: How do I check whether my instance supports Instant ADD COLUMN?
Run the SELECT version(); command to check whether the PostgreSQL version is 11 or later. RDS for PostgreSQL 11 and later versions support this feature.
Question 6: Does Instant ADD COLUMN require special permissions?
No special permissions are required. Any user with the ALTER permission can perform this operation. The permission requirements are the same as those for the ALTER TABLE ADD COLUMN statement.