Обсуждение: Adding Column on Huge Table

Moin Akther wrote:
> We want to add a column on table size of 1.5TB, we have to perform this activity with minimal downtime. 
> 
> What are the best practises to add a column on huge table ?
> 
> How much time alter statement will take to add column ?
> 
> Any alternate solution other than alter statement to add column ?
> 
> Will adding a new column on a huge table generate huge WAL files ?

As a quick experiment would have told you, adding a column without a DEFAULT
clause is very fast.  Since v11, it is fast even if the new column has a
DEFAULT clause.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

My experience is that adding a column is a rather quick operation even
on quite large tables, _unless_ the column has a default value, in which
case that default value is set for every row. In that case, the time
required for the operation converges with that of a mass-UPDATE.

On Tue, Mar 19, 2019 at 09:53:16PM +0100, Andrzej Zawadzki wrote:

> On 19.03.2019 16:56, Moin Akther wrote:
> 
>     Dear Experts,
> 
>     We want to add a column on table size of 1.5TB, we have to perform this
>     activity with minimal downtime. 
> 
>     What are the best practises to add a column on huge table ?
> 
>     How much time alter statement will take to add column ?
> 
>     Any alternate solution other than alter statement to add column ?
> 
>     Will adding a new column on a huge table generate huge WAL files ?
> 
> 
> No offense, but don't you have any test environment?
> 
> Try that or maybe you have so show times.
> 
> 
> --
> 
> Andrzej Zawadzki
> 

-- 
Alex Balashov | Principal | Evariste Systems LLC

Tel: +1-706-510-6800 / +1-800-250-5920 (toll-free) 
Web: http://www.evaristesys.com/, http://www.csrpswitch.com/

Alex Balashov schrieb am 19.03.2019 um 21:52:
> My experience is that adding a column is a rather quick operation even
> on quite large tables, _unless_ the column has a default value, in which
> case that default value is set for every row. In that case, the time
> required for the operation converges with that of a mass-UPDATE.

Which has changed with Postgres 11, where adding a default value does not update all rows in the table.