Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sRoQZ-001IuG-C2 for pgsql-general@arkaria.postgresql.org; Thu, 11 Jul 2024 07:41:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sRoQX-00BvxJ-Q0 for pgsql-general@arkaria.postgresql.org; Thu, 11 Jul 2024 07:41:17 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sRoQX-00BvxB-9b for pgsql-general@lists.postgresql.org; Thu, 11 Jul 2024 07:41:17 +0000 Received: from mail-vk1-xa2c.google.com ([2607:f8b0:4864:20::a2c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sRoQP-001V8v-Su for pgsql-general@lists.postgresql.org; Thu, 11 Jul 2024 07:41:15 +0000 Received: by mail-vk1-xa2c.google.com with SMTP id 71dfb90a1353d-4f2fcaa2a3aso216024e0c.0 for ; Thu, 11 Jul 2024 00:41:09 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1720683668; x=1721288468; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=FV1EHYx3jzPI38XhkrWlvvCF9A7YqLyskL9rmJvVIa8=; b=lOAnc0sr6azRGeHGqeL1oAjCVjtQpJ/pyKN4yAzF7xNsc/M8hivIsZo7Kr2dbEY1LQ TMhVXl6yE+Rf8q2XxEMvuPcffQt04WcUJLdINuhWtGJzWIN+YOQm71xW93X98zcO8pXe +bqVQQKj4nBZf+v1qJ9c55QXDiR4wJIbs2Xb8T8KxMsg5TajsEjePlWqQGrqc2icpn6Y d/KN8sAOH9fqMG1REr3WKvVaI+342JohSUxJaRG2rAmMV+xOpvEqvHuZWmKbndeMp8cO CJhx2kQGc8RbocnyHg3+ThRHwwLSfuiKraygbYz61OZV1NCtkrvttdSbKWtZyQJUg4tz AzCg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1720683668; x=1721288468; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=FV1EHYx3jzPI38XhkrWlvvCF9A7YqLyskL9rmJvVIa8=; b=bCLCtxtUpto7LOX7K6y5p+WMdahZ0KT7hTAHo9FGT8En55dwOGc6vNbGm04fHJ4DSg O5uzCS3pBxNV10xlike371Jkg3FQi/n5iOVcs13iK/rhRfq2RYTmiEieAzk+1tuL8jTZ prc+A6wZfUwVqHK70pU94hHth2Djeb3icu9TQ3HrSjMs0S1M1DmCn9o73d0ngo9DDMl/ z937qYn4v6ueyPXEhO/5DQj7vF6WIV8JqPOgIR0lDcvzwyfR1C4xEw/TgMV9X+7YmJf7 KFfXl4cxXxh2JpDYmeM8AlLXJoLkAIcOmdDCPHT29O/WrFOXkRN0PlB+Jg4ZJJ3RKusy eeCg== X-Gm-Message-State: AOJu0YxK0q0KSqtLsMV0fBSJXXx1Y+io18xxoFkSSjjNSthRYB+/wvZe y5eyyT0hkQZZb6w1N/NpZmTrMI4ZDzXoENn3KuO1jD+TMjOKr9M/OwSQp1twXXStFhOnyzoB01e Q/AXAamO8ONGILYCtb6g4YNfrrBM= X-Google-Smtp-Source: AGHT+IFEqlP6l9ISu/xrzdpidyRyoSIyRSN3EGMiAk/j9k0H3/gq+PRamZpPEcQR58Fc0oEpVQy/9eKMOMcDzOhyOFg= X-Received: by 2002:a05:6122:1d45:b0:4ef:5b2c:df25 with SMTP id 71dfb90a1353d-4f33f25bc54mr10064062e0c.6.1720683668530; Thu, 11 Jul 2024 00:41:08 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: sud Date: Thu, 11 Jul 2024 13:10:57 +0530 Message-ID: Subject: Re: Dropping column from big table To: Ron Johnson Cc: pgsql-general Content-Type: multipart/alternative; boundary="000000000000d57111061cf3e22b" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000d57111061cf3e22b Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, 11 Jul, 2024, 12:46 pm Ron Johnson, wrote= : > On Wed, Jul 10, 2024 at 11:28=E2=80=AFPM sud wrote: > >> >> >> >> Thank you so much. When you said *"you can execute one of the forms of >> ALTER TABLE that performs a rewrite* >> *of the whole table."* Does it mean that post "alter table drop column" >> the vacuum is going to run longer as it will try to clean up all the row= s >> and recreate the new rows? But then how can this be avoidable or made >> better without impacting the system performance >> > > "Impact" is a non-specific word. "How much impact" depends on how many > autovacuum workers you've set it to use, and how many threads you set in > vacuumdb. > > >> and blocking others? >> > > VACUUM never blocks. > > Anyway, DROP is the easy part; it's ADD COLUMN that can take a lot of tim= e > (depending on whether or not you populate the column with a default value= ). > > I'd detach all the partitions from the parent table, and then add the new > column to the not-children in multiple threads, add the column to the > parent and then reattach all of the children. That's the fastest method, > though takes some time to set up. > Thank you so much. Dropping will take it's own time for post vacuum however as you rightly said, it won't be blocking which should be fine. In regards to add column, Detaching all partitions then adding column to the individual partition in multiple sessions and then reattaching looks to be a really awesome idea to make it faster. However one doubt, Will it create issue if there already exists foreign key on this partition table or say it's the parent to other child partition/nonpartition tables? --000000000000d57111061cf3e22b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Thu, 11 Jul, 2024, 12:46 pm Ron Johnson, &= lt;ronljohnsonjr@gmail.com&g= t; wrote:
On Wed, Jul 10, 2024 at 11:28=E2=80=AFPM sud <suds1434@gmail.co= m> wrote:



Thank you so much. When you said "you can execute = one of the forms of ALTER TABLE that performs a rewrite
of= the whole table."=C2=A0Does it mean that post "alter table d= rop column" the vacuum is going to run longer as it will try to clean = up all the rows and recreate the new rows? But then how can this be avoidab= le or made better without impacting the system performance

"Impact" is a non-specific=C2= =A0word.=C2=A0 "How much impact" depends on how many autovacuum w= orkers you've set it=C2=A0to use,=C2=A0and how many threads you=C2=A0se= t in vacuumdb.
=C2=A0
and=C2=A0block= ing others?

VACUUM never = blocks.=C2=A0

Anyway, DROP is the easy part; it= 9;s ADD COLUMN that can take a lot of time (depending on whether or not you= populate the column with a default value).

I'= d detach all=C2=A0the partitions from the parent table, and then add the ne= w column to the not-children in multiple threads, add the column to the par= ent and then reattach all of the children.=C2=A0 That's the fastest met= hod, though takes some time to set up.
=


T= hank you so much.=C2=A0

= Dropping will take it's own time for post vacuum however as you rightly= said, it won't be blocking which should be fine.=C2=A0

In regards to add column, Detaching a= ll partitions then adding column=C2=A0 to the individual partition in multi= ple sessions and then reattaching looks to be a really awesome idea to make= it faster. However one doubt, Will it create issue if there already exists= foreign key on this partition table or say it's the parent to other ch= ild partition/nonpartition tables?=C2=A0

<= div dir=3D"auto">
--000000000000d57111061cf3e22b--