Re: How to do faster DML

[veem v <veema0000@xxxxxxxxx>]

On Thu, 15 Feb 2024 at 00:43, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:

It depends:

https://www.postgresql.org/docs/current/sql-altertable.html

"Adding a column with a volatile DEFAULT or changing the type of an existing column will require the entire table and its indexes to be rewritten. As an exception, when changing the type of an existing column, if the USING clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed. However, indexes must always be rebuilt unless the system can verify that the new index would be logically equivalent to the existing one. For example, if the collation for a column has been changed, an index rebuild is always required because the new sort order might be different. However, in the absence of a collation change, a column can be changed from text to varchar (or vice versa) without rebuilding the indexes because these data types sort identically. Table and/or index rebuilds may take a significant amount of time for a large table; and will temporarily require as much as double the disk space.

"

create table int_test(int_fld integer);

insert into int_test select * from generate_series(1, 10000, 1);

select ctid, int_fld from int_test ;

ctid   | int_fld
----------+---------
 (0,1)    |       1
 (0,2)    |       2
 (0,3)    |       3
 (0,4)    |       4
 (0,5)    |       5
 (0,6)    |       6
 (0,7)    |       7
 (0,8)    |       8
 (0,9)    |       9
 (0,10)   |      10

alter table int_test alter column int_fld set data type bigint;

select ctid, int_fld from int_test ;

  ctid   | int_fld
----------+---------
 (0,1)    |       1
 (0,2)    |       2
 (0,3)    |       3
 (0,4)    |       4
 (0,5)    |       5
 (0,6)    |       6
 (0,7)    |       7
 (0,8)    |       8
 (0,9)    |       9
 (0,10)   |      10

update int_test set  int_fld = int_fld;

select ctid, int_fld from int_test  order by int_fld;

(63,1)    |       1
 (63,2)    |       2
 (63,3)    |       3
 (63,4)    |       4
 (63,5)    |       5
 (63,6)    |       6
 (63,7)    |       7
 (63,8)    |       8
 (63,9)    |       9
 (63,10)   |      10

Where ctid is:

https://www.postgresql.org/docs/current/ddl-system-columns.html

 Thank you so much. 

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.

Regards

Veem