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 1scVC1-007u57-Gd for pgsql-general@arkaria.postgresql.org; Fri, 09 Aug 2024 19:22:29 +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 1scVBz-005VlV-Et for pgsql-general@arkaria.postgresql.org; Fri, 09 Aug 2024 19:22:27 +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 1scVBz-005VlN-2A for pgsql-general@lists.postgresql.org; Fri, 09 Aug 2024 19:22:27 +0000 Received: from mail-yw1-x112d.google.com ([2607:f8b0:4864:20::112d]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1scVBs-0041ET-CC for pgsql-general@lists.postgresql.org; Fri, 09 Aug 2024 19:22:26 +0000 Received: by mail-yw1-x112d.google.com with SMTP id 00721157ae682-664cfc5cf98so3490697b3.2 for ; Fri, 09 Aug 2024 12:22:19 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1723231338; x=1723836138; 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=dJJTsB33c44AW0Rux3CK/ZPMmPY6bKks7UoJBS5eCts=; b=HXhoZjbMg5B9fzbMLFebsHuUtgQz+sYSQjPawi7njuAmfX6V8svXphak4WBLDPnGrR qERdfSyBpwu2Vz9zTqIKlDf8VimR0jsgwOqlXHK/ygOmbTMxmVos+SjhMemeKWx0naoP QZJBdL/3Iajtdy6b0Jo7rCqLZU8KXb80dFtqDNRMA6wBldP7LpWT8XHEMoVCegyRTYLY C+BDibCMnb4ZNhMppNRgC0quUZfUuUA3GfoXkUBcBWIyWoCmzpp/IOVYXKI6txHVOYVG yLv7PseQlvZ9ABqoMpU1DqA97rLzB1k6k4JSzMm7njWfUCt8QJeSUh1GFyBfY5NCyR7T 9Igg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1723231338; x=1723836138; 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=dJJTsB33c44AW0Rux3CK/ZPMmPY6bKks7UoJBS5eCts=; b=A7CE5uOH7jQi5zSeAd/2+8Xb2ym7KnoW1nbhqQkB8hNhBZ5qpxIsXcOUX68PcGtF58 X0TABsQiN68/aLUCtPn90HPdoDZ0kunAZ2j+zVsRhiCtROBXUQWz1PaJzhtHHtANrGlg zBTFQIi0IhKpaqUBxNpkJK1neo2k0xkum1Dr8++rrBXhs8nRXV5xcHXLqAKxl8wtqOo1 zI9rqhhqxs2C7JFrkp94PQR6Lkh48nXw8bjO1zRkQ976MP+clL6nSGRjql4yuYqGrTp3 Ck7SPGPDjoyErTHRb2Ro6kzxh/F3aRFF/gE1yO5dvWDNJVxHaSX3X/3qRn6nfVEV7aqc qAzQ== X-Forwarded-Encrypted: i=1; AJvYcCWqOtvy/LsV4gNjIuStxG2c7AJ3YSnI24gkVP7OowD0BVOSrcPFRwrc1CbiEAICy295cEDQFJDC42cZSxoXdVMlfVQ/qNLWxdWaEZZBMQpNx/JF X-Gm-Message-State: AOJu0YxzFU30xKwxJrDxEfKsqEFp6h+DChRDGYYQ+qPxveLdSmUVUx5K NkK5fVqixP16832DN5EoewFsIgrRwxmYMDOAHREkecSAVJfCPr7SbQ7our6ehdkXzEMaAYTQg1t BlI3utsmADDcGojKA+PwxMKBfBpA= X-Google-Smtp-Source: AGHT+IFAwYTN4ZUcvjtVHBsNeJrSXm/LN4sM4zBtdwwVL20n7W5W+1pLPMAEv6JqpllFsjbQ3dQk+dNiSLTLovfKq50= X-Received: by 2002:a05:6902:15c8:b0:e02:f793:83ee with SMTP id 3f1490d57ef6-e0eb98e43b6mr1605429276.1.1723231337744; Fri, 09 Aug 2024 12:22:17 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Lok P Date: Sat, 10 Aug 2024 00:52:05 +0530 Message-ID: Subject: Re: Column type modification in big tables To: Greg Sabino Mullane Cc: sud , pgsql-general Content-Type: multipart/alternative; boundary="000000000000c096e5061f450f26" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000c096e5061f450f26 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Fri, Aug 9, 2024 at 9:19=E2=80=AFPM Greg Sabino Mullane wrote: > On Fri, Aug 9, 2024 at 6:39=E2=80=AFAM Lok P wrote= : > >> Thank you so much. Will definitely try to evaluate this approach. The >> Only concern I have is , as this data is moving downstream with exactly = the >> same data type and length , so will it cause the downstream code to brea= k >> while using this column in the join or filter criteria. Also I believe t= he >> optimizer won't be able to utilize this information while preparing the >> execution plan. >> > > Yes, this is not as ideal as rewriting the table, but you asked for > another approaches :) As to the impact of your downstream stuff, I think > you have to try and see. Not clear what you mean by the optimizer, it's n= ot > going to really care about numeric(10) versus numeric(8) or varchar(20) v= s > varchar(2). It's possible the varchar -> numeric could cause issues, but > without real-world queries and data we cannot say. > > >> Another thing , correct me if wrong, My understanding is , if we want >> to run the "validate constraint" command after running this "check >> constraint with not valid" command, this will do a full table scan acros= s >> all the partitions , but it's still beneficial as compared to updating t= he >> columns values for each rows. Correct me if I'm wrong. >> > > Yes, it needs to scan the entire table, but it's a lightweight lock, won'= t > block concurrent access, will not need to detoast, and makes no table or > index updates. Versus an entire table rewrite which will do heavy locking= , > take up tons of I/O, update all the indexes, and generate quite a lot of > WAL. > > Thank you so much Greg. Considering the option, if we are able to get large down time to get this activity done. Some teammates suggested altering the column with "USING" Clause. I am not really able to understand the difference, also when i tested on a simple table, it seems the "USING" clause takes more time as compared to normal ALTER. But again I don't see any way to see the progress and estimated completion time. Can you share your thoughts on this? ALTER TABLE foobar ALTER COLUMN mycol TYPE NUMERIC(3) USING mycol::NUMERIC(3); VS ALTER TABLE foobar ALTER COLUMN mycol TYPE NUMERIC(3) ; ***** Another thing also comes to my mind whether we should just create a new partition table(say new_part_table) from scratch from the DDL of the existing table(say old_part_table) and then load the data into it using command (insert into new_part_table.. select..from old_part_table). Then create indexes and constraints etc, something as below. Will this approach be faster/better as compared to the simple "alter table alter column approach" as above, considering we will have 4-6 hours of downtime for altering three different columns on this ~5TB table? *-- Steps* Create table exactly same as existing partition table but with the modified column types/lengths. drop indexes ; (Except PK and FK indexes may be..) drop constraints; insert into new_part_table (...) select (...) from old_part_table; create indexes concurrently ; create constraints; (But this table is also a child table to another partition table, so creating the foreign key may be resource consuming here too). drop the old_part_table; rename the new_part_table to old_part_table; rename all the partitions; VACUUM old_part_table ; ANALYZE old_part_table ; --000000000000c096e5061f450f26 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

On Fri, Aug 9, 2024 at 9:19=E2=80=AFPM Gr= eg Sabino Mullane <htamfids@gmail.= com> wrote:
On Fri, Aug 9, 2024 at 6:39=E2=80=AFAM= Lok P <loknat= h.73@gmail.com> wrote:
= Thank you so much. Will definitely try to evaluate this approach. The Only = concern I have is , as this data is moving downstream with exactly the same= data type and length , so will it cause the downstream code to break while= using this column in the join or filter criteria. Also I believe the optim= izer won't be able to utilize this information while preparing the exec= ution plan.

Yes, this is no= t as ideal as rewriting the table, but you asked for another=C2=A0approache= s :) As to the impact of your downstream stuff, I think you have to try and= see. Not clear what you mean by the optimizer, it's not going to reall= y care about numeric(10) versus numeric(8) or varchar(20) vs varchar(2). It= 's possible the varchar -> numeric could cause issues, but without r= eal-world queries and data we cannot say.
=C2=A0
=C2=A0Another thing , correct me if wrong, My understanding is= =C2=A0 , if we want to run the "validate constraint" command afte= r running this "check constraint with not valid" command, this wi= ll do a full table scan across all the partitions , but it's=C2=A0still= beneficial as compared to updating the columns values for each rows. Corre= ct me if I'm wrong.=C2=A0

