On Thu, 9 Jan 2025 at 21:57, Ron Johnson <ronljohnso...@gmail.com> wrote:
> On Thu, Jan 9, 2025 at 11:25 AM veem v <veema0...@gmail.com> 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"?
