Re: How to do faster DML

On Thu, 15 Feb 2024 at 00:43, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
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