Re: How to add columns faster

On Mon, Mar 4, 2024 at 12:43 AM Christophe Pettus <xof(at)thebuild(dot)com> wrote:

>
> > On Mar 3, 2024, at 11:06, yudhi s <learnerdatabase99(at)gmail(dot)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

Thanks for the clarification. In case of adding the column as volatile
default (like current_timestamp function as default) or say adding NOT NULL
column with some conditional population of existing values will be a full
table rewrite. In such scenarios, the full table rewrite operation is going
to take a long time , so what will be the fastest way to achieve that
with minimal to no downtime?

Apology if this is dumb one, but considering the partitions in postgres are
as good as different tables, can we add the new column someway at the table
level and add the columns to each of the partitions individually and then
attach or it has to happen at one shot only?