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 1tVy27-00D4C4-UF for pgsql-general@arkaria.postgresql.org; Thu, 09 Jan 2025 19:17:32 +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 1tVy27-006kaD-Df for pgsql-general@arkaria.postgresql.org; Thu, 09 Jan 2025 19:17:31 +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 1tVy27-006kXc-2m for pgsql-general@lists.postgresql.org; Thu, 09 Jan 2025 19:17:30 +0000 Received: from mail-ej1-x636.google.com ([2a00:1450:4864:20::636]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tVy24-000kqw-1c for pgsql-general@lists.postgresql.org; Thu, 09 Jan 2025 19:17:29 +0000 Received: by mail-ej1-x636.google.com with SMTP id a640c23a62f3a-aa67333f7d2so211656766b.0 for ; Thu, 09 Jan 2025 11:17:28 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1736450247; x=1737055047; 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=4tuXTyhGSKck3ZzlniKbjVY/SX2sqshqrZZpNHZY6jw=; b=ZzQoJkgs2S6jjpPUswPf+zJydI1xB4HZKda4wa3lDsU5ilgfiNAnO5UQytozZCiLBq wc+1eYGnMX75451hDLkK/WocJomqcEQJAquAdg2nOJtszTLjLd63Ax1+aiWQ3KUbaQfM UE8/5T0i0lPxcVtTcTDeUz8tk3tGpMMxkCaZi8wACZ2VkGnmpoZYPXGMUEJ9iJ+yuA0d T3lfWlV7V6lTTsPd3tdcFS/Crns+MCfLrU9arjF1LdApWicmRibs5V7wj1Zxj+2K78sy guNXmLn+j1MYPNq2IBHrYKkREAWp41iEpc938cwbJlCngXVYy9hiDz83zAzPuj34UwU9 4lkQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1736450247; x=1737055047; 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=4tuXTyhGSKck3ZzlniKbjVY/SX2sqshqrZZpNHZY6jw=; b=J2RW+uEAvUfOtFXg2k7EoxRUi2+aDuJ38aOcnSu847X3p+0ckr1QTVlYWkS+7nA38G ifj4WQ7R502ZVFzpNVbz6XX/jK1smWc81LXcw7QSJBPrRUm0U9LdATr2J12Tate4kCiH ibJTI8MR+L7lrrjghg2oHYpptHx4iyL623Fra0If5TUGqp7gNWJkUBNxALAlipA9/HH1 Gn/q1j5a5HltIo3YjY7lsKMmYLWHlsxuytiLtKfYGSVIRZBLIxGp52R23hNsBqmEVfMu l/LNzoQCTDLQRiXcCxM5NG3jFFIPlWC96jSWz9jYTV+C5o25zhRlW18luIhtwA+seexU THZg== X-Gm-Message-State: AOJu0YyG2Q/yrEdJ3i/ibyhj4oZg6PokZdoo0Z6Z8AsH22OoUEZmmFff uwhjsD/mmgRhICYmdoXaZJbOvesAto3xFunoJZQmuolkpz23BagZHDufSTFsgH4M4qqvb5rmLq2 i0aMv1+YBuw/ZBQhdMC4Cxm+59kM= X-Gm-Gg: ASbGncslyU85vpHAe3BO+bXLxrpdRcY40Aj5nkUFRyAitdRs1gaPrwxmVc467WYjIVO 3MNT3PojiLWrPpE8Z8ofcB4RSMahYxmas4zedhl3kcZa3kH+oAAfK8waLOL51pjoAL5ch X-Google-Smtp-Source: AGHT+IEBEECOSmIAxNDHCrkJZOxWYSXCF04+KLlh5pReVsMTMOeTaJg9ghOlA5J1hoQ0j8KQWq5E5hjrmXfuJKwMOhw= X-Received: by 2002:a17:907:9711:b0:aa6:1e9a:e45a with SMTP id a640c23a62f3a-ab2abdc368emr634999366b.46.1736450247138; Thu, 09 Jan 2025 11:17:27 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: veem v Date: Fri, 10 Jan 2025 00:47:14 +0530 X-Gm-Features: AbW1kvZGcaeXv3pVDYjgssy4ZeHT2KJGbq21TMzFdjucm2GYxQq-7niQSugqh7U Message-ID: Subject: Re: Alter table fast To: Ron Johnson Cc: pgsql-general Content-Type: multipart/alternative; boundary="00000000000026a49c062b4ad488" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000026a49c062b4ad488 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, 9 Jan 2025 at 21:57, Ron Johnson wrote: > On Thu, Jan 9, 2025 at 11:25=E2=80=AFAM veem v wrot= e: > >> Hello, >> It's postgres version 16.1, we want to convert an existing column data >> type from integer to numeric and it's taking a long time. The size of th= e >> table is ~50GB and the table has ~150million rows in it and it's not >> partitioned. We tried running the direct alter and it's going beyond hou= rs, >> so wanted to understand from experts what is the best way to achieve thi= s? >> >> > Out of curiosity, why NUMERIC(15,0) instead of BIGINT? > > > It's for aligning the database column types to the data model and it's happening across all the upstream downstream systems. I was thinking if this can be made faster with the single line alter statement "Alter table alter column type numeric(15,0) USING ::NUMERIC(15,0);" or through the UPDATE column rename column strategy. Additionally if this can be further improved using any session level parameter like "max_parallel_workers_per_gather", "max_parallel_workers", "maintenance_work_mem", "work_mem"? --00000000000026a49c062b4ad488 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Thu, 9 Jan 2= 025 at 21:57, Ron Johnson <ro= nljohnsonjr@gmail.com> wrote:
On Thu, Jan 9, 2025 = at 11:25=E2=80=AFAM veem v <veema0000@gmail.com> wrote:
Hel= lo,
It's postgres version 16.1, we want to convert an existing colu= mn data type from integer to numeric and it's taking a long time. The s= ize of the table is ~50GB and the table has ~150million rows in it and it&#= 39;s not partitioned. We tried running the direct alter and it's going = beyond hours, so wanted to understand from experts what is the best way to = achieve this?


Out of curiosity, why NUMERIC(15,0) instead of BIGINT?=C2=A0
<= /div>



It's for= aligning the database column types to the data model and it's happenin= g across all the upstream=C2=A0downstream systems.
I was thinking= if this can be made=C2=A0 faster with the single line alter statement &quo= t;Alter table <table_name> alter column <column_name> type nume= ric(15,0) USING <column_name>::NUMERIC(15,0);" or through the UP= DATE column=C2=A0rename column strategy. Additionally if this can be furthe= r=C2=A0improved using any session level parameter like=C2=A0"max_paral= lel_workers_per_gather", "max_parallel_workers", "maint= enance_work_mem", "work_mem"?
--00000000000026a49c062b4ad488--