On Thu, 9 Jan 2025 at 21:57, Ron Johnson <ronljohnsonjr@xxxxxxxxx> wrote:
On Thu, Jan 9, 2025 at 11:25 AM veem v <veema0000@xxxxxxxxx> 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"?