On Thu, 15 Feb 2024 at 22:40, Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> On 2/15/24 09:00, Greg Sabino Mullane wrote: > > On Thu, Feb 15, 2024 at 11:43 AM Adrian Klaver > > <adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.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
