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 1sbmSF-001NRy-Iv for pgsql-general@arkaria.postgresql.org; Wed, 07 Aug 2024 19:36:15 +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 1sbmSD-009VsW-Sm for pgsql-general@arkaria.postgresql.org; Wed, 07 Aug 2024 19:36:13 +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 1sbmSD-009Vpa-Ex for pgsql-general@lists.postgresql.org; Wed, 07 Aug 2024 19:36:13 +0000 Received: from mail-ua1-x936.google.com ([2607:f8b0:4864:20::936]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sbmS9-003gHs-Rs for pgsql-general@lists.postgresql.org; Wed, 07 Aug 2024 19:36:12 +0000 Received: by mail-ua1-x936.google.com with SMTP id a1e0cc1a2514c-821eab936d6so67243241.3 for ; Wed, 07 Aug 2024 12:36:09 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1723059368; x=1723664168; 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=UJ8jvvVn40Mc8SfETK+SmqQi7AOgVYKktJ0oITNxDZU=; b=l2ERCWCPey8PUz9W23ykkBkSRYDB+WktzI7Jlt131nuWYSspmashrHXa0aeDcrEyXK 4w5/JWMuKjtS1SgthxQyYtazRW1g5QSfeBHzzj93GvzMEaFVbQ+4zrWAPA1EjjrklwWV DQXHaEM2Zq5p1Q7EEf5Twr90VQvMR0DG2fzpt7GFFN1l9S/UJRyOlKOqedAXjXUSvQ3I 9drDavfiintoUPRLdxHdjfH8g7oivveNNEHLef/ZOaHSnxwvoRkGAFydZR8aQtXM/O4N Xe7Y/jn6SjnjjFnIVOzOFUFEeZVx3WvGqt376nF9g4NB4TvLECNtdkQe5qgqO2dyQP4w 4ewA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1723059368; x=1723664168; 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=UJ8jvvVn40Mc8SfETK+SmqQi7AOgVYKktJ0oITNxDZU=; b=WBIGaaiigjxff4wyNxgJBIXzol6A3t+VDkXo3sjFEoL6sEwxNwcKqlZt02US5QudNv YnuVHwZ7AP86HuFYm5Tx53UF5npRBlFWY2N5ECgdos1eqG4nRvYNO9ERLi2lQHvK5zGl oblGgjmyweLhXhKixy10aFcyTJNTQ/ntO4JO7dCwkIgUpcNVoUl8wYIMc6RcjfpKIZr6 KBY+YXYpHbDSMrWY4fDbdRAaNohtK2EN7zbpIjS5y2sE2LYtmSFRi25051tPvgcEKmA0 +yZE4qASWZNN7cFmVS0VHk35rA64p8pxVkDDicB/4KCzizvapS7PmZWFNJt8LsxXB84J 4YHg== X-Gm-Message-State: AOJu0YyRAs7iliDnULsk1tZDqAuv5o8HeDYkIYaDEgslGq1gbh54VzJd b4xMOYKg2DblzsEHyosfu5E/4CWxuNRl2HEZIArZGvBkbcv4paSh7kvlXNUwUtTERnUiW/X5JI+ GbX3lm5N9+mdfUZpAgwjYF5q/xJM= X-Google-Smtp-Source: AGHT+IHX/rtVRwBo42V9SXPr+6qnLSAQJHJG6skwfM8ByjIer6DEEaCkfb5yKHiBJ2gZr1q9VH02/sbQJkG6KbO0+Ro= X-Received: by 2002:a05:6102:f08:b0:48c:3db1:94dd with SMTP id ada2fe7eead31-4945bf4d95amr18734944137.30.1723059367878; Wed, 07 Aug 2024 12:36:07 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: sud Date: Thu, 8 Aug 2024 01:05:56 +0530 Message-ID: Subject: Re: Column type modification in big tables To: Lok P Cc: pgsql-general Content-Type: multipart/alternative; boundary="0000000000008ca938061f1d0572" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000008ca938061f1d0572 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, Aug 7, 2024 at 5:00=E2=80=AFPM Lok P wrote: > > > On Wed, Aug 7, 2024 at 4:51=E2=80=AFPM sud wrote: > >> >> >> Others may correct but i think, If you don't have the FK defined on thes= e >> columns you can do below. >> >> >> --Alter table add column which will be very fast within seconds as it >> will just add it to the data dictionary. >> >> ALTER TABLE tab1 ADD COLUMN new_column1 NUMERIC(3), new_column2 >> varchar2(3); >> >> >> *-- Back populate the data partition wise and commit, if it's really >> needed* >> >> UPDATE tab1_part1 SET new_column1 =3D CAST(old_column1 AS NUMERIC(3)), >> new_column2 =3D CAST(old_column2 AS varchar2(3)) ; >> commit; >> UPDATE tab1_part2 SET new_column1 =3D CAST(old_column1 AS NUMERIC(3)), >> new_column2 =3D CAST(old_column2 AS varchar2(3)) ; >> commit; >> UPDATE tab1_part3 SET new_column1 =3D CAST(old_column1 AS NUMERIC(3)), >> new_column2 =3D CAST(old_column2 AS varchar2(3)) ; >> commit; >> ..... >> >> >> *--Alter table drop old columns which will be very fast within seconds a= s >> it will just drop it from the data dictionary.* >> ALTER TABLE your_table DROP COLUMN old_column1, DROP COLUMN old_column2; >> > > > > Thank you so much. > > I understand this will be the fastest possible way to achieve the column > modification. > > But talking about the dropped column which will be sitting in the table > and consuming storage space, Is it fine to leave as is or auto vacuum wil= l > remove the column values behind the scene and also anyway , once those > partitions will be purged they will be by default purged. Is this > understanding correct? > > And also will this have any impact on the partition maintenance which is > currently done by pg_partman as because the template table is now differe= nt > internally(not from outside though). Will it cause conflict because of > those dropped columns from the main table? > I think leaving the table as is after the dropping column will be fine for you because your regular partition maintenance/drop will slowly purge the historical partitions and eventually they will be removed. But if you update those new columns with the old column values, then autovacuum should also take care of removing the rows with older column values (which are dead actually) . Not sure if pg_partman will cause any issue ,as because the table now has the column data type/length changed. Others may confirm. --0000000000008ca938061f1d0572 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
On Wed, Aug 7, 2024 at 5:00=E2=80=AFP= M Lok P <loknath.73@gmail.com> wrote:


On Wed, Aug 7, 2024 at 4:51=E2=80=AFPM s= ud <suds1434@gma= il.com> wrote:


Others may correct but i = think, If you don't have the FK defined on these columns you can do bel= ow.


--Alter table add column which will be very fast within seco= nds as it will just add it to the data dictionary.

ALTER TABLE tab1 = ADD COLUMN new_column1 NUMERIC(3),=C2=A0 =C2=A0new_column2 varchar2(3);
=

-- Back populate the data partition wise and commi= t, if it's really needed

UPDATE tab1_part1 SET new_column1 = =3D CAST(old_column1 AS NUMERIC(3)), new_column2 =3D CAST(old_column2 AS va= rchar2(3)) ;
commit;
UPDATE tab1_part2 SET new_column1 =3D CAST(old_c= olumn1 AS NUMERIC(3)), new_column2 =3D CAST(old_column2 AS varchar2(3)) ;commit;
UPDATE tab1_part3 SET new_column1 =3D CAST(old_column1 AS NUME= RIC(3)), new_column2 =3D CAST(old_column2 AS varchar2(3)) ;
commit;
.= ....

--Alter table drop old columns which will be very fast withi= n seconds as it will just drop it from the data dictionary.

ALTE= R TABLE your_table DROP COLUMN old_column1, DROP COLUMN old_column2;=C2=A0<= /div>



Thank you so much= .

I understand this will be the fastest possible way to achieve the= column modification.

But talking about the dropped column whi= ch will be sitting in the table and consuming=C2=A0storage space, Is it fin= e to leave as is or auto vacuum will remove the column values behind the sc= ene and also anyway , once those partitions will be purged they will be by = default purged. Is this understanding correct?

=C2= =A0And also will this have any impact on the partition=C2=A0maintenance whi= ch is currently done by pg_partman as because the template table is now dif= ferent internally(not from outside though). Will it cause conflict because= =C2=A0of those dropped columns from the main table?

I think leaving the table as is after the droppi= ng column will be fine for you because your regular partition maintenance/d= rop will slowly purge the historical partitions and eventually=C2=A0they wi= ll be removed. But if you update those new columns with the old column valu= es, then autovacuum=C2=A0should also take care of removing the rows with ol= der column values (which are dead actually) .

Not = sure if pg_partman will cause any issue ,as because the table now has the c= olumn data type/length changed. Others may confirm.

=C2=A0 --0000000000008ca938061f1d0572--