Slow Execution of ALTER TABLE on Large Table when changing column type

Last edited: 1/17/2025

If you encounter slow execution of the ALTER TABLE operation on a large table when changing a column data type, consider the following alternative approach.

Alternative Approach:

  1. Add a New Column with the New Type: ALTER TABLE "table_name" ADD COLUMN "new_column_name" new_data_type;

  2. Copy Values from the First Column to the Second: UPDATE "table_name" SET "old_column_name" = "new_column_name"::new_data_type;

  3. Drop the Old Column: ALTER TABLE "table_name" DROP COLUMN "old_column_name";

Why Use This Approach? The long execution time for the ALTER TABLE operation can be attributed to the large size of the table. This segmented approach helps in:

  • Efficiency: The process is more efficient as it avoids prolonged transactions.
  • Minimizing Disruption: Migrating data systematically minimizes the impact on other operations and users.

Additional Recommendations:

  • Set session statement_timeout to 0 to prevent potential transaction timeouts.

  • Monitor currently blocked database transactions during the process using the provided script:


_27
create view public.lock_monitor as
_27
select
_27
coalesce(
_27
blockingl.relation::regclass::text,
_27
blockingl.locktype
_27
) as locked_item,
_27
now() - blockeda.query_start as waiting_duration,
_27
[blockeda.pid](http://blockeda.pid/) as blocked_pid,
_27
blockeda.query as blocked_query,
_27
blockedl.mode as blocked_mode,
_27
[blockinga.pid](http://blockinga.pid/) as blocking_pid,
_27
blockinga.query as blocking_query,
_27
blockingl.mode as blocking_mode
_27
from
_27
pg_locks blockedl
_27
join pg_stat_activity blockeda on [blockedl.pid](http://blockedl.pid/) = [blockeda.pid](http://blockeda.pid/)
_27
join pg_locks blockingl on (
_27
blockingl.transactionid = blockedl.transactionid
_27
or blockingl.relation = blockedl.relation
_27
and blockingl.locktype = blockedl.locktype
_27
)
_27
and [blockedl.pid](http://blockedl.pid/) <> [blockingl.pid](http://blockingl.pid/)
_27
join pg_stat_activity blockinga on [blockingl.pid](http://blockingl.pid/) = [blockinga.pid](http://blockinga.pid/)
_27
and blockinga.datid = blockeda.datid
_27
where
_27
not blockedl.granted
_27
and blockinga.datname = current_database();

Notes: Execute these steps during a maintenance window or low-traffic period to minimize disruption.