From 11bd8318602fc2282a6201f714c15461dc2009c6 Mon Sep 17 00:00:00 2001 From: =?utf8?q?=C3=81lvaro=20Herrera?= Date: Fri, 14 Mar 2025 20:44:59 +0100 Subject: [PATCH] doc: Explain more thoroughly when a table rewrite is needed Author: Masahiro Ikeda Reviewed-by: Robert Treat Discussion: https://postgr.es/m/00e6eb5f5c793b8ef722252c7a519c9a@oss.nttdata.com --- doc/src/sgml/ddl.sgml | 8 ++---- doc/src/sgml/ref/alter_table.sgml | 46 +++++++++++++++++-------------- 2 files changed, 29 insertions(+), 25 deletions(-) diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index ae156b6b1cd..cdb1a07e9d3 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -1650,17 +1650,15 @@ ALTER TABLE products ADD COLUMN description text; - From PostgreSQL 11, adding a column with - a constant default value no longer means that each row of the table - needs to be updated when the ALTER TABLE statement + Adding a column with a constant default value does not require each row of + the table to be updated when the ALTER TABLE statement is executed. Instead, the default value will be returned the next time the row is accessed, and applied when the table is rewritten, making the ALTER TABLE very fast even on large tables. - However, if the default value is volatile (e.g., - clock_timestamp()) + If the default value is volatile (e.g., clock_timestamp()) each row will need to be updated with the value calculated at the time ALTER TABLE is executed. To avoid a potentially lengthy update operation, particularly if you intend to fill the column diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index dceb7a7593c..4f15b89a98f 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -1421,30 +1421,36 @@ WITH ( MODULUS numeric_literal, REM When a column is added with ADD COLUMN and a - non-volatile DEFAULT is specified, the default is + non-volatile DEFAULT is specified, the default value is evaluated at the time of the statement and the result stored in the - table's metadata. That value will be used for the column for all existing - rows. If no DEFAULT is specified, NULL is used. In - neither case is a rewrite of the table required. + table's metadata, where it will be returned when any existing rows are + accessed. The value will be only applied when the table is rewritten, + making the ALTER TABLE very fast even on large tables. + If no column constraints are specified, NULL is used as the + DEFAULT. In neither case is a rewrite of the table + required. - Adding a column with a volatile DEFAULT 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. However, indexes must always be rebuilt unless the system can - verify that the new index would be logically equivalent to the existing - one. For example, if the collation for a column has been changed, an index - rebuild is always required because the new sort order might be different. - However, in the absence of a collation change, a column can be changed - from text to varchar (or vice versa) without - rebuilding the indexes because these data types sort identically. - Table and/or index rebuilds may take a - significant amount of time for a large table; and will temporarily require - as much as double the disk space. + Adding a column with a volatile DEFAULT + (e.g., clock_timestamp()), a generated column + (e.g., GENERATED BY DEFAULT AS IDENTITY), a domain + data type with constraints will require the entire table and its + indexes to be rewritten, as will changing the type of an existing + column. 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. However, indexes must always be rebuilt unless the system + can verify that the new index would be logically equivalent to the + existing one. For example, if the collation for a column has been + changed, an index rebuild is required because the new sort + order might be different. However, in the absence of a collation + change, a column can be changed from text to + varchar (or vice versa) without rebuilding the indexes + because these data types sort identically. Table and/or index + rebuilds may take a significant amount of time for a large table, + and will temporarily require as much as double the disk space. -- 2.30.2