| From: | Christophe Pettus <xof(at)thebuild(dot)com> |
|---|---|
| To: | yudhi s <learnerdatabase99(at)gmail(dot)com> |
| Cc: | pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
| Subject: | Re: How to add columns faster |
| Date: | 2024-03-03 19:13:11 |
| Message-ID: | 017A6342-5001-4F43-9C3B-FD4C2CC866BE@thebuild.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
> 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.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Ron Johnson | 2024-03-03 19:16:42 | Re: How to add columns faster |
| Previous Message | yudhi s | 2024-03-03 19:06:09 | How to add columns faster |