Horton
Menu

Re: How to do faster DML

Adrian Klaver <adrian.klaver@aklaver.com>

View thread
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Greg Sabino Mullane <htamfids@gmail.com>, veem v <veema0000@gmail.com>
Cc: Ron Johnson <ronljohnsonjr@gmail.com>, "Peter J. Holzer" <hjp-pgsql@hjp.at>, Laurenz Albe <laurenz.albe@cybertec.at>, "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Date: 2024-02-15T16:43:17Z
Lists: pgsql-general 
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