Does ALTER on PostgreSQL, Oracle table (respectively) create intermediate copy of the table?

Posted on

Question :

I’ve heard MySQL admin claim that ALTER on MySQL table creates an intermediate copy of the table during the operation (don’t know if the type of table involved were supposed to be MyISAM or InnoDB), which is a problem when working on huge tables.

Does this apply to ALTER on PG db tables and Oracle db tables, respectively?

What are high-level mechanics of ALTER (I’m esp. interested in PG) that can impact db for huge tables (one legacy Postgres DB I inherited has a table that weights nearly 400GB.., entire DB is ~1TB)?

Answer :

From the Postgres manual:

Adding a column:

When a column is added with ADD COLUMN, all existing rows in the table are initialized with the column’s default value (NULL if no DEFAULT clause is specified). If there is no DEFAULT clause, this is merely a metadata change and does not require any immediate update of the table’s data; the added NULL values are supplied on readout, instead.

(Emphasis mine)

Changing a column’s type:

Adding a column with a DEFAULT clause or changing the type of an existing column will require the entire table and its indexes to be rewritten. As an exception when changing the type of an existing column, if the USING clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed; but any indexes on the affected columns must still be rebuilt.

Note the part about indexes being rebuilt for any column change.

Dropping a column:

The DROP COLUMN form does not physically remove the column, but simply makes it invisible to SQL operations. Subsequent insert and update operations in the table will store a null value for the column. Thus, dropping a column is quick but it will not immediately reduce the on-disk size of your table, as the space occupied by the dropped column is not reclaimed.

Leave a Reply

Your email address will not be published. Required fields are marked *