=
Yes, it needs to scan the entire table, but it's a lightweight loc= k, won't block concurrent access, will not need to detoast, and makes n= o table or index updates. Versus an entire table rewrite which will do heav= y locking, take up tons of I/O, update all the indexes, and generate quite = a lot of WAL.=C2=A0


<= /div>
Thank you so much Greg.

Considering the option, if we ar= e able to get large down time to get this activity done.

Some teamm= ates suggested altering the column with "USING" Clause. I am not = really able to understand the difference, =C2=A0also when i tested on a sim= ple table, it seems the "USING" clause takes more time as compare= d to normal ALTER. But again I don't see any way to see the progress an= d estimated completion time. Can you share your thoughts on this?=C2=A0
=
ALTER TABLE foobar ALTER COLUMN mycol TYPE NUMERIC(3) USING mycol::NUME= RIC(3);
VS
ALTER TABLE foobar ALTER COLUMN mycol TYPE NUMERIC(3) ;
*****
Another thing also comes to my m= ind whether we should just create a new partition table(say new_part_table)= from scratch from the DDL of the existing table(say old_part_table) and th= en load the data into it using command (insert into new_part_table.. select= ..from old_part_table). Then create indexes and constraints etc, something = as below.

Will this approach be faster/better as compared to the si= mple "alter table alter column approach" as above, considering we= will have 4-6 hours of downtime for altering three different columns on th= is ~5TB table?

-- Steps

Create table exactly same as e= xisting partition table but with the modified column types/lengths.
drop indexes ;=C2=A0 (Except=C2=A0PK and FK indexes may be..)
drop constraints;

inser= t into new_part_table (...) select (...) from old_part_table;

create= indexes concurrently ;
create constraints; (But this table is also a ch= ild table to another partition table, so creating the foreign key may be re= source consuming here too).

drop the old_part_table;
rename the n= ew_part_table to old_part_table;
rename all the partitions;

VACUU= M=C2=A0 old_part_table=C2=A0 ;
ANALYZE=C2=A0 old_part_table=C2=A0 ;=C2=A0
--000000000000c096e5061f450f26--