> On Mar 3, 2024, at 11:06, yudhi s <learnerdatabase99@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
systemcatalogs; 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
ofthe 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
itsindexes to be rewritten.
https://www.postgresql.org/docs/current/sql-altertable.html