Re: How to do faster DML

On 2/15/24 08:16, Greg Sabino Mullane wrote:
>     So as I also tested the same as you posted, there has been no change
>     in "ctid" , when I altered the column data type from 'int' to
>     'bigint' in the table, so that means full table rewriting
>     won't happen in such a scenario.
> 
> 
> No it was definitely rewritten - do not depend on the ctid to verify 
> that. Take our word for it, or 
> use*pg_relation_filenode('int_test');* before and after, as well as 
> *pg_relation_size('int_test')*;

That is a mixed bag:

test=# select pg_relation_filenode('int_test');
  pg_relation_filenode
----------------------
                 69999
(1 row)

test=# select pg_relation_size('int_test');
  pg_relation_size
------------------
            368640
(1 row)

test=# alter table int_test alter column int_fld set data type bigint;
ALTER TABLE
test=# select pg_relation_filenode('int_test');
  pg_relation_filenode
----------------------
                 70002
(1 row)

test=# select pg_relation_size('int_test');
  pg_relation_size
------------------
            368640

> 
> Cheers,
> Greg
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com