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 1sRo28-001GWH-Tq for pgsql-general@arkaria.postgresql.org; Thu, 11 Jul 2024 07:16:05 +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 1sRo25-00BlsP-IH for pgsql-general@arkaria.postgresql.org; Thu, 11 Jul 2024 07:16:01 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sRo25-00BlsH-5Z for pgsql-general@lists.postgresql.org; Thu, 11 Jul 2024 07:16:01 +0000 Received: from mail-oa1-x34.google.com ([2001:4860:4864:20::34]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sRo1y-001W6g-Oh for pgsql-general@lists.postgresql.org; Thu, 11 Jul 2024 07:16:00 +0000 Received: by mail-oa1-x34.google.com with SMTP id 586e51a60fabf-25e0663de76so274378fac.1 for ; Thu, 11 Jul 2024 00:15:54 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1720682152; x=1721286952; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=D47IDNX+Gsyv65Fa/b4HcTadtM2IJooax75k7Kz1230=; b=WfZ/wFeJiWsWxcNbKuDt3jOuyOxsOufIvNj9MznVI10CnGafxm7oBQHNbQ7RvEr285 St3yeQo8uRoBq4GJUya4Ra/41uWJVY84AETs16ymhzMBCxMEErcjSw+P3ypKswOzFodV zna8rkwguTBqsVSkqizwNHTcnfvcEniwV65qryC1gzPJ5B5vbln7iy0Ek+QCIkqLT/K/ upF/ZuxzJOq4b0AljATM6L0bWx+smWIuaWhjMXbkDJsWUZw7nscaUlsqOpRUSVr7Da4/ g0dO4qpOczHPov/KkqplLU8znDwoqc7ewrWG7C1FRMFCkGhdyEUIV97ugdHTRRiC0/Wa o0hA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1720682152; x=1721286952; h=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=D47IDNX+Gsyv65Fa/b4HcTadtM2IJooax75k7Kz1230=; b=BE4GT0tTSpCrHt1olK0+R4GW/5pVWETOxMrTHt3QdZoTtEVg8LM8bcRlnJUPXDrJi1 0hKqk2670cqQpkhYYVCjxL7zEIubHX7midw9bu4A9S/My6A5WH9nhJsvIRX6gl5g+LZv MeScfYY/B+yS4ydmYZ4TgXw1HzovnJhiNtMY2xyWA0vOb1jjQPVnldSoatSZRUt1alEd +Kq3GkxKLn0N/QXfyGOnA1TgpseWGEmXWFQBOb0i2DSo8bFv5Afoxnq9BQe+cq1E2fN9 yjDX8ac5zu1P9WSpqc+qftGEgNcKeZ673bw+nFlYlzvyUWVVSz909Pc3U7i68QmAs3qj buXA== X-Gm-Message-State: AOJu0YzpaYdft964arxzG1+Ie30l3sJg2p23DLqTsolx4Vct6yKRogbZ Qrcd4FR/951n4GGAdFieriuNSPB3KhsZanQlNzBROcig8LpV6lB76idYqWL6fAj6A9v8+X4/tup PFwGprdobFjsADeDLQs66DdC9pFpdvw== X-Google-Smtp-Source: AGHT+IFIrNUedioHN6GFv5y4VG4xpQtDC7oMtCbZufAIcYzNX567pPKsV47q8/u959qnLZv7NvBBwzBWfYViPgizTfc= X-Received: by 2002:a05:6870:658d:b0:258:456f:2fa5 with SMTP id 586e51a60fabf-25eae75e6fcmr6364173fac.7.1720682151719; Thu, 11 Jul 2024 00:15:51 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Thu, 11 Jul 2024 03:15:40 -0400 Message-ID: Subject: Re: Dropping column from big table To: pgsql-general Content-Type: multipart/alternative; boundary="0000000000006cc001061cf3881b" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000006cc001061cf3881b Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, Jul 10, 2024 at 11:28=E2=80=AFPM sud wrote: > > On Thu, Jul 11, 2024 at 2:52=E2=80=AFAM Adrian Klaver > wrote: > >> >> https://www.postgresql.org/docs/current/sql-altertable.html >> >> "The DROP COLUMN form does not physically remove the column, but simply >> makes it invisible to SQL operations. Subsequent insert and update >> operations in the table will store a null value for the column. Thus, >> dropping a column is quick but it will not immediately reduce the >> on-disk size of your table, as the space occupied by the dropped column >> is not reclaimed. The space will be reclaimed over time as existing rows >> are updated. >> >> To force immediate reclamation of space occupied by a dropped column, >> you can execute one of the forms of ALTER TABLE that performs a rewrite >> of the whole table. This results in reconstructing each row with the >> dropped column replaced by a null value. >> " >> >> > 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 rows > 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 time (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. --0000000000006cc001061cf3881b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Wed, Jul 10, 2024 at 11:28=E2=80=AFPM = sud <suds1434@gmail.com> wr= ote:

On Thu, Jul 11, 2024 at 2= :52=E2=80=AFAM Adrian Klaver <adrian.klaver@aklaver.com> wrote:

https://www.postgresql.org/docs/current/s= ql-altertable.html

"The DROP COLUMN form does not physically remove the column, but simpl= y
makes it invisible to SQL operations. Subsequent insert and update
operations in the table will store a null value for the column. Thus,
dropping a column is quick but it will not immediately reduce the
on-disk size of your table, as the space occupied by the dropped column is not reclaimed. The space will be reclaimed over time as existing rows are updated.

To force immediate reclamation of space occupied by a dropped column,
you can execute one of the forms of ALTER TABLE that performs a rewrite of the whole table. This results in reconstructing each row with the
dropped column replaced by a null value.
"


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 drop column" the vacuum is going to run l= onger as it will try to clean up all the rows and recreate the new rows? Bu= t then how can this be avoidable or made better without impacting the syste= m performance

"Impa= ct" is a non-specific=C2=A0word.=C2=A0 "How much impact" dep= ends on how many autovacuum workers you've set it=C2=A0to use,=C2=A0and= how many threads you=C2=A0set in vacuumdb.
=C2=A0
and=C2=A0blocking others?

VACUUM never blocks.=C2=A0

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

I'd detach all=C2=A0the partitions from the pare= nt table, and then add the new column to the not-children in multiple threa= ds, add the column to the parent and then reattach all of the children.=C2= =A0 That's the fastest method, though takes some time to set up.
<= div>
--0000000000006cc001061cf3881b--