> On Mar 3, 2024, at 11:06, yudhi s <learnerdatabas...@gmail.com> wrote:
> as the column addition using the traditional "Alter table" command in
> postgres looks to be a full table rewrite
That's not always (or, really, usually) true. Adding a new column in any
recent version of PostgreSQL just alters the system catalogs; it does not
rewrite the table. Make sure the new column is either NULL-able, or has a
simple DEFAULT expression (specifically, not using a VOLATILE function). Per
the documentation:
> When a column is added with ADD COLUMN and a non-volatile DEFAULT is
> specified, the default 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.
>
> 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.
https://www.postgresql.org/docs/current/sql-altertable.html
