Re: How to do faster DML

On Wed, Feb 14, 2024 at 11:58 PM veem v <veema0000(at)gmail(dot)com> wrote:

>
>
> 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.
>

It happened when I altered columns from INTEGER to BIGINT. How do I know?

The disk filled up.

>