Re: Column type modification in big tables

On Fri, Aug 9, 2024 at 6:39 AM Lok P <loknath(dot)73(at)gmail(dot)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
> 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 not
going to really care about numeric(10) versus numeric(8) or varchar(20) vs
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 across all the
> partitions , but it's still beneficial as compared to updating the 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.

Cheers,
Greg