Re: How to do faster DML

On Thu, 15 Feb 2024 at 22:40, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 2/15/24 09:00, Greg Sabino Mullane wrote:
> > On Thu, Feb 15, 2024 at 11:43 AM Adrian Klaver
> > <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
> >
> > That is a mixed bag:
> >
> >
> > Ha! Good point. Our contrived example table does suffer from that, so
> > perhaps the test should be:
> >
> > create table int_test(c1 int, c2 int);
>
> Alright now I see:
>
> test=# create table int_test(c1 int, c2 int);
> CREATE TABLE
>
> test=# select pg_relation_filenode('int_test');
> pg_relation_filenode
> ----------------------
> 70021
> (1 row)
>
>
> test=# insert into int_test select a, a+1 from generate_series(1,
> 10000, 1) as t(a);
> INSERT 0 10000
>
> test=# select pg_relation_size('int_test');
> pg_relation_size
> ------------------
> 368640
> (1 row)
>
> test=# alter table int_test alter column c2 set data type bigint;
> ALTER TABLE
>
> test=# select pg_relation_filenode('int_test');
> pg_relation_filenode
> ----------------------
> 70024
> (1 row)
>
> test=# select pg_relation_size('int_test');
> pg_relation_size
> ------------------
> 450560
> (1 row)
>
>
Thank you.

Did a similar test as below using DB fiddle. Same results for fixed length
data type i.e the size is getting increased. However for variable
length types (like numeric) , it remains the same, so it must be just
metadata change and thus should be quick enough even for a big table.
So one learning for me, i.e. one of the downside of fixed length data type
is, with fixed length data types any future changes to it , will be a full
table rewrite. And thus this sort of change for big tables will be a
nightmare.

https://dbfiddle.uk/_gNknf0D

Regards
Veem