public inbox for [email protected]  

help / color / mirror / Atom feed

From: veem v <[email protected]>
To: Ron Johnson <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Alter table fast
Date: Fri, 10 Jan 2025 00:47:14 +0530
Message-ID: <CAB+=1TVSEZB6XSxzAYSp1O91i3JKmMRgPjckLT0Dokrfd_PxWQ@mail.gmail.com> (raw)
In-Reply-To: <CANzqJaB-k8Sae4st0NP4BW_VuNRP6e1V=f0E7O28URR23MuEww@mail.gmail.com>
References: <CAB+=1TUcgyKXDKFS-G5GNjJHpP1cHyiXha1pAHc0HG5ZPPu9FQ@mail.gmail.com>
	<CANzqJaB-k8Sae4st0NP4BW_VuNRP6e1V=f0E7O28URR23MuEww@mail.gmail.com>

On Thu, 9 Jan 2025 at 21:57, Ron Johnson <[email protected]> wrote:

> On Thu, Jan 9, 2025 at 11:25 AM veem v <[email protected]> wrote:
>
>> 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 the
>> 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 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?
>
>
>
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 <table_name> alter column <column_name> type
numeric(15,0) USING <column_name>::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"?

view thread (6+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Alter table fast
  In-Reply-To: <CAB+=1TVSEZB6XSxzAYSp1O91i3JKmMRgPjckLT0Dokrfd_PxWQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox