Re: How to do faster DML

On Wed, 14 Feb, 2024, 10:30 am Ron Johnson, <ronljohnsonjr(at)gmail(dot)com> wrote:

> On Tue, Feb 13, 2024 at 4:17 PM veem v <veema0000(at)gmail(dot)com> wrote:
> [sni[]
>
>> One question here, if we have defined one column as a fixed length data
>> type "integer" and slowly we noticed the length of data keeps increasing
>> (in case of a sequence generated PK column which will keep increasing), and
>> we want to alter the column to "bigint" now. In such scenario, will it
>> append/pad the additional spaces to all the existing values which were
>> already stored with integer type initially in the table. And that would be
>> then an increase to the existing table storage. Please correct me if I'm
>> wrong.
>>
>
> ALTER TABLE foo ALTER COLUMN bar BIGINT; rewrites the whole table. Do
> yourself a favor, and start with BIGINT.
>

Not yet tried, but that's interesting. I was initially thinking as it will
be increasing the length, so it would be just a metadata change and finish
within seconds.

But as you mentioned, it seems to be the effect of "fixed length data
type" which is why it's going to rewrite whole table even we just increases
the column length. Hope it won't be the case in variable length data type.

>