On Mon, Mar 4, 2024 at 12:43 AM Christophe Pettus <xof@xxxxxxxxxxxx> wrote:
> On Mar 3, 2024, at 11:06, yudhi s <learnerdatabase99@xxxxxxxxx> 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?
