pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAdding Column on Huge Table
6+ messages / 6 participants
[nested] [flat]
* Adding Column on Huge Table
@ 2019-03-19 15:56 Moin Akther <moindba@hotmail.com>
0 siblings, 3 replies; 6+ messages in thread
From: Moin Akther @ 2019-03-19 15:56 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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 ?
Thanks and Best Regards,
Akther
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Adding Column on Huge Table
@ 2019-03-19 17:02 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Moin Akther <moindba@hotmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: Laurenz Albe @ 2019-03-19 17:02 UTC (permalink / raw)
To: Moin Akther <moindba@hotmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Adding Column on Huge Table
@ 2019-03-19 20:52 Alex Balashov <abalashov@evaristesys.com>
parent: Andrzej Zawadzki <zawadaa@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Alex Balashov @ 2019-03-19 20:52 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Adding Column on Huge Table
@ 2019-03-19 20:53 Andrzej Zawadzki <zawadaa@gmail.com>
parent: Moin Akther <moindba@hotmail.com>
2 siblings, 1 reply; 6+ messages in thread
From: Andrzej Zawadzki @ 2019-03-19 20:53 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<div class="moz-cite-prefix">On 19.03.2019 16:56, Moin Akther wrote:<br>
</div>
<blockquote type="cite"
cite="mid:HK0PR03MB2977B9460C4CC7D0383D05BDAD400@HK0PR03MB2977.apcprd03.prod.outlook.com">
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
<div style="font-family: "Segoe UI", "Helvetica
Neue", sans-serif; font-size: 11pt; color: rgb(38, 114,
236);">
Dear Experts,</div>
<div style="font-family: "Segoe UI", "Helvetica
Neue", sans-serif; font-size: 11pt; color: rgb(38, 114,
236);">
<br>
</div>
<div style="font-family: "Segoe UI", "Helvetica
Neue", sans-serif; font-size: 11pt; color: rgb(38, 114,
236);">
We want to add a column on table size of 1.5TB, we have to
perform this activity with minimal downtime. </div>
<div style="font-family: "Segoe UI", "Helvetica
Neue", sans-serif; font-size: 11pt; color: rgb(38, 114,
236);">
<br>
</div>
<div style="font-family: "Segoe UI", "Helvetica
Neue", sans-serif; font-size: 11pt; color: rgb(38, 114,
236);">
What are the best practises to add a column on huge table ?</div>
<div style="font-family: "Segoe UI", "Helvetica
Neue", sans-serif; font-size: 11pt; color: rgb(38, 114,
236);">
<br>
</div>
<div style="font-family: "Segoe UI", "Helvetica
Neue", sans-serif; font-size: 11pt; color: rgb(38, 114,
236);">
How much time alter statement will take to add column ?</div>
<div style="font-family: "Segoe UI", "Helvetica
Neue", sans-serif; font-size: 11pt; color: rgb(38, 114,
236);">
<br>
</div>
<div style="font-family: "Segoe UI", "Helvetica
Neue", sans-serif; font-size: 11pt; color: rgb(38, 114,
236);">
Any alternate solution other than alter statement to add column
?</div>
<div style="font-family: "Segoe UI", "Helvetica
Neue", sans-serif; font-size: 11pt; color: rgb(38, 114,
236);">
<br>
</div>
<div style="font-family: "Segoe UI", "Helvetica
Neue", sans-serif; font-size: 11pt; color: rgb(38, 114,
236);">
Will adding a new column on a huge table generate huge WAL files
?</div>
<div style="font-family: "Segoe UI", "Helvetica
Neue", sans-serif; font-size: 11pt; color: rgb(38, 114,
236);">
<br>
</div>
<div id="signature">
<div id="divtagdefaultwrapper" dir="ltr" style=""><font
face="Calibri, Arial, Helvetica, sans-serif, EmojiFont,
Apple Color Emoji, Segoe UI Emoji, NotoColorEmoji, Segoe UI
Symbol, Android Emoji, EmojiSymbols" color="#2672ec"><span
id="ms-rterangepaste-start" style="font-size: 12pt;"></span></font></div>
</div>
</blockquote>
<p>No offense, but don't you have any test environment?</p>
<p>Try that or maybe you have so show times.</p>
<p><br>
</p>
<p>-- <br>
</p>
<p>Andrzej Zawadzki<br>
</p>
</body>
</html>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Adding Column on Huge Table
@ 2019-03-19 21:06 Thomas Kellerer <spam_eater@gmx.net>
parent: Alex Balashov <abalashov@evaristesys.com>
0 siblings, 0 replies; 6+ messages in thread
From: Thomas Kellerer @ 2019-03-19 21:06 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Adding Column on Huge Table
@ 2019-03-20 02:00 Shreeyansh Dba <shreeyansh2014@gmail.com>
parent: Moin Akther <moindba@hotmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: Shreeyansh Dba @ 2019-03-20 02:00 UTC (permalink / raw)
To: Moin Akther <moindba@hotmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi Moin,
Go through below link hope this will help you.
https://leopard.in.ua/2016/09/20/safe-and-unsafe-operations-postgresql#.XJGan6AzbIU
Thanks & Regards,
*Shreeyansh DBA Team*
www.shreeyansh.com
On Tue, Mar 19, 2019 at 9:26 PM Moin Akther <moindba@hotmail.com> 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 ?
>
> Thanks and Best Regards,
> *Akther*
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2019-03-20 02:00 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-03-19 15:56 Adding Column on Huge Table Moin Akther <moindba@hotmail.com>
2019-03-19 17:02 ` Laurenz Albe <laurenz.albe@cybertec.at>
2019-03-19 20:53 ` Andrzej Zawadzki <zawadaa@gmail.com>
2019-03-19 20:52 ` Alex Balashov <abalashov@evaristesys.com>
2019-03-19 21:06 ` Thomas Kellerer <spam_eater@gmx.net>
2019-03-20 02:00 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox